Documentation

Shopify

Suggest Edits

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 get unexpected '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 7 months ago

Whatā€™s Next

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