How to Find Discrepancies Between Shopify Orders & GA4 Purchases

Use our spreadsheet template to find issues with your GA4 purchase data and learn what's causing those issues.

20
 min read

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.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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

1. Make a copy of the template and rename it for your store.

Screenshot of: Make a copy of the template and rename it for your store.

2. 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"

Screenshot of: 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"

3. Paste your slug in the designated cell of the "Store Info" tab of the template.

Screenshot of: Paste your slug on the in the designated cell of the "Store Info" tab of the template.

Add Shopify Orders

4. Go to your store's admin page and select orders.

Screenshot of: Go to your store's admin page and select orders.

5. 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".

Screenshot of: 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"

6. Wait for your orders to export and then download the CSV file from the email that Shopify sends to the designated email address

Screenshot of: Wait for your orders to export and then download the CSV file from the email that Shopify sends to the email address it shows you.

7. Return to the template, select the "Shopify Raw Data" tab and go to "File" and then "Import" and upload the CSV file.

Screenshot of: Return to the template, select the "Shopify Raw Data" tab and go to "File" and then "Import" and upload the CSV file.

8. Select "Replace current sheet" and then click "Import Data."

Screenshot of: Select "Replace current sheet" and then click "Import Data."

Add the Google Analytics 4 Purchases

9. 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.

Screenshot of: 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.

10. Import the "Transaction ID" and "Date + hour" dimensions into the palette

Screenshot of: Import the "Transaction ID" and "Date + hour" dimensions into the palette

11. Import the "Purchases", "Purchase Revenue", "Shipping Amount", "Tax Amount", and "Refunds" metrics into the palette.

Screenshot of: Import the "Purchases", "Purchase Revenue", "Shipping Amount", "Tax Amount", and "Refunds" metrics into the palette.

12. Drag the dimensions and metrics onto the canvas to create the report.

Screenshot of: Drag the dimensions and metrics onto the canvas to create the report.

13. Create a filter for transaction IDs that do not match "not set" and click "Apply."

Screenshot of: Create a filter for transaction IDs that do not match "not set" and click "Apply."

14. Select the same date range you used when exporting from Shopify and click "Apply."

Screenshot of: Select the same date range you used when exporting from Shopify and click "Apply."

15. Name the Exploration "GA4 to Shopify Transaction ID Comparison" so you can easily reuse it.

Screenshot of: Name the Exploration "GA4 to Shopify Transaction ID Comparison" so you can easily reuse it.

16. Export the data from the Google Analytics 4 report to a Google Sheet.

Screenshot of: Export the data from the Google Analytics 4 report to a Google Sheet.

17. Select and copy the data from the Google Sheet. Do not select the "Grand total" row or header rows, just the raw data rows.

Screenshot of: Select and copy the data from the Google Sheet. Do not select the "Grand total" row or header rows, just the raw data rows.

18. Go to the "GA4 Raw Data" tab of the template

Screenshot of: Go to the "GA4 Raw Data" tab of the template

19. Paste the GA4 data into the sheet starting at the second row.

Screenshot of: Paste the GA4 data into the sheet starting at the second row.

Find Discrepancies and Use the Results

20. 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.

Screenshot of: 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.

21. The template will automatically populate with data from Shopify and Google Analytics 4, allowing you to identify any discrepancies.

Screenshot of: The template will automatically populate with data from Shopify and Google Analytics 4, allowing you to identify any discrepancies.

22. 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.

Screenshot of: 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.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Kevin McLaughlin

Analytics Consultant

I write the Shopify Analytics Newsletter to help Shopify stores get the most out of their data. As an analytics consultant, I’ve helped many Shopify stores derive accurate, insightful metrics from their data.

Want to receive the best tips and tools for analyzing your Shopify store? Subscribe now!

In our free weekly Shopify Analytics Newsletter, we cover how to use your store’s data to find the specific insights you need to increase your profits and make the right decisions for your business.