Help Center

Timeline Query Connection

How to use the BlueConic Timeline Query Connection to add/aggregate values from multiple timeline event types?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 BlueConic Support to have this plugin installed in your environment.

Learn more about Connections

BlueConic University Logo.svgSee the Introduction to Connections video in the BlueConic University.

Creating a Timeline Query Connection

Once the Timeline Query plugin has been added to your environment, you can follow the steps below to create a query for data rollups 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 chevron at the top of the page.
  6. Enter a name for your query at the top of the page. Next you'll configure and run the query.

Using Timeline Query connection 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 a 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 the BlueConic Timeline to calculate events, orders, transactions, total revenue, and amount spent
    If you choose SQL in the dropdown menu, you can write a full SQL query (for example, to use multiple Timeline event types in a 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
    How to calculate total revenue or total amount customers spent using the BlueConic CDP
  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 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('2022-12-1') AND eventDate < DATE('2023-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('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'))

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:

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.

 

Was this article helpful?
3 out of 3 found this helpful