---
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-29"
---

# 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/1-gzj97d.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](/product/spreadsheets) 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).

## 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)

## 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)
- [Google Sheets](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md
- [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
