Docs/Google Analytics 4/Sessions
Google Analytics 4

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:

session ID alone against user plus IDOpen in Querylab.io
12345678910
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
Result in BigQuery
Rowby_id_aloneby_user_and_id
123
1 row

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:

events with no ga_session_idOpen in Querylab.io
1234567891011
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
Result in BigQuery
Roweventssessionsevents_without_session
1312
1 row

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:

sum of days against the whole periodOpen in Querylab.io
1234567891011121314
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
Result in BigQuery
Rowsum_of_dayswhole_period
132
1 row

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:

engagement rate from session_engagedOpen in Querylab.io
1234567891011121314
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
Result in BigQuery
Rowengaged_eventsengaged_sessionssessionsengagement_rate
13230.667
1 row

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:

first and last page of each sessionOpen in Querylab.io
1234567891011
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
Result in BigQuery
Rowsession_keylanding_pageexit_page
1a-1/home/shoes
2b-1/shoes/cart
2 rows

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:

elapsed time against engaged timeOpen in Querylab.io
1234567891011
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
Result in BigQuery
Rowelapsed_secondsengaged_seconds
11200.015.0
1 row

Use engagement time when you want to match GA4’s engagement metrics.

Open the playground. It is the real app on public datasets and needs no sign-in.