GhazanfarApril 16, 20266 min read

BigQuery’s GA4 export creates a nested data structure where event parameters live in an array called event_params. Each parameter object contains key, string_value, int_value, float_value, and double_value fields. Extracting insights from custom event parameters requires understanding unnesting patterns, particularly when analyzing events across multiple parameters. This guide covers the SQL patterns that transform nested arrays into analyzable rows and shows real-world queries you can adapt to your GA4 data. Learn more in our guide on BigQuery GA4 User ID.

BigQuery GA4 Event Params Array Unnesting: SQL Patterns for Custom Event Analysis

Understanding GA4 Event Params Structure in BigQuery

Every event in BigQuery’s events table contains: Learn more in our guide on BigQuery GA4 vs Firebase.

  • event_name: The type of event (page_view, purchase, view_item, custom_event_name)
  • event_params: An ARRAY of STRUCT containing [{key, value}] pairs
  • user_id: The GA4 user ID
  • event_timestamp: Microseconds since epoch
  • user_properties: Another nested array similar to event_params

The event_params array is the challenge. If a purchase event includes parameters like item_id, item_name, item_price, quantity, and currency, each parameter is a separate row in the array. A single purchase event might have 10-15 parameters. Analyzing this requires unnesting to flatten the structure. Learn more in our guide on GA4 Custom Dimensions BigQuery.

Basic UNNEST Pattern for Single Parameters

The simplest pattern extracts a single parameter value:

SELECT
  event_timestamp,
  user_id,
  event_name,
  ep.key,
  ep.value.string_value
FROM `project.dataset.events_*` as events,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20240115'
  AND event_name = 'purchase'
  AND ep.key = 'transaction_id'
LIMIT 1000;

This query flattens the event_params array using UNNEST, creating one row per parameter. The comma in the FROM clause is equivalent to CROSS JOIN. Each row now has a single ep (event_param) with its key and value. You can access the value through ep.value.string_value (for text), ep.value.int_value (for integers), or ep.value.float_value (for floats).

Key insight: UNNEST creates one output row per array element. If an event has 12 parameters, UNNEST creates 12 rows from that single event. Be careful with aggregation—grouping by event_timestamp and user_id might double-count because multiple rows exist per event.

Extracting Multiple Parameters with Conditional Unnesting

Most queries need multiple parameters from the same event. Use nested UNNESTs and GROUP BY:

SELECT
  event_timestamp,
  user_id,
  MAX(CASE WHEN ep.key = 'transaction_id' THEN ep.value.string_value END) AS transaction_id,
  MAX(CASE WHEN ep.key = 'item_id' THEN ep.value.string_value END) AS item_id,
  MAX(CASE WHEN ep.key = 'purchase_value' THEN ep.value.float_value END) AS purchase_value,
  MAX(CASE WHEN ep.key = 'currency' THEN ep.value.string_value END) AS currency
FROM `project.dataset.events_*` as events,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20240115'
  AND event_name = 'purchase'
GROUP BY event_timestamp, user_id;

This pattern unnests all parameters, then uses CASE statements to extract specific parameters, grouping by event identity. MAX() is a dummy aggregation (since each CASE matches at most one row per event). This returns one row per purchase event with transaction_id, item_id, purchase_value, and currency in separate columns.

BigQuery GA4 Event Params Array Unnesting: SQL Patterns for Custom Event Analysis implementation guide

Advanced Pattern: Multi-Level Unnesting for Item Arrays

GA4’s items parameter is itself an array of objects. Each item in an items array has its own nested parameters:

SELECT
  event_timestamp,
  user_id,
  items.item_id,
  items.item_name,
  items.quantity,
  items.price,
  items.currency
FROM `project.dataset.events_*` as events,
UNNEST(items) AS items
WHERE _TABLE_SUFFIX = '20240115'
  AND event_name = 'purchase';

BigQuery’s GA4 export handles items flattening automatically. Each item becomes its own row. You don’t need complex nested unnesting.

Pattern: Unnesting with Value Type Detection

The tricky part of event_params is that numeric values might be in int_value, float_value, or double_value fields. Use COALESCE to get the right value regardless of type:

SELECT
  event_timestamp,
  user_id,
  ep.key,
  COALESCE(
    ep.value.string_value,
    CAST(ep.value.int_value AS STRING),
    CAST(ep.value.float_value AS STRING),
    CAST(ep.value.double_value AS STRING)
  ) AS param_value
FROM `project.dataset.events_*` as events,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20240115'
LIMIT 1000;

This extracts the correct value field regardless of type. Use COALESCE to return the first non-null value. Now you don’t need separate CASE statements for each value type.

Performance Pattern: Filtering Before Unnesting

Unnesting large arrays creates memory pressure. Filter before unnesting when possible:

SELECT user_id, COUNT(DISTINCT event_timestamp) as purchase_count
FROM `project.dataset.events_*` as events,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20240115'
  AND event_name = 'purchase'
  AND ep.key = 'transaction_id'
GROUP BY user_id;

Filter to specific event_name before unnesting to reduce array processing. If purchases are 2% of all events, you reduce array processing by 98%.

Pattern: Array Aggregation for Reconstruction

After unnesting, you sometimes need to re-aggregate unnested data back into arrays:

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
  user_id,
  ARRAY_AGG(STRUCT(
    ep.key,
    COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) AS value
  )) AS reconstructed_params
FROM `project.dataset.events_*` as events,
UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20240115'
GROUP BY event_date, user_id;

This pattern unnests parameters to individual rows, then uses ARRAY_AGG to rebuild arrays per user per day. Useful for creating summary tables or exporting data to other systems.

Debugging Unnesting Issues

Common problems when unnesting:

  • Null arrays: If event_params is null or empty, UNNEST produces zero rows. Use LEFT JOIN UNNEST instead of implicit CROSS JOIN to preserve events with no parameters.
  • Duplicate rows: If you UNNEST multiple arrays from the same event, the Cartesian product doubles rows. Use GROUP BY or separate queries.
  • Type mismatches: Check if the value is in string_value, int_value, or float_value before selecting. Use COALESCE instead.
  • Ordering lost: UNNEST doesn’t preserve array order. Add a position parameter if needed: UNNEST(event_params) AS ep WITH OFFSET AS pos.

Unnesting is fundamental to GA4 analysis in BigQuery. These patterns handle 95% of custom event queries. Save them as templates and customize the parameter names and event types for your specific events.

Leave a Comment