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
   

    TRUNCATE TABLE crm_conversions;
   INSERT INTO crm_conversions(net_revenue,transaction_id,crm_type)
   SELECT DISTINCT 
   sum(customers.customer_total_spent::REAL)  as net_revenue,
   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;