The GA4 Database Export Schema, Explained Field by Field for Marketers

You link GA4 to BigQuery, open the dataset expecting a table called events, and find 400 tables named events_20260112. You open one and the columns make no sense — where is the page URL, where is the transaction value, and why is everything inside something called event_params?

This is where most marketers bounce off the GA4 export. The interface thinks in dimensions and metrics; the export thinks in events, parameters, and nested records. Same data, reorganised for a warehouse — and four ideas unlock 90% of what you will ever query: date-sharded tables, one UNNEST pattern, two identity columns, and a short list of fields.

Why the export is date-sharded, not one flat table

GA4 writes one table per day — events_20260812, events_20260813, and so on — plus an events_intraday_* table that fills during the current day and is deleted once the daily table lands. It looks messy in the explorer. It is a cost control.

Each daily table holds only that day's events, so a query over the last 7 days reads 7 tables and ignores the other 400. BigQuery charges for bytes scanned, so sharding keeps a "last week's purchases" query at megabytes instead of terabytes. You select across days with a wildcard plus a _TABLE_SUFFIX filter — BigQuery's way of saying "only open these shards":

-- BigQuery prunes shards before reading anything. This filter is load-bearing.
SELECT event_name, COUNT(*) AS events
FROM `my-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260807'
GROUP BY event_name
ORDER BY events DESC;

Three rules follow. First, always filter _TABLE_SUFFIX — a bare wildcard opens every daily table back to the start of the export, the most common GA4 cost accident. Second, never build reporting on events_intraday_* — intraday rows are deleted and re-written into the daily table, so anything built on them double-counts or vanishes. Intraday is for same-day debugging only. Third, expect late data: daily tables finalise 24–72 hours after the day ends with conversions backfilled, so yesterday's numbers move slightly when re-run next week.

event_params: the UNNEST pattern, explained once

Everything interesting lives inside event_params — page location, campaign source, transaction id, value, search term. The table itself carries only the skeleton: event_name, event_timestamp, user_pseudo_id, geo, device, traffic_source. The flesh is a repeated record of key-value pairs, unpacked with exactly one pattern. Learn it once and you are done.

Why scalar subqueries instead of CROSS JOIN UNNEST? A cross join multiplies rows — one per parameter — forcing you to collapse them back with conditional aggregation. The subquery form keeps one row per event and performs the same. Each parameter has a typed value — string_value, int_value, float_value — and you must pick correctly: purchase value is a float, ga_session_id is an int, page_location is a string. The wrong type returns NULL silently, the cause of half of all "my GA4 query returns nothing" sessions.

The same pattern unpacks the rest. items holds one row per product on purchase events — unnest it for line-item detail rather than the order total. user_properties mirrors event_params at user scope. ecommerce and collected_traffic_source are flattened conveniences, but event_params is the source of truth when they disagree, because it is what the SDK actually sent.

user_pseudo_id vs user_id

GA4 gives you two identity columns for different questions. user_pseudo_id is the device-and-browser identifier GA4 assigns itself — on every event, stable until cookies clear, and the only identity most events carry. user_id is your identifier, present only when you set it — after login or checkout. Confusing them skews user counts in whichever direction embarrasses you most.

Use user_pseudo_id for top-of-funnel counting: sessions, landing-page users, campaign reach. It overcounts humans — phone plus laptop is two pseudo-ids — but it is complete, and completeness beats precision in acquisition measurement. Use user_id for bottom-of-funnel truth: purchasers, subscribers, repeat buyers. It undercounts — logged-out purchases are NULL — but everyone counted is a real known person.

The honest setup reports both and names the gap. Distinct user_pseudo_id is reach, distinct user_id is identified customers, and their ratio is login coverage — typically 5% to 25% on content and commerce sites. Ten times more pseudo-users than user_ids is normal, not broken; presenting one as the other is what breaks.

One consequence: joining GA4 to orders must go through user_id, never user_pseudo_id, and only for logged-in purchasers. There is no click id in the export, so campaign-level joins still aggregate both sides to date-plus-source rather than any row-level key. The export is your behavioural source of truth, the warehouse your revenue source of truth, reconciled at the day grain.

The fields marketers query 90% of the time

Strip the nesting and the export reduces to a short list — the fields behind nearly every marketing question:

Sessions confuse everyone once: GA4 has no session id column — a session is user_pseudo_id plus the ga_session_id parameter, and counting sessions means counting distinct pairs. The query below is the template: daily active users, sessions, purchases and revenue in one pass, with the schema mapping from parameters to columns made explicit.

Partition-pruning note: the _TABLE_SUFFIX predicate is what makes this cheap. BigQuery resolves the wildcard before reading data and opens only the 31 matching shards; the other 370-odd tables cost nothing. Write the range as BETWEEN on the raw suffix string — wrapping it in PARSE_DATE or CAST in the WHERE clause can defeat pruning, silently turning a 2 GB query into a 200 GB one. Parse dates in the SELECT, filter raw strings in the WHERE.

Frequently asked questions

Why do my BigQuery user counts not match the GA4 interface?

Small differences are expected. The interface applies thresholding, sampling on large ranges, and identity resolution (blended or device-based, per your settings) that the raw export does not. The export counts exactly what was collected. If the gap exceeds a few percent, check you excluded events_intraday_* and that both sides use the same timezone.

How do I get session source and medium reliably?

Prefer collected_traffic_source.manual_source and manual_medium where populated, falling back to the session_source and session_medium parameters. First-touch source lives on session_start; stamping it onto every event needs a window function over the session pair. For channel reporting, aggregate session_start events rather than stamping every page view.

Should I query the streaming intraday tables?

Only for same-day spot checks. Intraday tables are best-effort, can contain duplicates, and are deleted when the daily export lands. Scheduled queries and dashboards must read events_* with a suffix range ending at least two days back, treating the most recent two days as provisional.

How far back should I keep daily tables?

A busy site generates tens of gigabytes per day. Keep raw dailies for 13–14 months to cover year-over-year comparisons, then rely on your own materialised daily rollups for longer history. The rollup is gigabytes per year; the raw export is terabytes — the standard bytes scanned advice: shrink what recurring queries read.

What about consent mode and missing data?

Declined-consent events arrive with limited identifiers — often no user_id and reduced traffic_source fidelity. Do not try to recover them with clever SQL. Report the identified share separately, keep blended totals as the headline, and treat the gap like unattributed orders: visible, labelled, part of the number.

The summary

• The export is one table per day plus a transient intraday table — always query events_* with a _TABLE_SUFFIX range, never the bare wildcard.
• The suffix filter is your cost control: it decides bytes scanned before anything else runs, and wrapping it in date functions can defeat pruning.
• event_params holds everything interesting; the scalar-subquery UNNEST pattern unpacks it while keeping one row per event, with the correct typed value per key.
• user_pseudo_id measures reach, user_id measures known customers — label which one every user count means, and join to the warehouse only on user_id.
• Eight fields — pseudo-id, session pair, source and medium, page, transaction fields, items, device and geo — answer 90% of marketing questions once the schema mapping is written down.