Step 1 - Set the Query
After connecting to your warehouse, you can start syncing Events by configuring the query that Vitally will run against your warehouse on each incremental sync.
To sync Events, the query must select or alias all three of the following column names:
event: the name or type of eventtimestamp: the time the event was generated in ISO 8601 format (1970-01-01T00:00:00.000Z)message_id: a unique ID for the event
And at least one of:
user_id: the ID of the user that 'owns' the eventaccount_id: the ID of the account that 'owns' the eventorganization_id: the ID of the organization that 'owns' the event (only available if you have organization hierarchy enabled)
Incremental Sync
Incremental sync is a process of syncing warehouse data that has only been added or modified since the last sync cycle instead of processing the entire dataset each time. This approach is more efficient as it minimizes the amount of data queried and processed during each sync, making the update process quicker and resource-efficient.
Incremental sync is required for syncing Events
In order to incrementally query your data each sync, you'll need to add a clause to your query with the variable $LAST_SYNC_TIMESTAMP. We'll fill in that variable with the timestamp of the last sync each time we query the warehouse.
When previewing a query that uses $LAST_SYNC_TIMESTAMP, the preview substitutes epoch time (1970-01-01) rather than your actual last sync timestamp. This means the preview may return more rows than a live sync would. During actual syncs, the variable is replaced with the true last sync time.
Incremental syncs should compare $LAST_SYNC_TIMESTAMP with a timestamp that reflects when the data was loaded into the data warehouse, not when the event occurred. This is particularly important when data is synced to the data warehouse in batches — events that happened before the last sync but were loaded after it can be missed if you use an event-occurrence timestamp.
SELECT
'pageView' AS event,
user_id AS "user_id",
created_at AS timestamp,
id AS message_id,
page,
application
FROM page_views
WHERE created_at > $LAST_SYNC_TIMESTAMP
Adding Another Event Source
Events can be configured to sync from multiple queries, such as when events are in different tables. In order to sync an additional event query, select the "Add another event source" button at the bottom of the query list:
It is recommended that each event type (i.e., Login, File Downloaded, Page Clicked) has its own event source.
Step 2 - Configure Properties
In order to pull in additional metadata associated with the event, simply include those columns in your SELECT statement, and they will be added as event properties.
For the example query above, the following event properties will be synced:
{ "page": "...", "application": "..." }What if my properties are all in one column?
If your event properties are contained in a single nested or JSON column, and you've selected it:
SELECT
event_name AS event,
user_id AS "user_id",
created_at AS timestamp,
id AS message_id,
meta_data
FROM all_events
WHERE created_at > $LAST_SYNC_TIMESTAMP
And the responses looks like:
{
"event": "pageView",
"user_id": "2dc30ead-2e64-4d87-8395-aad4be299698",
"timestamp": "2019-11-15T16:08:59.915Z",
"message_id": "7ca60dbd-0ad0-4ef6-b049-aac3fb5e0116",
"meta_data": {
"page": "home-page",
"duration_ms": 3253
},
}Then you will want to set the optional "property field" to meta_data in order to track those values.
Step 3 - Continue Configuration
If you have other features to sync continue configuring those.
OR Step 3 - Enable Sync
FAQ
Q: Why are some events missing in Vitally but correctly appear in my data warehouse?
A: This usually happens because the column used in the WHERE clause reflects when the event occurred rather than when it was loaded into the warehouse. If your warehouse loads data in batches, events that happened before the last sync but were loaded after it can be missed. To avoid this, use a warehouse load timestamp (sometimes called inserted_at, loaded_at, or similar) in your WHERE clause instead of the event-occurrence timestamp.
Q: Why can't I see events for churned accounts?
A: Once an account is marked as churned, Vitally automatically stops tracking product events for that account. If you're sending events for a churned account, they won't appear in Vitally.