Shopify
Completed Order
Customer purchased one or more products
Type: Activity Transformation
For Snowflake - ORDER table
If you use an EL tool like Fivetran into Snowflake, you'll have an ORDER table. To update the query to work for Snowflake use
SHOPIFY."ORDER"
(notice the extra "" around the table name) otherwise you'll getunexpected 'order'.syntax error
.
SELECT
o.id
,o.processed_at
, NULL AS anonymous_customer_id
, o.email AS customer
,'completed_order' as activity
,d.code as feature_discount_code
,o.name as feature_order_name
,o.total_price as feature_order_price -- what was the total value of the order
,o.total_discounts as feature_discounts -- what value of discounts did we give the customer
,o.total_tax as feature_tax -- was tax charged?
,o.subtotal_price as revenue_impact -- the net revenue figure for the business
,NULL as link
FROM shopify.order o
LEFT JOIN shopify.order_discount_code d
ON (d.order_id = o.id)
WHERE
o.cancelled_at is NULL
and o.email is not NULL
and o.email <> ''
Canceled order
A customer's order was canceled.
Type: Activity Transformation
SELECT
o.id AS activity_id
, o.cancelled_at AS ts
, NULL AS anonymous_customer_id
, lower(o.email) AS customer
, 'canceled_order' AS activity
, o.cancel_reason AS feature_1
, d.code AS feature_2 -- discount code
, NULL AS feature_3
-- this is the merchandize_price with discounts applied
, o.subtotal_price AS revenue_impact
, NULL AS link
FROM shopify.order AS o
left outer join shopify.location l
on (l.id = o.location_id)
LEFT OUTER JOIN shopify.order_discount_code d
on (d.order_id = o.id)
WHERE o.cancelled_at is not NULL
and o.email is not NULL
and o.email <> ''
SELECT
o.id AS "activity_id"
, o.cancelled_at AS "ts"
, NULL AS "anonymous_customer_id"
, lower(o.email) AS "customer"
, 'canceled_order' AS "activity"
, o.cancel_reason AS "feature_1"
, NVL(l.province, o.shipping_address_province) AS "feature_2"
, d.code AS "feature_3"
, o.total_price - o.total_discounts AS "revenue_impact"
, NULL AS "link"
FROM shopify.order AS o
left outer join shopify.location l
on (l.id = o.location_id)
LEFT OUTER JOIN shopify.order_discount_code d
on (d.order_id = o.id)
WHERE o.cancelled_at is not NULL
and o.email is not NULL
and o.email <> ''
Purchased Product
Customer purchased a single product as part of an order
This transformation generates a single activity for each product purchased in an order and modifies the timestamp so that the product is purchased 1 second after the order. This makes it easier to relate to the Completed Order
and Purchased Product
activities within a dataset. For example, you can generate a dataset with all products and orders by choosing ALL Purchased Product
activities and appending the last before Completed Order
.
Type: Activity Transformation
SELECT
TO_HEX(MD5(ol.id || q.num)) AS activity_id -- Order line Id
, TIMESTAMP_ADD(o.created_at, INTERVAL 1 second) as ts
, NULL AS anonymous_customer_id
, o.email AS customer
, 'purchased_product' AS activity
, ol.sku as feature_1
, p.product_type AS feature_2
, p.title AS feature_3
, ol.price AS revenue_impact
, NULL AS link
FROM shopify.order_line ol
-- below we break each item purchased by quantity
join (
select row_number() over() as num from shopify.order_line limit 100
) q
on (q.num <= ol.quantity)
JOIN shopify.order AS o
on (ol.order_id = o.id)
join shopify.product p
on (ol.product_id = p.id)
WHERE o.cancelled_at is NULL
and o.email is not NULL
and o.email <> ''
SELECT
MD5(ol.id || q.num) AS "activity_id" -- Order line Id
, DATE_ADD('second', 1, o.created_at) AS "ts"
, NULL AS "anonymous_customer_id"
, o.email AS "customer"
, 'purchased_product' AS "activity"
, ol.sku as "feature_1"
, p.product_type AS "feature_2"
, p.title AS "feature_3"
, ol.price AS "revenue_impact"
, 'https://YOURCOMPANY.com/products/' || p.handle AS "link"
FROM shopify.order_line ol
-- below we break each item purchased by quantity
join (
select row_number() over() as num from shopify.order_line limit 100
) q
on (q.num <= ol.quantity)
JOIN shopify.order AS o
on (ol.order_id = o.id)
join shopify.product p
on (ol.product_id = p.id)
WHERE o.cancelled_at is NULL
Refunded Product
The customer received a refund per item
Type: Activity Transformation
SELECT
CONCAT(olr.id || q.num) AS activity_id
, r.created_at AS ts
, NULL AS anonymous_customer_id
, o.email AS customer
, 'refunded_product' AS activity
, ol.sku AS feature_1
, ol.name as feature_2
, olr.restock_type AS feature_3
, ol.price AS revenue_impact
, NULL AS link
FROM shopify.order_line_refund olr
join shopify.refund r
on (r.id = olr.refund_id)
join shopify.order_line ol
on (olr.order_line_id = ol.id)
-- below we break each item purchased by quantitiy
join (
select row_number() over() as num from shopify.order_line limit 100
) q
on (q.num <= olr.quantity)
join shopify.order o
on o.id = ol.order_id
SELECT
CONCAT(olr.id || q.num) AS activity_id
, r.created_at AS ts
, NULL AS anonymous_customer_id
, o.email AS customer
, 'refunded_product' AS activity
, ol.sku AS feature_1
, ol.name as feature_2
, olr.restock_type AS feature_3
, ol.price AS revenue_impact
, NULL AS link
FROM shopify.order_line_refund olr
join shopify.refund r
on (r.id = olr.refund_id)
join shopify.order_line ol
on (olr.order_line_id = ol.id)
-- below we break each item purchased by quantitiy
join (
select row_number() over() as num from shopify.order_line limit 100
) q
on (q.num <= olr.quantity)
join shopify.order o
on o.id = ol.order_id
Enriched Orders
Generates an enrichment table to add additional features to the Completed Order
activity.
Sometimes you need to do an analysis on the different aspects of price and that is why enriching the Completed Order
activity with all the different components of the amount charged.
Type: Enrichment Transformation
SELECT
o.id AS enriched_activity_id
, o.created_at AS enriched_ts
, o.subtotal_price as subtotal_price
, o.total_price as total_price
, o.total_tax as tax
, s.price as shipping_price
, s.title as shipping_kind
, o.name as order_name
, o.total_discounts
, o.total_weight
FROM shopify.order AS o
LEFT JOIN shopify.order_shipping_line s
on (o.id = s.order_id)
SELECT
o.id AS "enriched_activity_id"
, o.created_at AS "enriched_ts"
, o.subtotal_price as item_price
, o.total_tax as tax
, s.price as shipping_price
, s.title as shipping_kind
, o.name as order_name
, o.total_discounts
, o.total_weight
FROM shopify.order AS o
LEFT JOIN shopify.order_shipping_line s
on (o.id = s.order_id)
LEFT OUTER JOIN dw.exchange_rates e
on (e.date = o.created_at::DAte and e.from_currency = o.currency)
Started Checkout
A customer started their checkout.
Type: Activity Transformation
SELECT
o.id AS "activity_id"
, o.created_at AS "ts"
, NULL AS "anonymous_customer_id"
, lower(o.email) AS "customer"
, 'started_checkout' AS "activity"
, o.total_line_items_price AS "feature_1"
, o.shipping_address_country_code AS "feature_2"
, o.total_tax AS "feature_3"
, o.total_price - o.total_discounts AS "revenue_impact"
, NULL AS "link"
FROM shopify.checkout AS o
Shipped Product
A customer's product was shipped
Type: Activity Transformation
SELECT
f.id || ol.id AS "activity_id"
, f.created_at AS "ts"
, NULL AS "anonymous_customer_id"
, lower(o.email) AS "customer"
, 'shipped_product' AS "activity"
, p.handle AS "feature_1"
, f.tracking_company AS "feature_2"
, l.name AS "feature_3"
, NULL AS "revenue_impact"
, CASE f.tracking_company
WHEN 'FedEx' THEN 'https://www.fedex.com/apps/fedextrack/?tracknumbers=' || f.tracking_number
WHEN 'Canada Post' THEN 'https://www.canadapost.ca/trackweb/en#/search?searchFor=' || f.tracking_number
WHEN 'USPS' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || f.tracking_number
WHEN 'Stamps.com' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || f.tracking_number
WHEN 'DHL eCommerce' THEN 'https://www.dhl.com/en/express/tracking.html?brand=DHL&AWB=' || f.tracking_number
END AS "link"
FROM shopify.fulfillment f
LEFT OUTER JOIN shopify.location l
on (f.location_id = l.id)
JOIN shopify.order AS o
on (f.order_id = o.id)
LEFT OUTER JOIN shopify.order_line ol
on (ol.order_id = o.id)
LEFT OUTER JOIN shopify.product p
on (ol.product_id = p.id)
WHERE o.cancelled_at is NULL
SELECT
f.id || ol.id AS activity_id
, f.created_at AS ts
, NULL AS anonymous_customer_id
, lower(o.email) AS customer
, 'shipped_product' AS activity
, p.handle AS feature_1
, f.tracking_company AS feature_2
, l.name AS feature_3
, NULL AS revenue_impact
, CASE f.tracking_company
WHEN 'FedEx' THEN 'https://www.fedex.com/apps/fedextrack/?tracknumbers=' || f.tracking_number
WHEN 'Canada Post' THEN 'https://www.canadapost.ca/trackweb/en#/search?searchFor=' || f.tracking_number
WHEN 'USPS' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || f.tracking_number
WHEN 'Stamps.com' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || f.tracking_number
WHEN 'DHL eCommerce' THEN 'https://www.dhl.com/en/express/tracking.html?brand=DHL&AWB=' || f.tracking_number
END AS link
FROM shopify.fulfillment f
LEFT OUTER JOIN shopify.location l
on (f.location_id = l.id)
JOIN shopify.order AS o
on (f.order_id = o.id)
LEFT OUTER JOIN shopify.order_line ol
on (ol.order_id = o.id)
LEFT OUTER JOIN shopify.product p
on (ol.product_id = p.id)
WHERE o.cancelled_at is NULL
Delivered Product
A customer's product was delivered
Type: Activity Transformation
SELECT
f.id || ol.id AS "activity_id"
, f.happened_at AS "ts"
, NULL AS "anonymous_customer_id"
, lower(o.email) AS "customer"
, 'delivered_product' AS "activity"
, NVL(p.handle, ol.name) AS "feature_1"
, ff.tracking_company AS "feature_2"
, f.message AS "feature_3"
, NULL AS "revenue_impact"
, CASE ff.tracking_company
WHEN 'FedEx' THEN 'https://www.fedex.com/apps/fedextrack/?tracknumbers=' || ff.tracking_number
WHEN 'Canada Post' THEN 'https://www.canadapost.ca/trackweb/en#/search?searchFor=' || ff.tracking_number
WHEN 'USPS' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || ff.tracking_number
WHEN 'Stamps.com' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || ff.tracking_number
WHEN 'DHL eCommerce' THEN 'https://www.dhl.com/en/express/tracking.html?brand=DHL&AWB=' || ff.tracking_number
END AS "link"
FROM shopify.fulfillment_event f
JOIN shopify.fulfillment ff
on (f.fulfillment_id = ff.id)
JOIN shopify.order AS o
on (f.order_id = o.id)
LEFT OUTER JOIN shopify.order_line ol
on (ol.order_id = o.id)
LEFT OUTER JOIN shopify.product p
on (ol.product_id = p.id)
WHERE f.status = 'delivered'
SELECT
f.id || ol.id AS activity_id
, f.happened_at AS ts
, NULL AS anonymous_customer_id
, lower(o.email) AS customer
, 'delivered_product' AS activity
, coalesce(p.handle, ol.name) AS feature_1
, ff.tracking_company AS feature_2
, f.message AS feature_3
, NULL AS revenue_impact
, CASE ff.tracking_company
WHEN 'FedEx' THEN 'https://www.fedex.com/apps/fedextrack/?tracknumbers=' || ff.tracking_number
WHEN 'Canada Post' THEN 'https://www.canadapost.ca/trackweb/en#/search?searchFor=' || ff.tracking_number
WHEN 'USPS' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || ff.tracking_number
WHEN 'Stamps.com' THEN 'https://tools.usps.com/go/TrackConfirmAction?qtc_tLabels1=' || ff.tracking_number
WHEN 'DHL eCommerce' THEN 'https://www.dhl.com/en/express/tracking.html?brand=DHL&AWB=' || ff.tracking_number
END AS link
FROM shopify.fulfillment_event f
JOIN shopify.fulfillment ff
on (f.fulfillment_id = ff.id)
JOIN shopify.order AS o
on (f.order_id = o.id)
LEFT OUTER JOIN shopify.order_line ol
on (ol.order_id = o.id)
LEFT OUTER JOIN shopify.product p
on (ol.product_id = p.id)
WHERE f.status = 'delivered'
Contribute to the Narrator Community
Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.
Have questions?
Our data team is here to help! Here are a couple ways to get in touch...
š¬ Chat us from within Narrator
š Email us at [email protected]
š Or schedule a 15 minute meeting with our data team
Updated 6 months ago
Don't see what you're looking for? Browse the Activity Index for more examples...