Counting GA4 Sessions in BigQuery: ga_session_id Is Not a Key

GA4's event model replaced Universal Analytics' session-centric hits, yet many BigQuery queries still treat ga_session_id as if it were a UA session key. That assumption skews session counts, sessions per user and every funnel built on them.

In the export, ga_session_id and ga_session_number are not columns. They are event parameters stored in the event_params array and must be extracted with UNNEST, for example (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'). Every event in a session carries the same value, but the value is unique only within a user: it is derived from the session start time, so two users can share the same ga_session_id. A plain COUNT(DISTINCT ga_session_id) therefore undercounts sessions. The session key is the combination of user_pseudo_id and ga_session_id.

Two edge cases need explicit handling. Some events arrive without ga_session_id, for example from server-side sources or integrations that do not set it; they should be handled deliberately rather than silently forming a null session. Sessions that cross midnight are split across two daily events_YYYYMMDD tables, so session-level queries should read adjacent dates together.

A dedicated sessions model in dbt resolves this once. Identify session_start events to mark where each GA4 session begins, join them to the events table on user_pseudo_id and ga_session_id, and order events within each session by event_timestamp (in microseconds) to derive first and last events, duration and the session-level attributes that the raw stream does not provide. Counting session_start events and counting distinct user_pseudo_id and ga_session_id pairs should give similar totals; a large difference is worth investigating.

Without this layer, sessions per user, a UA-style bounce rate and conversion funnels are unreliable, and many reported differences between the GA4 interface and BigQuery trace back to this error.

ga_session_id also depends on client-side cookies, which consent choices and browser restrictions increasingly break. Owning the event stream at collection, for example with first-party server-side tracking such as Robin Signals, allows session logic to be defined at the source rather than inherited.