Practical Guide: Building a Flat GA4 BigQuery Events Table in dbt

The GA4 BigQuery export stores event parameters as ARRAY<STRUCT<key, value>>. Every query requires UNNEST with conditional logic — verbose, error-prone, and expensive (scanning the full nested structure even when you need one parameter).

Create models/staging/stg_ga4_events.sql to flatten the nested structure: SELECT event_date, event_timestamp, event_name, user_pseudo_id, then extract each parameter with (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, and so on for page_title (string_value), ga_session_id and engagement_time_msec (both int_value).

Include device.category, geo.country, traffic_source.medium, and traffic_source.source as native columns — bearing in mind that traffic_source holds the user's first-touch acquisition source, not the session's (session-level source comes from collected_traffic_source or session_traffic_source_last_click). Set materialisation to incremental with merge strategy on a generated surrogate key, partitioned by event_date (the export stores it as a 'YYYYMMDD' STRING, so convert it with PARSE_DATE('%Y%m%d', event_date)), clustered by event_name and user_pseudo_id.

Add tests: unique on surrogate_key, not_null on event_timestamp and user_pseudo_id, accepted_values on event_name. Run 'dbt build' daily.

Your query costs drop 80-95% because you're scanning typed columns instead of UNNEST-ing arrays on every query. Every analyst on your team now writes simple SELECT statements instead of nested subqueries.

Build additional intermediate models on top: int_sessions (group events by user_pseudo_id and ga_session_id — ga_session_id is only unique per user — and compute duration, page_count, entry/exit pages), int_users (first-touch attribution, lifetime metrics). These become the foundation for all downstream analytics — dashboards, notebooks, and ML features all reference the same tested, documented models.