GA4 BigQuery event export (events_ / events_intraday_ tables)
The raw event-level export GA4 streams into BigQuery, which people wrongly assume will reconcile to the GA4 reporting UI, it will not, because the UI is modeled, thresholded, and sampled while this export is raw, unmodeled, and un-deduplicated.
The shape, how the file is really laid out
Daily tables named analytics_<propertyId>.events_YYYYMMDD, plus a rolling
events_intraday_YYYYMMDD for the current, still-collecting day. One row per
event. Load-bearing columns: event_date (STRING, YYYYMMDD, in the property's
reporting timezone), event_timestamp (INT64, microseconds since epoch, in UTC),
event_name, and event_params, a REPEATED RECORD (array) of {key, value}, where
value splits across four typed subfields (string_value, int_value, float_value,
double_value). user_pseudo_id and user_id identify users; items, ecommerce,
device, geo, and traffic_source are further nested records. There is no session
table and no unique event-id column.
The traps
Timezone split: event_date is on the property clock while event_timestamp is UTC microseconds, so grouping by one versus deriving the day from the other disagrees at every day boundary, an 11pm local event can land on a different UTC date.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Google, "[GA4] BigQuery Export schema" · Google, "Structure of Google Analytics data in BigQuery" · Google, "[GA4] Data freshness" (intraday vs daily export) · Google, "[GA4] Data thresholding"