Timeline Query
You can use the Timeline Query plugin 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.
Learn more about BlueConic Timeline events and the default Timeline event types in BlueConic.
Creating a Timeline Query
To add the Timeline Query feature to your BlueConic tenant, contact your Customer Success Manager to have this plugin installed in your environment.
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 in category 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 a product in category X but did not order a product in category Y in last Z 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 DATEDIF(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 days |
Y |
Number of days will be stored in the profile; Y can be defined at the segment. |
|
All profiles that ordered a 'Women' product |
yes |
select distinct tag from order_product |
Category |
Category |
Distinct is not absolutely necessary. |
|
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 category X exceeded 500 dollars |
maybe |
select SUM(quantity) as totalOrderValue from order_event where id in (select eventId from order_product inner join order_product_tag ON order_product.parentId = order_product_tag.id where order_product_tag.name = 'X') |
Total order value |
500 |
The total order value will be stored; 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 |
Total order value |
100 |
The total order value will be stored; 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 |
|
|
|
|
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 unique products |
|
|
|
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 |
|
|
|
Last date of a specific kind of event |
yes |
select MAX(eventDate) from order_event |
|
|
|
|
All categories somebody ordered products for |
yes |
select DISTINCT(name) FROM order_product_tag |
|
|
Distinct is not absolutely necessary. |
|
Top 3 categories somebody ordered 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 |
Category and number products |
|
|
|
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.
