BigQuery for web analytics: working with the GA4 export

The BigQuery export is the single biggest reason measurement teams end up on GA4: it hands you the raw, unsampled, unaggregated event stream that the reporting UI only shows you summaries of. It is also the first place many analysts meet nested data, sharded tables, and per-scan billing all at once. This guide covers the schema as it actually is, the unnesting patterns you will write daily, and the habits that keep the bill boring.

What the export actually delivers

Linking a GA4 property to a BigQuery project (a property-level setting, no code required) produces up to three things in a dataset named analytics_<property_id>:

• events_YYYYMMDD — one sharded table per calendar day, one row per event, finalized on a delay. Daily tables can be restated up to about 72 hours after the day closes as late hits (Measurement Protocol, offline imports, consent-mode modeling adjustments) arrive — remember this when you build incremental models on top.
• events_intraday_YYYYMMDD — the streaming version of today, populated within minutes and deleted once the corresponding daily table is written. Useful for freshness; never treat it as final.
• User-data tables (users_* / pseudonymous_users_*) — a per-user snapshot export, separate from the event stream.

The export itself is free for standard properties; you pay only BigQuery storage and query costs. As of mid-2026 the daily export for standard properties is capped at 1 million events per day (streaming export is not capped, but streaming inserts are billed) — check the current limits before architecting around them, because they have shifted over the years.

The schema: one row per event, context nested inside

Every row is an event — GA4 has no hit types, as covered in events and schemas. The columns that matter most:

Column	Type	What it is
event_date	STRING	The property-timezone date, matching the table shard
event_timestamp	INTEGER	Microseconds since epoch, UTC
event_name	STRING	page_view, purchase, your custom names
event_params	ARRAY of STRUCT	Key plus a typed value struct per parameter
user_pseudo_id	STRING	The device/cookie identifier
user_id	STRING	Your authenticated ID, if you set one
user_properties	ARRAY of STRUCT	Same shape as event_params, user-scoped
items	ARRAY of STRUCT	E-commerce line items
device, geo, traffic_source	STRUCT	Flattened context records
privacy_info	STRUCT	Consent-state flags

The part that trips everyone up is event_params: a repeated field where each element is STRUCT<key STRING, value STRUCT<string_value, int_value, float_value, double_value>>. A parameter lives in exactly one of the four typed slots, and which slot can vary if your implementation is sloppy about types — a plan_id sent as "42" on one page and 42 on another lands in different slots. The dataLayer event spec exists partly to prevent this.

Unnesting parameters

The idiomatic pattern is a correlated subquery per parameter — readable, and it stays cheap because BigQuery only scans the columns you reference:

select
  event_date,
  event_name,
  user_pseudo_id,
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  (select value.int_value    from unnest(event_params) where key = 'ga_session_id') as ga_session_id,
  (select value.int_value    from unnest(event_params) where key = 'ga_session_number') as session_number
from `myproject.analytics_123456789.events_*`
where _table_suffix between '20260701' and '20260707';

Use unnest(...) in the from clause instead when you genuinely want one row per parameter or per item (e-commerce analysis over items is the classic case). Resist the urge to flatten everything into a wide table on day one — flatten the parameters you actually query, in one place, once. That place is your staging layer, which is the subject of the companion guide on modeling web events with dbt.

Cost control: the four habits

BigQuery’s on-demand pricing bills per byte scanned, and the GA4 export is designed in a way that punishes carelessness. Four habits cover most of it:

1. Always filter _table_suffix. The events_* tables are sharded by name, not partitioned — a wildcard query without a _table_suffix predicate scans every day you have ever exported. Every query, every time, even “quick checks.”
2. Never select *. Billing is columnar: you pay for the columns you touch. event_params and items are the widest things in the table; touching them costs real money at volume, and select * touches everything.
3. Dry-run first. The console shows the estimated bytes scanned before you run; the CLI has bq query --dry_run. Make checking it a reflex — a query that says it will scan 2 TB is telling you something.
4. Materialize once, query many. If a flattened view of yesterday’s events gets queried twenty times a day, write it once to a date-partitioned, clustered derived table (partition on event_date, cluster on something like event_name, user_pseudo_id) and point everyone there. Partitioned derived tables also fix what sharding can’t: BI tools can prune them automatically.

Teams with heavy, predictable load eventually move from on-demand to capacity (slot) pricing; that is a finance decision, not a modeling one, and good hygiene matters either way.

Sessionization: a working sketch

GA4 exports its own session identifier, so unlike a generic event stream you rarely need to re-derive sessions from timestamp gaps — you reconstruct the vendor’s sessions instead. A session is a distinct user_pseudo_id + ga_session_id pair (the session ID alone is not unique — it is a timestamp, and two users can collide):

with events as (
  select
    user_pseudo_id,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id,
    event_timestamp,
    event_name
  from `myproject.analytics_123456789.events_*`
  where _table_suffix between '20260701' and '20260707'
)
select
  concat(user_pseudo_id, '.', cast(ga_session_id as string)) as session_key,
  timestamp_micros(min(event_timestamp)) as session_start,
  timestamp_micros(max(event_timestamp)) as session_end,
  countif(event_name = 'page_view') as pageviews,
  countif(event_name = 'purchase')  as purchases
from events
where ga_session_id is not null
group by session_key;

Two caveats. First, expect small gaps versus the GA4 UI: the interface may estimate distinct counts and applies modeling your SQL does not — the sessionization guide walks through every reason the numbers can differ. Second, a session can span two daily shards (it starts before midnight UTC-adjusted-to-property-time and ends after), so date-bounded queries clip sessions at the edges; widen the suffix range by a day when session integrity matters.

Where this goes next

Ad-hoc SQL over the export answers questions; it does not build a reporting layer. Once the same unnesting and sessionization logic appears in three analysts’ saved queries, it is time to encode it once — staging views, incremental session facts, tested and documented — which is exactly the dbt pattern covered next in this pillar. And if the export is your system of record for revenue reporting, write down which numbers come from the warehouse and which from the UI, because they will not match to the decimal and both can be right.