GA4 sessions in BigQuery
The GA4 export has no sessions table. A session is user_pseudo_id plus the ga_session_id parameter, because session IDs repeat across users. How to count sessions, engaged sessions and landing pages.
Practice this in free lessons on learn.querylab.io: Deriving a session key and 2 more.
The GA4 export has no sessions table. Each event carries a ga_session_id parameter, and a session is the pair of user_pseudo_id and ga_session_id. The ID alone isn’t unique: it is the session’s start time in seconds, so two users who start in the same second share it. Count sessions with COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)). A session ends after 30 minutes of inactivity by default, and the timeout can be changed for each data stream.
The examples below start from flat columns. Getting ga_session_id out of event_params is covered in GA4 event_params.
Build a session key
The same ID appears for two users here, so the ID alone undercounts:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('user_a' AS user_pseudo_id, 1767225600 AS session_id), ('user_a', 1767225600), ('user_b', 1767225600), ('user_b', 1767229200) ]) ) SELECT COUNT(DISTINCT session_id) AS by_id_alone, COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)) AS by_user_and_id FROM events
| Row | by_id_alone | by_user_and_id |
|---|---|---|
| 1 | 2 | 3 |
This happens in real data. In one week of Google’s sample there are 25,581 different IDs and 26,331 sessions.
Events without a session
CONCAT returns NULL if any input is NULL, and COUNT(DISTINCT ...) skips NULLs, so these events vanish from the count with no error:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('user_a' AS user_pseudo_id, 1767225600 AS session_id), ('user_b', NULL), ('user_c', NULL) ]) ) SELECT COUNT(*) AS events, COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)) AS sessions, COUNTIF(session_id IS NULL) AS events_without_session FROM events
| Row | events | sessions | events_without_session |
|---|---|---|---|
| 1 | 3 | 1 | 2 |
Google adds the session ID automatically for gtag.js and the Firebase SDK. It isn’t added to events from the Measurement Protocol or Data Import, so check for NULLs if you send data that way.
Don’t count session_start events either. In the same week, 299 of 26,331 sessions have no session_start event, and 4 have more than one.
Sessions across midnight
A session that crosses midnight has events in two daily tables. Counted per day, it appears in both, so daily counts don’t add up to the period’s total:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('20260101' AS event_date, 'user_a' AS user_pseudo_id, 1767225000 AS session_id), ('20260102', 'user_a', 1767225000), ('20260102', 'user_b', 1767300000) ]) ), per_day AS ( SELECT event_date, COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)) AS sessions FROM events GROUP BY event_date ) SELECT (SELECT SUM(sessions) FROM per_day) AS sum_of_days, (SELECT COUNT(DISTINCT CONCAT(user_pseudo_id, '-', session_id)) FROM events) AS whole_period
| Row | sum_of_days | whole_period |
|---|---|---|
| 1 | 3 | 2 |
The sample has 43 such sessions out of 26,331 in a week. The same rule applies to any distinct count: compute it over the whole period, not as a sum of days. Reading only one day also cuts off the part of the session that fell on the other side.
Engaged sessions
GA4 calls a session engaged if it lasts longer than 10 seconds (the default), has a key event, or has 2 or more page or screen views. The export records it in the session_engaged parameter, as a string or an integer (details). One session can hold both values: in the sample’s week, 24,602 of 26,331 sessions have a 0 on some events and a 1 on others, in no fixed order. Treat a session as engaged if any of its events has 1. That gives 24,899 sessions, and 1,432 have only 0.
Count distinct sessions, never events. Here 3 engaged events belong to 2 sessions:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT('a-1' AS session_key, 'page_view' AS event_name, '0' AS session_engaged), ('a-1', 'user_engagement', '1'), ('a-1', 'scroll', '1'), ('b-1', 'page_view', '0'), ('c-1', 'page_view', '0'), ('c-1', 'purchase', '1') ]) ) SELECT COUNTIF(session_engaged = '1') AS engaged_events, COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL)) AS engaged_sessions, COUNT(DISTINCT session_key) AS sessions, ROUND(COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL)) / COUNT(DISTINCT session_key), 3) AS engagement_rate FROM events
| Row | engaged_events | engaged_sessions | sessions | engagement_rate |
|---|---|---|---|---|
| 1 | 3 | 2 | 3 | 0.667 |
The engagement rate is engaged sessions divided by all sessions, and the bounce rate is the rest. IF(..., session_key, NULL) gives NULL for the other rows, and COUNT(DISTINCT ...) ignores NULLs.
Landing pages
The landing page is the session’s first page. MIN_BY(page, event_timestamp) returns the page of the earliest event, and MAX_BY the last:
WITH page_views AS ( SELECT * FROM UNNEST([ STRUCT('a-1' AS session_key, 100 AS event_timestamp, '/home' AS page), ('a-1', 200, '/shoes'), ('b-1', 150, '/shoes'), ('b-1', 300, '/cart') ]) ) SELECT session_key, MIN_BY(page, event_timestamp) AS landing_page, MAX_BY(page, event_timestamp) AS exit_page FROM page_views GROUP BY session_key ORDER BY session_key
| Row | session_key | landing_page | exit_page |
|---|---|---|---|
| 1 | a-1 | /home | /shoes |
| 2 | b-1 | /shoes | /cart |
GA4 also sets entrances = 1 on the first page view of a session, and filtering on it is simpler to write. It isn’t reliable on its own: of 24,998 sessions with a page view in the sample’s week, 329 have no entrance and 10 have more than one. Count distinct session keys if you use it, and prefer MIN_BY when every session must have exactly one landing page. Two events can share a timestamp, so if ties matter, also order by batch_event_index on properties that export it.
page_location is the full URL, with its query string. Strip that before grouping, or each campaign link becomes its own landing page. SPLIT(page_location, '?')[OFFSET(0)] does it.
Session length
The time between a session’s first and last event isn’t how long someone was engaged. GA4 sends engagement_time_msec, the time the page was in the foreground, and adding it up gives a much smaller number:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT(1767225600000000 AS event_timestamp, 0 AS engagement_time_msec), (1767225660000000, 12000), (1767226800000000, 3000) ]) ) SELECT (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 AS elapsed_seconds, SUM(engagement_time_msec) / 1000 AS engaged_seconds FROM events
| Row | elapsed_seconds | engaged_seconds |
|---|---|---|
| 1 | 1200.0 | 15.0 |
Use engagement time when you want to match GA4’s engagement metrics.