Deriving a session key

Session counts are usually the first number stakeholders ask for, and the export has no session table. To get them right you need to know what identifies a session.

How it works

An event is one row. To count sessions, read ga_session_id from each event, combine it with the event’s user_pseudo_id, and count the distinct combinations. The result covers only the export you query.

Common mistake

CONCAT without a delimiter can produce collisions, and a NULL part makes the whole result NULL. Even a safe key is only unique under the export’s identity assumptions. It does not stay unique across properties or rewritten identifiers.

Worked example

WITH events AS (
  SELECT 'user-a' AS user_id, 12 AS session_id UNION ALL
  SELECT 'user-a', 12 UNION ALL SELECT 'user-b', 12
), unique_sessions AS (
  SELECT DISTINCT user_id, session_id FROM events
)
SELECT COUNT(*) AS sessions
FROM unique_sessions

The first two events share one identity. user-b has the same numeric id but is a different session.

The result is two sessions in this inline data.

All BigQuery lessons