User Tools

Site Tools


Shopify CLV Attribution

To model net revenue attribution and CLV attribution with your Shopify data follow these instructions:

  1. Connect your Shopify, Google Analytics and media connectors on
  2. On 2. Data Destinations select Cloud database and create two tables
    1. shopify_orders
      Connector URL:[your API key]&date_preset=last_90d&fields=order_id,order_current_total_price,order_email,order_updated_at,order_name,date,order_tags 

      Destination table name: shopify_orders
      Schedule: 12h
      Click Add New

    2. shopify_customers
      Connector URL:[your API key]&date_preset=last_90d&fields=customer_id,date,customer_email,customer_total_spent

      Destination table name: shopify_customers
      Schedule: 12h
      Click Add New

  3. Head to SQL Lab and run the following statement
    TRUNCATE TABLE crm_conversions;
    INSERT INTO crm_conversions(net_revenue,transaction_id,crm_type)
    		sum(customers.customer_total_spent::REAL) AS net_revenue,
    		REPLACE(orders.order_name,'#','') as transaction_id,
    		'shopify - net revenue' AS crm_type
    	FROM shopify_customers AS customers
    	LEFT JOIN shopify_orders AS orders ON orders.order_email = customers.customer_email
    	GROUP BY 2,3;
shopify_clv_attribution.txt · Last modified: 2021/09/29 14:46 by windsor_ai