Timeline Query Connection
What:You can use the Timeline Query connection to run queries against BlueConic Timeline events, such as transactions, order events, or email events. You can store the query results in a profile property, which you can then use for segmentation, targeting, reporting, data activation, and more.
About BlueConic: The BlueConic customer data platform harnesses the data required to power the recognition of an individual at each interaction, and then synchronizes their intent across the marketing ecosystem.
Why: A BlueConic Timeline stores time-based data on events that occur for a profile, such as product orders, page views, clicks, etc. You can use BlueConic Timeline events to capture information about timing and sequence of events for a BlueConic profile. A Timeline belongs to a profile and includes the set of all events belonging together for that profile.
Note: To add the Timeline Query feature to your BlueConic tenant, contact your Customer Success Manager to have this plugin installed in your environment.
Watch the video: Timeline Query Connection
Creating a Timeline Query
Once Timeline Query is added to your environment, you can follow the steps below to create a query and configure the details.
- Contact your BlueConic Customer Success Manager to add the Timeline Query plugin to your BlueConic environment.
- Click Connections in the BlueConic navigation bar.
- Click Add Connection.
- A pop-up window appears. Enter "Timeline Query" in the Search bar.
- Click Timeline Query.
The Timeline Query configuration page opens. You can expand or collapse the metadata fields (favorite, labels, and description) by clicking the gray chevron at the top of the page. - Enter a name for your query at the top of the page. Next you'll configure and run the query.
Using Timeline Query goals
Within each Timeline Query you use query goals to add specifics and write queries. You create query goals using the Add goal menu in the left-hand panel of the Timeline Query page.
You can use goals to group a set of queries together, for example to create a set of queries for a marketing campaign. When the campaign is finished, you can delete that goal and create new ones.
Configuring Timeline Query goals
- In the left-hand panel, click "Update profiles with timeline data" to open the configuration page. Select a segment to run queries against the BlueConic Timelines for customer profiles in this segment.
- In this step you define your query. There are two options: Basic or SQL (for writing more complicated queries, using wildcards, etc.).
Click the Add query button and select the Basic query builder to construct a SQL query using a series of dropdown selections. These queries store the results from the Timeline event field on the left into a BlueConic profile property on the right.
This example selects Timeline events of the event type Order, and sums or adds the product quantity field of the order type when the revenue exceeds $200. It stores the results in a profile property named Total Orders Q2, which can be used to create segments, for example. (You can also use this option to see how your choices in the Basic query builder look in SQL.)
If you choose SQL in the dropdown menu, you can write a full SQL query. The second query shown here sums the revenue from the order event in the Total Revenue timeline event and stores it in a profile property named Tot revenue.
Tip: Use the Preview results icon to see a preview of your query results.
- In the Reporting step, you can preview your query results.
Sample SQL queries for BlueConic Timeline events
When you store transactions, orders, and event data in BlueConic Timeline events you can use the default Timeline event types. The following table shows some common SQL queries you can use with the default Timeline events.
|
Request |
Basic |
Query |
Stored |
Filter on segment? |
Comments |
|
All profiles that ordered in last X days |
no |
select DATEDIFF(Day, MAX(eventDate), NOW()) as nrDays from order_event |
Number of days |
X |
Number of days will be stored in the profile; X can be defined at the segment. |
|
All profiles that ordered a product with the tag 'X' in last Y days |
no |
select DATEDIFF(Day, MAX(eventDate), NOW()) as nrDays from order_event inner join order_product ON order_event.id = order_product.eventId inner join order_product_tag ON order_product_tag.parentId = order_product.id where order_product_tag.name = 'X' |
Number of days |
Y |
Number of days will be stored in the profile; Y can be defined at the segment. |
|
All profiles that ordered Z products with tag 'X', but they did not buy products with tag 'Y' in the last 10 days. |
no |
select COUNT(*) as nrProducts from order_event inner join order_product ON order_event.id = order_product.eventId inner join order_product_tag ON order_product_tag.parentId = order_product.id where DATEDIFF(Day, eventDate, NOW()) < 10 AND order_product_tag.name = 'X' AND (select COUNT(*) as nrProducts from order_event inner join order_product ON order_event.id = order_product.eventId inner join order_product_tag ON order_product_tag.parentId = order_product.id where DATEDIFF(Day, eventDate, NOW()) < 10 AND order_product_tag.name = 'Y') = 0 |
Number of products ordered |
Z |
Number of products ordered will be stored in the profile; Z can be defined at the segment. |
|
All profiles that placed an order for a product with the tag 'Women' |
yes |
select distinct tag from order_product |
Order tag |
Women |
Order tag will be stored in the profile; tag ‘Women’ can be defined at the segment. |
|
All profiles of customers that ordered at least twice in the last 1 year |
no |
select COUNT(id) from order_event where DATEDIFF(Year, eventDate, NOW()) < 1 |
Number of orders |
twice |
The number of orders will be stored in the profile; "twice" can be defined at the segment. |
|
All profiles of customers where the total order value for products with tag 'X' exceeded 500 dollars |
maybe |
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') |
Total order value |
500 |
The total order value will be stored in the profile; 500 can be defined at the segment. |
|
All profiles of customers where the average order value exceeded 100 dollars |
yes |
select AVG(total_revenue) from order_event |
Average order value |
100 |
The average order value will be stored in the profile; 100 can be defined at the segment. |
|
All profiles of customers where the total order value exceeded 1000 dollars |
yes |
select SUM(total_revenue) from order_event |
Total order value |
1000 |
The total order value will be stored in the profile; 1000 can be defined at the segment. |
|
All profiles that clicked an email in period X - Y |
no |
select count(*) as nrClicked from email_link_clicked_event where eventDate > DATE('2019-12-1') AND eventDate < DATE('2020-1-1') |
Number of emails clicked |
> 0 |
Number of clicked emails will be stored in the profile; > 0 can be defined at the segment. |
|
All profiles that received an email in period X - Y and did not click an email in period X - Y |
no |
select count(*) from email_delivered_event where eventDate > DATE('2019-12-1') AND eventDate < DATE('2020-1-1') and email_id not in (select email_id from email_link_clicked_event where eventDate > DATE('2019-12-1') AND eventDate < DATE('2020-1-1')) |
Number of emails delivered |
> 0 |
|
|
Number of products purchased in the profile lifetime |
not yet |
select count(distinct(sku)) from order_product |
Number of unique products |
|
|
|
Number of products purchased in last A months |
no |
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 |
Number of products purchased in the last month |
|
|
|
Number of days between last 2 orders |
no |
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 |
Number of days |
|
|
|
Date of last purchase |
yes |
select MAX(eventDate) from order_event |
Date of last order |
|
|
|
All product tags that someone ordered products for |
yes |
select DISTINCT(name) FROM order_product_tag |
All unique tags |
|
Distinct is not absolutely necessary. |
|
Top 3 tags that a customer bought products for |
no |
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 |
Tags and the number products bought |
|
|
|
Total transaction value from confirmed orders |
yes |
select SUM(total_revenue) from order_event |
|
|
|
|
Average transaction value from confirmed orders |
yes |
select AVG(total_revenue) from order_event |
|
|
|
Note that if you have customized the default Timeline events, or created your own custom events, you would need to write custom SQL queries against your Timeline event fields.
Running a Timeline Query
Click Run and results in the left-hand panel of the Timeline Query page to schedule and run your queries. Once you've saved your configuration and turned the configuration page On, you can choose to schedule the query (cog wheel icon) or click the Run now button.
For example, you might choose to run your query each weekday night at 3:00 AM, as shown here:
See Scheduling connections for details on scheduling and running your queries.
Privacy management
Timeline queries are run as BlueConic Connections, that can be added to BlueConic Objectives, which manage the privacy and consent management of the information that is being picked up. A connection will only process the profiles of customers or visitors who have consented to at least one of the objectives the connection is linked to.
