GA4 to BigQuery: Export Schema and Query Patterns That Scale

2026-07-13

Unpacking the nested event_params array, flattening strategies, and cost-efficient query patterns for large GA4 datasets.

The GA4 BigQuery export is the only way to get at your raw event data, and the schema it lands in confuses almost everyone on first contact. Nested arrays of key-value structs are not what an analyst expects from a table called events. The first query someone writes usually returns either one row per parameter instead of one row per event, or a five-figure bytes-scanned bill for a question that should have cost pennies.

This covers the schema as it actually is, the query patterns worth memorizing, how to keep cost predictable, and the questions the export answers that the GA4 interface simply cannot.

What the export schema actually looks like

Enable the export under Admin, then Product Links, then BigQuery Links, and GA4 writes into a dataset named analytics_<property_id> in your project. Inside it you get date-sharded tables: events_20260315, events_20260316, and so on.

One row per event. That's the core fact and everything follows from it. A single page view is a row. A purchase is a row. There is no session table and no user table in the standard export, only events, which means sessions and users are things you reconstruct.

The columns that carry the interesting data are repeated structs:

event_date              STRING     -- 'YYYYMMDD', not a DATE type
event_timestamp         INT64      -- microseconds since epoch
event_name              STRING
user_pseudo_id          STRING     -- the client ID
user_id                 STRING     -- your own ID, if you set it

event_params  ARRAY<STRUCT<
  key    STRING,
  value  STRUCT<
    string_value  STRING,
    int_value     INT64,
    float_value   FLOAT64,
    double_value  FLOAT64
  >
>>

user_properties  ARRAY<STRUCT<key STRING, value STRUCT<...>>>
items            ARRAY<STRUCT<item_id STRING, item_name STRING, ...>>
device           STRUCT<category, operating_system, web_info, ...>
geo              STRUCT<country, region, city, ...>
ecommerce        STRUCT<purchase_revenue, transaction_id, ...>

The value struct is the part that trips people. Each parameter stores its value in exactly one of four fields depending on type, and the other three are NULL. page_location lives in string_value. ga_session_id lives in int_value. value on a purchase might land in double_value or float_value depending on how it was sent. When a parameter comes back empty, you're usually reading the wrong type field, not missing data.

What you want	Where it lives	Type field
Page URL	`event_params.page_location`	`string_value`
Session ID	`event_params.ga_session_id`	`int_value`
Session number	`event_params.ga_session_number`	`int_value`
Engagement time	`event_params.engagement_time_msec`	`int_value`
Purchase revenue	`ecommerce.purchase_revenue`	top-level struct
Transaction ID	`ecommerce.transaction_id`	top-level struct
Campaign source	`collected_traffic_source.manual_source`	top-level struct
Membership tier	`user_properties.<your_key>`	whichever you set

Daily tables, streaming, and freshness

Two export modes, and they behave differently enough to matter. Daily export writes events_YYYYMMDD once per day, typically within a few hours of midnight in the property's timezone, and that table is complete and stable. Streaming export writes events_intraday_YYYYMMDD continuously through the day, then GA4 replaces it with the finished daily table.

The intraday table is not a preview of the daily table. It lacks some enrichment, late-arriving events are missing, and rows can change. Use it for operational monitoring (did the purchase event stop firing in the last hour) and never for reporting numbers anyone will quote.

Query them together with a wildcard and you will accidentally double-count the current day, because for a window of time both tables exist. Either scope your wildcard to events_2*, which excludes events_intraday_* by pattern, or exclude today's date explicitly.

Also know that yesterday's daily table can be rewritten. GA4 backfills late-arriving events, so a table you queried at 9 AM may hold slightly different totals at 6 PM. If you materialize daily aggregates on a schedule, run the job late enough that the source is settled, and build the job so a rerun replaces the partition rather than appending to it. Idempotent loads save you from explaining why last Tuesday has two versions of the truth.

Two other tables show up in the dataset depending on your settings. pseudonymous_users_YYYYMMDD and users_YYYYMMDD carry user-level attributes and audience membership, which is convenient when you want current state without aggregating every event a person ever sent. They're separate exports with their own toggles, so check whether you actually have them before writing a query against them.

