If you’ve ever opened the GA4 interface and seen a nice clean session count—don’t take it at face value. Well, you can trust it… with some caveats. Because in BigQuery, there’s no ready-made “sessions” table. You’ll have to build it yourself. Yes, sessions in GA4 are like ghosts: they exist, but good luck finding them.
What counts as a session?
In GA4, each session is defined by two key identifiers:
• user_pseudo_id — the user identifier.
• ga_session_id — a unique session ID (per user).
Together, these two form a unique session key. That’s all the magic.
What you need to do:
• Select events where ga_session_id IS NOT NULL
• Group by user_pseudo_id and ga_session_id
• Then you can add:
• MIN(event_timestamp) — session start
• MAX(event_timestamp) — session end
• COUNT(*) — number of events
• ARRAY_AGG(event_name) — what the user did
Tips and tricks:
• There might be more sessions than you think: even if GA4 didn’t send a session_start event, the session still exists.
• Want to calculate session duration? Subtract session start from session end.
• You can add traffic_source.source and medium to see where the user came from - just remember, source is usually tied to the first visit.
Takeaway:
GA4 says: “Here’s your session count—trust me.”
BigQuery says: “Build it yourself—but I’ll show you the truth.”
And once you build a session table manually, you’ll understand your traffic better than from a hundred standard reports. No need for session_start anymore. Just user_pseudo_id, ga_session_id, and a little persistence.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE.
