GA4 and BigQuery: When, Why and How

The BigQuery export is GA4’s escape hatch: raw, unsampled, event-level data in a real database, free to link on every property. This guide covers both halves of the decision — when and why a marketing team should bother, and what the exported data actually looks like once it lands.

When and why marketers reach for BigQuery

If you work in marketing, you have probably been told to "just use BigQuery" by someone who made it sound obvious. It is not obvious, and you are not behind for finding it intimidating. The good news: the core idea is simple, and you can get useful answers out of it without ever calling yourself technical. This guide walks through what BigQuery actually is, why a marketer would touch it, and how to read your first query without a surprise on the bill.

What BigQuery actually is

BigQuery is a cloud data warehouse: a place to store and query very large tables of data. It is part of Google Cloud, Google's platform for storage and computing. The key thing to understand is what it is not. It is not a dashboard you log into every morning to glance at numbers. It is closer to a giant, fast spreadsheet engine that you ask questions of in a query language, and that hands back a table of results.

Because it is built for scale, BigQuery can answer questions across billions of rows in seconds. That power is the whole point, and it is also where the cost model comes in later. For now, hold one picture in your head: data goes in, you write a question, a fresh table of answers comes out.

Why a marketer would ever touch it

The most common reason is the raw GA4 BigQuery export. If you connect Google Analytics 4 to BigQuery, Google drops a copy of every single event into the warehouse: no sampling, no thresholding, no rows hidden because the volume was too low. The GA4 interface is excellent for everyday work, but it estimates and aggregates once you push it hard. The export gives you the unfiltered source. If you are still finding your feet in GA4 itself, our Google Analytics 4 for beginners guide is a gentler first stop.

Beyond un-sampled data, marketers reach for BigQuery to:

• Join sources together, for example stitching GA4 sessions to Google Ads spend and CRM revenue in one query.
• Query long histories and volumes the GA4 interface simply will not show.
• Build a reliable base that feeds dashboards and reports the same way every time.

How it is organised: project, dataset, table

BigQuery has three layers, and the easiest way to picture them is a filing cabinet.

• Project: the container and the thing that gets billed. Think of it as the whole cabinet, with your Google Cloud account attached.
• Dataset: a folder inside the project that groups related tables.
• Table: the actual data, in rows and columns, sitting inside a dataset.

The GA4 export uses this structure in a predictable way. Each day of data lands in its own table named events_YYYYMMDD, so a single day looks like events_20260601. One table per day keeps each one a manageable size and, as you will see, lets you query only the dates you need.

Your first query, line by line

Queries are written in SQL, a plain language for asking questions of tables. Here is a small, correct one that counts events per day across the GA4 export for a single month.

SELECT
  event_date,
  COUNT(*) AS event_count
FROM `my-project.analytics_123456.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260630'
GROUP BY event_date
ORDER BY event_date;

Reading it from the top: SELECT lists the columns you want back, here the date and a count. COUNT(*) tallies how many event rows exist, and AS event_count names that column. FROM points at the tables; the events_* wildcard means "all the daily export tables at once." The WHERE line is the important one: _TABLE_SUFFIX is the date part of each table name, so this limits the query to June 2026 only. GROUP BY rolls the count up per day, and ORDER BY sorts the result. The output is a tidy table: one row per day, with its event total. To go deeper on what each event carries, see GA4 event parameters explained.

The cost model, and how to stay safe

This is the part worth slowing down for. With BigQuery on-demand pricing, you are charged by the volume of data scanned to answer a query, measured in bytes, not by how many rows come back. Asking for ten rows can still scan a terabyte if you point the query at everything.

Those three habits keep almost everyone out of trouble. Naming columns instead of using a star avoids reading data you will never look at. Filtering on the date means BigQuery touches only the day tables in range rather than all of history. And the validator's estimate, shown next to the editor before you run anything, tells you the size of the bill in advance. If the estimate looks large, you can fix the query before it costs you, not after.

BigQuery or just the GA4 interface?

Most reporting does not need BigQuery at all. Reach for the warehouse when the question outgrows the interface, and stay in GA4 when it does not.

Where to start

You do not need to master SQL to benefit from BigQuery. Start by enabling the GA4 export so the data is accumulating, then run the small query above against a single month and watch the bytes estimate. Keep the three cost habits in your hands from day one: name your columns, filter on the date, check the estimate. From there, every new question is a small variation on something you already understand.

The 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.

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.