Knowledge Base

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;