Querying GA4 data in BigQuery means querying nested arrays. Every useful field — page URLs, session IDs, custom dimensions, product details — lives inside event_params, user_properties, or items. UNNEST is not optional; it’s the cost of entry.
This hub connects the concepts you need to extract GA4 data correctly and efficiently, from the foundational UNNEST mechanics through production dbt patterns.
The Foundation
GA4 Event Data Structure — Why GA4 uses nested arrays instead of flat columns. The event-centric model, the three nested arrays (event_params, user_properties, items), and the four typed value fields. Start here if you’re new to GA4’s BigQuery export.
GA4 BigQuery Query Patterns — The two UNNEST approaches: correlated subqueries for extracting specific values (preserves row count) versus FROM clause UNNEST for analyzing array structure (expands rows). _TABLE_SUFFIX filtering, cost control, and reusable dbt macros.
The UNNEST Mechanics
GA4 Event Params Type Detection — How GA4 auto-assigns parameter values to string_value, int_value, float_value, or double_value. The silent null failure when you query the wrong type. The COALESCE defensive pattern for parameters with uncertain or historically inconsistent types.
GA4 CROSS JOIN versus LEFT JOIN UNNEST — The comma syntax in FROM table, UNNEST(array) is a CROSS JOIN that silently drops rows where the array is empty. When to use LEFT JOIN UNNEST to preserve all events. The rule of thumb: CROSS JOIN for event_params (always populated), LEFT JOIN for items and user_properties (often empty).
Query Recipes
GA4 Engagement Event Query Recipes — Copy-paste SQL for page views with URL parsing, scroll depth analysis with scroll rate calculation, outbound click tracking, file download tracking, video engagement funnels with milestone progress, and session metrics with composite keys.
GA4 Ecommerce Checkout Funnel Pattern — Session-based funnel analysis from view_item through purchase. The COUNT(DISTINCT CASE WHEN ...) pattern, product-level conversion rates, temporal funnel trends, and the distinction between loose and strict funnels.
E-commerce Specifics
GA4 E-commerce Schema in BigQuery — The ecommerce RECORD (transaction-level totals) versus the items REPEATED RECORD (product-level details). The item_params nested array for custom product dimensions. Revenue calculation patterns that avoid double-counting.
GA4 Ecommerce Items UNNEST Pattern — Building a dedicated item-level grain model in dbt. Why items need a separate model from the event-grain pipeline. The intentional Cartesian UNNEST, the ARRAY_LENGTH guard, and the view materialization choice.
Production Patterns
GA4 Parameter Extraction Macro — The dbt macro for correlated subquery extraction: extract_event_param(column, key, value_type). The numeric COALESCE variant. Variable-driven extraction for custom parameters.
GA4 Flattened Events Materialization — When and how to pre-unnest GA4 events into a flat table for cost reduction and BI tool compatibility. The CREATE TABLE pattern, scheduling strategies, and when materialization isn’t worth it.
GA4 dbt Unnesting Layer Architecture — The complete three-layer dbt structure: base model for unnesting with incremental loading, intermediate models for event-specific filtering, mart models for analytics-ready aggregations. Testing strategies for UNNEST correctness.
The Bigger Picture
The unnesting patterns here feed into two downstream pipelines:
- Sessionization: Once events are flattened, session tables add session context through window functions — landing pages, traffic source attribution, conversion flags propagated to every event.
- E-commerce analysis: The items model feeds product performance marts, checkout funnel analysis, and category-level reporting.
Both pipelines depend on correct unnesting. A type error in the base layer (extracting ga_session_id as string_value instead of int_value) cascades through every downstream model. Get the unnesting right once, test it, and build everything else on top.