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 your 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
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
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
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;
Shoopify 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
Free E-Commerce Dashboard Templates
Connect Shopify to Google Sheets
Shopify Google Data Studio Connector
How to connect Google Analytics to your CRM system?
What is Marketing Attribution and how does it work?
Addressing Shopify attribution problems for subscription products