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.

  1. Contact your BlueConic Customer Success Manager to add the Timeline Query plugin to your BlueConic environment.
  2. Click Connections in the BlueConic navigation bar.
  3. Click Add Connection.
  4. A pop-up window appears. Enter "Timeline Query" in the Search bar.
  5. 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 + or -.
  6. 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. 

How to create queries against BlueConic timelines, events, orders, and transactions, to store order data in profile properites

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

  1. 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. 
  2. 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.) 
    How to write SQL queries against BlueConic Timeline events, orders, and transactions
    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.
    How to preview SQL query results in BlueConic
    Preview-your-Timeline-query-results-window.png
  3. 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:

How to run a Timeline Query in BlueConic

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.