Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Analytics › Advanced

Reconciling the BigQuery Export with GA4 for a Soap Maker

You take three sets of numbers for the same week at an invented soap brand, from the analytics interface, a finance query on the warehouse export and the shop platform, and explain every gap. The exercise practises separating differences the systems create by design from errors in a query or in the tracking, writing the corrected logic in plain steps and telling finance which figure answers which question.
ExerciseAdvanced levelModule 1: Warehouse data and server-side collection5 minUpdated

Why this matters

The first time finance queries the export, the numbers will not match the interface, and someone will conclude that one of them is broken. Usually both are working and the query is not, or the tracking has a hole that neither system can see. The BigQuery export basics guide explains the schema and the daily tables, and the diagnosis of an export that does not match the interface lists the usual causes. Here expected gaps and errors are tangled together.

The shop and the two sets of numbers

Pumicestone Soap Company is an invented handmade soap brand selling online. Its Google Analytics property (GA4) reports in the UK time zone, uses consent mode with modelled data included in reports, and exports to BigQuery daily and by streaming. The purchase tag in Tag Manager (GTM) fires on the order confirmation page. Customers who pay through an external payment provider are sometimes returned to the account page instead.

Illustrative example — not a real result

Pumicestone Soap Company: one week, three sources

MetricInterface reportFinance query on the exportShop platform
Users9,800 active users8,950n/a
Sessions13,40014,250n/a
Purchases612571640 orders
Revenue£15,300£14,275£16,000
Purchases from one small townHidden under a threshold4n/a

How finance built its figures, pulled at breakfast time the day after the week ended:

  • Users: distinct pseudonymous user identifiers across all events in the week.
  • Sessions: distinct session identifier parameters counted for each day, then the seven days added together.
  • Purchases and revenue: purchase events and their e-commerce revenue, with the date taken from the event timestamp; daily tables for the first six days and the intraday table for the last.

The shop platform shows 25 orders paid through the external provider that have no matching purchase event in any source.

Expected gaps and real errors

Expected gaps come from things the interface does on purpose: modelled users and key events where consent was declined, estimated user and session counts, and thresholds that hide small rows. They are stable, explainable and need a note, not a fix. Errors come from the query or the tracking: sessions built the wrong way, dates cut in the wrong zone, provisional tables read as final, or events that never fire. They move without warning and need fixing at source. Separate them as the diagnosis page does: one closed day, one metric at a time.

Task

  1. Classify each gap in the table as expected, query error or tracking error, with the reason.
  2. Write the corrected logic for sessions, dates and table choice in plain steps.
  3. Explain the remaining gap between the corrected export and the shop platform.
  4. Decide whether the fix for that gap needs server-side tagging.
  5. Write a short note for finance on which figure to use for what, and the routine that keeps the figures honest.

Worked answer

Users: expected. The interface blends in modelled users and reports active users; the export holds only collected events, so it reads lower. Report users as an estimate from either source.

Sessions: query error. Counting session identifiers per day and adding the days counts visits across midnight twice, and the identifier alone is not unique without the user identifier.

Purchases and revenue: query error first. The last day came from a provisional intraday table read before the daily table landed, and dates cut from the UTC timestamp move late-evening orders into the wrong day.

Small town: expected. Thresholds hide small rows; use the export for small segments.

Corrected logic, in plain steps:

  • Read daily tables only, and only for days whose table exists; schedule the query after the usual arrival time.
  • Filter on the table suffix or the stored event date, which follow the property's time zone, never a date cut from the timestamp.
  • Build a session as the user identifier and the session identifier together, counted once across the whole range.
  • Keep this logic in one shared view that every report reads.
Illustrative example — not a real result

Pumicestone after the query fixes

MetricInterfaceCorrected exportShop platformRemaining gap and cause
Sessions13,40013,150n/aAbout 2%: estimation and modelled sessions, expected
Purchases61259864014 against the interface: modelled key events, expected
Revenue£15,300£14,950£16,000Follows the purchase gap

Shop orders not in the export: 640 − 598 = 42. Of these, 25 are external-provider orders whose customers never reached the confirmation page: a tracking error. The other 17, about 3% of orders, are consistent with tags that were blocked or never ran: expected, and worth tracking as a tolerance.

Remaining gap. Fire the purchase event wherever the provider returns the customer, or return them to the confirmation page. The rest is a tolerance to monitor.

Server-side tagging. Not for this. The client-side versus server-side tagging comparison puts a server container behind a real data-control need and an owner to run it. Pumicestone's gap is a missing trigger that a tag change fixes; a server would add hosting and debugging for a small team without touching the cause.

Note for finance. Revenue and order counts for any financial KPI come from the shop platform. Channel mix, behaviour and conversion rates come from the interface, labelled as including modelled data. Exact counts, small segments and joins with cost or margin come from the export, closed days only. Each week, compare purchases across all three for the previous closed week, record the ratios and investigate any move outside tolerance, as the data quality guide recommends.

Common mistakes

  • Calling the interface wrong because the export is raw. Each follows its own rules.
  • Reading intraday tables for closed figures. They are provisional and disappear.
  • Cutting dates from UTC timestamps. Daily totals then never match at the edges.
  • Explaining every gap as consent. The external-provider orders were a tracking fault.

Test this judgement in the warehouse and server-side checkpoint, or revisit event design at the intermediate analytics level.

Pages this lesson applies

Terms used on this page

Also related

Related