GA4 BigQuery Export schema: the tables, the fields and how dates work
A reference for the GA4 export to BigQuery: which tables it writes and when they change, how to read the schema with INFORMATION_SCHEMA, what sits inside event_params, user_properties, device, geo and items, how traffic_source differs from collected_traffic_source and session_traffic_source_last_click, why event_date and event_timestamp disagree, and how to turn timestamps into dates and hours in any time zone. Every query runs on Google's public GA4 sample.
The GA4 export to BigQuery writes one dataset per property, named analytics_<property_id>, with up to four kinds of daily table. Events land in events_YYYYMMDD (and in events_intraday_YYYYMMDD while the day is running); users land in pseudonymous_users_YYYYMMDD and users_YYYYMMDD if you turned on the user-data export. Each event row has around 30 top-level columns, a third of them nested records.
| Table | One row per | Written | Watch out for |
|---|---|---|---|
events_YYYYMMDD |
event | once a day, then updated for a few days | late events still arrive after the table exists |
events_intraday_YYYYMMDD |
event | continuously, best effort | deleted when the daily table is complete |
pseudonymous_users_YYYYMMDD |
pseudonymous ID (device or browser) | daily, user-data export only | holds only users whose data changed that day |
users_YYYYMMDD |
user ID you send | daily, user-data export only | empty if you never set user_id |
| Column | Type | What it means |
|---|---|---|
event_date |
STRING, YYYYMMDD |
day in the property’s time zone |
event_timestamp |
INT64 | microseconds, UTC |
event_params |
ARRAY of key and value | everything sent with the event |
user_properties |
ARRAY of key and value | user properties you set |
traffic_source |
RECORD | the source that first acquired the user |
collected_traffic_source |
RECORD | UTM and click IDs on this event |
session_traffic_source_last_click |
RECORD | last-click source of the session |
Note: Originally published in April 2024. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026.
Reading to get this done?
The data used in every example
The data used in every example
Google’s public sample, bigquery-public-data.ga4_obfuscated_sample_ecommerce, is a real GA4 export of the Google Merchandise Store with values obfuscated. Anyone with a BigQuery project can query it. It was exported in 2021, so it uses the schema of that time: some newer columns are missing and some fields are empty. Both are shown below, because your own export may have the same gaps.
Which tables the export writes
Which tables the export writes
INFORMATION_SCHEMA.TABLES lists the tables in a dataset without reading any data:
SELECT
REGEXP_REPLACE(table_name, r'\d{8}$', 'YYYYMMDD') AS table_pattern,
COUNT(*) AS tables_,
MIN(table_name) AS first_table,
MAX(table_name) AS last_table
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.INFORMATION_SCHEMA.TABLES`
GROUP BY table_pattern;| table_pattern | tables_ | first_table | last_table |
|---|---|---|---|
| events_YYYYMMDD | 92 | events_20201101 | events_20210131 |
The sample has daily event tables only: 92 days, 1 November 2020 to 31 January 2021. Run the same query on your own analytics_<property_id> dataset and you will see which of the four patterns your export has turned on, and since when. Add creation_time to the select to see when each daily table was written.
What Google says about each table (all read in October 2026):
events_YYYYMMDDis “the full daily export of events”. Daily tables “are created after Analytics collects all of the events for the day”, and the export “typically exports mid-afternoon in your property’s timezone”.events_intraday_YYYYMMDDis filled when streaming export is on. Google calls streaming a “Best-effort service: operates without a completeness SLO and may contain data gaps”, and the table “is deleted whenevents_YYYYMMDDis complete”. Thetraffic_sourcerecord is not populated in it.- The user tables come from the user-data export. Google’s user-data schema page calls them the Pseudo ID table, which “contains a row for every pseudonymous identifier”, and the User ID table, which “contains a row for every user ID”. Both include “any user whose data has changed that day”, so one day’s table is not a list of all users. Their columns are user-level:
user_info.last_active_timestamp_micros,user_ltv,predictions,audiences,device,geo,user_properties,privacy_info, plusoccurrence_dateandlast_updated_date.
How to read user properties and user-level metrics out of the event table is in extract GA4 user properties and metrics.
Your GA4 export is full of nested records, and daily counts drift from GA4 because event_date and the timestamp disagree.
Paste this into your AI assistant. It reads the schema of your own GA4 export in BigQuery and writes queries that unnest the fields you use and count days in your property's time zone.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/ga4-bigquery-export-event-table-schema-and-dates.md. The task: write me SQL against my GA4 export in BigQuery that unnests the event params I use and groups events by date and hour in my property's time zone. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
When a daily table stops changing
When a daily table stops changing
A daily table that exists is not necessarily final. Google’s schema page says Analytics “will update the daily tables (events_YYYYMMDD) with events for those dates for up to three days after the dates of the events”. The export setup page words it as “up to 2 calendar days, plus today beyond the date of the table” (both read in October 2026).
In practice: a report that reads yesterday’s table will grow a little if you rerun it two days later. For numbers you send to someone, read days that are at least three days old, or rerun the last three days on every refresh instead of only appending yesterday.
Read the schema with INFORMATION_SCHEMA
Read the schema with INFORMATION_SCHEMA
The table preview in the BigQuery console shows the schema one table at a time. INFORMATION_SCHEMA.COLUMNS gives it as rows you can filter and compare:
SELECT column_name, data_type
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'events_20210131'
ORDER BY ordinal_position;23 columns come back. The flat ones:
| column_name | data_type |
|---|---|
| event_date | STRING |
| event_timestamp | INT64 |
| event_name | STRING |
| event_previous_timestamp | INT64 |
| event_value_in_usd | FLOAT64 |
| event_bundle_sequence_id | INT64 |
| event_server_timestamp_offset | INT64 |
| user_id | STRING |
| user_pseudo_id | STRING |
| user_first_touch_timestamp | INT64 |
| stream_id | INT64 |
| platform | STRING |
There is no DATE or TIMESTAMP column anywhere. event_date is text and every time is an integer count of microseconds, so each date calculation starts with a conversion.
The other 11 columns are records. INFORMATION_SCHEMA.COLUMN_FIELD_PATHS has one row per nested field, so you can count what is inside each:
SELECT
SPLIT(field_path, '.')[OFFSET(0)] AS top_column,
COUNT(*) - 1 AS nested_fields
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name = 'events_20210131'
GROUP BY top_column
HAVING nested_fields > 0
ORDER BY nested_fields DESC;| top_column | nested_fields | what is inside |
|---|---|---|
| items | 26 | item_id, item_name, item_category to item_category5, price, quantity, item_revenue, coupon, promotion and list fields |
| device | 15 | category, operating_system, language, mobile_brand_name, time_zone_offset_seconds, web_info.browser |
| ecommerce | 11 | transaction_id, purchase_revenue, tax_value, shipping_value, refund_value, total_item_quantity, unique_items |
| user_properties | 7 | key, value.string_value, int_value, float_value, double_value, set_timestamp_micros |
| event_params | 6 | key, value.string_value, int_value, float_value, double_value |
| geo | 6 | continent, sub_continent, country, region, city, metro |
| app_info | 5 | id, version, install_store, firebase_app_id, install_source |
| privacy_info | 3 | analytics_storage, ads_storage, uses_transient_token |
| traffic_source | 3 | source, medium, name |
| user_ltv | 2 | revenue, currency |
| event_dimensions | 1 | hostname |
Three kinds of field, and each is read differently:
- Flat columns are selected by name:
event_name. - Records (
device,geo,ecommerce,traffic_source) hold one value per event and are read with a dot:geo.country,device.web_info.browser. - Repeated records (
event_params,user_properties,items) hold a list per event. You read them withUNNEST, and that is where most mistakes start.event_paramsis a list of keys, each with four value fields, only one of which is filled. The patterns are in how to unnest GA4 event parameters.
Page URL, page title, session ID and campaign parameters are not columns. They are keys in event_params: page_location, page_title, ga_session_id, source, medium, campaign.
Columns the sample does not have
Columns the sample does not have
Google’s schema page (read in October 2026) lists these top-level fields that the 2021 sample does not have: collected_traffic_source, session_traffic_source_last_click, is_active_user, batch_event_index, batch_page_id, batch_ordering_id and publisher. You can check your own export the same way:
SELECT
wanted AS column_name,
c.data_type IS NOT NULL AS in_this_export,
COUNT(DISTINCT c.table_name) AS daily_tables_with_it
FROM UNNEST(['traffic_source', 'collected_traffic_source',
'session_traffic_source_last_click', 'is_active_user',
'batch_event_index', 'publisher']) AS wanted
LEFT JOIN `bigquery-public-data.ga4_obfuscated_sample_ecommerce.INFORMATION_SCHEMA.COLUMNS` AS c
ON c.column_name = wanted
GROUP BY wanted, in_this_export;| column_name | in_this_export | daily_tables_with_it |
|---|---|---|
| traffic_source | true | 92 |
| collected_traffic_source | false | 0 |
| session_traffic_source_last_click | false | 0 |
| is_active_user | false | 0 |
| batch_event_index | false | 0 |
| publisher | false | 0 |
On a long-running export, daily_tables_with_it tells you the day a column started. That matters for events_* queries: BigQuery’s docs say a wildcard query “uses the schema for the most recently created table that matches the wildcard”, and for tables that lack a column “BigQuery returns NULL values for that column” (read in October 2026). A trend built on session_traffic_source_last_click therefore shows nothing before the day the column appeared in your export, without an error.
Three traffic source records
This is the schema question marketers ask most, because three records all look like “source / medium”. Google’s descriptions (read in October 2026):
| Record | Google’s description | Use it for |
|---|---|---|
traffic_source |
“the traffic source that first acquired the user” | first-user acquisition; the same on every event of that user |
collected_traffic_source |
“the traffic source data that was present within the events that were collected” | the raw UTM values and click IDs on this event: manual_source, manual_medium, manual_campaign_name, manual_term, manual_content, gclid, dclid, srsltid |
session_traffic_source_last_click |
“the last-click attributed session traffic source data across Google ads and manual contexts, where available” | session source / medium close to the GA4 session reports, split into manual_campaign, google_ads_campaign, cross_channel_campaign, sa360_campaign, dv360_campaign and cm360_campaign |
The sample only has traffic_source, so these two newer records are described from Google’s schema page, not tested here. If your export has session_traffic_source_last_click, it saves you rebuilding session source in SQL. If it does not, or for dates before it appeared, the rebuild from the source and medium event parameters is in how to query GA4 event data in BigQuery, which also shows how far traffic_source is from the session source.
Not every field is filled
A column in the schema can still be empty. One day of the sample, 26,489 events:
SELECT
COUNT(*) AS events,
COUNTIF(event_previous_timestamp IS NOT NULL) AS with_previous_ts,
COUNTIF(event_server_timestamp_offset IS NOT NULL) AS with_server_offset,
COUNTIF(user_first_touch_timestamp IS NOT NULL) AS with_first_touch,
COUNTIF(ARRAY_LENGTH(user_properties) > 0) AS with_user_properties,
COUNTIF(user_id IS NOT NULL) AS with_user_id,
COUNTIF(ARRAY_LENGTH(items) > 0) AS with_items,
COUNTIF(device.time_zone_offset_seconds IS NOT NULL) AS with_tz_offset
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20210131';| events | with_previous_ts | with_server_offset | with_first_touch | with_user_properties | with_user_id | with_items | with_tz_offset |
|---|---|---|---|---|---|---|---|
| 26,489 | 0 | 0 | 25,531 | 0 | 0 | 2,959 | 0 |
5.0 MB processed. Five of the eight are empty for the whole day. In the sample that is partly obfuscation; in your export, user_id and user_properties are empty unless your tagging sets them, and other fields may be filled or empty depending on the platform and SDK. Run this check before you build a report on any field, because a query on an empty field returns no rows rather than an error.
event_date and event_timestamp
event_date and event_timestamp
Google’s schema page (read in October 2026) defines them on different clocks:
event_date: “The date when the event was logged (YYYYMMDD format in the registered timezone of your app).”event_timestamp: “The time (in microseconds, UTC) when the event was received by Google Analytics.”user_first_touch_timestamp: “The time (in microseconds) at which the user first opened the app or visited the site.”user_properties.value.set_timestamp_micros: “The time (in microseconds) at which the user property was last set.”
TIMESTAMP_MICROS turns the integer into a TIMESTAMP, which is always UTC. DATETIME(timestamp, 'zone') and DATE(timestamp, 'zone') give the wall-clock time and the calendar day in any IANA time zone. The last purchase in the sample’s 31 January table:
SELECT
event_date,
event_timestamp,
FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp)) AS utc_time,
FORMAT_DATETIME('%F %T', DATETIME(TIMESTAMP_MICROS(event_timestamp), 'America/Los_Angeles')) AS la_time,
FORMAT_DATETIME('%F %T', DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')) AS tokyo_time,
FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(user_first_touch_timestamp)) AS first_touch_utc
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX = '20210131' AND event_name = 'purchase'
ORDER BY event_timestamp DESC
LIMIT 1;| event_date | event_timestamp | utc_time | la_time | tokyo_time | first_touch_utc |
|---|---|---|---|---|---|
| 20210131 | 1612136266409473 | 2021-01-31 23:37:46 | 2021-01-31 15:37:46 | 2021-02-01 08:37:46 | 2020-12-16 21:31:39 |
1.0 MB processed. One purchase, three calendar days depending on the clock: 31 January in UTC and Los Angeles, 1 February in Tokyo. event_date agrees with UTC here because the sample property’s day runs midnight to midnight UTC. The user first arrived 46 days before this purchase, which user_first_touch_timestamp gives you without looking up their first event.
Two rules follow:
event_dateis the property’s day, the same day GA4 reports use.- For hours, or for any other time zone, convert
event_timestampand name the zone. Never compare a UTC hour with anevent_dateday.
What goes wrong when the two are mixed in one report, with the number of events that move day, is mistake 5 in GA4 BigQuery Export mistakes.
Hour of day in a time zone
EXTRACT(HOUR FROM ...) on a TIMESTAMP returns the UTC hour unless you add AT TIME ZONE. Peak purchase hours in January, both ways:
SELECT clock, APPROX_TOP_COUNT(hour_, 3) AS top_purchase_hours
FROM (
SELECT 'UTC' AS clock,
EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp)) AS hour_
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131' AND event_name = 'purchase'
UNION ALL
SELECT 'America/Los_Angeles',
EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/Los_Angeles')
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131' AND event_name = 'purchase'
)
GROUP BY clock;| clock | 1st (hour: purchases) | 2nd | 3rd |
|---|---|---|---|
| America/Los_Angeles | 14: 76 | 5: 65 | 11: 62 |
| UTC | 22: 76 | 13: 65 | 19: 62 |
25.2 MB processed. Same purchases, same counts, shifted by eight hours (Los Angeles is UTC−8 in January). Without AT TIME ZONE, the “best hour to send the email” for a US shop comes out as 10 pm when it is 2 pm. If you build hour-of-day charts in Data Studio, do the conversion in the SQL, so every chart reads the same hour.
Choose days with _TABLE_SUFFIX
Choose days with _TABLE_SUFFIX
The export is date-sharded: one table per day, not one partitioned table. In events_*, _TABLE_SUFFIX is the part of the table name after events_, and it is the only filter that limits which tables are read. Dates in your SQL are usually DATE values, so convert them on the way in with FORMAT_DATE and turn the suffix back into a DATE with PARSE_DATE on the way out:
SELECT
PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS table_date,
COUNT(*) AS events,
COUNTIF(event_date != _TABLE_SUFFIX) AS event_date_differs,
COUNTIF(event_name = 'purchase') AS purchases
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE '2021-01-25')
AND FORMAT_DATE('%Y%m%d', DATE '2021-01-31')
GROUP BY table_date
ORDER BY table_date;| table_date | events | event_date_differs | purchases |
|---|---|---|---|
| 2021-01-25 | 41,718 | 0 | 61 |
| 2021-01-26 | 42,232 | 0 | 55 |
| 2021-01-27 | 48,665 | 0 | 54 |
| 2021-01-28 | 43,131 | 0 | 69 |
| 2021-01-29 | 40,877 | 0 | 54 |
| 2021-01-30 | 30,395 | 0 | 31 |
| 2021-01-31 | 26,489 | 0 | 19 |
6.2 MB processed for seven days. In a daily table, every row’s event_date equals the table’s suffix: the table is the property’s day. That is why PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) is a safe date column for grouping.
Three rules for the suffix filter:
- Keep it constant. BigQuery’s docs say “filters on
_TABLE_SUFFIXthat include conditions without constant expressions don’t limit the number of tables scanned” (read in October 2026).FORMAT_DATEover a literal orCURRENT_DATE()is fine; a suffix taken from a subquery or a join reads every table. - Close the range at both ends. An open range such as
>= '20210101'also matchesintraday_...suffixes; why that double counts is mistake 6 in GA4 BigQuery Export mistakes. - Do not filter on
event_dateto save money. It returns the same rows and still opens every table; the bytes are measured in how to query GA4 event data in BigQuery. The mechanics of_TABLE_SUFFIXare in BigQuery wildcard tables.
From schema to a report people trust
From schema to a report people trust
The schema decisions above are the ones that make two GA4 reports disagree: which traffic source record, which clock, which days are final. Make each once, in one query, and let every report read the result.
The Standard data model makes the traffic source choice by keeping the three apart. User Traffic Sources holds how a user was first acquired, Traffic Sources holds the source a session is attributed to, and Event Traffic Sources holds the source on a single event or page view. Each is its own Data Mart, joined to Sessions, Events or Pageviews.

In OWOX Data Marts that query becomes a Data Mart over your GA4 export tables, with the choices written in its column descriptions (event_date is the property’s day; hours are in America/Los_Angeles; source is last-click session). The same Data Mart then feeds Google Sheets, Data Studio, Excel, email or Slack, so a marketer opens the number instead of rebuilding it. OWOX Data Marts runs your SQL as written in your own BigQuery project and does not transform the data; GA4 data reaches BigQuery through Google’s own BigQuery export. More on modelling the export in modeling, and on setting the export up in the GA4 to BigQuery guide.
Your GA4 export is full of nested records, and daily counts drift from GA4 because event_date and the timestamp disagree.
Paste this into your AI assistant. It reads the schema of your own GA4 export in BigQuery and writes queries that unnest the fields you use and count days in your property's time zone.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/ga4-bigquery-export-event-table-schema-and-dates.md. The task: write me SQL against my GA4 export in BigQuery that unnests the event params I use and groups events by date and hour in my property's time zone. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
What is user_pseudo_id in the GA4 BigQuery Export?
`user_pseudo_id` is the pseudonymous ID GA4 assigns to a browser or app instance, stored as a STRING on every event row. It changes when cookies are cleared or the app is reinstalled, so it counts devices rather than people; `user_id` is filled only if your tagging sends one.
What is the GA4 BigQuery Export limit for standard properties?
Google states that standard properties have a daily BigQuery export limit of 1 million events. To stay under it, exclude data streams or events you do not need from the export, or use streaming export; Analytics 360 properties have a higher limit.
How do I see when each events_YYYYMMDD table was created?
Query `INFORMATION_SCHEMA.TABLES` for the export dataset and select `table_name` and `creation_time.` It lists every daily, intraday and user table without reading any event data, so it shows which tables the export writes and since when.
What does the GA4 export create in BigQuery?
One dataset per property, named `analytics_` followed by the property ID. Inside it are `events_YYYYMMDD` daily tables, `events_intraday_YYYYMMDD` tables if streaming export is on, and `pseudonymous_users_YYYYMMDD` and `users_YYYYMMDD` tables if the user-data export is on.
How do I query several days of GA4 data in BigQuery?
Query the wildcard table `events_*` and filter `_TABLE_SUFFIX` with constant values, for example BETWEEN `FORMAT_DATE`('%Y%m%d', DATE '2021-01-25') AND `FORMAT_DATE`('%Y%m%d', DATE '2021-01-31'). Only the suffix filter limits which daily tables are read; a filter on `event_date` still opens every table.
How is GA4 data structured in BigQuery?
The event tables have one row per event, with flat columns such as `event_date`, `event_timestamp` and `event_name`, single records such as device, geo, ecommerce and `traffic_source`, and repeated records such as `event_params`, `user_properties` and items that you read with UNNEST. The user tables have one row per pseudonymous ID or per user ID whose data changed that day.
Which traffic source field should I use in the GA4 export?
`traffic_source` is the source that first acquired the user, so it is the same on every event of that user. `collected_traffic_source` holds the UTM values and click IDs on each event, and `session_traffic_source_last_click` holds the last-click session source; the last two exist only in newer exports, so check `INFORMATION_SCHEMA.COLUMNS` first.
What data types are used for dates and times in the GA4 BigQuery schema?
There is no DATE or TIMESTAMP column. `event_date` is a STRING in YYYYMMDD format in the property's time zone, and `event_timestamp` is an INT64 count of microseconds in UTC, which you convert with `TIMESTAMP_MICROS` and then DATE or DATETIME with a named time zone.
When is a GA4 daily export table final?
Google says daily tables are updated with late events for up to three days after the date of the events. For numbers you share, read days at least three days old, or rerun the last three days on each refresh.
