GA4's BigQuery Export Schema Has Surprises Nobody Warns You About

The first time I queried a client's GA4 BigQuery export directly, I wrote a query that assumed event_params was a flat set of columns, like a normal table. It isn't. It's a repeated record field, an array of key-value structs, and a naive query against it either returns nothing or silently returns wrong results if you don't UNNEST it correctly. This isn't a bug, it's documented, but it's exactly the kind of thing that burns someone coming from a flat-table mental model, which is most analysts.

The schema quirks that actually trip people up

-- Wrong: this silently fails to get the value you expect
SELECT event_name, event_params.value
FROM `project.analytics_XXX.events_*`

-- Right: event_params is repeated, needs unnesting
SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location
FROM `project.analytics_XXX.events_*`

• event_params and user_properties are both repeated RECORD fields, not columns — every parameter query needs an UNNEST or subquery.
• Each value is typed into one of four possible value columns (string_value, int_value, float_value, double_value), and picking the wrong one returns null silently instead of erroring.
• The table is sharded by day (events_YYYYMMDD) with a separate events_intraday_ table for the current day that gets overwritten, not appended, as the day progresses.
• User pseudo IDs reset on certain conditions (app reinstall, cookie clear) in ways that make longitudinal user-level analysis less reliable than it initially appears.

The GA4 export schema isn't badly designed, it's designed for a nested event model that most SQL habits were built against flat tables, and that mismatch is where the surprises live.

What actually helped

I keep a small library of validated UNNEST query templates for the parameters I pull most often — purchase values, campaign params, custom event parameters — and I never write a fresh query against event_params from scratch anymore. Every analyst I've onboarded onto this export hits the same wall in the same place, and the fix isn't cleverness, it's just having the templates ready before the surprise shows up in a stakeholder-facing number.