How to unnest GA4 event_params in BigQuery: page_location, ga_session_id and items

Which value field to read

Pick the wrong field and the column is NULL, with no error: reading ga_session_id from string_value returned NULL on every row. This query shows which field each key uses on one day of the sample:

SELECT
  p.key,
  COUNT(*) AS occurrences,
  COUNTIF(p.value.string_value IS NOT NULL) AS as_string,
  COUNTIF(p.value.int_value IS NOT NULL) AS as_int
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`,
  UNNEST(event_params) AS p
GROUP BY p.key
ORDER BY occurrences DESC;

Run it on your own property once and keep the result: it is the map for every query you write later.

A parameter can arrive as two types

session_engaged came as a string 17,890 times and as an integer 2,200 times on the same day. A query that reads only string_value misses 2,200 values. Read both:

(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING))
 FROM UNNEST(event_params)
 WHERE key = 'session_engaged') AS session_engaged

Custom parameters do this often when different pages or apps send them differently. The type check above finds them.

Sessions: ga_session_id is not unique on its own

ga_session_id is a timestamp of when the session started, so two users can share one. A session is the pair user_pseudo_id and ga_session_id:

COUNT(DISTINCT CONCAT(
  user_pseudo_id, '-',
  CAST((SELECT value.int_value FROM UNNEST(event_params)
        WHERE key = 'ga_session_id') AS STRING)
)) AS sessions

A full sessions-per-day query, with its result, is in BigQuery query examples.

What a plain UNNEST does to row counts

The other way to read the array, joining it to the table, multiplies rows:

• UNNEST(event_params) gives one row per parameter, about ten per event. Any COUNT(*) or SUM after it is ten times too large. Use it only to explore keys, as in the type query above.
• , UNNEST(items) drops every event without items. 19,711 of the 22,096 events had none, and they disappear. Use LEFT JOIN UNNEST(items) when the other events must stay.

The items array

Ecommerce events carry products in items, which is itself an array of records. Unnest it when the question is about products, and keep event-level totals out of that query:

SELECT
  i.item_name,
  SUM(i.quantity) AS quantity,
  ROUND(SUM(i.item_revenue_in_usd), 2) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'purchase'
GROUP BY i.item_name;

ecommerce.purchase_revenue_in_usd is an order total. Summed after unnesting items, it is counted once per product line. The result of this query and the fan-out effect are in query examples and JOIN and UNION.

Keep the cost down

• Always filter _TABLE_SUFFIX on events_*. The wildcard tables guide measures the difference.
• Each subquery over event_params reads the whole array column. Ten parameter columns cost about the same as one; the column is read once. What drives the cost is the number of days.
• Flatten once. For daily reporting, a scheduled query that turns yesterday’s events into a table with one column per parameter you use is cheaper and simpler than unnesting in every report. The export itself is covered in the GA4 to BigQuery guide.

One flat table, one definition

Every team that uses the GA4 export ends up writing the same unnesting: page location, session ID, source and medium, the session key. Copied into each report, it drifts: one copy reads session_engaged as a string only, another forgets the user in the session key. In OWOX Data Marts that flattening query is published once as a Data Mart, with a description per column, and reports in Google Sheets or dashboards read the flat result. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data. Arrays in general are in the array functions guide.

Google BigQueryGA4 BigQuery Export Schema: The Tables, the Fields and How Dates WorkOctober 16, 2026→Google BigQueryHow to Query GA4 Event Data in BigQuery: Sessions, Sources and RevenueOctober 16, 2026→Google BigQueryBigQuery SQL: Syntax, Clause Order and Tested ExamplesOctober 16, 2026→

See all articles →