First, confirm the comparison is like for like
Pick a closed day well past processing and one metric, starting with the simplest: the event count for a single event name. Query that day's daily table, not the intraday one, and select the interface's event count for the same event and date in a standard report. Event counts should agree closely; if they do not, the table is incomplete or the date is cut differently, and the time-zone and export rows of the table below apply. Only once events agree, move on to sessions, users, key events and source dimensions, each of which adds logic on one side or the other.
Check the scope of the question as well. The BigQuery export basics guide describes which records hold user-level first touch, event-level collected source and the session's last-click source; choosing the wrong one is an attribution error, not a data fault. And keep the interface side honest: compare with a standard report rather than an exploration, which may be sampled. A dashboard built on the property's API rather than the export has its own sibling page, on Looker Studio not matching GA4.
Diagnosis table
Diagnosis
| Symptom | Check | Likely cause | Fix |
|---|---|---|---|
| Interface users or key events above the export in consent-heavy markets | Whether the property's reports include modelled data | Interface includes modelled data the export does not | Treat the gap as expected; compare observed-only views |
| Event counts agree but sessions or users differ | How the SQL builds sessions and which user metric the interface shows | Session and user counting logic in SQL differs from GA4's | Count sessions from user and session identifiers; match the user definition |
| Daily totals drift between adjacent days but agree over a week | Whether the query cuts dates from the UTC timestamp or the table date | Time zone and table date boundaries | Use the table date or convert timestamps to the property time zone |
| Channel or source totals disagree while overall totals match | Which traffic-source record the query reads | Export carries event-level traffic source; reports use the attribution model | Read the record matching the report's scope; expect attributed views to differ |
| Recent days short in the export, or whole days missing | Table existence, intraday versus daily, and export status in admin | Daily export limits or streaming tables incomplete | Wait for daily tables; check the export limit and streaming option |
| Small rows present in the export but missing in the interface | The threshold icon and the interface's estimation notes | Thresholds and estimations applied in the interface only | Accept as expected; use the export for small segments |
Fix, verify and prevent each difference
Modelled data on one side only
Where consent mode is in its advanced form and the property meets the documented eligibility conditions, the interface can blend modelled estimates into users, sessions and key events. The export contains collected events only, so in markets with many refusals the interface can read higher.
Fix: classify the gap as expected, and when an observed-only comparison is needed, switch the interface to a reporting identity that excludes modelled data, as the privacy, consent and measurement guide explains.
Verify: the gap shrinks markedly under the observed-only identity.
Prevent: label every reported figure as observed or modelled, and keep the export as the observed record.
Sessions and users counted differently
The export has no session rows. A session is the combination of the pseudonymous user identifier and the session identifier parameter, and many queries count one of them alone, or count sessions per day so that a visit across midnight counts twice. Users in most interface reports are active users, not every identifier that sent an event.
Fix: count distinct combinations of user and session identifier over the whole range, and match the user definition the report uses, following the counting and grain advice in SQL for marketers.
Verify: sessions for a closed day now sit within the small gap the interface's estimation explains.
Prevent: keep session and user logic in one shared view that every query reads, rather than rewritten in each notebook.
Dates cut in another time zone
Daily tables are named by date in the property's reporting time zone, while event timestamps are stored in microseconds in UTC. A query that derives the date from the timestamp without converting it shifts events across midnight and moves totals between days.
Fix: filter on the table suffix and the stored event date, or convert timestamps to the property time zone before truncating, as the BigQuery export basics guide describes for the daily tables.
Verify: day-level totals match the interface, not just weekly ones.
Prevent: write the date logic once, in the shared view, and note the property time zone beside it.
Source read at the wrong scope
The export holds several traffic-source records: the user's first touch, the source collected with each event, and the session's last-click source. Interface reports use first-user, session or attributed dimensions, and attributed ones follow the reporting AttributionAssigning conversion credit to touchpoints. model, which the export does not apply.
Fix: read the session last-click record for session acquisition comparisons and the first-touch record for user acquisition, and treat attributed channel credit as a modelled view the export cannot reproduce exactly, as the attribution settings guide explains.
Verify: session channel totals line up once the right record is used.
Prevent: name the scope in every query's output columns.
Tables not complete
Daily tables appear only when a day's processing finishes, intraday tables are provisional and are replaced, and the help centre documents a daily event limit for the daily export on standard properties, above which it pauses. A reconciliation run too early, or across intraday rows, will come up short.
Fix: compare closed days from daily tables only, check the export status in the property's link settings, and use streaming only for monitoring, as the BigQuery export basics guide recommends.
Verify: the table for the day exists and its event count matches.
Prevent: schedule reconciliation after the usual arrival time, with a check that the table exists before it runs.
Interface-only adjustments
The interface estimates user and session counts with an approximation technique, and withholds small rows under thresholds; neither happens in the export.
Fix: accept a small, stable gap, and use the export for small segments the interface hides, as the reports and dimensions guide describes under thresholds.
Verify: the gap stays small and stable across days.
Prevent: document the expected tolerance so nobody chases it again.
Prevention: reconciliation queries kept with the project
A gap explained once should not need explaining again. Keep a small set of reconciliation queries in the same repository as the reporting models: event count by name per day, sessions and users per day using the shared logic, key events per day, and sessions by session source. Run them after each daily table lands and write the results beside the interface figures for the same closed days. Record the expected tolerance for each metric and the reason for it: estimation, modelling, thresholds. Alert when a ratio moves outside its tolerance, because a sudden change usually means a schema change, a paused export or an edited query. The headline measures (KPIA metric tied directly to an objective.) reported from the warehouse then come with their own evidence, and the SQL for marketers guide shows the comparison pattern for tying model totals to a source.
The same queries fit inside a wider suite of key, freshness, volume and total checks, which the tutorial on writing data tests for marketing tables builds and schedules with each load.
A session gap traced to two query habits
A travel retailer's warehouse reports 182,400 sessions for a week against 171,300 in the interface. Event counts
for page_view agree within 0.2%, so the tables are complete. The session query counts distinct session
identifiers per day and sums the days, so 6,900 visits crossing midnight count twice; it also cuts dates from
the UTC timestamp while the property reports in a time zone several hours ahead. Rewritten to count distinct
user and session pairs over the week, using the table date, the query returns 172,100. The remaining 0.5% gap
sits within the estimation tolerance the team records.
SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
FROM `travel-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260907' AND '20260913'
Common mistakes
Comparing intraday tables with closed interface days. Counting session identifiers without the user identifier. Deriving dates from UTC timestamps. Comparing attributed channels with event-level source fields. Treating the interface's modelled totals as an export failure.
