User Tools

Site Tools


shopify_clv_attribution

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 https://onboard.windsor.ai
  2. On 2. Data Destinations select Windsor.ai Cloud database and create two tables
    1. shopify_orders
      Connector URL:
       https://connectors.windsor.ai/shopify?api_key=[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:
       https://connectors.windsor.ai/shopify?api_key=[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
    BEGIN;
    TRUNCATE TABLE crm_conversions;
    INSERT INTO crm_conversions(net_revenue,transaction_id,crm_type)
    	SELECT
    		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;
    COMMIT;
shopify_clv_attribution.txt · Last modified: 2021/09/29 14:46 by windsor_ai