BigQuery GA4 Export Schema Reference & Production SQL Query Library
Production-ready BigQuery SQL recipes for GA4 event export tables. Includes session reconstruction, event unnesting, customer lifetime value and flat-table views.
GA4 to BigQuery Data Pipeline Architecture
Data flow architecture from raw nested event tables (`events_*`) through UNNEST operations, sessionization window functions, and cost-effective partition filtering.
BigQuery GA4 Export Schema Reference & Production SQL Query Library Configurator
BigQuery GA4 Export — Production Event & Parameter Unnesting Mart
Production SQL data mart unnesting raw GA4 event parameters, session IDs, and user device dimensions over a 30-day lookback window.
1 Input Parameters & Assumptions
| Parameter | Value | Context & Provenance |
|---|---|---|
| GCP Project ID | gcp-analytics-prod-01 GCP Project | Google Cloud Platform target project |
| GA4 BigQuery Dataset | analytics_308192841 BigQuery Dataset | Daily export dataset ID containing events_* partition tables |
| Query Architecture Type | EVENT_UNNESTING Data Mart Type | Flattens repeated event_params record into typed columnar dimensions |
| Lookback Date Range | LAST_30_DAYS Time Partition | Trailing 30-day table suffix partition filter |
2 Explicit Mathematical Formula
Date Filter: _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
Unnesting Syntax: (SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'page_location') AS param_page_location
Session ID Composite Key: CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING))
Source Table: `gcp-analytics-prod-01.analytics_308192841.events_*`
Output Limit: 1,000 rows preview with full export capability3 Computed Output Metrics
| Computed Metric | Result | Interpretation & Threshold |
|---|---|---|
| Unnested Custom Dimensions | 3 Parameters Fields | page_location, page_referrer, and engagement_time_msec unnested into typed columns |
| Session Tracking Dimensions | ga_session_id / number Session Keys | Deterministic session identifiers enabling end-to-end journey reconstruction |
| Row Thresholding Status | ZERO Thresholding Data Fidelity | Raw unaggregated event log guarantees 100% visibility into custom parameter cardinality |
BigQuery GA4 Export Schema Reference & Production SQL Query Library — Scope & Limitations
Explicit operational boundaries and constraints defining target use cases and out-of-scope scenarios.
Built For (Target Use Cases)
- Generating parameterised BigQuery SQL for GA4's native events_* export schema across 4 query types.
- Unnesting repeated event_params fields into typed columns for session, purchase funnel, retention, and pathing analysis.
- Filtering by a 7-day, 30-day, or custom date range against the _TABLE_SUFFIX partition.
Not Built For (Limitations & Out-of-Scope)
- Running against Firebase or non-GA4 BigQuery export schemas with a different table or field structure.
- Validating that the entered GCP project ID and dataset ID actually exist or are accessible.
- Estimating BigQuery query cost or bytes scanned before running the generated SQL.
Operational Assumptions & Defaults
- Assumes the standard GA4 BigQuery Linking export table naming convention (events_YYYYMMDD) is unchanged.
- Custom event parameters must be typed in manually; the tool does not read a live schema.
- Generated SQL is a starting query, not validated against BigQuery's dry-run or query validator.
GA4 BigQuery SQL Query Library & Unnesting Builder
Production Standard SQLGenerate enterprise-grade, cost-optimized BigQuery SQL queries for GA4 raw event exports. Unnest custom dimensions, build multi-step purchase funnels, and calculate weekly retention cohorts without thresholding limits.
Query Configuration
Generated BigQuery SQL
Query Specs & Optimization
-- GA4 Event & Parameter Unnesting Mart
-- Extracts custom event dimensions, session identifiers, and user device context
SELECT
event_date,
TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
event_name,
user_pseudo_id,
user_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.float_value AS STRING), CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'page_location') AS param_page_location,
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.float_value AS STRING), CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'page_referrer') AS param_page_referrer,
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.float_value AS STRING), CAST(value.double_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS param_engagement_time_msec,
geo.country AS geo_country,
geo.city AS geo_city,
device.category AS device_category,
device.web_info.browser AS device_browser,
traffic_source.name AS campaign,
traffic_source.medium AS medium,
traffic_source.source AS source
FROM `gcp-analytics-prod-01.analytics_314892019.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
LIMIT 1000;BigQuery Partition-Filtered
Deploy SQL Queries & Scheduled Data Models
Copy parameterized SQL queries directly into BigQuery Query Editor or dbt data models. Ensure date partitions are filtered with _TABLE_SUFFIX to minimize query scan costs.
Why Query GA4 Directly in BigQuery
The standard GA4 user interface enforces data thresholding on properties with Google Signals enabled and caps custom dimension cards at high cardinalities. Querying raw export tables in BigQuery bypasses thresholding and gives you exact, row-level session and purchase data.
Implementation Code & Script
Reconstructs web sessions from raw unthresholded event tables, unnesting ga_session_id and calculating session duration.
-- Reconstruct user sessions and map first touch attribution
WITH session_events AS (
SELECT
user_pseudo_id,
event_date,
event_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
traffic_source.source AS source,
traffic_source.medium AS medium,
event_name,
event_value_in_usd
FROM
`your-gcp-project.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20260801' AND '20260822'
)
SELECT
user_pseudo_id,
session_id,
MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
TIMESTAMP_DIFF(MAX(TIMESTAMP_MICROS(event_timestamp)), MIN(TIMESTAMP_MICROS(event_timestamp)), SECOND) AS session_duration_seconds,
COUNT(1) AS event_count,
COUNTIF(event_name = 'page_view') AS page_view_count,
ARRAY_AGG(source IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS initial_source,
ARRAY_AGG(medium IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS initial_medium,
COALESCE(SUM(event_value_in_usd), 0.0) AS total_revenue_usd
FROM
session_events
WHERE
session_id IS NOT NULL
GROUP BY
user_pseudo_id,
session_id;BigQuery Cost & Query Performance QA
Validate query bytes processed with DRY RUN, reconcile session counts against GA4 UI, and ensure proper partition pruning.
Pre-Production Verification Checklist
Always check that the query validator shows minimal GBs scanned by bounding _TABLE_SUFFIX.
Compare total purchase count within ±1.5% of GA4 standard reports (variance due to thresholding).
Use events_intraday_* for current day data and events_* for historical settled dates.
Terminal Diagnostic & Debug Commands
Calculates exact bytes processed before executing query on BigQuery.
bq query --use_legacy_sql=false --dry_run "SELECT COUNT(1) FROM `project.analytics_123.events_*` WHERE _TABLE_SUFFIX = '20260801'"Failure Remediation & Troubleshooting
Cause: Missing _TABLE_SUFFIX filter scans entire multi-year dataset across all shards.
Fix: Always wrap _TABLE_SUFFIX in a date bounding clause (e.g. BETWEEN 20260101 AND 20260131).
Cause: User engagement or server background events dispatched without active browser session context.
Fix: Filter WHERE ga_session_id IS NOT NULL when aggregating session-level metrics.
How to cite and attribute this tool
MIT LicenceThis resource is free, open and un-gated under the MIT Open Source Licence. You are encouraged to use, integrate and cite it with attribution:
@misc{geraghty_ga4_bigquery_sql_query_library,
author = {Geraghty, Gordon},
title = {BigQuery GA4 Export Schema Reference & Production SQL Query Library},
year = {2026},
url = {https://gordongeraghty.com/resources/gtm-analytics/ga4-bigquery-sql-query-library},
note = {Head of Performance Media, Empire Amplify}
}Changelog & Version History
v1.0.0Initial release of GA4 BigQuery SQL library covering sessionization and unnested ecommerce views.
Strategic Takeaway & Operational Guidelines
GA4 standard UI reports apply aggressive row thresholding and cardinality caps. Writing modular unnesting queries in BigQuery unlocks 100% of raw user journey data, powering custom attribution and warehouse-native conversion modeling.