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. Create a SQL Scheduled Event Definition from the Event Library to generate events

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 [e-mail] 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
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.

Scheduled Identify

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.

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:

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's insert_id

• The customer_id who placed the order is used as the event's user_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's insert_id. Note that while this is the same insert_id as the "Order Placed" event, Freshpaint will correctly generate both the "Order Placed" and "Order Fulfilled" event for each order because insert_ids 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 the time

• 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: