Skip to main content
All CollectionsOrganizing & Storing DataProfiles & Timeline EventsTimeline Events
Migrating from the Timeline Query Connection to timeline event rollups
Migrating from the Timeline Query Connection to timeline event rollups
Updated over 2 weeks ago

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)

Note: Both the connection and rollups offer Preview functionality, allowing you to validate that your queries produce the expected results for sample profiles before going live.

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:

  1. Copy the configuration from Timeline Query Connection to one or more timeline event rollups.

  2. Turn off the Timeline Query Connection.

  3. 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.

  • If you 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.

    • Recommendation: Create new profile properties for rollups - do not use the same ones you used for the Timeline Query Connection. This allows you to compare the results between rollups and the connection first and make sure your old properties are empty and don’t contain stale data. With this option, you must update all downstream connections, segments, and other uses of the old profile properties to use the new ones from timeline event rollups.

  • Your initial rollup run can take a comparable amount of time as an initial Timeline Query Connection run, as it needs to process all profiles with an event of the selected timeline event type. Subsequent updates will be faster.

    • Recommendation: For faster processing, start with rollups with timeline event types that affect fewer profiles.

Other migration recommendations

  • Consider running the old Timeline Query Connection in parallel with the new rollups for a period to validate the migration.

  • Check if a rollup has worked as expected by creating a segment on “profile property has value” <selected profile property>. Open a profile from that segment and take note of that property’s value on the “All profile properties” tab.

  • Turn on multiple rollups simultaneously, as this will prevent BlueConic from processing each profile multiple times, leading to fewer data operations.

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

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

select AVG(total_revenue) from order_event

select AVG(total_revenue) from order_event

All profiles of customers where the total order value exceeded $1,000

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

Related Articles
Using SQL mode for timeline event rollups
SQL query examples for timeline event rollups
Create timeline event rollups
FAQ: Timeline event rollup queries
Timeline Query Connection
Did this answer your question?