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

Marketing Analytics › Troubleshooting

BigQuery Export Doesn't Match GA4

Why BigQuery export counts differ from the Google Analytics interface: modelled and thresholded data, consent, time zones, session logic and SQL.
Troubleshootingadvanced7 min readUpdated

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

SymptomCheckLikely causeFix
Interface users or key events above the export in consent-heavy marketsWhether the property's reports include modelled dataInterface includes modelled data the export does notTreat the gap as expected; compare observed-only views
Event counts agree but sessions or users differHow the SQL builds sessions and which user metric the interface showsSession and user counting logic in SQL differs from GA4'sCount sessions from user and session identifiers; match the user definition
Daily totals drift between adjacent days but agree over a weekWhether the query cuts dates from the UTC timestamp or the table dateTime zone and table date boundariesUse the table date or convert timestamps to the property time zone
Channel or source totals disagree while overall totals matchWhich traffic-source record the query readsExport carries event-level traffic source; reports use the attribution modelRead the record matching the report's scope; expect attributed views to differ
Recent days short in the export, or whole days missingTable existence, intraday versus daily, and export status in adminDaily export limits or streaming tables incompleteWait for daily tables; check the export limit and streaming option
Small rows present in the export but missing in the interfaceThe threshold icon and the interface's estimation notesThresholds and estimations applied in the interface onlyAccept 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 Attribution 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 (KPI) 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.

Illustrative example — not a real result

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.

Frequently asked questions

Which number is right, the export or the interface?

They answer slightly different questions. The export is the exact record of events that were collected and exported. The interface adds modelled data where eligible, estimates user and session counts, withholds thresholded rows and applies the reporting attribution model. Use the export as the exact record for collected events, and explain the gap to the interface by cause rather than forcing the two to agree.

Why do event counts match closely while sessions and users do not?

Event counts are simple row counts in both places. Sessions and users are built: in the export from identifier combinations in your SQL, in the interface with an estimation technique the help centre documents. Different counting logic, plus the estimation, makes those metrics diverge more than raw events do.

Why is yesterday's table missing or different later in the day?

The daily table appears once processing for that day is complete, which is not guaranteed to a fixed hour, and intraday tables from the streaming export are provisional and replaced when the daily table lands. Compare closed days only, and schedule reconciliation after the daily table normally arrives.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. [GA4] Set up BigQuery Export — Google Analytics Help
  2. Google Analytics Help — Google Analytics Help

Related