GA4 BigQuery SQL cookbook

What this is

A lookup reference for querying the GA4 BigQuery event export: the nested schema, the read patterns for event_params, the sessionization primitive, and the reasons BigQuery counts diverge from the GA4 UI. Every value below is drawn from Google’s export schema, basic/advanced query docs, and the reporting-surface comparison pages.

Core reference tables

Top-level columns (one row per event)

event_params value structure

Exactly one value.* field is populated per parameter; the others are NULL.

user_properties mirrors this shape and adds one extra field that distinguishes it: user_properties.value.set_timestamp_micros (INTEGER).

Common event parameters and how to read them

Note: session_engaged living in value.string_value (the literal '1' / '0') is documented for the web export by practitioner sources, but Google’s official schema page does not state which value.* field it uses or guarantee it is identical across web and app streams. Coalesce defensively where streams may differ — see the Gotchas section.

Session definitions and metrics

Ecommerce fields

Purchase-type events: event_name IN ('in_app_purchase', 'purchase').

Consent and traffic source

Datasets, tables, and export limits

Note: export volume limits and the exact behavior when the Standard limit is exceeded are periodically revised by Google — confirm the current caps against the BigQuery Export doc before relying on them.

Why BigQuery differs from the GA4 UI

Gotchas

1. Zero engaged sessions when filtering value.int_value = 1 — cause: web export stores session_engaged as the string '1' in value.string_value, so int_value is NULL — fix: read value.string_value, or defensively COALESCE(value.string_value, CAST(value.int_value AS STRING)) = '1'.
2. Engaged counts inflated or a whole session misclassified — cause: session_engaged is not written on every event, only on engagement-bearing events — fix: promote to session scope with MAX(session_engaged) OVER (PARTITION BY CONCAT(user_pseudo_id, ga_session_id)) before counting.
3. COUNT(*)/SUM() inflated after UNNEST — cause: UNNEST(event_params) in the FROM clause cross-joins each event to one row per parameter — fix: use a correlated scalar subquery (SELECT value.int_value FROM UNNEST(event_params) WHERE key = '...') to keep one row per event.
4. BigQuery session count is a few percent off the UI and never ties — cause: by design (UI sampling, (other) collapse, modeling, Google Signals, reporting identity) — fix: accept the 2–5% band; set both to Device-ID identity, match time zone, exclude the same streams/events, and treat BigQuery as the raw source.
5. Sessions crossing midnight are undercounted — cause: events partition into events_YYYYMMDD by event_date (property TZ), so one session splits across two daily tables — fix: query events_* with a buffered _TABLE_SUFFIX range and aggregate by CONCAT(user_pseudo_id, ga_session_id) across the range.
6. Counts change between runs, or today is partial — cause: daily tables can update for up to 3 days, and same-day data lives in events_intraday_YYYYMMDD — fix: for finalized reporting query dates older than the 3-day window via events_*; union intraday only when needed and treat it as provisional.
7. Grouped totals by a high-cardinality dimension exceed the UI — cause: the UI folds any dimension with > 500 unique values/day into (other); BigQuery keeps every value — fix: expected; replicate the collapse in SQL (rank, bucket beyond top N) only if you must match the UI.
8. user_pseudo_id user counts are below the UI’s Active/Total users — cause: the default UI metric is Active users plus behavioral modeling and Signals de-duplication, none exported — fix: compare like-for-like with is_active_user = TRUE and set the UI to Device-ID identity.

Quick recipes

-- UI-matching session count: ga_session_id alone is not globally unique
SELECT
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  )) AS sessions
FROM `analytics_<property_id>.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

-- Read session_engaged as a string, promote to session scope, then count
WITH ev AS (
  SELECT
    CONCAT(
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
    ) AS unique_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged
  FROM `analytics_<property_id>.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
),
sess AS (
  SELECT
    unique_session_id,
    MAX(session_engaged) OVER (PARTITION BY unique_session_id) AS session_engaged
  FROM ev
)
SELECT COUNT(DISTINCT CASE WHEN session_engaged = '1' THEN unique_session_id END) AS engaged_sessions
FROM sess;

-- Reusable temp UDF for a named integer parameter (avoids repeated UNNEST)
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING) AS (
  (SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name)
);

SELECT
  event_timestamp,
  GetParamInt(event_params, 'ga_session_id')     AS ga_session_id,
  GetParamInt(event_params, 'ga_session_number') AS ga_session_number
FROM `analytics_<property_id>.events_*`
WHERE _TABLE_SUFFIX = '20201231';

-- Average transactions per purchaser (official pattern)
SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
  AND event_name IN ('in_app_purchase', 'purchase');

-- Each user's most recent ga_session_id via a descending window
SELECT DISTINCT
  user_pseudo_id,
  FIRST_VALUE(
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
  ) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS latest_session_id
FROM `analytics_<property_id>.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

-- Robust numeric parameter read: COALESCE across the numeric value.* fields
SELECT
  event_timestamp,
  (SELECT COALESCE(value.int_value, value.float_value, value.double_value)
   FROM UNNEST(event_params) WHERE key = 'value') AS event_value
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201231'
  AND event_name = 'purchase';

Related

• Hub: GA4 guides
• GA4 limits reference — export volume caps and cardinality thresholds
• Consent Mode v2 reference — pairs with the privacy_info.* consent fields

Sources

• BigQuery Export schema — Analytics Help (read 2026-07-20)
• Basic queries for GA4 event data export — Google for Developers (read 2026-07-20)
• Advanced queries — Google for Developers (read 2026-07-20)
• Compare Analytics reports and data exported to BigQuery — Analytics Help (read 2026-07-20)
• BigQuery Export — Analytics Help (read 2026-07-20)
• Reporting surfaces comparison — Analytics Help (read 2026-07-20)
• Session — Analytics Help (read 2026-07-20)