GA4 event_params in BigQuery
GA4 stores event parameters as a repeated STRUCT of key and typed values. A scalar subquery reads one parameter and keeps every event. A join on UNNEST drops events without that key and multiplies the rest.
Practice this in free lessons on learn.querylab.io: Unpacking event parameters and 1 more.
In the GA4 export each event has an event_params column, an array of structs with a key and a value. value has four fields, string_value, int_value, float_value and double_value, and a parameter normally fills one of them. To read a parameter as a column, use a scalar subquery: (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'). It returns one value per event, or NULL if the event doesn’t have that key. user_properties works the same way.
Read a parameter with a scalar subquery
Each subquery looks inside the current row’s array. The number of rows doesn’t change, and an event with no campaign gets a NULL:
WITH events AS ( SELECT 1 AS event_id, [ STRUCT('page_location' AS key, STRUCT('/home' AS string_value, CAST(NULL AS INT64) AS int_value) AS value), ('ga_session_id', (NULL, 1700))] AS event_params UNION ALL SELECT 2, [('page_location', ('/shoes', NULL)), ('ga_session_id', (NULL, 1700)), ('campaign', ('spring', NULL))] ) SELECT event_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id FROM events ORDER BY event_id
| Row | event_id | page | campaign | session_id |
|---|---|---|---|---|
| 1 | 1 | /home | null | 1700 |
| 2 | 2 | /shoes | spring | 1700 |
In Querylab.io the Params tab lists the keys in your data and inserts this subquery with the right value field.
A join changes the rows
Writing FROM events, UNNEST(event_params) AS p makes one row per parameter. A filter on p.key then keeps only the events that have the key, so event 1 disappears here:
WITH events AS ( SELECT 1 AS event_id, [ STRUCT('page_location' AS key, STRUCT('/home' AS string_value) AS value)] AS event_params UNION ALL SELECT 2, [('page_location', STRUCT('/shoes')), ('campaign', STRUCT('spring'))] ) SELECT e.event_id, p.value.string_value AS campaign FROM events AS e, UNNEST(e.event_params) AS p WHERE p.key = 'campaign'
| Row | event_id | campaign |
|---|---|---|
| 1 | 2 | spring |
Without a filter, every count is a count of parameters. Two events become five rows:
WITH events AS ( SELECT 1 AS event_id, [ STRUCT('page_location' AS key, STRUCT('/home' AS string_value) AS value), ('page_title', STRUCT('Home')), ('campaign', STRUCT('spring'))] AS event_params UNION ALL SELECT 2, [('page_location', STRUCT('/shoes')), ('page_title', STRUCT('Shoes'))] ) SELECT COUNT(*) AS rows_after_join, COUNT(DISTINCT e.event_id) AS events FROM events AS e, UNNEST(e.event_params) AS p
| Row | rows_after_join | events |
|---|---|---|
| 1 | 5 | 2 |
The join is the right tool when you want to work across all parameters, such as listing keys. To read specific parameters, use the subquery. WHERE p.key = 'a' AND p.key = 'b' can never match, because each joined row has one key. UNNEST has the rules for both forms.
Pick the right value field
Reading the wrong field gives a NULL with no error. ga_session_id is an integer, so string_value is empty:
WITH events AS ( SELECT [STRUCT('ga_session_id' AS key, STRUCT(CAST(NULL AS STRING) AS string_value, 1700 AS int_value) AS value)] AS event_params ) SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS wrong_field, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS right_field FROM events
| Row | wrong_field | right_field |
|---|---|---|
| 1 | null | 1700 |
The types used in Google’s sample for one week:
| Parameter | Field |
|---|---|
page_location | string_value |
page_title | string_value |
ga_session_id | int_value |
ga_session_number | int_value |
engagement_time_msec | int_value |
entrances | int_value |
session_engaged | string_value (198,352), int_value (24,555) |
value | double_value (89), int_value (49) |
The last two rows are the problem: one parameter can arrive in different fields. Across the whole sample, value is an int_value on all 1,832 events where the amount has no decimals, and a double_value on the 3,410 where it does. Find out what your property does before you trust a field:
WITH events AS ( SELECT [ STRUCT('page_location' AS key, STRUCT('/home' AS string_value, CAST(NULL AS INT64) AS int_value) AS value), ('session_engaged', ('1', NULL))] AS event_params UNION ALL SELECT [('page_location', ('/shoes', NULL)), ('session_engaged', (NULL, 1))] ) SELECT p.key, COUNTIF(p.value.string_value IS NOT NULL) AS as_string, COUNTIF(p.value.int_value IS NOT NULL) AS as_int FROM events AS e, UNNEST(e.event_params) AS p GROUP BY p.key ORDER BY p.key
| Row | key | as_string | as_int |
|---|---|---|---|
| 1 | page_location | 2 | 0 |
| 2 | session_engaged | 1 | 1 |
When a parameter varies, read all the forms. Reading only double_value here loses two of the three amounts:
WITH events AS ( SELECT * FROM UNNEST([ STRUCT(1 AS event_id, CAST(NULL AS STRING) AS string_value, 10 AS int_value, CAST(NULL AS FLOAT64) AS double_value), (2, NULL, NULL, 19.5), (3, '7.25', NULL, NULL) ]) ) SELECT event_id, double_value AS double_only, COALESCE(double_value, int_value, SAFE_CAST(string_value AS FLOAT64)) AS any_form FROM events ORDER BY event_id
| Row | event_id | double_only | any_form |
|---|---|---|---|
| 1 | 1 | null | 10.0 |
| 2 | 2 | 19.5 | 19.5 |
| 3 | 3 | null | 7.25 |
A repeated key breaks the subquery
A scalar subquery must return at most one row. If an event has the same key twice, the whole query fails:
WITH events AS ( SELECT [ STRUCT('item' AS key, STRUCT('shoe' AS string_value) AS value), ('item', STRUCT('hat'))] AS event_params ) SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item') AS item FROM events
Scalar subquery produced more than one element
We found no repeated keys in a week of Google’s sample, 245,209 events, and normal collection doesn’t produce them. Data you import or send yourself can. LIMIT 1 inside the subquery stops the failure, at the price of picking one value arbitrarily.
A helper function
A query with ten parameters repeats the same subquery ten times. A temp function takes the array and a name. ANY TYPE lets it accept the event_params struct without spelling out its type:
CREATE TEMP FUNCTION get_param(params ANY TYPE, name STRING) AS ( (SELECT p.value.string_value FROM UNNEST(params) AS p WHERE p.key = name LIMIT 1) ); WITH events AS ( SELECT 1 AS event_id, [ STRUCT('page_location' AS key, STRUCT('/home' AS string_value) AS value), ('campaign', STRUCT('spring'))] AS event_params UNION ALL SELECT 2, [('page_location', STRUCT('/shoes'))] ) SELECT event_id, get_param(event_params, 'page_location') AS page, get_param(event_params, 'campaign') AS campaign FROM events ORDER BY event_id
| Row | event_id | page | campaign |
|---|---|---|---|
| 1 | 1 | /home | spring |
| 2 | 2 | /shoes | null |
A temp function lives for one script. For a shared version, use a persistent UDF.
What a parameter costs
Every parameter lives in the same nested columns, so asking for one key still reads key and the value field for all parameters of all events. One integer parameter reads 43.5 MiB for the week, against 3.0 MiB for a top-level column:
SELECT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
This query will process 43.5 MiB when run.
The whole array reads 70.8 MiB:
SELECT event_params FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
This query will process 70.8 MiB when run.
Reading a second integer parameter adds nothing: with ga_session_number as well, the estimate is still 43.5 MiB, because it uses the same nested columns. If a report reads a few parameters every day, extract them once into flat columns in your own table.