Freshpaint
Search…
⌃K
Links

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. 1.
    Import data from anywhere into Freshpaint
  2. 2.
    Create a SQL Scheduled Event Definition from the Event Library to generate events
  3. 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.

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. Certain column names returned by your 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.

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: