When you export Google Analytics 4 (GA4) data to BigQuery, each row represents an event, but many event attributes are stored in nested and repeated fields. Understanding how to work with this structure is key to unlocking the full value of your analytics data.
The GA4 Export Schema
Here's a simplified view of the GA4 export schema:
- event_date (STRING)
- event_name (STRING)
- event_params (REPEATED RECORD)
- key (STRING)
- value (RECORD with multiple types: string_value, int_value, double_value)
- user_properties (REPEATED RECORD)
- key (STRING)
- value (RECORD with multiple types: string_value, int_value, double_value)
1. Basic Event Query
To start, you can run a simple query to see the top events for a given day:
SELECT
event_date,
event_name,
COUNT(*) AS event_count
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20251001' -- filter for a single export date
GROUP BY event_date, event_name
ORDER BY event_count DESC;
This query is straightforward because it doesn't yet involve nested fields.
2. Extracting Event Parameters (Nested and Repeated Fields)
The challenge begins when you need to query event_params, since each event can include dozens of parameters stored as key-value pairs.
Here's how to get the page_location and page_title parameters:
SELECT
event_date,
event_name,
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = "page_location") AS page_location,
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = "page_title") AS page_title
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20251001'
AND event_name = "page_view"
LIMIT 100;
Here, UNNEST(event_params) function flattens the array of parameters, and each subquery extracts the value that matches the given key.
3. Flattening Parameters into Rows
Sometimes you may want every parameter to appear in its own row. In that case, you can unnest the entire array:
SELECT
event_name,
ep.key AS param_name,
ep.value.string_value AS string_val,
ep.value.int_value AS int_val,
ep.value.double_value AS double_val
FROM `myproject.analytics_123456789.events_*`,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20251001'
AND event_name = "purchase"
LIMIT 100;
This expands each event into multiple rows with one per parameter.
4. Transforming Parameters into Columns
If you prefer a wider format with parameters as columns, you can use conditional aggregation:
SELECT
event_name,
MAX(IF(ep.key = "transaction_id", ep.value.string_value, NULL)) AS transaction_id,
MAX(IF(ep.key = "value", ep.value.double_value, NULL)) AS purchase_value,
MAX(IF(ep.key = "currency", ep.value.string_value, NULL)) AS currency
FROM `myproject.analytics_123456789.events_*`,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20251001'
AND event_name = "purchase"
GROUP BY event_name
LIMIT 100;
Now each event row has transaction_id, purchase_value, and currency columns extracted from the nested structure.
5. Querying User Properties
User-level attributes (like subscription_plan or loyalty_status) are also stored as repeated nested records in user_properties.
SELECT
user_pseudo_id,
(SELECT value.string_value
FROM UNNEST(user_properties)
WHERE key = "subscription_plan") AS subscription_plan,
(SELECT value.string_value
FROM UNNEST(user_properties)
WHERE key = "loyalty_status") AS loyalty_status
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20251001'
LIMIT 100;
6. Why This Matters
If GA4 stored every event parameter as its own column, the dataset would contain hundreds of fields and be much harder to manage. BigQuery's nested and repeated structure keeps the schema flexible and storage-efficient. You can query only the parameters you need without scanning unnecessarily large tables.
Working with GA4 BigQuery exports can be challenging, especially when dealing with event_params and user_properties. Extracting, flattening, and transforming this data into a format that's ready for reporting requires both technical skill and practical experience with BigQuery's advanced SQL functions.
Ready to Get Started?
Calibrate Analytics helps organizations make sense of GA4 exports, from building efficient queries and reusable datasets to designing scalable reporting solutions in Looker Studio or other BI platforms.
If you want to save time, reduce query costs, and unlock the full value of your GA4 data, our team can help you get there.
