Google BigQuery

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.

Google BigQueryAlyona Samovar13 min read

GA4 BigQuery Export schema: the tables, the fields and how dates work

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_YYYYMMDD is “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_YYYYMMDD is 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 when events_YYYYMMDD is complete”. The traffic_source record 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, plus occurrence_date and last_updated_date.

How to read user properties and user-level metrics out of the event table is in extract GA4 user properties and metrics.

Hand this to your AI

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

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

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 with UNNEST, and that is where most mistakes start. event_params is 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

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

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_date is the property’s day, the same day GA4 reports use.
  • For hours, or for any other time zone, convert event_timestamp and name the zone. Never compare a UTC hour with an event_date day.

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

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_SUFFIX that include conditions without constant expressions don’t limit the number of tables scanned” (read in October 2026). FORMAT_DATE over a literal or CURRENT_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 matches intraday_... suffixes; why that double counts is mistake 6 in GA4 BigQuery Export mistakes.
  • Do not filter on event_date to 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_SUFFIX are 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 GA4 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.

GA4 Standard data model: ten Data Marts with Sessions at the hub; Transactions, Pageviews, Events, Users, Geo, Devices and the traffic-source Data Marts around it

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 data modeling, and on setting the export up in the GA4 to BigQuery guide.

Hand this to your AI

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

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

FAQ

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.

Who wrote this

Alyona Samovar

Alyona Samovar Senior Digital Analyst

Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making.