The UNNEST pattern you write constantly

There are two ways to pull a parameter out, and picking the wrong one is the classic beginner error.

-- Correct for most cases: scalar subquery, one row per event
SELECT
  PARSE_DATE('%Y%m%d', event_date)   AS event_dt,
  TIMESTAMP_MICROS(event_timestamp)  AS event_ts,
  event_name,
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location')    AS page_location,
  (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')    AS ga_session_id
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
  AND event_name = 'page_view';

Each parameter gets its own correlated subquery over the array, and the row count stays equal to the event count. Slightly verbose, entirely predictable.

The alternative, CROSS JOIN UNNEST(event_params) AS p, flattens the array into rows. One event with twelve parameters becomes twelve rows. That's what you want when you're auditing which parameters exist across your events, and it's a disaster when you're counting anything, because every aggregate silently inflates. If a purchase count comes back roughly ten times too high, this is why.

Useful audit query in that second form: group by event_name and p.key, count distinct events, and you get a complete inventory of which parameters actually arrive on which events. That single query answers questions that take twenty minutes of clicking in the GA4 interface, and it finds the parameters that are configured but never populated.

Building a flattening view

Writing those subqueries every time is tedious and error-prone. Build one view that surfaces the parameters your organization actually uses and query that instead.

CREATE OR REPLACE VIEW `my-project.marts.ga4_events` AS
SELECT
  PARSE_DATE('%Y%m%d', event_date)   AS event_dt,
  TIMESTAMP_MICROS(event_timestamp)  AS event_ts,
  event_name,
  user_pseudo_id,
  user_id,
  CONCAT(user_pseudo_id, '.', CAST(
    (SELECT value.int_value FROM UNNEST(event_params)
       WHERE key = 'ga_session_id') AS STRING))  AS session_key,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location')   AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'form_name')       AS form_name,
  (SELECT value.double_value FROM UNNEST(event_params)
     WHERE key = 'value')           AS event_value,
  device.category                   AS device_category,
  geo.country                       AS country,
  ecommerce.transaction_id          AS transaction_id,
  ecommerce.purchase_revenue        AS purchase_revenue
FROM `my-project.analytics_123456789.events_2*`;

Be honest about what a view does: it's a saved query, not stored data. Every hit against it re-scans the underlying tables, so it saves typing and nothing else. That's fine for exploratory work.

Once a query runs on a schedule or backs a dashboard, materialize instead. A scheduled query that appends yesterday's flattened events into a table partitioned on event_dt and clustered by event_name and user_pseudo_id turns a repeated full scan into a cheap partition read. Add only the columns you use, because every column you carry forward is bytes you pay for on every downstream query.

Keeping query cost under control

On-demand BigQuery bills for bytes scanned, and scanning is per column, not per row. That single fact drives every cost decision here.

Always filter _TABLE_SUFFIX. With sharded tables this is what prunes tables before any scanning happens. A WHERE event_date = '20260315' filter alone still touches every table the wildcard matches.
Never SELECT *. The star pulls event_params, user_properties, and items, which are the fattest columns in the table. To eyeball rows, use the Preview tab in the BigQuery console instead, which is free.
Set a maximum bytes billed. Under Query Settings, Advanced, set a ceiling. The query fails instead of running, which is a much better outcome than discovering the cost afterwards.
Develop against a few days. Write and debug against a three-day suffix range, then widen once the logic is right. Most expensive queries are expensive drafts.
Use TABLESAMPLE SYSTEM (1 PERCENT) for shape questions. When you want to know the distribution of a parameter rather than an exact count, sampling is orders of magnitude cheaper.

Check the bytes estimate in the top right of the query editor before every run. It updates as you type, it's free, and it's the single habit that separates people who get surprised by BigQuery bills from people who don't.

Reconstructing sessions properly

There is no session ID column. Sessions come from ga_session_id, an integer that is the session start time in seconds, unique per user but not globally. Two different users can hold the same ga_session_id, so the session key is always the concatenation:

WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '.', CAST(
      (SELECT value.int_value FROM UNNEST(event_params)
         WHERE key = 'ga_session_id') AS STRING))     AS session_key,
    ANY_VALUE(user_pseudo_id)                          AS user_pseudo_id,
    MIN(event_timestamp)                               AS session_start,
    MAX(event_timestamp)                               AS session_end,
    COUNTIF(event_name = 'page_view')                  AS pageviews,
    COUNTIF(event_name = 'purchase')                   AS purchases,
    SUM(ecommerce.purchase_revenue)                    AS revenue
  FROM `my-project.analytics_123456789.events_2*`
  WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
  GROUP BY session_key
)
SELECT
  COUNT(*)                                             AS total_sessions,
  COUNTIF(purchases > 0)                               AS converting_sessions,
  ROUND(AVG((session_end - session_start) / 1000000), 1) AS avg_duration_sec
