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

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

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 $1,000

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 of 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

All profiles that ordered in last X days

select MAX(date) as last_order_date from order_event

Last order date

The last order date will be stored in the profile. Within the segment, use condition “within last X days.”

All profiles that ordered a product with the tag 'X' in last Y days

select MAX(date) as 'last X order date'

from order_event

inner join order_product ON order_event.row_id = order_product.order_event_row_id

inner join order_product_tag ON order_product_tag.order_product_row_id = order_product.row_id

where order_product_tag.value = 'X'

Last order date

The last order date will be stored in the profile. Within the segment, use condition “within last X days.”

All profiles that ordered Z products with tag 'X', but they did not buy products with tag 'Y' in the last 10 days.

select COUNT(DISTINCT order_event.row_id) as nrProducts

from order_event

inner join order_product ON order_event.row_id = order_product.order_event_row_id

inner join order_product_tag ON order_product_tag.order_product_row_id = order_product.row_id

where DATEDIFF(Day, date, NOW()) < 10

AND order_product_tag.value = 'X'

AND (

select COUNT(row_id) as nrProducts

from order_event inner join order_product ON order_event.row_id = order_product.order_event_row_id

inner join order_product_tag ON order_product_tag.order_product_row_id = order_product.row_id

where DATEDIFF(Day, date, NOW()) < 10 AND order_product_tag.value = 'Y') = 0".

Number of products ordered

Number of products ordered will be stored in the profile; Z can be defined at the segment.

All profiles of customers that ordered at least twice in the last 1 year

select COUNT(row_id)
from order_event
where DATEDIFF(Year, date, NOW()) < 1

Number of orders

The number of orders will be stored in the profile; "twice" can be defined at the segment.

All product tags that someone ordered products for

select order_product_tag.value
from order_event
left join order_product on order_product.order_event_row_id = order_event.row_id
left join order_product_tag on order_product_tag.order_product_row_id = order_product.row_id
WHERE DATEDIFF(Month, date, NOW()) < 1

All unique tags