How to Rebuild GA4 Sessions in BigQuery

The GA4 interface shows you sessions, but the BigQuery export does not: there is no session row anywhere, only events. To count sessions you have to rebuild them yourself, and the logic is simpler than it looks. Here is how.

A session is two IDs together

GA4 identifies a session by combining the device identifier, user_pseudo_id, with a session number stored in the ga_session_id parameter. Neither is unique on its own – ga_session_id repeats across users – so a session is the pair of them together. Get that concept and the SQL follows.

Counting sessions

To count sessions, count the distinct combination of the two IDs:

SELECT
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id')
  )) AS sessions
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

Concatenating the user and the session number gives each session a unique key, and counting the distinct keys gives your session total.

Engaged sessions

GA4's engagement rate rests on engaged sessions, which are flagged per event by the session_engaged parameter. You can count those the same way:

SELECT
  COUNTIF(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='session_engaged') = '1'
  ) AS engaged_events
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

For a true engaged-session count, wrap the session key logic in a subquery and keep only sessions with at least one engaged event.

Why your numbers may differ from the UI

Your rebuilt count will rarely match the GA4 interface to the digit, and that is expected rather than broken. The interface applies its own session-timeout logic, thresholding, and sometimes modeling, while your SQL counts raw session keys. Treat the export as the precise, literal version and the interface as the processed one.

The export withholds the session rows the interface hands you, but it gives you the raw material to rebuild them exactly. Combine user_pseudo_id with ga_session_id for the key, count distinct for totals, use session_engaged for engagement, and expect a small gap from the interface. Do that and sessions go from missing to fully under your control.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.