GA4 BigQuery Query Generator

Why GA4 Analysts Use BigQuery Instead of the GA4 UI

The GA4 UI is designed for marketers who need quick insights from pre-aggregated reports. But when you need to answer questions like "what is the exact sequence of events that leads to purchase?" or "which specific products were viewed by users who returned within 7 days?", the GA4 UI hits its limit. BigQuery gives you direct access to every individual event, every parameter value, and every user session — at the raw row level — with the full power of standard SQL. This GA4 BigQuery query generator produces syntactically correct BigQuery SQL for the most common GA4 analysis patterns, handling the notoriously tricky UNNEST(event_params) syntax automatically.

Understanding the GA4 BigQuery Schema

The GA4 BigQuery export uses a nested, repeated schema — very different from a standard relational database. Understanding this schema is essential for writing correct queries:

• One row = one event. Every event fired by every user gets its own row. A session with 50 page views creates 50+ rows. Events are not pre-aggregated — you count, sum, and group them yourself in SQL.
• event_params is a REPEATED RECORD. Each event can have many parameters, stored as an array of {key, value} structs. To access a specific parameter, you must UNNEST(event_params) and filter by key. For example, to get the page path: UNNEST(event_params) AS ep WHERE ep.key = 'page_location'.
• Values are typed. Each parameter value has four sub-fields: value.string_value, value.int_value, value.float_value, and value.double_value. Only one will be non-null for any given parameter — use COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)) when the type is uncertain.
• Wildcard tables for date ranges. Daily export tables are named events_YYYYMMDD. Query multiple days with events_* and WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'. The _TABLE_SUFFIX pseudo-column is automatically available in wildcard queries.

GA4 BigQuery Query Patterns You'll Use Every Day

Three query patterns cover the vast majority of GA4 BigQuery analysis work:

• Event count by dimension — count how many times a specific event fired, grouped by a dimension (page, device, country). Core of any traffic or engagement analysis. The generator produces this pattern when you select an event with group-by dimensions.
• Funnel analysis with session-level joins — identify which users completed step A but not step B in the same session. Requires self-joining the events table on user_pseudo_id and ga_session_id — the generator handles this join for checkout funnel queries.
• Revenue and ecommerce analysis — sum purchase event values, count unique transaction_ids, and compute average order value. These queries access the items nested record in addition to event_params — a second UNNEST(items) call is required to get item-level data.