Docs/Google Analytics 4/event_params
Google Analytics 4

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:

three parameters as columnsOpen in Querylab.io
12345678910111213
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
Result in BigQuery
Rowevent_idpagecampaignsession_id
11/homenull1700
22/shoesspring1700
2 rows

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:

a join drops events without the keyOpen in Querylab.io
12345678
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'
Result in BigQuery
Rowevent_idcampaign
12spring
1 row

Without a filter, every count is a count of parameters. Two events become five rows:

a join multiplies rowsOpen in Querylab.io
12345678
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
Result in BigQuery
Rowrows_after_joinevents
152
1 row

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:

string_value on an integer parameterOpen in Querylab.io
1234567
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
Result in BigQuery
Rowwrong_fieldright_field
1null1700
1 row

The types used in Google’s sample for one week:

ParameterField
page_locationstring_value
page_titlestring_value
ga_session_idint_value
ga_session_numberint_value
engagement_time_msecint_value
entrancesint_value
session_engagedstring_value (198,352), int_value (24,555)
valuedouble_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:

which fields each key usesOpen in Querylab.io
12345678910111213
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
Result in BigQuery
Rowkeyas_stringas_int
1page_location20
2session_engaged11
2 rows

When a parameter varies, read all the forms. Reading only double_value here loses two of the three amounts:

one parameter in three fieldsOpen in Querylab.io
12345678910111213
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
Result in BigQuery
Rowevent_iddouble_onlyany_form
11null10.0
2219.519.5
33null7.25
3 rows

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:

two parameters with the same keyBigQuery rejects this query
1234567
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
Error in BigQuery

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:

a helper for string parametersOpen in Querylab.io
123456789101112
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
Result in BigQuery
Rowevent_idpagecampaign
11/homespring
22/shoesnull
2 rows

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:

one parameter
123
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'
BigQuery dry run

This query will process 43.5 MiB when run.

The whole array reads 70.8 MiB:

all parameters
123
SELECT event_params
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
BigQuery dry run

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.

Open the playground. It is the real app on public datasets and needs no sign-in.