A detailed guide to sessionizing the GA4 BigQuery export — from identifying sessions and reliably ordering events to two attribution models (First Event Available and Session Start), their limitations, and validating agreement between them.
If you report on GA4 data from the BigQuery export, sooner or later you'll run into discrepancies between the numbers in the GA4 UI and your own calculations. Some of these are expected (sampling, thresholding, timezone), while others arise when you're building your own session-level table in BigQuery and have to decide how to handle the attribution of individual visits.
This article describes how we at MeasureDesign sessionize the GA4 BigQuery export — i.e., how we build a session-level table with attribution parameters (source, medium, campaign) from raw events. You'll find two attribution models here, along with guidance on when each is appropriate. All the queries mentioned are available in MeasureDesign's public GitHub repository (links included in the text).
By sessionization we mean the process of turning raw event-level data into a session-level table, where each row corresponds to a single session with assigned attribution parameters. Before getting into the individual models, two things need to be addressed: how to identify a session, and how to reliably order events within a session.
Although it might seem like the natural choice, ga_session_id in BigQuery is not a unique session ID. It's actually a timestamp, so multiple users (with different user_pseudo_id values) can share the same ga_session_id. For this reason, we construct a unique session ID as a combination of both fields.
SELECT
CONCAT(
user_pseudo_id, "-",
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
) AS unique_session_id
FROM `project_name.analytics_12345.events_20240121`
For events in the GA4 BigQuery export, we work with two timestamps:
• event_timestamp – the time the event was received by the server (UTC); generated automatically by GA4
• actual_timestamp – a custom event parameter (a best practice in implementation) representing the time the event occurred on the client side (in the user's browser); it needs to be implemented separately into the measurement setup
For correctly ordering events within a session, we prefer actual_timestamp, since event_timestamp can be skewed. However, actual_timestamp has its own limitations too — a user's device may have an incorrectly set date or time. In practice, we've encountered edge cases with timestamps in the future, or conversely, far in the past.
For this reason, our sessionization uses a UDF called safe_actual_ts_ms, which returns actual_timestamp only if it falls within a reasonable range of event_timestamp. Otherwise, it falls back to event_timestamp.
GitHub: Safe actual_timestamp (FUNCTION).sql.
If no source/medium signal is available for a session, we classify it as direct/none. This serves as the default fallback for both models.
There are two approaches to deriving attribution parameters from a session. Each has its own use cases and limitations.
We take the attribution parameters from the first event within the session that contains this information — i.e., has at least one non-null value in source/medium/campaign/content/term.
"First" in this context means chronologically first according to our explicit ordering, not the order in which events arrive in the BQ export. Relying on row order in the export would be unreliable, since events in batches don't necessarily arrive in strict order, and Google guarantees no default ordering. We therefore order the data ourselves via ORDER BY actual_timestamp ASC (with a fallback to event_timestamp through the safe_actual_ts_ms function), using event_bundle_sequence_id ASC as a tie-breaker for events with identical timestamps.
GitHub: model_1_first_event_available.sql.
Main logical steps:
Since November 2, 2023, automatically collected session_start events contain the same parameters as the first client-triggered event within the session (Google's release notes). This means attribution parameters can be obtained directly from the session_start event, without needing to scan through all of the session's events.
GitHub: model_2_session_start.sql.
Main logical steps:
MERGE is a BigQuery DML statement that, in a single step, decides whether to insert a row into the target table (INSERT) or update an existing record (UPDATE). For sessionization, this is useful for two reasons:
MERGE has a consistent structure in BigQuery that's worth knowing, since all sessionization queries use it in a similar way. It starts with the definition of the target table and the source query, which are joined via a condition in the ON clause — typically through unique_session_id within the current data window. This is followed by branches specifying what should happen on a match (WHEN MATCHED) and on a non-match (WHEN NOT MATCHED).
Simplified syntax looks like this:
MERGE INTO `project.dataset.target_table` t
USING (
-- zdrojový SELECT: vrátí sessions za dané datové okno
) s
ON t.unique_session_id = s.unique_session_id
AND t.session_date BETWEEN start_date AND end_date
WHEN MATCHED AND (
t.session_source IS DISTINCT FROM s.session_source
OR t.session_medium IS DISTINCT FROM s.session_medium
OR t.session_campaign IS DISTINCT FROM s.session_campaign
) THEN UPDATE SET
t.session_source = s.session_source,
t.session_medium = s.session_medium,
t.session_campaign = s.session_campaign,
t.last_update_timestamp = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
More details are available in the official Google documentation.
Specific Implementation Rules
After deploying sessionization for a new client (property) or when migrating between models, we check how much the outputs of the two models differ. A small difference (a few percentage points) is normal and expected. A larger difference may indicate a non-standard implementation that's worth investigating.
GitHub: models_comparison.sql.
The query compares the daily session count from both models — cnt_sessions_fev (Model 1) vs. cnt_sessions_ss (Model 2) — and calculates the percentage difference.
In practice, we consider a 1–2% difference between the models to be acceptable noise — it typically stems from a minority of sessions without a session_start event, which FEV captures but SeS does not. A difference above 5% is a signal that it's worth taking a closer look at the implementation — most often this points to a non-standard consent flow, SPA navigation without a captured session_start, or an event-ordering issue within batches. Before deploying to production, we recommend running the validation over at least a monthly window, so the result isn't skewed by a short-term anomaly (a campaign peak, a measurement outage).
Sessionization of the GA4 BigQuery export, as described in this article, forms the foundation for an attribution pipeline. It's a robust, repeatedly tested approach that constitutes the first layer (L0) of session-level data. In practice, however, for most clients we build further on top of this foundation:
Key Takeaways
All the queries mentioned in this article can be found in the team's GitHub repository:
If you have any questions about sessionization, or run into an edge case that this approach doesn't cover, feel free to reach out to us.
Part 3 of our first-party data series gets technical again – we'll show you how to check that data is correctly reaching each system and doing what it should. We'll look at outgoing hits in DevTools and at checks directly in the ad platforms.
A detailed guide to sessionizing the GA4 BigQuery export — from identifying sessions and reliably ordering events to two attribution models (First Event Available and Session Start), their limitations, and validating agreement between them.
Take a look at our ClickUp backup solution. Automated exports, a GitHub repository, and a practical guide to keeping your company data under control.
Learn how to deploy server-side tracking on Google Cloud Run. Compare Stape vs Cloud Run, configure load balancers, choose billing types, and test your setup.
Complete technical guide to collecting first-party data via dataLayer, normalizing, hashing, and sending through server-side GTM to Google Ads and Meta.
Testing ClickUp MCP: hands-on experience with AI-powered automation, security concerns with access tokens, practical limitations, and who should use it.
Learn how first-party data improves campaign performance, measurement accuracy, and cross-device tracking. Discover practical ways to collect and use it.
Learn how we migrated 250 media tags (Facebook, Google Ads, Sklik, Bing) to server-side GTM. Practical tips, templates, and lessons learned.
Workshops on advanced digital analytics: BigQuery, cookieless tracking, consent, attribution, and building data warehouses for reporting and activation.
Why analytics is an excellent career for women, including those returning from maternity leave. A personal story about transitioning into data analytics.
Learn how to transfer historical GA4 data between BigQuery projects using Data Transfer Service. Step-by-step guide for dataset migration and billing.
A look back at Reshoper - advising e-shop owners on tracking and measurement, plus a roundtable on marketing automation with insights on self-hosted N8N.
From Idea to App in 48 Hours 🚀 Building AI-powered apps at #HackYourWeekend using Claude Code, tracking with BigQuery, and lessons from team development.
Recap of MeasureCamp Brno 2025: server-side tracking insights, legal tracking without consent, and Women in Analytics session by MeasureDesign team.
Recap of PPC Camp: my presentation on legally measuring data without user consent - cookieless tracking, sGTM, BigQuery, Facebook conversions, and Advanced Consent Mode risks.
Ready-to-use BigQuery SQL script to calculate Easter dates (2024-2100) using Computus algorithm. Perfect for filtering GA4 data and analyzing seasonal trends.
GA4 + BigQuery in practice: connecting analytics, CRM & media data, real-world use cases from IKEA, Shoptet, McDonald's & Česká spořitelna, and what it unlocks for marketing.
80-minute podcast with Vašek Jelen on GA4, server-side tracking, BigQuery, and connecting customer data with campaign performance for e-commerce.
Vašek and Anička presented at MeasureCamp Prague on using Google Ads export in BigQuery, combining it with GA4 and CRM data to solve attribution issues.
Learn how to extend GA4 data retention from 2 to 14 months. Understand what retention affects, how to change settings, and what happens after data expires.
MeasureDesign led a Google Analytics 4 workshop for Tereza non-profit, focusing on practical data use for their Učíme se venku program.
At the Reshoper conference, I had the opportunity to give a talk where I summarized new opportunities for e-commerce analytics.
Workshop on working with GA4 data in BigQuery and Google Cloud. Learn to move beyond the GA4 interface and unlock the potential of raw GA4 data.
Vojta works at MeasureDesign on developing technical and data solutions that are not only functional, but also practical and easy to use. He enjoys combining web development, automation, and data work to create solutions that make sense both from the user’s perspective and in terms of the technical foundations behind them. What he finds most rewarding is turning a more complex problem into a clean and reliable solution.
Jirka has been working in marketing for over 10 years, and if there is anything he enjoys more than numbers themselves, it is connecting them. He loves mathematics and data analytics, and thanks to his interest in exploring source code, he can easily communicate with developers in their own language. At MeasureDesign, he specializes in connecting new data sources - building custom connectors in Python, testing data quality, and exploring which data combinations make the most sense from a business perspective. He is completely at home in Looker Studio and also has extensive experience evaluating PPC campaign performance.
Lenka joined MeasureDesign at the end of 2025, bringing extensive experience from PPC marketing, where she spent many years working with Google Ads, Meta Ads, and other advertising platforms. While managing campaigns, she repeatedly ran into the same issue - poorly set up or insufficient web analytics, which made effective optimization nearly impossible. This challenge initially led her to analytics out of necessity, but over time she discovered that she enjoyed it even more than advertising itself. Today, she focuses primarily on implementing web analytics and data solutions that provide companies with high-quality, reliable data for strategic decision-making and performance marketing. She continues to work on selected PPC projects as well - not only because she still enjoys them, but mainly to stay closely connected to the reality of media platforms and the real needs of clients.
Marťa helps integrate AI into everyday work—making it faster, more efficient, and accessible to every team member. What excites her most is finding practical ways to use AI and turning new technologies into useful tools.
Anička has over 7 years of experience in the agency world, where she has managed social media ad campaigns for clients, and especially for content-driven websites, her favorite. Wanting to broaden her perspective beyond campaign data, she gradually shifted her focus toward web analytics. She joined our team in 2022 and now specializes in data analytics, using GA4, BigQuery, Looker Studio, and other tools to connect and dig deeper into data — delivering insightful analyses and valuable input for business decisions. Anička was a member of our team until 2026.
Zuzka's career path led her through corporate innovation and research management, running word-of-mouth projects, and later to a digital agency, where she managed website development projects. However, Zuzka is naturally curious and wanted to understand how a website actually works once it is launched into the world. That curiosity led her to study web analytics — and eventually to a key collaboration with Vašek. In 2019, they founded the company together.
Vašek has been working in digital analytics for over 15 years — from setting up tracking to data storage, visualization, and interpretation. He helps companies keep their data in order and make full use of it. He focuses primarily on data from digital platforms such as websites, apps, and client zones, and on connecting that data with other business data like media and customer data. After years of freelancing, he co-founded the analytics studio MeasureDesign, where, in addition to working on analytics projects and bespoke training sessions, he also mentors and educates new analysts.
Blanka joined our team in 2024 and has been responsible for back-office operations, including invoicing and administrative tasks, ever since. She draws on her experience from the Czech Post and her background in financial management to keep everything running smoothly. In her free time, she enjoys traveling with her two children and finds relaxation in working in her garden.
Markéta started out in marketing, but then came maternity leave — and with it, total chaos. In an effort to hold on to the last bits of sanity, she turned to data. After all, numbers don’t yell, spill cereal into your keyboard, and at least they make some sense. She completed a data analytics course at Engeto Academy, where she bonded with SQL, Power BI, Excel, and Python, and started looking for patterns outside the bounds of children’s coloring books. Today, at MeasureDesign, she helps clients understand what their numbers are really saying.
She joined MeasureDesign in 2023, specialising in measurement implementation and reporting. Her favourite moment is when, after all the setup and testing, the first data finally starts flowing in. Her biggest challenge? The unexpected (and often undocumented) changes from Google – those are the times when every analyst turns into a paranormal behaviour expert. 👻 She was a member of our team until summer 2026.
Klára has been with the company since 2019. She focuses mainly on web analytics but is not afraid to dive into data work in BigQuery. What she enjoys most is guiding clients through the entire process — from defining their needs to implementing tracking and creating the final data visualizations. She gets an almost suspicious amount of joy from a clean and well-organized GTM container or a report full of useful data.
