skip to content

In the GA4 BigQuery export, how do you read a named value out of event_params?

level: middleimportance: must knowfreq 60%

answer

  1. parameters are an array, not columns
  2. key plus a typed value struct
  3. only one value sub-field is populated
  4. scalar subquery keeps one row per event
  5. filter the table suffix or scan everything

basics

~20 s

event_params is a repeated record of a key plus a value struct holding string_value, int_value, float_value and double_value. You pick the matching key with a scalar subquery over UNNEST and read whichever sub-field the parameter's type populates.

solid answer

~40 s

In the export, each row is one event and `event_params` is an `ARRAY<STRUCT<key STRING, value STRUCT<string_value, int_value, float_value, double_value>>>`. To get one parameter you write a scalar subquery: `(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')`. Repeat that per parameter you need. The classic bug is reading the wrong sub-field — only one of the four is populated for a given parameter, so asking for `string_value` on a numeric parameter like `ga_session_id` returns NULL for every row rather than an error. Two other habits matter: cross-joining `UNNEST(event_params)` instead of using a scalar subquery multiplies your row count by the number of parameters, and querying the `events_*` wildcard without a `_TABLE_SUFFIX` filter scans the whole history and is the usual cause of a surprise BigQuery bill.

code

sql · 9 lines
sql
SELECT
  event_date,
  user_pseudo_id,
  event_name,
  (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
FROM `my_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240107'
  AND event_name = 'page_view';

go deeper

for a junior

Know that the export gives one row per event and that parameters live in a nested array, so a plain SELECT will not show page_location as a column. Being able to run a provided UNNEST snippet is enough here.

for a middle

Write the scalar-subquery extraction from memory and explain why the value struct has four typed sub-fields and only one is filled. Know the difference between the subquery and the cross join, and what the cross join does to row counts.

for a senior

Show cost and maintenance judgment: suffix-filtered wildcard queries, a single flattening model everyone builds on rather than ad-hoc UNNEST in every query, and a plan for a parameter whose type drifted between web and app implementations.

for a principal

Decide whether the raw export is the interface your organisation should query at all, or whether a curated event model with typed columns and tests sits in front of it, and who pays for the scan when analysts query the raw tables directly.

## The shape of the export When you link a GA4 property to BigQuery, GA4 writes into a dataset named `analytics_<property_id>`. Inside it, each day of data lands in a table named `events_YYYYMMDD`, and (if streaming export is enabled) the current day accumulates in `events_intraday_YYYYMMDD`. One row is one event. The row carries scalar columns such as `event_name`, `event_timestamp`, `event_date`, `user_pseudo_id` and `user_id`, nested records such as `device`, `geo` and `traffic_source`, and two repeated records that hold the interesting part: `event_params` and `user_properties`. ## Why the parameters are nested GA4 events do not share a schema — a `purchase` and a `scroll` carry entirely different parameters — so the export cannot give each parameter its own column. Instead it stores them as an array of key/value pairs. In BigQuery terms: ```sql event_params ARRAY<STRUCT< key STRING, value STRUCT< string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 > >> ``` Because a parameter can be text or a number, the value is itself a struct with one sub-field per type, and **only the sub-field matching the parameter's type is populated**; the rest are NULL for that element. ## The extraction pattern The idiomatic read is a correlated scalar subquery over `UNNEST`: ```sql SELECT event_timestamp, (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 FROM `my_project.analytics_123456789.events_20240115` WHERE event_name = 'page_view'; ``` `UNNEST(event_params)` inside the subquery flattens only that row's array, the `WHERE key = ...` picks one element, and the subquery yields a single scalar — so the outer row count is unchanged. That last property is what makes this pattern preferable to the alternative below. ## The three mistakes people make **Reading the wrong value sub-field.** Ask for `value.string_value` on a numeric parameter and you get NULL on every row, with no error and no warning. `ga_session_id`, `ga_session_number` and `engagement_time_msec` are integers; `page_location`, `page_title` and `source` are strings; a monetary `value` parameter typically lands in `double_value`. A defensive idiom is `COALESCE(CAST(value.int_value AS STRING), value.string_value)` when you are unsure or when a parameter's type has changed over time — which happens when web and app implementations disagree. **Cross-joining instead of sub-querying.** Writing `FROM events_20240115, UNNEST(event_params) AS p` produces one output row per *parameter*, not per event. If you then `COUNT(*)`, your event counts are inflated by however many parameters each event happened to carry. The cross join is legitimate when you genuinely want a long key/value table — for a generic parameter dictionary, or to pivot with `MAX(IF(key = 'x', ...))` grouped back to the event — but it must be a deliberate choice. **Scanning the whole history.** The daily tables are separate tables, not partitions of one table, so multi-day queries use the wildcard `events_*` with a `_TABLE_SUFFIX` predicate: `WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'`. Omit that filter and BigQuery scans every daily table in the dataset — on a busy property that is the classic runaway-cost incident. Note also that the wildcard `events_*` matches `events_intraday_*` tables too, so a suffix filter is what keeps provisional intraday rows out of a query meant to read finalized days. ## User properties and items `user_properties` has the same key/value shape but with a `set_timestamp_micros` alongside the value, and it holds user-scoped attributes rather than event-scoped ones — the values sent via the SDK as user properties, not the same thing as event parameters even when the name looks similar. Ecommerce events additionally carry an `items` array of structs (`item_id`, `item_name`, `price`, `quantity`, …) which you flatten with a genuine cross join because you usually do want one row per line item. ## Practical modelling advice Because every consumer needs the same handful of parameters, teams almost always build one flattening layer — a view or a model that lifts `page_location`, `ga_session_id`, `event_name`, `user_pseudo_id` and the business-specific parameters into real typed columns — and let everyone downstream query that instead of writing `UNNEST` subqueries by hand. Doing it once also gives you a single place to handle a parameter whose type changed and a single place to encode which sub-field each parameter lives in.

  • A numeric parameter returns NULL for every row. What is the first thing you check?
    Which sub-field you selected. The value struct has string_value, int_value, float_value and double_value, and only the one matching the parameter's type is populated — the others are NULL with no error raised. Confirm by selecting the whole value struct for a few rows, then read the populated field. If web and app disagree on the type, coalesce across two sub-fields.
  • When is cross-joining UNNEST(event_params) the right call rather than a scalar subquery?
    When you actually want one row per key/value pair: building a parameter dictionary, auditing which parameters a new event sends, or pivoting many parameters at once with MAX(IF(key = ...)) grouped back to the event key. The rule is that a cross join changes the grain, so it is fine when you intend to change the grain and a bug when you do not.
  • How do you keep a query over the GA4 export from scanning the whole dataset?
    Query the events_* wildcard with a _TABLE_SUFFIX predicate bounding the date range, and select only the columns you need — BigQuery is columnar, so the nested event_params column is expensive to touch. The daily tables are separate tables rather than partitions of one, so a WHERE clause on event_date alone will not prune them.

saying these in an interview costs you the question

  • Expects each event parameter to be its own column
  • Reads string_value for a numeric parameter and blames the export
  • Cross joins UNNEST and then counts rows as events
  • Queries events_* with no _TABLE_SUFFIX filter
  • Confuses user_properties with event_params

context

resolving links…