With the introduction of the SQL editor for timeline event rollups (More > Timeline events > Timeline event rollups), your organization can now pursue more advanced use cases that were previously achievable only through the Timeline Query Connection.
Unlike the Timeline Query Connection, the SQL editor in timeline event rollups operates in near real time and eliminates the need for scheduling run times, reducing unnecessary data operations.
Even if you've been using the Timeline Query Connection for some time, we encourage you to use timeline event rollups to ensure better usability and performance. BlueConic will continue to add functionality and improvements to the timeline event rollups feature over time, making this transition beneficial for your long-term business goals.
Important note: Because of certain naming conventions, the SQL in the Timeline Query Connection is slightly different in timeline event rollups. As a result, you cannot simply copy and paste SQL queries from your connection instance to a rollup.
Differences between the Timeline Query Connection and Timeline event rollups
Here are two sample scenarios that illustrate the differences between the Timeline Query Connection and timeline event rollups.
Faster processing, less data operations
Scenario: order events
The tenant contains 1 million profiles.
We look at data operations for a timespan of 30 days.
On average, each profile places an order every other 30 days (so 1 million order events added).
Timeline Query Connection | Timeline event rollups |
Segment: All visitors
No date property / Event date optimization
Run schedule: each day | No extra configuration required |
Rollup runs each day for 1M profiles | Rollup runs each time an order is placed (1M order events) |
Result: 30M runs | Result: 1M runs |
Data operations = 30M profile reads (x4) + 30M timeline event reads (x4) + 1M profile writes (x1) | Data operations = 1M profile reads (x4) + 1M timeline event reads (x4) + 1M profile writes (x1) = 9M |
241M data operations | 9M data operations (96% less) |
Mind the data—Data is rolled up as it rolls in
Scenario: page views
The tenant contains 1 million profiles.
We look at data operations for a timespan of 30 days
On average, each profile has 3 page view sessions per day (90 sessions per profile; 90 million page view event sessions).
Timeline Query Connection | Timeline event rollups |
Segment: All visitors
No date property / Event date optimization | No extra configuration required |
Segment: All visitors
No date property / event date optimization
Run schedule: each day | No extra configuration required |
Rollup runs each day for 1M profiles
Result: 30M runs | Rollup runs each page view session (90M)
Result: 90M runs |
Data operations = 30M profile reads (x4) + 30M timeline event reads (x4) + 30M profile writes (x1) | Data operations = 90M profile reads (x4) + 90M timeline event reads (x4) + 90M profile writes (x1) |
270M data operations | 810M data operations (3x more) |
Migration steps
Before altering your queries, as outlined in the next section, it’s important to understand how you would migrate from the Timeline Query Connection to timeline event rollups. Here are the steps:
Copy the configuration from Timeline Query Connection to one or more timeline event rollups.
Turn off the Timeline Query Connection.
Turn on the rollup.
As you review these steps and plan your migration, keep the following things in mind:
One Timeline Query Connection instance can have multiple queries. One rollup consists of one query.
You can use the same profile property that the Timeline Query Connection uses. The rollup will overwrite the profile property values. If the profile doesn’t have any eligible timeline events, it will not run the query and will not clear the profile property.
Altering your queries for rollups
Now that you better understand the differences between the Timeline Query Connection and the rollups feature, review the information below, which outlines how to alter your existing connection queries to make them work in timeline event rollups.
Description | Timeline Query Connection | Timeline event rollup |
The default event property date (denoted with Property ID date in the timeline event type) | eventDate (e.g. order_event.eventDate) | date (e.g. order_event.date) |
The identifier for the timeline event type | eventID (e.g. order_product.eventID) | <event type name>_row_id (recommended)
For example: order_product.order_event_row_id = order_event.row_id
or
<event type name>_id
For example: order_product.order_event_id = order_event.id |
The identifier of the parent property (in case of a multi-value property) | parentID
order_product_tag.parentId = order_product.id | <parent name>_row_id (recommended)
For example:
`order_product` LEFT JOIN `order_product_category` ON `order_product`.`row_id` = `order_product_category`.`order_product_row_id`
or
<parent name>_id
For example
`order_product` LEFT JOIN `order_product_category` ON `order_product`.`id` = `order_product_category`.`order_product_id` |
A value of a multi-value property | name
For example order_product_tag.name | value
for example order_product_tag.value |
Count the number of events | SELECT COUNT(*) | Count(*) is not allowed.
SELECT COUNT(order_event.row_id) |
Query on only a nested/multi-value property | SELECT count(distinct(sku))
FROM order_product | You always need to join a main timeline event type table
SELECT count(distinct(sku))
FROM order_product
LEFT JOIN order_event ON order_product.order_event_id = order_event.id |
Using multiple functions, a subquery, static value, or a case statement requires an alias | SELECT DATEDIFF(Day, MIN(eventDate), MAX(eventDate))
FROM order_event
WHERE id IN (
select top 2 id
from order_event
order by eventDate desc
)
AND (select COUNT(*) from order_event) > 1 | SELECT DATEDIFF(Day, MIN(date), MAX(date)) AS `Number of days between the last two orders`
FROM order_event
WHERE id IN (
SELECT top 2 id
FROM order_event
ORDER BY date DESC
)
AND (select COUNT(*) from order_event) > 1 |
Advanced SQL query examples
These additional examples are based on the sample SQL queries found in the Timeline Query Connection article and illustrate how the Timeline event rollup queries operate.
Request | Connection Query | Timeline Event Rollup Query |
All profiles that placed an order for a product with the tag 'Women' | select distinct tag from order_product | SELECT
`order_product_tag`.`value` AS `all_tags`
FROM
`order_event`
LEFT JOIN `order_product` ON `order_event`.`row_id` = `order_product`.`order_event_row_id`
LEFT JOIN `order_product_tag` ON `order_product`.`row_id` = `order_product_tag`.`order_product_row_id` |
All profiles of customers where the total order value for products with tag 'X' exceeded 500 dollars | select SUM(total_revenue) as totalOrderValue from order_event where id in (select eventId from order_product inner join order_product_tag ON order_product.id = order_product_tag.parentId where order_product_tag.name = 'X') | select SUM(total_revenue) as totalOrderValue
from order_event
where row_id in (
select order_product.order_event_row_id
from order_product
inner join order_product_tag ON order_product.row_id = order_product_tag.order_product_row_id
where order_product_tag.value = 'X') |
All profiles of customers where the average order value exceeded 100 dollars | select AVG(total_revenue) from order_event | select AVG(total_revenue) from order_event |
All profiles of customers where the total order value exceeded 1000 dollars | select SUM(total_revenue) from order_event | select SUM(total_revenue) from order_event |
All profiles that clicked an email in period X - Y | select count(*) as nrClicked from email_link_clicked_event where eventDate > DATE('2022-12-1') AND eventDate < DATE('2023-1-1') | select count(row_id) as nrClicked
from email_link_clicked_event
where date > DATE('2022-12-1')
AND date < DATE('2023-1-1') |
All profiles that received an email in period X - Y and did not click an email in period X - Y | select count(*) from email_delivered_event where eventDate > DATE('2022-12-1') AND eventDate < DATE('2023-1-1') and email_id not in (select email_id from email_link_clicked_event where eventDate > DATE('2022-12-1') AND eventDate < DATE('2023-1-1')) | select count(email_delivered_event.row_id)
from email_delivered_event
where date > DATE('2022-12-1')
AND date < DATE('2023-1-1')
and email_id not in (
select email_id
from email_link_clicked_event
where date > DATE('2022-12-1')
AND date < DATE('2023-1-1')
) |
Number of products purchased in the profile lifetime | select count(distinct(sku)) from order_product | select count(distinct(sku)) `Number of unique products`
from order_event
left join order_product on order_product.order_event_id = order_event.id |
Number of products purchased in last A months | select count(distinct(order_product.sku)) from order_event inner join order_product ON order_event.id = order_product.eventId WHERE DATEDIFF(Month, eventDate, NOW()) < 1 | select count(distinct(sku)) `Number of unique SKUs`
from order_event
left join order_product on order_product.order_event_id = order_event.id
WHERE DATEDIFF(Month, date, NOW()) < 1 |
Number of days between last 2 orders | select DATEDIFF(Day, MIN(eventDate), MAX(eventDate)) from order_event WHERE id IN (select top 2 id from order_event order by eventDate desc) AND (select COUNT(*) from order_event) > 1 | select DATEDIFF(Day, MIN(date), MAX(date)) `Number of days between the last two orders`
from order_event
WHERE
id IN (
select top 2 id
from order_event
order by date desc
)
AND (select COUNT(*) from order_event) > 1 |
Date of last purchase | select MAX(eventDate) from order_event | select MAX(eventDate) from order_event |
Top 3 tags that a customer bought products for | select top 3 order_product_tag.name, count(*) as myCount from order_event inner join order_product on order_product.eventId = order_event.id inner join order_product_tag on order_product_tag.parentId = order_product.id group by order_product_tag.name order by count(*) desc | select top 3 order_product_tag.value,
count(order_product.row_id) as myCount
from order_event
inner join order_product on order_product.order_event_row_id = order_event.row_id
inner join order_product_tag on order_product_tag.order_product_row_id = order_product.row_id
group by order_product_tag.value
order by count(*) desc |