After linking GA4 to BigQuery, Google Analytics 4 exports data to BigQuery using a nested structure. Each row in the export corresponds to a single GA4 event, but each event can contain multiple event parameters, user properties, and, in e-commerce implementations, multiple items. That structure is efficient for storage, but it also means you need to know how to unnest the data before you can query it properly.
Contents
Structure of GA4 tables in BigQuery
As mentioned above, GA4 tables in BigQuery use a nested schema.
Each row represents one GA4 event, but some fields inside that row are repeated records rather than simple flat values. In practice, this means that a single event can contain an array of event parameters, an array of user properties, and an array of e-commerce items. Instead of creating a new row for each of those values, BigQuery stores them inside repeated fields within the event row itself.
To understand the structure more clearly, the easiest thing to do is open BigQuery, go to your dataset, and inspect the schema of one of the exported tables. There you will see fields such as event_params, user_properties, and items defined as repeated RECORD fields.
You can also spot these repeated fields in the preview tab, where some columns contain multiple values inside the same event row.
If you try to query one of these nested fields directly as if it were a normal scalar field, BigQuery will return an error. To work with those values correctly, you need to use UNNEST.
Using UNNEST to flatten GA4 tables in BigQuery
BigQuery’s UNNEST function allows you to flatten a repeated field and extract the values stored inside it.
When you use UNNEST, the query returns a flattened result in which each element of the repeated field can be accessed like a normal row. This is what makes it possible to work with event parameters, user properties, or item data in SQL.
There are two common ways to use it. One option is to use UNNEST inside SELECT when you want to extract a specific parameter from a repeated field such as event_params. For example:
SELECT
DISTINCT (
SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location'
) AS page
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE event_name = 'page_view'This approach is useful when you want to pull out a specific value from the repeated field without flattening the whole array into multiple rows.
The other option is to use UNNEST in the FROM clause. This is especially useful when working with repeated records such as e-commerce items:
SELECT
item.item_name,
SUM(item.quantity) AS quantity,
SUM(item.item_revenue) AS item_revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202101*`,
UNNEST(items) AS item
WHERE event_name = 'purchase'
GROUP BY 1
ORDER BY 2 DESCIn this case, UNNEST expands the repeated field into multiple rows, which makes it easier to aggregate or analyze the nested records directly.
One important difference is that unnesting from the FROM clause usually generates more rows than extracting a single value from SELECT, so it can also increase the amount of data processed. That is why the best option depends on what you are trying to do.
Why this matters
Understanding GA4’s nested structure in BigQuery is essential if you want to work with the export properly.
At first, the schema can feel less intuitive than a flat table, especially if you are used to more traditional analytics exports. But once you understand how repeated fields work and how to use UNNEST, querying GA4 data becomes much easier.
In the end, the export structure is one of the main differences between simply looking at reports in GA4 and working with the raw data in BigQuery. Once you get comfortable with nested and repeated fields, you can analyze GA4 data with far more flexibility.
