Unpacking event parameters
Most of what you want to know about a GA4 event, such as its page or campaign, is stored in its parameters. You need to read them before most other GA4 queries work.
How it works
Each GA4 event is one row with an array of key and value records for its parameters. UNNEST turns those elements into rows. A correlated scalar subquery pulls out one value and keeps one row per event.
UNNEST(array)turns each array element into a row.- Read the value from the typed field that matches the parameter:
string_value,int_value,double_valueorfloat_value. - A correlated subquery filtered by key extracts one parameter without multiplying the event.
CREATE TEMP FUNCTION get_param_string(params ANY TYPE, param_name STRING) AS ((SELECT p.value.string_value FROM UNNEST(params) AS p WHERE p.key = param_name LIMIT 1))packages that extraction so you can reuse it within one script.ANY TYPElets BigQuery infer the array type at each call. The argument still needs the key andvalue.string_valuefields that the body uses. The function returns aSTRING, inferred from its expression.
Common mistake
A cross join with UNNEST multiplies each event by its number of parameters. A scalar helper keeps one row per event. It returns NULL when the key is missing or has no string_value. LIMIT 1 picks an arbitrary value when a key repeats, so handle duplicates deliberately if they matter.
Worked example
WITH events AS (
SELECT 101 AS event_id, [
STRUCT('page_location' AS key, STRUCT('/docs' AS string_value) AS value),
STRUCT('campaign' AS key, STRUCT('autumn' AS string_value) AS value)
] AS event_params
)
SELECT event_id,
(SELECT p.value.string_value FROM UNNEST(event_params) AS p WHERE p.key = 'page_location') AS page
FROM eventsThe raw event has two key and value structs inside event_params.
The correlated UNNEST subquery keeps one row for the event and pulls /docs out of the page_location element.