GA4 BigQuery Export Schema Deep Dive
Daily tables and the intraday table
Once linked, GA4 writes one table per day into your BigQuery dataset: events_YYYYMMDD. A separate events_intraday_YYYYMMDD table streams the current, not-yet-finalized day in near real time (typically a few hours of latency) and is deleted once the finalized events_YYYYMMDD table lands, usually by the following morning. If you need same-day data, you query the intraday table explicitly — most production pipelines exclude it from historical queries since its data can shift until finalized.
Anatomy of an event row
Each row is one event. The flat top-level columns include event_date, event_timestamp (microseconds since epoch, UTC), event_name, user_pseudo_id (the GA4 client ID), and user_id (if you set it). Everything variable — custom parameters, ecommerce items, device and geo detail — lives in nested and repeated STRUCT/RECORD fields: event_params, items, device, geo, traffic_source, collected_traffic_source, and user_properties. The nested design is deliberate: GA4 events don't share a fixed parameter schema, so flattening would mean an unworkably wide, mostly-NULL table.
-- event_params: an array of {key, value} structs where value is a one-of
SELECT
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_ms,
-- items is an array of structs with its own nested fields
(SELECT COUNT(*) FROM UNNEST(items)) AS item_count
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
LIMIT 100value is a typed one-of, not a generic column
event_params and user_properties carries a value STRUCT with four mutually-exclusive fields: string_value, int_value, float_value, and double_value. Only one is ever populated for a given key; the rest are NULL. You must know (or discover via SELECT key, value FROM UNNEST(event_params)) which field a given parameter populates before writing a query against it, or you'll get a silently NULL result from asking for the wrong type.Pseudonymous identity and the ecommerce items array
user_pseudo_id is GA4's device-scoped identifier — stable per device/browser install, reset on app reinstall or cookie clear, and the join key for nearly every cross-session query. user_id is populated only if you explicitly set it via gtag('set', {'user_id': ...}) or the GTM equivalent, and is what lets you stitch logged-in behavior across devices. The items array holds one struct per line item on ecommerce events (purchase, add_to_cart, view_item), each with its own item_id, item_name, price, quantity, and up to 27 custom item-scoped parameters — these don't count against the 50 custom-dimension UI limit, which makes items a useful escape hatch for granular product metadata.
Key nested fields at a glance
| Field | Type | Common use |
|---|---|---|
| event_params | Repeated STRUCT {key, value} | Any custom or recommended event parameter |
| items | Repeated STRUCT | Ecommerce line-item detail |
| device | STRUCT (not repeated) | device.category, device.operating_system, device.web_info.browser |
| traffic_source | STRUCT (not repeated) | First-touch acquisition source/medium/campaign for the user |
| collected_traffic_source | STRUCT (not repeated) | Session-level traffic source, including gclid/manual UTM capture |
traffic_source vs. collected_traffic_source is a frequent source of attribution bugs
traffic_source reflects the user's *first-ever* acquisition touch and barely changes thereafter. collected_traffic_source reflects the session's actual entry source, including UTM parameters and click IDs captured on that specific session. A query mixing these up will attribute every session to a user's original acquisition channel instead of the channel that drove that particular session — a common and hard-to-spot error in self-built attribution models.What's Next?
Understanding the raw schema is the prerequisite for everything downstream — building reliable attribution tables, feeding BigQuery ML models, or exporting clean segments back out to ad platforms all start from correctly reading these nested structures rather than fighting them.
I build these systems professionally.
Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.