The native GA4-to-BigQuery export isn’t an analytical table, it’s a nested event log: every query re-writes the same UNNEST and carries the risk of double-counting purchases.
On the public Google Merchandise Store dataset (4.3M events): session isn’t a column, purchase duplicates, and attribution arrives in two scopes that can’t be collapsed without losing information.
Four dbt models across two layers: staging flattens and types the event; the marts expose sessions, deduplicated purchases, and users. 25 quality tests and CI on GitHub Actions.
360,129 sessions and 270,154 users modeled, 4,451 deduplicated purchases, 25/25 tests passing locally and in CI, documentation with a navigable lineage graph published on GitHub Pages.
Context
This project doesn’t come from a client. It comes from needing to demonstrate something a CV can’t demonstrate on its own: not just querying GA4 in BigQuery, but knowing how to model it. The dataset is deliberately public (the obfuscated Google Merchandise Store sample that Google’s own documentation uses), so any technical recruiter can reproduce the exact project and compare results.
How it was actually solved
Why session needs its own key
ga_session_id lives inside event_params and resets per user: two different users can share the same ga_session_id without it being the same session. session_key is built by concatenating user_pseudo_id and ga_session_id, giving a unique, stable key that is, in fact, GA4’s native session grain, just made explicit instead of left implicit in every query.
Deduplicating purchase without losing the real first transaction
GA4 re-fires the purchase event if the user refreshes the confirmation page. Left unchecked, total_revenue and AOV come out inflated. The fix is a qualify row_number() over (partition by transaction_id order by purchased_at) = 1, which keeps the first occurrence by timestamp and drops duplicates along with null or "(not set)" transaction_id values.
Why the dataset was created in US, not Europe
The public GA4 dataset lives in the US region, and BigQuery doesn’t allow cross-region JOINs. Creating the destination dataset in europe-west (the default instinct for an EU-based analyst) would have made the project unworkable without duplicating the data first. A conscious, documented decision, not an oversight.
Modeling real product data, even from a public dataset and not a client’s own, forces the same decisions an analytics engineer makes day to day: how to define a session, how to deduplicate, where the data lives and why. Querying GA4 in BigQuery and modeling it are different skills, and only the second one is proven with a repo, tests, and CI, not a dashboard screenshot.
FAQ
Is this real client data?
No, it’s GA4’s public sample dataset from the Google Merchandise Store, the same one Google’s own documentation uses, obfuscated and open to anyone. Stated explicitly to avoid any misunderstanding: the project’s value is in the modeling, not the data’s origin.
Why dbt instead of just raw SQL in BigQuery?
dbt provides what loose SQL doesn’t: version control for the model, automated quality tests (25 in this project), auto-generated documentation and lineage, and a CI pipeline that validates every change before merging. It’s the difference between a one-off query and a maintainable data model.