How to Create a Reconciliation Report in Google Analytics

Ever pull a sales report from Google Analytics only to find it doesn't match the numbers in your Shopify dashboard? This kind of data puzzle is incredibly common and often leaves marketers and business owners wondering which number to trust. This article will show you how to build a reconciliation report to understand exactly why your numbers differ and how to find confidence in your data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Reconciliation Report?

A reconciliation report is a simple, comparative analysis used to line up data from two different sources to identify and understand discrepancies. The goal isn't necessarily to make the numbers match perfectly - they almost never will. Instead, the objective is to understand why they are different and determine an acceptable variance you can rely on for future decisions.

For example, you might create a reconciliation report to compare:

• E-commerce revenue in Google Analytics 4 vs. Net Sales in Shopify.
• Leads captured in GA4 (via Forms submissions event) vs. new contacts created in HubSpot or Salesforce.
• Total users reported by GA4 vs. a different web analytics platform.

Building this report moves you from feeling uncertain about your data to confidently explaining the nuances of your business's performance. It’s the key to turning data confusion into data clarity.

Common Reasons Your Data Doesn't Match

Before building the report, it's helpful to understand the usual suspects behind data discrepancies. Pinpointing these potential causes makes the investigation process much smoother once you have the numbers side-by-side.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Tracking Mechanisms (Client-Side vs. Server-Side)

This is the most frequent cause. Google Analytics typically runs in a user's browser (client-side). This means it can be blocked by ad blockers, browser privacy settings (like iOS 17's Link Tracking Protection), or simply fail to load if the user's internet connection drops. Your CRM or e-commerce platform (like Shopify) records transactions on its server (server-side), making it a much more reliable "source of truth" for core business metrics like revenue or leads.

Example: A customer purchases a product, but their ad blocker prevents the GA4 "purchase" event from firing. Shopify records the sale perfectly, but it's invisible to GA4.

Attribution Models and Session Definitions

Platforms define "sessions" and assign credit for conversions differently. Google Analytics 4 uses a data-driven attribution model by default, which distributes credit across multiple touchpoints. Your e-commerce platform, on the other hand, likely just records the fact that a transaction occurred, without worrying about whether the customer came from an email, an ad, or a direct link.

Time Zone Gaps

A simple but often overlooked issue is mismatched time zone settings. If your Google Analytics view is set to Pacific Time (PT) but your Shopify store is set to Eastern Time (ET), your daily, weekly, and monthly reports will never line up because the "start" and "end" of each day are different.

Bot Traffic and Spam Filters

Each platform has its own way of filtering out bot and spam traffic. GA4 has built-in filters, but some spam can still slip through, artificially inflating your session and user counts compared to other systems that might have more aggressive filtering.

Refunds, Test Orders, and Order Edits

Your Shopify report for "Net Sales" automatically accounts for refunds. Google Analytics, however, only knows about a refund if you've correctly configured a data import or refund event to send that information back to GA4. Otherwise, GA4 holds onto the original purchase value, creating an immediate discrepancy.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create a Reconciliation Report in 4 Steps

Ready to build your report? Grab a coffee and open up a blank spreadsheet. We'll walk through this using the common example of reconciling GA4 revenue with Shopify sales.

Step 1: Choose Your Source of Truth

First, you must decide which platform serves as your "source of truth" for the particular metric you're measuring. This is your baseline, the number you trust implicitly.

• For Revenue: Your e-commerce platform (Shopify, BigCommerce) or payment processor (Stripe) is always the source of truth. It tracks actual money in the bank.
• For Leads: Your CRM (HubSpot, Salesforce) is the source of truth. It tracks the actual contacts you can engage with.
• For Top-of-Funnel Traffic: Google Analytics is generally the best source of truth, as it's specifically designed to measure website engagement.

For our example, Shopify is the source of truth for revenue.

Step 2: Collect Your Data

Now, let’s pull the data. Consistency is everything here. Make sure you use the exact same date range and apply the same filters (if any) in both platforms.

Exporting Data from Shopify:

1. Log into your Shopify admin panel.
2. Navigate to Analytics → Reports.
3. Find a report that shows total financial data, like the Finances summary report. A "Sales by channel" report also works well.
4. Select your date range (e.g., Last Quarter, April 1-30). Be specific.
5. Export the data as a CSV file. We care about the "Net Sales" figure.

Exporting Data from Google Analytics 4:

1. Log into your GA4 property.
2. Go to Reports → Monetization → Ecommerce purchases.
3. In the top right, set the exact same date range you used in Shopify.
4. Look for the summary card showing 'Total revenue' or 'Purchase revenue'.
5. Export the report using the 'Share' icon (a right-facing arrow) in the top right and download it as a CSV.

Step 3: Organize Your Spreadsheet

Time to bring everything together. In a new Google Sheet or Excel workbook, set up a simple summary table.

Create a tab called "Reconciliation Summary." It should feel clean and simple. Here’s a basic structure:

Now, copy and paste the key numbers from your exported files into this table. For this exercise, you only need the totals. For instance, Shopify's total Net Sales and GA4's Purchase Revenue.

Step 4: Calculate the Variance and Investigate

This is where the insights come from. Use simple formulas to show how different the numbers are.

• Difference ($ and #): A simple subtraction. =B2-C2. This shows you the raw difference.
• Variance (%): This is the most important calculation. Use the formula: =(GA4_Value - Source_of_Truth_Value) / Source_of_Truth_Value ...or in our sheet, =(C2-B2)/B2. Format this cell as a percentage.

Your completed table might look something like this:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Interpreting the Results

A variance of -4.61% for revenue reveals that GA4 underreported revenue by about 5% compared to the true sales figures in Shopify. This is a very common scenario. Now, refer back to the "Common Causes" list to write out a quick analysis in the 'Notes' column.

You might write:

GA4 captures about 95% of our Shopify revenue. The ~5% gap is likely due to users with ad blockers preventing the purchase event from firing, and occasional payment gateways that redirect users off-site without correctly returning them to the confirmation page. This variance is an acceptable range for our marketing reporting.

If you see a large variance (e.g., >15-20%), that's a signal something is likely broken. It could be a misconfigured GA4 tag on your confirmation page, issues with a new plugin, or a problem tracking across subdomains. This report gives you the evidence you need to tell your developers, "Hey, I've noticed a major data gap between our analytics and sales platforms, can we look into why the purchase event isn't firing for so many orders?"

Final Thoughts

Building a data reconciliation report is a foundational skill for anyone serious about making informed decisions. It transforms your data from a source of confusion into a tool for strategic insight by helping you understand your entire data ecosystem, identify technical gaps, and build trust in your reporting. You'll stop asking "which number is right?" and start explaining "why the numbers are different."

While building these reports manually in spreadsheets is valuable, the process of exporting CSVs and updating tables can be time-consuming. This is why we built Graphed to help. We connect directly to your data sources like Google Analytics and Shopify, so you can pull your most important data into a single, real-time dashboard instantly. You can ask our AI to simply create a chart that compares your Shopify sales to your GA4 revenue, and it will build and update it automatically, saving you from any manual CSV wrangling and formula-writing.