With the Timeline event rollup feature, BlueConic enables you to query timeline events and store the results in profile properties for activation or export.
Using the SQL query builder to write queries in AlaSQL
For advanced use cases, BlueConic developers can write SQL queries directly in AlaSQL using the built-in SQL editor. This uses AlaSQL (version 4.5.1), and its syntax differs from other SQL dialects. For example, column names containing spaces are enclosed in square brackets [] or backquotes `, rather than double quotes "". For more details, review this AlaSQL page.
Example queries
Request | Query | Stored | Comments |
All profiles that placed an order for a product with the tag 'Women' | 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` | Order tag | Order tag will be stored in the profile; tag ‘Women’ can be defined at the segment. |
All profiles of customers where the total order value for products with tag 'X' exceeded 500 dollars | 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') | Total order value | 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 | select AVG(total_revenue) from order_event | Average order value | 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 | select SUM(total_revenue) from order_event | Total order value | 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 | select count(row_id) as nrClicked
from email_link_clicked_event
where date > DATE('2022-12-1')
AND date < DATE('2023-1-1') | Number of emails clicked | 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 | 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 emails delivered | |
Number of products purchased in the profile lifetime | 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 unique products | |
Number of products purchased in last A months | 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 products purchased in the last month | |
Number of days between last 2 orders | 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 | Number of days | |
Date of last purchase | select MAX(eventDate) from order_event | Date of last order | |
Top 3 tags that a customer bought products for | 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 | Tags and the number products bought | |
Joining multiple event types
In SQL mode, it is possible to join multiple event types, as long as there is a linking identifier. | SELECT
COUNT(order_event.row_id) AS Number_of_unrefunded_orders
FROM
order_event
WHERE order_event.order_id not in (
select order_refund_event.source_order_id from order_refund_event
) | Count of the number of orders that aren’t partially refunded | A sub-query is used so that an order that has multiple related refund events aren’t counted as duplicates. |
Like | SELECT
COUNT(order_event.row_id) AS Number_of_Iphone_orders
FROM
order_event
WHERE
order_event.row_id IN (
SELECT DISTINCT
order_product.order_event_row_id
FROM
order_product
WHERE
LOWER(order_product.name) LIKE ('%iphone%')
) | Count of the number of iPhones ordered | The exact model (iPhone 14, iPhone 15, etc.) doesn’t matter. |
Top event | SELECT
TOP(1) order_product.name AS Last_ordered_product_name
FROM
order_event
LEFT JOIN order_product ON order_event.row_id = order_product.order_event_row_id
ORDER BY order_event.date DESC | Name of the latest ordered product |