skip to content

Why do GA4 UI report totals disagree with the same metric computed from the BigQuery export?

level: seniorimportance: should knowfreq 52%

answer

  1. two products, not two views of one dataset
  2. one side estimates what it could not see
  3. small rows are withheld to protect identity
  4. a long tail collapses into one row
  5. definitions and time zones differ too

basics

~20 s

They are different datasets. GA4 reports add modelled data, apply thresholding and collapse high-cardinality dimensions into an other row, while the BigQuery export contains only observed events with no modelling and no thresholds — so exact reconciliation is not achievable.

solid answer

~50 s

Four causes account for almost every gap. **Modelling**: GA4 reports can include modelled users and conversions where consent was declined; the export contains observed events only. **Thresholding**: reports withhold rows that could identify individuals when Google Signals is active, so small segments show zero in the UI and real rows in the export. **Cardinality**: standard reports are pre-aggregated and roll a long tail of dimension values into a single `(other)` row, which the export never does. **Definitional drift**: the UI's sessions, active users and engaged sessions are computed with GA4's own rules, and your SQL almost certainly counts something subtly different — plus the UI honours the property's reporting time zone while `event_timestamp` is UTC. The honest answer in an interview is that you pick one as the source of truth, document why, and reconcile to a tolerance rather than to zero.

code

sql · 11 lines
sql
-- a defensible session count from the export; it will still not match the UI exactly
SELECT
  event_date,
  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 `my_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240107'
GROUP BY event_date
ORDER BY event_date;

go deeper

for a junior

Know that the GA4 interface and the BigQuery export will not produce identical numbers and that this is expected, so a mismatch is not automatically a bug in a query you wrote.

for a middle

Name the concrete causes — modelled data, thresholding, the (other) cardinality row, differing metric definitions and time zones — and describe how you would recompute sessions from the export.

for a senior

Show a reconciliation method: a stable date, one unambiguous metric, no breakdown, then add dimensions one at a time to isolate the cause, and a tolerance rather than a demand for an exact match.

for a principal

Own which surface is authoritative for which decision, make sure a metric name never carries two different values across the organisation, and set expectations before a consent or identity setting change moves a headline number.

## Start by accepting that they will not match The GA4 interface and the BigQuery export are not two views of one dataset. The export is the observed event stream. The interface is a reporting product built on top of a differently-processed copy, with privacy protections, modelling and aggregation applied. An interviewer asking this question wants to hear that you know the difference is structural, not a bug in your SQL, and that you can enumerate the causes. ## Cause one: modelled data When consent mode signals that a visitor declined analytics storage, GA4 does not collect the usual identified events, but its reports can still include **modelled** users and conversions estimated from the traffic that was collected. Behavioural modelling of that kind lives entirely on the reporting side. The BigQuery export carries observed events; it does not contain modelled users, so a user count from the export sits below the UI's figure, and the gap widens as the share of consent-declining traffic rises. A change in your consent banner can therefore move the UI number without changing the export at all. ## Cause two: thresholding To prevent individuals being identified from a small cohort, GA4 applies **data thresholding** to reports when identity data such as Google Signals is in play: rows below a size the platform decides are simply withheld. The symptom is distinctive — a report shows a total that does not equal the sum of its visible rows, or a narrow segment shows nothing at all — while the export happily returns the underlying rows. Thresholding is applied at query time in the UI, so the same report can be thresholded at one breakdown and not at another. ## Cause three: cardinality and the (other) row Standard reports read pre-aggregated tables. When a dimension has more distinct values than those tables can hold, the long tail is collapsed into a single **`(other)`** row. A report on page paths or on a high-cardinality custom dimension can therefore attribute a large block of traffic to `(other)` while the export has every individual value. This is the usual explanation when a specific page or campaign appears to be missing from the UI but is plainly present in SQL. ## Cause four: definitions and time Even with identical underlying rows, the metrics are defined by GA4, and reimplementing them is harder than it looks: - **Sessions.** The UI counts sessions with GA4's rules, including modelling. Recomputing them from the export means counting distinct combinations of `user_pseudo_id` and the `ga_session_id` parameter — a defensible definition that will still not match exactly. - **Active users.** GA4's active-user definition depends on engagement signals, not merely on having sent any event. - **Engaged sessions.** Defined by a duration threshold, a conversion, or a page-count rule configured on the property; your SQL has to encode the same rule. - **Time zone.** The interface reports in the property's reporting time zone. `event_timestamp` in the export is UTC microseconds and `event_date` follows the property zone, so a date grouping chosen carelessly shifts a slice of traffic into the neighbouring day. ## Cause five: freshness and provisional data If you are comparing today, you are comparing a provisional intraday table against a UI that is also still filling. If you are comparing a recent past day, the export may have been rewritten with late-arriving events since you last loaded it. Always reconcile on a date old enough that both sides are stable. ## How to run the reconciliation Pick a stable day, a single simple metric, and no breakdown — event count for one named event is ideal, because it is closest to "count the rows" on both sides and least contaminated by definitional differences. If that matches within a small tolerance, the pipeline is fundamentally sound and the remaining gaps are the structural causes above. Then add one dimension at a time; the step where the gap appears names the cause. Comparing users or sessions first, with a breakdown applied, mixes all five causes at once and teaches you nothing. ## The organisational answer Decide which side is the source of truth for which decision and write it down. Marketing attribution and audience activation generally have to live in the interface, because they use identity and modelling that were never exported. Product and revenue metrics that must reconcile to the rest of the warehouse should be computed from the export, with definitions in code and tests. What you must not do is let two numbers for the same metric circulate under the same name — that is how a quarter gets spent arguing about which dashboard is right instead of about what the business should do.

  • Which single metric would you compare first when reconciling, and why?
    The count of one specific event on a day old enough to be stable, with no breakdown applied. It is the closest thing to counting rows on both sides, so it isolates whether the pipeline itself is sound. Users and sessions bring in modelling, thresholding and definitional differences simultaneously, so a mismatch there tells you almost nothing about where the problem is.
  • A page that clearly gets traffic is missing from a GA4 report but present in the export. What is the likely cause?
    Cardinality. Standard reports read pre-aggregated tables and roll the long tail of a high-cardinality dimension into a single (other) row, so individual low-volume values disappear from the breakdown while the total stays correct. The export applies no such aggregation. Explorations or an export-based query are the way to see the individual values.
  • How does turning on Google Signals change what your export-based numbers can do?
    It changes the interface, not the export. Signals contributes cross-device identity to reports and brings thresholding with it, so UI numbers may shift and small segments may be withheld. The export still contains only observed events keyed by user_pseudo_id and any user_id you set, so cross-device figures from the UI remain unreproducible in SQL.

saying these in an interview costs you the question

  • Assumes a gap between UI and export means the SQL is wrong
  • Thinks the export contains modelled users and conversions
  • Ignores the (other) row when a dimension value seems missing
  • Reconciles today's provisional data and expects a match
  • Recomputes sessions without matching the time zone

context

resolving links…