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 (GA4Google's event-based analytics platform.) 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 (GTMTag management system.) fires on the order confirmation page. Customers who pay through an external payment provider are sometimes returned to the account page instead.
Pumicestone Soap Company: one week, three sources
| Metric | Interface report | Finance query on the export | Shop platform |
|---|---|---|---|
| Users | 9,800 active users | 8,950 | n/a |
| Sessions | 13,400 | 14,250 | n/a |
| Purchases | 612 | 571 | 640 orders |
| Revenue | £15,300 | £14,275 | £16,000 |
| Purchases from one small town | Hidden under a threshold | 4 | n/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
- Classify each gap in the table as expected, query error or tracking error, with the reason.
- Write the corrected logic for sessions, dates and table choice in plain steps.
- Explain the remaining gap between the corrected export and the shop platform.
- Decide whether the fix for that gap needs server-side tagging.
- 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.
Pumicestone after the query fixes
| Metric | Interface | Corrected export | Shop platform | Remaining gap and cause |
|---|---|---|---|---|
| Sessions | 13,400 | 13,150 | n/a | About 2%: estimation and modelled sessions, expected |
| Purchases | 612 | 598 | 640 | 14 against the interface: modelled key events, expected |
| Revenue | £15,300 | £14,950 | £16,000 | Follows 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 KPIA metric tied directly to an objective. 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.
