---
title: "Unnest GA4 event_params in BigQuery: tested patterns"
canonical: "https://www.owox.com/blog/articles/unnest-ga4-event-parameters-in-bigquery"
updated: "2026-10-21"
---

# How to unnest GA4 event\_params in BigQuery: page\_location, ga\_session\_id and items

In the GA4 BigQuery Export, values like page\_location and ga\_session\_id sit inside the event\_params array, one key and four possible value fields per parameter. This guide shows the subquery pattern that reads one parameter per column, which value field each common key uses, a parameter that arrives as two types, and how a plain UNNEST turns 22,096 events into 215,602 rows or drops half of them. Every query runs on Google's public GA4 sample.

[Google BigQuery](/blog/topics/bigquery) · Updated October 16, 2026 · [Alyona Samovar](/team/alyona-samovar) · 4 min read

![How to unnest GA4 event\_params in BigQuery: page\_location, ga\_session\_id and items](https://cdn.owox.com/www/content-marketing-article/unnest-ga4-event-parameters-in-bigquery/thumbnail-20261016124445.png/public)

In the GA4 BigQuery Export, an event’s details are not columns. They sit in **`event_params`**, an array of key-value pairs, and each value is stored in one of four fields: `string_value`, `int_value`, `float_value` or `double_value`. To read one parameter as a column, use a subquery over the array:

```
SELECT
  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 `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`
WHERE event_name = 'page_view';
```

| event\_name | page\_location | ga\_session\_id |
| --- | --- | --- |
| page\_view | [https://shop.googlemerchandisestore.com/Google+Redesign/Apparel/Google+Dino+Game+Tee](https://shop.googlemerchandisestore.com/Google+Redesign/Apparel/Google+Dino+Game+Tee) | 3209612510 |
| page\_view | [https://shop.googlemerchandisestore.com/](https://shop.googlemerchandisestore.com/) | 7473279052 |

One row per event, one column per parameter, and nothing multiplied. Change the key and the value field for any other parameter.

**_Note:_** _Originally published in August 2024. Fully rewritten in October 2026: every query below was run on Google’s public GA4 sample, `bigquery-public-data.ga4_obfuscated_sample_ecommerce`, on 16 October 2026._

## 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;
```

| key | occurrences | as\_string | as\_int |
| --- | --- | --- | --- |
| page\_location | 22096 | 22096 | 0 |
| ga\_session\_id | 22096 | 0 | 22096 |
| ga\_session\_number | 22096 | 0 | 22096 |
| page\_title | 21985 | 21982 | 0 |
| engaged\_session\_event | 20776 | 0 | 20776 |
| session\_engaged | 20090 | 17890 | 2200 |
| page\_referrer | 17241 | 5795 | 0 |
| engagement\_time\_msec | 12965 | 0 | 12965 |
| source, medium, campaign | 6660 each | 6660 | 0 |

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](/blog/articles/bigquery-query-examples).

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## What a plain UNNEST does to row counts

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

| Query on one day (22,096 events) | Rows |
| --- | --- |
| `FROM events_20210101` | 22,096 |
| `FROM events_20210101, UNNEST(event_params)` | 215,602 |
| `FROM events_20210101, UNNEST(items)` | 18,716 |
| `FROM events_20210101 LEFT JOIN UNNEST(items)` | 38,427 |

*   **`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](/blog/articles/bigquery-query-examples) and [JOIN and UNION](/blog/articles/bigquery-join-and-union-difference).

## Keep the cost down

*   **Always filter `_TABLE_SUFFIX`** on `events_*`. The [wildcard tables guide](/blog/articles/bigquery-wildcard-tables) 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](/blog/articles/google-analytics-4-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](https://www.owox.com/app-signup) 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](/blog/articles/bigquery-array-functions).

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I count GA4 sessions in BigQuery?

A session is the pair of user\_pseudo\_id and the ga\_session\_id parameter: COUNT(DISTINCT CONCAT(user\_pseudo\_id, '-', CAST(ga\_session\_id AS STRING))). ga\_session\_id alone is not unique, because two users can start a session at the same moment.

How do I get page\_location from the GA4 export in BigQuery?

With a subquery over event\_params: (SELECT value.string\_value FROM UNNEST(event\_params) WHERE key = 'page\_location') AS page\_location. It returns one value per event and does not multiply rows.

What are event\_params in the GA4 BigQuery Export?

An array of key-value pairs on each event. Each value sits in one of four fields, string\_value, int\_value, float\_value or double\_value, depending on its type; page\_location is a string and ga\_session\_id an integer.

Why does my GA4 parameter come back NULL in BigQuery?

Usually the wrong value field: ga\_session\_id is stored in int\_value, so reading string\_value returns NULL with no error. Some parameters arrive in two types: in Google's sample, session\_engaged was a string 17,890 times and an integer 2,200 times in one day. Read both with COALESCE.

Why does UNNEST multiply rows in GA4 queries?

Joining the table to UNNEST(event\_params) creates one row per parameter: 22,096 events became 215,602 rows in our test, so counts and sums after it are about ten times too large. A comma join to UNNEST(items) also drops events with no items; use LEFT JOIN UNNEST(items) to keep them.

## Who wrote this

![Alyona Samovar](https://cdn.owox.com/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public)

[Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.

[Google BigQuery](/blog/topics/bigquery)

## Related articles

[Google BigQuery · GA4 BigQuery Export schema: the tables, the fields and how dates work · October 16, 2026](/blog/articles/ga4-bigquery-export-event-table-schema-and-dates)

[Google BigQuery · How to query GA4 event data in BigQuery: sessions, sources and revenue · October 16, 2026](/blog/articles/query-ga4-event-data-in-bigquery)

[Google BigQuery · BigQuery SQL: syntax, clause order and tested examples · October 16, 2026](/blog/articles/bigquery-sql-guide)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google BigQuery](https://www.owox.com/blog/topics/bigquery) — /blog/topics/bigquery.md
- [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md
- [BigQuery query examples](https://www.owox.com/blog/articles/bigquery-query-examples) — /blog/articles/bigquery-query-examples.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [JOIN and UNION](https://www.owox.com/blog/articles/bigquery-join-and-union-difference) — /blog/articles/bigquery-join-and-union-difference.md
- [wildcard tables guide](https://www.owox.com/blog/articles/bigquery-wildcard-tables) — /blog/articles/bigquery-wildcard-tables.md
- [GA4 to BigQuery guide](https://www.owox.com/blog/articles/google-analytics-4-to-bigquery-guide) — /blog/articles/google-analytics-4-to-bigquery-guide.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [array functions guide](https://www.owox.com/blog/articles/bigquery-array-functions) — /blog/articles/bigquery-array-functions.md
- [Google BigQuery · GA4 BigQuery Export schema: the tables, the fields and how dates work · October…](https://www.owox.com/blog/articles/ga4-bigquery-export-event-table-schema-and-dates) — /blog/articles/ga4-bigquery-export-event-table-schema-and-dates.md
- [Google BigQuery · How to query GA4 event data in BigQuery: sessions, sources and revenue · October…](https://www.owox.com/blog/articles/query-ga4-event-data-in-bigquery) — /blog/articles/query-ga4-event-data-in-bigquery.md
- [Google BigQuery · BigQuery SQL: syntax, clause order and tested examples · October 16, 2026](https://www.owox.com/blog/articles/bigquery-sql-guide) — /blog/articles/bigquery-sql-guide.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md