FROM sessions;

Your numbers will not exactly match the GA4 interface, and you should stop trying to make them. The interface applies its own session-stitching rules, includes modeled data for unconsented users, may sample large explorations, and handles late-arriving hits differently. A gap of a few percent is normal. A gap of thirty percent means something is genuinely wrong, usually a missing ga_session_id caused by a tag firing before the session starts.

A funnel query that scales

Funnels in the GA4 interface are limited in step count, awkward to modify, and impossible to join to anything else. In SQL a funnel is one aggregation, and enforcing step order is a comparison of timestamps.

WITH step_times AS (
  SELECT
    CONCAT(user_pseudo_id, '.', CAST(
      (SELECT value.int_value FROM UNNEST(event_params)
         WHERE key = 'ga_session_id') AS STRING))  AS session_key,
    MIN(IF(event_name = 'view_item',      event_timestamp, NULL)) AS t_view,
    MIN(IF(event_name = 'add_to_cart',    event_timestamp, NULL)) AS t_cart,
    MIN(IF(event_name = 'begin_checkout', event_timestamp, NULL)) AS t_checkout,
    MIN(IF(event_name = 'purchase',       event_timestamp, NULL)) AS t_purchase
  FROM `my-project.analytics_123456789.events_2*`
  WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260331'
    AND event_name IN ('view_item','add_to_cart','begin_checkout','purchase')
  GROUP BY session_key
)
SELECT
  COUNT(*)                              AS viewed_item,
  COUNTIF(t_cart     > t_view)          AS added_to_cart,
  COUNTIF(t_checkout > t_cart)          AS began_checkout,
  COUNTIF(t_purchase > t_checkout)      AS purchased
FROM step_times
WHERE t_view IS NOT NULL;

The strict inequalities are doing real work. Without them you count sessions that purchased without ever adding to cart, which happens with re-order flows and quick-buy buttons and will quietly inflate every step. Adding a dimension is one line: put device.category or geo.country into the CTE and group by it at the end.

Where the export beats the interface

Not everything belongs in SQL. Standard reports, quick trend checks, and realtime debugging are faster in the GA4 interface, and you should use it for those. The export wins decisively in five situations:

High-cardinality dimensions. GA4 reports collapse anything past its cardinality limit into "(other)". BigQuery has no such notion, so full page path and product ID analysis stays intact.
Joins to data GA4 never had. CRM closed-won values, ad platform cost, margin by SKU, support tickets. Join on user_id or transaction_id and you get blended reporting that no interface can produce.
Retroactive redefinition. Change what counts as a qualified lead and rerun three years of history. In the interface, a definition change applies going forward only.
Data quality auditing. Find every purchase with a null transaction_id, or every session where page_location is missing. These queries take a minute and catch tracking bugs the reports hide.
Retention past the interface limits. BigQuery keeps whatever you choose to keep, long after GA4's own event-level retention window has closed.

There's a structural advantage underneath all five. Your event data ends up sitting next to the rest of the company's data, queryable in a language your team already knows, instead of behind a reporting interface only the analytics people can operate.

A sensible first week

Turn on both daily and streaming export, then wait. The export is not retroactive, so the day you enable it is the first day you have. That alone is a reason to enable it on every property you own even if nobody will query it for months.

Once data lands, write the parameter inventory query first, because it tells you what you actually have rather than what you think you configured. Then build the flattening view for the columns that survive that audit. Then set a maximum bytes billed on the project and tell everyone about it before they find it the hard way.