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.
- 1.Import data from anywhere into Freshpaint
- 2.
- 3.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 [email protected] to request access.

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.
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, ...
The events generated can either be normal events or identify events by changing the selected Event Type.

Will produce new events and send to a destination. Certain column names returned by the query have special semantics:
Column name | Type | Required? | Description |
---|---|---|---|
insert_id | text | Required | A unique identifier for the event. Freshpaint will generate one event for each distinct insert_id returned by the query. |
user_id | 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. |
time | 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. |
Will use the identify API on supported destination to update properties on an identified user.
Column name | Type | Required? | Description |
---|---|---|---|
user_id | 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. |
device_id | text | Optional | A unique identifier for the device the event was sent from. This is sometimes referred to as the anonymous_id . |
time | 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.
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:
SELECT
id AS insert_id,
customer_id AS user_id,
sku,
amount,
UNIX_TIMESTAMP(created_at) AS time
FROM orders
- 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:
SELECT
id AS insert_id,
customer_id AS user_id,
sku,
amount,
UNIX_TIMESTAMP(created_at) AS order_created_at,
UNIX_TIMESTAMP(fulfilled_at) AS time
FROM orders
WHERE fulfilled_at IS NOT NULL
- 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" event - The
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 modified 7mo ago