Narrator Docs

Completed Order

Customer purchased one or more products

Type: Activity Transformation

SELECT
     o.id AS activity_id

     , o.processed_at AS ts

     , NULL     AS source
     , NULL     AS source_id

     , o.email  AS customer

     , 'completed_order' AS activity

     , d.code   AS feature_1 -- discount code
     , o.name   AS feature_2 -- order name
     , NULL     AS feature_3

     , (o.total_price - o.total_discounts) AS revenue_impact

     , NULL  AS link

FROM shopify.order AS 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 <> ''



Cancelled order

A customer's order was cancelled.


Type: Activity Transformation

SELECT
     o.id AS "activity_id"
     , o.cancelled_at AS "ts"
     
     , NULL  AS "source"
     , NULL  AS "source_id"
     
     , lower(o.email) AS "customer"
     
     , 'cancelled_ordered' 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



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
     MD5(ol.id || q.num) AS activity_id -- Order line Id
     , TIMESTAMP_ADD(o.created_at, INTERVAL 1 second) as ts
     
     , NULL  AS source
     , NULL  AS source_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 quantitiy
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
SELECT
     MD5(ol.id || q.num) AS "activity_id" -- Order line Id
     , DATE_ADD('second', 1, o.created_at) AS "ts"
     
     , NULL  AS "source"
     , NULL  AS "source_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 quantitiy
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



Returned 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 source
     , NULL  AS source_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 source
     , NULL  AS source_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 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 shopify2.order AS o
LEFT JOIN shopify2.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 "source"
     , NULL  AS "source_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 "source"
     , NULL  AS "source_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 shopify2.fulfillment f
LEFT OUTER JOIN shopify.location l 
    on (f.location_id = l.id)
JOIN shopify2.order AS o
    on (f.order_id = o.id)
LEFT OUTER JOIN shopify2.order_line ol 
    on (ol.order_id = o.id)
LEFT OUTER JOIN shopify2.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 "source"
     , NULL  AS "source_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 source
     , NULL  AS source_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-min meeting with our data team

Updated 11 days ago


What's Next

Don't see what you're looking for? Browse the Activity Index for more examples...

Activity Index

Shopify


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.