The GA4 BigQuery Export Schema, Explained

The GA4 BigQuery export is the most honest version of your analytics data: one row per event, nothing aggregated, nothing sampled. It is also the version that stops most marketers cold the first time they open a table. The columns are not the tidy list you expected, and the value you actually want, the page URL, the session ID, the item revenue, seems to be hiding inside something called event_params. This guide walks the schema in plain English and ends with one query that pulls a value out of that nest.

What the export creates

When you link a GA4 property to BigQuery, GA4 writes a dataset for that property (named analytics_XXXXXXXXX, where the digits are your property ID). Inside it, you get one table per day, named events_YYYYMMDD, so events_20260601 holds everything that happened on 1 June 2026. There is also an events_intraday_YYYYMMDD table that fills up through the current day before being folded into the finished daily table. For the bigger picture of why this export matters, see our pillar on BigQuery for marketers.

The shape to hold in your head is simple: one row equals one event. A single page view, a single scroll, a single purchase. Everything about that event, who fired it, from where, with what parameters, lives on that one row. That is why the columns get deep rather than wide.

The top-level fields

The columns at the top of the row fall into a few groups. The flat, single-value ones are easy:

• Time: event_date (a string like 20260601) and event_timestamp (microseconds since 1970).
• Identity: user_pseudo_id (the cookie or device level ID, always present) and user_id (your own logged-in ID, only if you set it).
• The event: event_name, the string such as page_view, session_start, or purchase.

Then come the nested ones. Two BigQuery terms unlock the whole schema. A RECORD (also called a STRUCT) is a group of sub-fields bundled under one column, so device.category and device.operating_system both live under device. A REPEATED field is an array: zero, one, or many values in the same cell. Put them together and a REPEATED RECORD is an array of those little groups.

The nested columns include device, geo, and traffic_source (each a single RECORD), plus event_params, user_properties, and items (each a REPEATED RECORD). The single RECORDs are friendly: you read geo.country directly. The repeated ones are where people get stuck.

Why event_params is the hard part

Here is the crux. event_params is a REPEATED RECORD, an array with one entry per parameter the event carried. Each entry has a key (the parameter name, like page_location or ga_session_id) and a value struct. That value struct has four possible typed slots: string_value, int_value, float_value, and double_value. Only the one matching the parameter's type is filled; the rest are null.

So a page_view event does not have a neat page_location column. It has an array of key-value pairs, and one of those pairs happens to have the key page_location with its URL sitting in string_value. That is why SELECT event_params.page_location fails: you cannot reach into an array with a dot. You have to unpack it first. If the parameter idea itself is new, our guide to GA4 event parameters covers what GA4 attaches to each event and why.

Common fields at a glance

Pulling a value out with UNNEST

To read a parameter you flatten the array with UNNEST and pick out the row whose key you want. This query pulls the page URL from every page_view across a date range:

SELECT
  event_date,
  user_pseudo_id,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location
FROM `your_project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
  AND event_name = 'page_view'

Reading it line by line: the SELECT lists the flat columns you want directly. The bracketed subquery does the unpacking, it runs UNNEST(event_params) to turn the array into a small set of rows, filters to the one row where key = 'page_location', and returns its value.string_value. We pick string_value because a URL is text; for ga_session_id you would read value.int_value instead. The FROM clause uses a wildcard table (events_*) so the query can span many daily tables at once. The WHERE clause then uses _TABLE_SUFFIX, the part of the table name the wildcard matched, to limit the scan to one week, and event_name = 'page_view' narrows it to the events we care about.

The items array (your ecommerce products) works exactly the same way. Each purchase event carries an items array with one entry per product, and you UNNEST(items) to read item_name, price, or quantity per row. Same pattern, different array.

In Clearly

Once you have an UNNEST query you trust, you do not want to re-run it by hand every Monday. In Clearly you can save that exact SQL as a BigQuery data source, so the flattening is defined once and refreshed on a schedule. The nested mess stays in the query; what comes out is a tidy table with a page_location column you can actually work with.

From there you point a report block at the source and chart it like any other metric: page views over time, top URLs in a breakdown table, sessions by device. The marketer reading the report never sees an array or a struct. They see the answer, which is the whole point of doing the unpacking once and reusing it.

Where to go next

The export looks intimidating because it trades convenience for completeness, but the schema is more regular than it first appears: flat fields you read directly, single RECORDs you reach with a dot, and repeated RECORDs you flatten with UNNEST. Learn that one UNNEST pattern and most of GA4's raw data opens up. Filter on the date, name your columns, and let the query do the unpacking so your reports do not have to.