In this post, I’ll show you how to use this spreadsheet to compare your Google Analytics 4 purchases to your Shopify orders and find any discrepancies like missing transactions or revenue differences. It will also help you find the cause of discrepancies so you can improve your GA4’s accuracy.
Get accurate an complete ecommerce data in Google Analytics 4
Features
The spreadsheet compares order ids in Shopify to transaction ids in Google Analytics 4. By comparing each order individually, you can pinpoint exactly what’s causing any discrepancies. With it, you’ll find:
- Missing orders
- Duplicate purchases
- Revenue discrepancies
- Potential sources of issues such as:
- Payment method
- Timezones
- Taxes and shipping
- Apps
- An order link to the actual order in Shopify for further investigation
It also works whether your sending order names (#850253), order numbers (850253), or order IDs (5146079592553) as the transaction id to GA4.
Sources of Discrepancies
Here are some common reasons for discrepancies that the spreadsheet will surface.
Missing Upsells Revenue
Upsell revenue cannot be calculated correctly with a client-side GA4 integration (like Shopify's built-in integration). If you have upsells, GA4 will underreport revenue and purchases. You need a sever-side integration like our app.
Payment Methods
Apps such as Global E or Recharge can create orders in Shopify that are not sent over to Google Analytics 4, leading to missing purchases.
Subscriptions
Subscription orders that are not the first order of the subscription will not be reported in Google Analytics 4 with client-side only integrations.
Duplicate Purchases
Integrations that depend on order confirmation page views may report duplicate transactions. This is most common when you use Google Tag Manager to implement GA4, but can happen if you hardcoded GA4 on your order confirmation page.
You may also have two implementation methods (for instance, SlideRule’s app and hardcoded purchases) both sending purchase data to GA4.
How to use the Spreadsheet
You’ll need permissions to export orders from Shopify and create explorations in GA4. The spreadsheet doesn’t require any app installations or special API calls.
If it’s easier for you, follow along with the video here.
Setup
- Make a copy of the template and rename it for your store.
- Copy your store slug from your store's admin url. It's either "admin.shopify.com/store/YOUR-STORE-SLUG" or "YOUR-STORE-SLUG.myshopify.com/admin"
- Paste your slug in the designated cell of the "Store Info" tab of the template.
Add Shopify Orders
- Go to your store's admin page and select orders.
- Export your orders from Shopify by selecting "Export" and then "Orders by Date" and selecting a date range that doesn't include yesterday's data. Click "Export".
- Wait for your orders to export and then download the CSV file from the email that Shopify sends to the designated email address
- Return to the template, select the "Shopify Raw Data" tab and go to "File" and then "Import" and upload the CSV file.
- Select "Replace current sheet" and then click "Import Data."
Add the Google Analytics 4 Purchases
- Create a report in Google Analytics 4 by logging in, selecting the account you want to use, and going to "Explorations." Create a blank Exploration.
You only need to create the Exploration one time. Then you can reuse it for future comparisons.
- Import the "Transaction ID" and "Date + hour" dimensions into the palette
- Import the "Purchases", "Purchase Revenue", "Shipping Amount", "Tax Amount", and "Refunds" metrics into the palette.
- Drag the dimensions and metrics onto the canvas to create the report.
- Create a filter for transaction IDs that do not match "not set" and click "Apply."
- Select the same date range you used when exporting from Shopify and click "Apply."
- Name the Exploration "GA4 to Shopify Transaction ID Comparison" so you can easily reuse it.
- Export the data from the Google Analytics 4 report to a Google Sheet.
- Select and copy the data from the Google Sheet. Do not select the "Grand total" row or header rows, just the raw data rows.
- Go to the "GA4 Raw Data" tab of the template
- Paste the GA4 data into the sheet starting at the second row.
Find Discrepancies and Use the Results
- On the "Results" tab of the template, select how you send transaction IDs to GA4.The default Shopify plugin uses Order Names. The SlideRule Analytics app uses Order IDs.
- The template will automatically populate with data from Shopify and Google Analytics 4, allowing you to identify any discrepancies.
- Scroll to the right to see info about the orders from Shopify to help determine what might be causing discrepancies. Click on the "Order Link" cell to open the actual order in Shopify.
Conclusion
With this spreadsheet you can easily compare your Shopify orders to your GA4 purchases and uncover any discrepancies. By regularly monitoring and reconciling your data, you can ensure the accuracy of your metrics in GA4 and make data-driven decisions to optimize your online store's performance.
If your purchases or revenue in GA4 is off by more than 10%, we recommend switching to our GA4 app for Shopify.