SQL Scheduled Event Definitions
Activate your data by generating events with a SQL query
Your website is only one way that customers interact with your business. To be able to see the complete customer journey, you need to harness data from many different places.
Import data from anywhere into Freshpaint
Create a SQL Scheduled Event Definition from the Event Library to generate events
See the complete customer journey in any of our supported server-side destinations
SQL Scheduled Event Definitions are part of the Freshpaint SQL add-on. Contact support@freshpaint.io to request access.
How it works
Freshpaint will run your SQL query every hour. Each row that the query returns becomes an event in Freshpaint.
The query will be executed for each environment in the project. Ensure that the data that you're querying has been imported into the correct environment.
Just like other Freshpaint event definitions, you can pick which of your configured destinations to send the events to. You'll need to make sure you've set up your destination first.
Writing the query
Use any ANSI SQL language to write the scheduled event definition query. The query must return required columns as listed below to generate events.
Any additional columns returned by the query will be included as additional event properties. The name of the property will be the same as the name of the column. You can use SELECT <column value> AS <column name>
syntax to specify an exact name for a column.
In order to generate a boolean (true/false) event property, you may need to use
:> bool
to ensure that the SQL query returns a boolean-typed column. For example, you could generate a property indicating whether a promo code was used when placing an order:SELECT ..., (promo_code IS NOT NULL) :> bool AS promo_code_used, ...
Event Type
The events generated can either be normal events or identify events by changing the selected Event Type.
Scheduled Event
Will produce new events and send to a destination. Certain column names returned by the query have special semantics:
Column name | Type | Required? | Description |
---|---|---|---|
| text | Required | A unique identifier for the event. Freshpaint will generate one event for each distinct |
| text | Required | An identifier that uniquely identifies the user that performed the event. Destinations may use this to link this event with other data about the user. |
| number | Optional | The time the event occurred, represented as the number of seconds after January 1st 1970. By default the event is assumed to have occurred at the time it's first returned by the query. |
Scheduled Identify
Will use the identify API on supported destination to update properties on an identified user.
Column name | Type | Required? | Description |
---|---|---|---|
| text | Required | An identifier that uniquely identifies the user that is to be updated. Will be treated as the distinct_id and passed to destinations as the $user_id event property. |
| text | Optional | A unique identifier for the device the event was sent from. This is sometimes referred to as the |
| number | Optional | The time the event occurred, represented as the number of seconds after January 1st 1970. By default the event is assumed to have occurred at the time it's first returned by the query. |
Identify calls will be suppressed and not sent to a destination if the SQL query returns the same data for a particular user_id.
Example
Suppose there is a table imported into Freshpaint called orders
. It contains a row for each order placed with your business by one of your customers. It has columns id
, customer_id
, sku
, amount
, and created_at
.
Let's create a SQL Scheduled Event Definition to generate an event each time a customer places an order. The event could be called "Order Placed" and the query would look like this:
The order's
id
is used as the event'sinsert_id
The
customer_id
who placed the order is used as the event'suser_id
Each order comes with a timestamp when it was created, and that can be used as the
time
for the Order Placed event.
Suppose that the orders table also has a fulfilled_at
timestamp. When the order is placed, fulfilled_at
is NULL
. Then, when the item is shipped, fulfilled_at
is updated with the timestamp that the shipment was sent.
We can create a second event definition using the orders table. The event could be called "Order Fulfilled" and the query would look like this:
The order's
id
is again used as the event'sinsert_id
. Note that while this is the sameinsert_id
as the "Order Placed" event, Freshpaint will correctly generate both the "Order Placed" and "Order Fulfilled" event for each order becauseinsert_id
s are scoped to the specific event definition.The
fulfilled_at
timestamp is used as the time for the "Order Fulfilled" eventThe
created_at
timestamp is sent as a custom property instead of thetime
The
WHERE fulfilled_at IS NOT NULL
clause ensures that Order Fulfilled events will only be generated for orders that have been fulfilled
Freshpaint will run the queries hourly so that new orders and shipped orders are promptly added to your analysis. If both event definitions are configured to send to Mixpanel, here's what the data might look like:
Last updated