Shopify CLV & Net Revenue Attribution (Step-by-Step)

Shopify attribution is a tricky topic. Often times Google Analytics can have big discrepancies to your eCommerce store data in Shopify. Your Shopify store data is likely the single source of truth for net revenue figures, customer counts, and customer lifetime value (CLV). Connecting this data with Google Analytics allows you to implement Shopify attribution which helps you to understand how marketing contributes to:

Subscription acquisition (Subscription D2C)
If you are selling your product as a subscription it’s important to understand (a) which marketing activities acquire customers and (b) which marketing activities bring the whales (customers with a high customer lifetime value).
New customer acquisition
Understanding how marketing impacts new customer acquisition helps you to understand where to spend less on advertising on existing customers.
Net margin
If you run an E-commerce store with 100s or 1000s of SKUs understanding net margin impact helps you to optimize your campaigns towards higher net margin contribution.
Net revenue
Depending on your industry or product it can happen that you have issues with returns. In this case a net revenue model will help you to understand which campaigns have high cancellations and spend less on them.

The goal of this how to post is to give you hands on samples and inspiration for joining your Shopify and Analytics data together to make net revenue and net margin adjusted decisions in your marketing efforts.

Shopify and Google Analytics

In this example, you’ll learn how to connect Google Analytics and Shopify. You’ll need access to Google Analytics and Shopify.

Step 1: Connecting your data

Connecting google analytics and shopify attribution: Shopify CLV

Create a Windsor.ai account and connect Google Analytics, Shopify and your advertising data.

Step 2: Exporting the data

In onboard.windsor.ai go to Step 2. Select Destination and click on Multitouch Attribution Dashboard (ROAS). Now the data from your ad platforms and Google Analytics is imported. Next we will need to join in your Shopify data.

Shopify

Next you click on Windsor.ai Cloud Database and create two tables

Shopify orders

Shopify CLV

Connector URL: https://connectors.windsor.ai/shopify?api_key=[your API key]&fields=date,order_name,order_email,order_net_sales

Destination table name: custom_crm_shopify_orders

Schedule: 24h

Columns to match: date,order_name

Shopify customers

shopify attribution configuration 2

Connector URL: https://connectors.windsor.ai/shopify?api_key=[your API key]&fields=date,customer_date,customer_email,customer_last_order_id,customer_last_order_name,customer_orders_count,customer_tags,customer_total_spent

Destination table name: custom_crm_shopify_customers

Schedule: 24h

Columns to match: date,customer_email

Step 3: Joining the data

Now we’re ready to analyse our data and drill down and getting started with the Shopify attribution topic. Here a few useful queries you can run now to analyse your customer journeys and run customer lifetime value attribution, net revenue attribution and net margin attribution.

The join of the Google Analytics data is done in the following way

GA Transaction ID = Shopify Order Name

It’s important that you check that they match.

We’re using SQL lab for our queries.

Analysing customer journeys

Net revenue journeys

Let’s start with the basics: The acquisition journeys. As a first exercise we’re joining the journeys from the conversions table (source, medium, campaign dimensions from Google Analytics) with the Shopify Net revenue.

To join your own data run the query below in SQL lab.

SELECT 
    transaction_id,  
    TO_DATE(custom_crm_shopify_orders.date, 'YYYY-MM-DD')::date as date,
    order_email, 
    net_revenue,
    sourcepath,
    mediumpath,
    campaignpath,
    dayslag      
FROM 
    conversions, 
    crm_conversions, 
    custom_crm_shopify_orders  
WHERE 
    crm_conversions.transaction_id = conversions.transactionid 
    AND transaction_id = order_name 
ORDER BY custom_crm_shopify_orders.date DESC;

This will help us to visualise the customer journeys for each transaction and give you more context in the next step where we look at Shopify CLV attribution.

Tip: Click on Explore to create a chart which you can add to any dashboard.

Journeys with CLV

Now we can modify this so we can get the acquisition journey of each user by running a SELECT DISTINCT ON (customers.customer_email) to understand the acquisition journey of each customer and then join it with the sum total of the order net sales of all the Shopify orders of that customer:

SELECT 
  DISTINCT ON (customers.customer_email)
    orders.date::date AS order_date,
    SUM(orders.order_net_sales::REAL) AS clv_revenue,
    customers.customer_email,
    conversions.sourcepath,
    conversions.mediumpath,
    conversions.campaignpath
FROM custom_crm_shopify_customers AS customers
LEFT JOIN custom_crm_shopify_orders AS orders ON orders.order_email = customers.customer_email
LEFT JOIN conversions ON orders.order_name = conversions.transactionid
WHERE conversions.sourcepath IS NOT NULL
GROUP BY 1,3,4,5,6
ORDER BY customer_email,order_date ASC;

Shopify CLV attribution and Shopify CAC attribution

In the previous paragraph, we covered the basics on how the tables are joined together. As next step we’re automating the data pipelines so that marketing costs are joined together with the Google Analytics transactions and the Shopify orders.

The conversions table contains all the standard Google Analytics conversion events. Now lets look at the crm_conversions table, where we need to insert the Shopify net revenue and CLV data.

BEGIN;
TRUNCATE TABLE crm_conversions;
INSERT INTO crm_conversions(datasource,net_revenue,transaction_id,crm_type)
WITH crm_data as (
    SELECT DISTINCT on (customers.customer_email)
    'Shopify Customers' as datasource,
    min(orders.date::date) as order_date,
    customers.customer_email,
    SUM(orders.order_net_sales::REAL) AS net_revenue,
    orders.order_name as transaction_id,
    'Shopify - CLV revenue' AS crm_type
  FROM custom_crm_shopify_customers AS customers
  LEFT JOIN custom_crm_shopify_orders AS orders ON orders.order_email = customers.customer_email
  LEFT JOIN conversions ON orders.order_name = conversions.transactionid
  WHERE customers.customer_orders_count::REAL > 0 and orders.date::date IS NOT NULL
  GROUP BY 1,3,5,6)
SELECT datasource,net_revenue,transaction_id,crm_type from crm_data
;
INSERT INTO crm_conversions(datasource,net_revenue,transaction_id,crm_type)
SELECT 
  'Shopify Orders' AS datasource,
	sum(order_net_sales::REAL) AS net_revenue,
	order_name as transaction_id,
	'Shopify - Net Revenue' AS crm_type
	FROM custom_crm_shopify_orders
	GROUP BY 1,3,4
;
COMMIT;

This query inserts a row into the crm_conversions table for

Each transaction recorded in Shopify with the net revenue
Each customer and her/his first transaction and the customer lifetime value (calculated as sum of the net sales of all transactions belonging to this customer)

Now that this data is present in the crm_conversions table, you can start working with your Shopify CLV attribution data in your windsor.ai dashboards.

Wrapping it up/TLDR

If you have made it all the way here, you should now know how to join your data from Shopify with Google Analytics. To join other Shopify objects such as inventory fields to calculate to net margin you can easily do this by joining the data from the order object with the inventory fields. The same principle applies for platform other than Shopify. The prerequisite is that you have a join key to connect the analytics to the CRM data.

Further reading

Best Shopify Report Templates

Free E-Commerce Dashboard Templates

Connect Shopify to Excel

Connect Shopify to Tableau

Connect Shopify to Power BI

Connect Shopify to Google Sheets

Connect Shopify to Snowflake

Shopify Google Data Studio Connector

Shopify BigQuery Integration

How to connect Google Analytics to your CRM system?

What is Marketing Attribution and how does it work?