GA4 export to BigQuery
Google Analytics 4 exports one events_YYYYMMDD table per day, with parameters in a repeated STRUCT. Tables are sharded and not clustered, so only _TABLE_SUFFIX and column choice reduce the bytes a query reads. An event_name filter doesn't.
Practice this in a free lesson on learn.querylab.io: Events per day.
The Google Analytics 4 BigQuery export writes raw events to a dataset named analytics_<property id>, one table per day called events_YYYYMMDD. One row is one event. Parameters sit in a repeated event_params STRUCT, purchased products in a repeated items STRUCT. The tables are date shards with no partitioning or clustering, so the bytes a query reads depend on two things: which days _TABLE_SUFFIX selects and which columns you name.
The byte counts in this section come from Google’s public sample, bigquery-public-data.ga4_obfuscated_sample_ecommerce: 92 daily tables from 2020-11-01 to 2021-01-31, and a good place to try these queries for free in the sandbox.
Which tables you get
| Table | What it holds | When it updates |
|---|---|---|
events_YYYYMMDD | The full daily export | Once a day, then late events for about 3 days |
events_intraday_YYYYMMDD | Streaming export for the current day | Continuously, best effort, deleted when the daily lands |
pseudonymous_users_YYYYMMDD | One row per pseudo ID whose data changed | Daily, if user-data export is on |
users_YYYYMMDD | One row per user ID whose data changed | Daily, if user-data export is on |
Analytics 360 also has a Fresh Daily export with the same schema as the daily one. Daily tables can change after they appear: Google updates them for up to 2 calendar days plus the current day when late events arrive, so yesterday’s numbers can still move. Build reports on data older than 72 hours if they must match later runs.
Limits and costs of the export
A standard property can export 1 million events a day to the daily table. A property that keeps going over has its daily export paused, and Google’s filter settings can drop events or streams to stay under. Analytics 360 goes up to 20 billion. The streaming export has no event limit and costs $0.05 per GB, roughly 600,000 events per GB. On top of that you pay BigQuery storage and query costs for whatever is exported.
You choose the dataset location when you link the property, and the location can’t be changed in place. In the sandbox there is no streaming export, and tables expire after 60 days, so old days disappear unless you enable billing. Once a day has been exported, Google won’t export it again.
What a query reads
events_* is a wildcard table. With no filter it reads every shard, here 53.4 MiB for a single column:
SELECT event_name FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
This query will process 53.4 MiB when run.
A _TABLE_SUFFIX range limits it to the shards you ask for, 3.0 MiB for a week:
SELECT event_name FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
This query will process 3.0 MiB when run.
A filter on event_name doesn’t reduce the bytes at all. The tables aren’t clustered, so BigQuery reads the whole column and filters after:
SELECT event_name FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107' AND event_name = 'purchase'
This query will process 3.0 MiB when run.
SELECT * over the same week reads 178.3 MiB, 59 times more than one column. Each nested field in a GA4 row is its own column in storage, and SELECT * reads them all:
SELECT * FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
This query will process 178.3 MiB when run.
In Querylab.io, the GA4 events page shows how the schema browser collapses the shards into one node and lists the events and parameters in the data from a free sample.
Filtering on the suffix
_TABLE_SUFFIX is a string, and events_* also matches events_intraday_*, whose suffix starts with intraday_. Letters sort after digits, so a range with no upper end picks up intraday tables:
WITH shards AS ( SELECT * FROM UNNEST(['20260101', '20260102', 'intraday_20260103']) AS suffix ) SELECT suffix, suffix >= '20260102' AS open_ended, suffix BETWEEN '20260102' AND '20260131' AS bounded FROM shards ORDER BY suffix
| Row | suffix | open_ended | bounded |
|---|---|---|---|
| 1 | 20260101 | false | false |
| 2 | 20260102 | true | true |
| 3 | intraday_20260103 | true | false |
Always write BETWEEN. To read finished days and today’s intraday table together, match both forms with a regular expression. BigQuery still prunes on it: this one reads 14.8 MiB for January, against 53.4 MiB for everything:
SELECT event_name FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r'^(intraday_)?202101\d{2}$')
This query will process 14.8 MiB when run.
Google deletes an intraday table once the daily table for that date is complete, so for a while both can exist, and a query that reads both counts that day twice. Intraday tables also have no traffic_source or user_ltv data.
Dates and time zones
event_date is a string in the property’s time zone. event_timestamp is an integer, microseconds in UTC. For a Los Angeles property, an event at 03:30 UTC on New Year’s Day sits in the 2025-12-31 shard:
WITH events AS ( SELECT '20251231' AS event_date, 1767238200000000 AS event_timestamp ) SELECT PARSE_DATE('%Y%m%d', event_date) AS property_day, TIMESTAMP_MICROS(event_timestamp) AS received_utc, DATE(TIMESTAMP_MICROS(event_timestamp)) AS utc_day, DATE(TIMESTAMP_MICROS(event_timestamp), 'America/Los_Angeles') AS los_angeles_day FROM events
| Row | property_day | received_utc | utc_day | los_angeles_day |
|---|---|---|---|---|
| 1 | 2025-12-31 | 2026-01-01 03:30:00 UTC | 2026-01-01 | 2025-12-31 |
Group by event_date to match the reports. If the property’s time zone changes, Google warns that a day’s export can be shorter, longer or skipped. Dates and time zones covers the functions.
When to build your own table
If you run many queries over the same months, copy the export into a table that is partitioned by date and clustered by event name. This also makes event_name filters cheaper, which the shards can’t do. We validated this statement with a dry run. It isn’t run on this page because it creates a table:
CREATE TABLE reporting.ga4_events
PARTITION BY event_day
CLUSTER BY event_name
AS
SELECT PARSE_DATE('%Y%m%d', event_date) AS event_day, *
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
Load only finished days, and reload the last three so late events are included. A scheduled query can do that. Partitioning vs clustering explains the difference between the two.
Why numbers differ from the GA4 reports
Export numbers won’t match the GA4 interface exactly. Google lists the reasons: reports add modeling, Google Signals and attribution that the export lacks, they estimate counts such as sessions and active users with HyperLogLog++, and high-cardinality reports group rows into (other). Compare data older than 72 hours, in the property’s time zone, and count sessions the way GA4 does. APPROX_COUNT_DISTINCT is also HyperLogLog++, so it can come closer to the reports than an exact count.