BigQuery & warehouse architecture
The free BigQuery export is the single biggest upgrade available to a serious GA4 user: raw, unsampled, permanent event data you can query and join without limits. This capstone module covers enabling it correctly, surviving the nested schema with UNNEST, controlling cost, rebuilding sessions in SQL, modeling raw events into trusted tables, and closing the loop back to activation — responsibly.
What you will learn
Why export GA4 to a warehouse at all
The free BigQuery export sends GA4’s raw, event-level data into a data warehouse you control — and it is the single biggest upgrade available to a serious GA4 user. It removes sampling entirely, keeps data far longer than GA4’s retention limits, exposes every parameter the UI hides, and lets you join GA4 behavior to CRM, cost, and offline data with SQL. The interface is where you look; the warehouse is where you can finally think without limits.
Every constraint that frustrated you in earlier modules — sampling on big Explorations, the 14-month retention wall, cardinality collapsing into ‘(other),’ audience logic the UI cannot express — dissolves the moment the raw events land in BigQuery. You trade a point-and-click interface for SQL, and in return you get the complete, unsampled, permanent truth of what your users did. For any organization whose decisions actually matter, that trade is overwhelmingly worth it.
BigQuery is the only answer right now to help ensure sessions with multiple traffic sources are properly accounted for.
That is a striking admission from one of the field’s most respected practitioners: for certain questions, the GA4 interface simply cannot give a correct answer, and the warehouse is the only place to get one. This module is how you get there — and how to avoid the very real ways warehouse projects waste money and trust.
Enabling the export
You enable the export in GA4 Admin under BigQuery Links: connect a Google Cloud project, pick a region, and choose daily export (a full batch once a day, free on standard GA4) and/or streaming export (events within minutes, which incurs BigQuery streaming costs). Data lands in a dataset of date-sharded tables named events_YYYYMMDD. Set this up on day one of any property — it only exports data from the link date forward.
The ‘day one’ point is not a nicety, it is the whole game. The export is not retroactive: it captures events from the moment you link it, never before. A property running for a year with no BigQuery link has a year of raw data that is simply gone — you can never get it back at event level. So the very first thing to do on any new GA4 property, before reports, before audiences, is turn on the free daily export, even if you have no immediate plan to query it. Storage is nearly free; regret is not.
- Create or pick a Cloud projectIn Google Cloud, have a billing-enabled project ready (the BigQuery free tier covers small workloads; see the next section).
- Link in GA4 Admin → BigQuery LinksChoose the project, select a data location/region that fits your data-residency rules, and confirm.
- Choose daily and/or streamingDaily batch is free on standard GA4 and fine for most analysis; add streaming only if you need near-real-time data and accept the cost.
- Verify the dataset appearsWithin ~24 hours (daily) you should see events_YYYYMMDD tables in the analytics_XXXXXX dataset. Confirm before relying on it.
My non-negotiable first action on any new GA4 property — before a single report is customized — is enabling the free daily BigQuery export. No exceptions, no ‘we’ll do it when we need it.’
The reason is brutal and simple: the export is not retroactive. Every day it is off is a day of event-level data that is permanently, unrecoverably gone. There is no ‘backfill’ conversation to have later.
It costs almost nothing to store and asks nothing of you until the day you finally need raw data — at which point you will have years of it instead of a painful gap.
Cost: free, paid, and how to not get burned
Two cost layers exist and people confuse them. The GA4 daily export is free on standard GA4. But BigQuery itself charges for storage and for queries (by data scanned). Google’s free tier covers a meaningful amount each month — on the order of the first 10 GB of storage and 1 TB of query processing — which is plenty for small properties. The way teams get burned is a careless SELECT * over years of data, scanning terabytes on one query.
BigQuery bills mostly on how much data each query scans, not how much it returns, and that single fact is the key to controlling cost. A query that reads two specific columns from one day costs a rounding error; the same query with SELECT * across every events_* table can scan terabytes and cost real money for an identical-looking result. Cost control in BigQuery is almost entirely the discipline of scanning less — and the date-sharded table design hands you the main lever for free.
Claim: BigQuery’s free tier includes roughly the first 1 TB of query data processed and 10 GB of storage per month; on-demand queries are then billed by bytes scanned, not rows returned. Source: Google Cloud — BigQuery pricing. Context: Pricing and free-tier amounts change over time and vary by region and pricing model; always confirm current figures before sizing a project.
- Ready a billing-enabled Cloud projectThe free tier covers small workloads, but the project needs billing attached to link.
- Link in GA4 Admin → BigQuery LinksPick the project, choose a region for data residency, enable Daily export (free).
- Wait for the first tablesWithin ~24 hours an
analytics_XXXXXXdataset appears with anevents_YYYYMMDDtable. - Filter the date first, alwaysStart every query with
WHERE _TABLE_SUFFIX BETWEEN ...so you scan only the days you need. - UNNEST a parameterPull a value with
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_location'). - Check the bytes-scanned estimateBefore running, read the validator’s ‘this query will process X’ estimate — that is your bill. Prune until it’s small.
The date filter and UNNEST are 90% of GA4 BigQuery work; the bytes-scanned check is your cost guardrail.
Rough model: ~2 KB per GA4 event, on-demand pricing ~$6.25/TB scanned, first 1 TB/month free. Drop ‘% of row scanned’ toward 5% to see what selecting two columns instead of * saves. Also a standalone tool.
The schema: nested and repeated fields
The GA4 export schema is the part that intimidates SQL veterans, because it is not flat. Each row is one event, and inside that row, event_params and user_properties are repeated, nested fields — arrays of key–value records rather than plain columns. Items in an ecommerce event nest the same way. Understanding this nested structure is the one conceptual hurdle; once it clicks, everything else is ordinary SQL.
Why nested? Because GA4’s event model is flexible — any event can carry any parameters — and a rigid flat table cannot represent ‘an arbitrary bag of parameters per event.’ So Google stores parameters as a repeated record: a little table tucked inside each row. The cost is that you cannot just write WHERE page_location = ...; you must reach into the nested array to pull a parameter out. That reaching-in is done with UNNEST, which is the single most important function in the entire export.
event_date '20260615' -- flat
event_name 'purchase' -- flat
user_pseudo_id '8f3..a1' -- flat: the browser/device id
event_params [ { key:'page_location', value:{ string_value:'/checkout' } },
{ key:'value', value:{ double_value: 49.0 } },
{ key:'currency', value:{ string_value:'USD' } } ] -- REPEATED
items [ { item_id:'SKU-7', item_name:'Starter', price:49.0, quantity:1 } ] -- REPEATEDevent_params is an array of key/value records, and the value itself is a struct with typed fields (string_value, int_value, double_value). You pick the type that matches the parameter.
Querying: UNNEST and the daily tables
Two patterns unlock 90% of GA4 BigQuery work. To read a parameter, UNNEST the nested array and select the matching key — a subquery like (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'). To control cost and scope, filter the date-sharded tables with the events_* wildcard and a _TABLE_SUFFIX BETWEEN clause, so you scan only the days you need. Master these two and you can answer almost anything.
The date filter is doing double duty: it scopes your analysis and it is your primary cost control, because filtering _TABLE_SUFFIX means BigQuery only reads the matching daily tables instead of years of history. The UNNEST pattern, meanwhile, is how every parameter, user property, and item gets pulled into a normal column you can group and filter on. They look strange the first time and become muscle memory by the tenth.
SELECT
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'page_location') AS page_location,
COUNTIF(event_name = 'scroll') AS scrolls
FROM `project.analytics_123456.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630' -- prunes scan to June only
GROUP BY page_location
ORDER BY scrolls DESCThe UNNEST subquery turns a nested parameter into a column; the _TABLE_SUFFIX filter keeps the query reading one month, not all history. Pattern shown by Simo Ahava’s GA4-BigQuery work.
You can get far with just Google BigQuery, but using R and Google Sheets for creating the base dataset can prove extremely valuable in the long run.
Rebuilding sessions and users
GA4’s export gives you events, not sessions — so you rebuild sessions yourself in SQL. A session is identified by combining user_pseudo_id (the browser/device identifier GA4 assigns) with the ga_session_id parameter. To count users you use user_pseudo_id; to count sessions you count distinct user_pseudo_id + ga_session_id pairs. This is why warehouse numbers can differ from the UI: you are reconstructing the metrics, and small definitional choices shift them.
This is the moment many analysts realize how much the GA4 interface was doing for them. In the warehouse there is no ‘sessions’ column — there are events with a session id parameter, and you assemble sessions, engaged sessions, and session-scoped attribution with SQL. The upside is total control and transparency: every number is one you can see the definition of. The discipline is to document those definitions, because two analysts sessionizing the same data slightly differently will produce two slightly different dashboards and a credibility problem.
Claim: In the GA4 BigQuery export, user_pseudo_id stores the client identifier GA4 assigns to each browser/app instance, and the session is identified by combining it with the ga_session_id event parameter. Source: Simo Ahava — querying GA4 data in BigQuery. Context: user_pseudo_id approximates devices, not people; without a logged-in User-ID join, warehouse ‘users’ are really browser instances.
Pointing a dashboard straight at the raw events_* tables means every refresh re-scans and re-sessionizes all of history — slow, and billed by the terabyte, forever.
Instead I run one scheduled query each night that sessionizes only yesterday and appends to a compact sessions mart. Dashboards query that small, pre-modeled table; the heavy lifting happens once, not on every page load.
Cost and latency both collapse, and everyone queries the same agreed definition of a session instead of re-deriving it ad hoc.
COUNT(DISTINCT user_pseudo_id) over a year of events is exact, expensive, and slow — and for a trend line you almost never need the last digit of precision.
On large ranges I reach for APPROX_COUNT_DISTINCT(user_pseudo_id). It is within a fraction of a percent of exact, runs far faster, and scans dramatically less — reserve the exact count for the one final, billable report that truly needs it.
Right-sizing precision to the decision is a cost lever hiding in plain sight.
Warehouse modeling: from raw to trusted
Raw exported events are a starting point, not a reporting layer. Mature teams model them in stages: a staging layer that flattens and cleans the nested events, then marts — trusted tables like sessions, users, and orders that the whole business queries. Tools like dbt make these transformations version-controlled and tested. The goal is one agreed definition of each metric, computed once, that everyone builds on — instead of every analyst re-deriving sessions in a slightly different way.
This is the leap from ‘I can write a clever query’ to ‘our organization has trustworthy data infrastructure.’ Without a modeling layer, a warehouse becomes a swamp: dozens of one-off queries, each with its own quiet definition of a session or a conversion, none reconcilable. With staging-and-marts, the messy nested events are transformed once into clean, documented, tested tables, and dashboards sit on those. It is the same instinct as the data layer from Module 1 — define the contract once, in one owned place, and let everything downstream inherit it.
The fastest way to lose a stakeholder’s trust in a warehouse is to show them two dashboards with two different session counts. It happens constantly, and it is never a bug — it is two analysts who sessionized the raw events slightly differently.
So before any reporting gets built, I model the core entities — sessions, users, orders — into tested tables with documented definitions, and make those the only sanctioned source. Dashboards may not re-derive a session from raw events.
It feels like over-engineering on day one and it is the thing that makes the warehouse believable on day one hundred.
Activation: closing the loop
The warehouse is not a dead end — data flows back out. You can import warehouse-computed values (a lead score, an LTV bucket) back into GA4 as user properties or audiences, push segments to Google Ads, or use reverse ETL / a CDP to sync warehouse audiences into ad and email platforms. This closes the full circle from Module 4: behavior is measured, exported, enriched with logic the UI could never express, and sent back out to act on real spend.
This is where the whole series connects. Module 1’s clean events become Module 6’s clean warehouse rows; Module 4’s audiences, once limited by the UI builder, can now be defined with arbitrary SQL over GA4 behavior joined to CRM and offline purchases, then pushed back to GA4 or Ads for activation. The warehouse removes the ceiling on what an audience can be. A ‘high-LTV customers who bought offline and browsed the new collection’ audience is impossible in the GA4 UI and routine in the warehouse.
Privacy and governance in the warehouse
Raw event data is powerful and therefore sensitive, so governance is not optional. Never let personally identifying information (emails, names) into the export — keep identifiers hashed. Control access with Cloud IAM so only the right people query user-level data. Apply retention and deletion policies that honor your privacy commitments and law (GDPR/CCPA deletion requests must reach the warehouse too). The warehouse can outlive GA4’s own safeguards, which means you inherit responsibility for them.
The uncomfortable reality is that exporting to BigQuery moves data out from under GA4’s built-in privacy controls and into your hands. GA4’s thresholding and retention no longer protect what is in your warehouse; you do. That means deletion requests have to propagate to BigQuery, access has to be locked down per role, and PII must be kept out by design rather than scrubbed after the fact. Treat the warehouse with the same seriousness as any system holding behavioral data about real people, because that is exactly what it is.
Where warehouse projects go wrong
BigQuery projects fail in recognizable ways: enabling the export too late (permanent data gap), runaway costs from unpruned SELECT * queries, fighting the nested schema instead of learning UNNEST, no modeling layer (contradictory numbers), letting PII into the export, and treating warehouse metrics as identical to the UI when they are reconstructed. Each is avoidable, and each is far cheaper to prevent than to repair.
The export is not retroactive; every day it is off is event-level data lost forever.
SELECT * across all events_* tables scans terabytes and bills real money for a result you could have gotten for cents.
Trying to treat event_params as flat columns leads to wrong results and frustration.
Ad-hoc queries with private definitions of ‘session’ produce contradictory dashboards and lost trust.
Emails or names in the export, or open access, turns a useful asset into a serious liability.
Your BigQuery checklist
A sound GA4 warehouse practice is captured here. Tick what is genuinely true of this property’s export and warehouse today.
Prove it. Earn your passcode.
Ten questions, CASE method (Context · Analysis · Strategy · Execution). Pass at 90% to unlock this module’s completion passcode — retake as many times as you like.