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.
ga_session_idis only unique per user, so a session needs bothuser_pseudo_idandga_session_id.- Count distinct identities with a
STRUCTthat holds both parts, or join the parts as strings with a delimiter between them, so that different pairs cannot look alike. - Leave out rows where either part is
NULL, or classify them on purpose.
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_sessionsThe 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.