Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Google Analytics 4

BigQuery Export Basics

What the GA4 BigQuery export gives you, how to set it up, the event table schema and nested fields, daily versus streaming, cost awareness, and first queries.
Guideadvanced10 min readUpdated

Why export raw events

Four things justify the export for most marketing teams. The first is exactness: the interface estimates user and session counts, withholds small rows under thresholds, and rolls long tails into an "(other)" row, while the export has every event and every value. The second is joins: campaign cost, CRM outcomes, product margins and offline conversions can be joined to on-site behaviour in one query, which no GA4 report can do. The third is ownership: the property's retention setting limits how far back explorations can look, but tables in your project stay as long as you keep them, which matters when a KPI needs a multi-year view. The fourth is freedom from the interface's shape: a custom Attribution rule, a funnel defined on parameters the interface cannot combine, or a cohort built on a purchase attribute are a query away.

The export is not a replacement for the interface. Standard reports answer recurring questions faster, and the explorations tutorial covers the funnel, path and segment techniques that are quicker to build in the interface than in SQL. The usual division is reports for the routine, explorations for investigation, and the export for anything that needs joins, exact counts or a definition the interface refuses.

Setting up the export

The link is created from the GA4 property, and Google's setup page is the authoritative sequence. In outline: create or choose a Google Cloud project, enable the BigQuery API in it, and make sure the account doing the linking has the editor role on the GA4 property and the documented permissions on the project. Then, in GA4 Admin under product links, create a BigQuery link, select the project, and choose the data location, which is the region where the dataset will live and cannot be changed afterwards; pick the region where other marketing data already sits. Next choose which data streams to include, and optionally list event names to exclude, which is how noisy events are kept out of the tables and the bill. Choose the export frequency, daily or streaming or both, and whether to include the user data export, which adds separate daily tables describing users rather than events. Review and submit.

Three documented behaviours matter from the first day. Nothing is backfilled: the first table is the first full day after linking. The daily export on standard properties is subject to a documented daily event volume limit, above which Google pauses it, while the streaming export is not subject to that limit. And a project without billing enabled can use the BigQuery sandbox, where tables have a documented expiry, so a property intended for real analysis needs billing enabled and a storage policy before the data matters. Note the link date in the measurement log; it is the first date any query can return. The measurement change log tutorial sets up that log and backfills it from container versions and tickets.

The schema: events, nested params, user and item records

The export creates a dataset named for the property and, within it, one table per day with a name ending in the date, so a query addresses a date range with a wildcard on the table name and a filter on the suffix. Each row is one event, and most of the row is flat: the event name, the date string, a timestamp in microseconds, the pseudonymous user identifier, the user ID where the site sets one, and records for device, geography, platform, app information and privacy status. The user's first-touch traffic source sits in its own record, which is user-level acquisition rather than session or event attribution.

The parts that trip people up are nested. Event parameters are stored as a repeated record of key and value pairs, where the value is itself a record with a slot for each type, string, integer, float and double, and only one slot is filled per parameter. User properties follow the same pattern. The items array on e-commerce events is a repeated record with the documented item fields and derived revenue fields, and the e-commerce record on the same row holds the transaction identifier, revenue, tax, shipping and quantity totals. Newer rows also carry a record of the traffic source collected with the event, including manual campaign fields and click identifiers, and a record of the session's last-click source, which is the closest the export comes to the interface's session attribution.

Field groupShapeWhat it holdsHow to read it
Event coreFlat columnsName, date string, timestamp, bundle and batch identifiersFilter and group directly
event_paramsRepeated record of key and typed valueEvery parameter the event carried, including ga_session_id, page_location, engagement_time_msecExtract one key with a sub-select over UNNEST
user_propertiesRepeated record of key and typed value with a set timestampUser-scoped attributes set by the siteSame extraction pattern as parameters
user_pseudo_id, user_idFlat columnsDevice-level identifier and the site's own user identifierCount distinct for users; combine with the session parameter for sessions
traffic_sourceRecordThe user's first-touch source, medium and campaignUser acquisition, not session attribution
collected_traffic_sourceRecordSource fields collected with the event, including manual campaign values and click identifiersEvent-level raw source
session_traffic_source_last_clickRecordThe session's last-click source across manual and Google campaign fieldsNearest match to the interface's session dimensions
device, geo, app_info, platformRecords and flat columnsDevice category, browser, operating system, country and city, app versionGroup and filter directly
ecommerceRecordTransaction identifier, purchase and refund revenue, tax, shipping, item totalsRevenue per transaction without touching items
itemsRepeated recordOne entry per product line with the documented item fields and item revenueUNNEST to get item-level rows

The extraction pattern for a parameter is the one idiom every GA4 query uses. A sub-select over the unnested parameter array, filtered to one key, returns the value from the slot that holds it:

SELECT
  event_date,
  event_name,
  (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 session_id
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN @start_date AND @end_date

Two details follow from the schema. Sessions do not exist as rows; a session is the combination of the pseudonymous user identifier and the session identifier parameter, and session counts are built by counting those distinct combinations. And key events are not flagged in the export; they are ordinary events selected by name, so the list of key events belongs in the query or in a reference table rather than being assumed.

Daily vs streaming and what changes

With the daily export, one table per day appears once Google has finished processing that day, which is usually during the following day but is not guaranteed to a fixed hour. The table is the complete, processed record and is the right basis for reporting and analysis. With the streaming export, events are written continuously into an intraday table for the current day, which exists only until the daily table for that day is complete and is then removed. Intraday rows are provisional: Google documents that some fields which depend on daily processing are absent or incomplete in intraday tables, so a query that reads both the daily and intraday tables must tolerate different completeness and should not be used for period-closing figures.

The practical rule is to build reporting on daily tables only, to use the intraday table for monitoring and alerting where today matters, and to write the date logic once so that scheduled queries do not accidentally read a day that has not landed. A query that runs early in the morning and expects yesterday's table will sometimes find it missing; schedule after the usual arrival time, or check for the table's existence and retry. The user data export, when enabled, adds its own daily tables describing users rather than events and follows the daily cadence.

Cost awareness without quoting prices

BigQuery charges for two things, storing data and processing queries, and the export itself is free. The charges are modest for most marketing properties and surprising for a few, and the difference is almost entirely query habits. Processing is billed on the bytes a query reads, not the rows it returns, so a query that selects every column across every day costs the same whether it returns one row or a million. Google documents current prices and the free allowances on its pricing page; this section is about the habits that keep the figure small whatever the price.

  • Select columns, never everything. The nested parameter and item columns are the bulk of each row; touching them only when needed is the biggest single saving.
  • Bound every query by date. The suffix filter on the wildcard table prunes whole tables; a query without one reads the entire history.
  • Read the estimate before running. The console shows the bytes a query will process before it runs; make reading it a reflex.
  • Set a limit on bytes billed. A per-query maximum and a project-level quota turn a runaway query into an error instead of an invoice.
  • Build summary tables. Scheduled queries that aggregate raw events into daily tables by channel, page or product mean dashboards read small tables rather than raw events on every refresh.
  • Keep dashboards off raw events. A dashboard connected straight to the events tables scans them for every viewer and filter change; point it at the summaries instead, as the reporting and BI hub recommends.
  • Decide retention deliberately. Storage is cheap per unit and grows for ever; set an expiry on intraday and scratch tables and an explicit policy on the daily tables.

First queries

The queries below are the first five most teams write, and each introduces a pattern that the SQL for marketers guide develops further: counting events, extracting a parameter, building sessions, reading e-commerce revenue, and attributing key events to a session source. They are written against a fictional project and property, with literal dates to show the suffix filter; replace the identifiers and prefer parameters in scheduled work.

Illustrative example — not a real result

Daily events, page views and sessions for a retailer's property

A homeware retailer links property 123456789 in project homeware-analytics and, after the first week of tables arrives, runs three queries over 1 to 7 March. The first counts events by name per day and shows page_view at 412,380 for the week, view_item at 96,110 and purchase at 2,215, which matches the interface's event counts to within a few dozen. The second extracts the page location to find the ten most viewed pages. The third builds sessions from the user and session identifiers and returns 171,900 sessions for the week against 174,200 in the interface, the gap being the interface's estimation. The analyst saves all three as views so the dashboard team never has to write the UNNEST idiom themselves.

-- Events by name per day
SELECT event_date, event_name, COUNT(*) AS events
FROM `homeware-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
GROUP BY event_date, event_name
ORDER BY event_date, events DESC;
-- Top pages by views
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(*) AS views
FROM `homeware-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
  AND event_name = 'page_view'
GROUP BY page_location
ORDER BY views DESC
LIMIT 10;
-- Sessions and users per day
SELECT
  event_date,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `homeware-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
GROUP BY event_date
ORDER BY event_date;
Illustrative example — not a real result

Revenue and key events by session source for the same week

The same analyst then answers the two questions the marketing lead actually asked. Purchase revenue per day comes from the e-commerce record without unnesting items, and totals £188,400 for the week against £188,950 in the shop's own system, the difference being three refunds not yet sent as refund events. Key events by session source use the session last-click record and show that organic search sessions produced 34% of purchase events and paid search 29%, with generate_lead behaving differently, 41% from paid social. The attribution settings in the interface would have distributed these differently under data-driven credit; the export gives the raw last-click view the team can rebuild under any rule.

-- Purchase revenue and transactions per day
SELECT
  event_date,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  SUM(ecommerce.purchase_revenue) AS revenue
FROM `homeware-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date;
-- Key events by session last-click source and medium
SELECT
  session_traffic_source_last_click.manual_campaign.source AS source,
  session_traffic_source_last_click.manual_campaign.medium AS medium,
  event_name,
  COUNT(*) AS key_events
FROM `homeware-analytics.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
  AND event_name IN ('purchase', 'generate_lead')
GROUP BY source, medium, event_name
ORDER BY key_events DESC
LIMIT 25;

Each of these grows naturally: the sessions query becomes a landing-page and channel table, the revenue query joins to a product margin table, and the key events query becomes a custom attribution model once the touchpoints of each user are ordered in time. The business intelligence hub sets out the modelling, metrics layer and governance work that turns a folder of queries into something a company can rely on, and the GA4 hub links the interface-side pages, from events to reports, whose definitions the queries need to respect.

When query results and the interface disagree, the diagnosis page for a BigQuery export that does not match GA4 sorts each gap into expected difference or query error.

Common mistakes

  • Linking late. Nothing is backfilled; the export only ever knows the days after the link.
  • Selecting every column. The nested columns are the cost; touch them only when needed.
  • Forgetting the suffix filter. A wildcard query without a date bound reads the whole history.
  • Reading intraday tables for closed figures. They are provisional and disappear when the daily table lands.
  • Counting sessions by the session identifier alone. A session is the user identifier and the session identifier together.
  • Assuming key events are flagged. They are plain events; keep the list of key event names in a reference table.
  • Expecting the interface and the export to match exactly. Estimation, modelling, thresholds and identity settings exist only in the interface.
  • Connecting a dashboard to raw events. Summarise first, then visualise; every viewer otherwise pays for a full scan.
  • No owner for the project. Billing alerts, quotas, retention and access need a named person, or the first surprise invoice ends the project.

Frequently asked questions

Does the BigQuery export include historical GA4 data?

No. The export starts with the day after the link is created and nothing earlier is backfilled, which is the strongest argument for linking a property early even if nobody plans to query it yet. Historical data stays available only through the GA4 interface, subject to the property's data retention setting for explorations.

Why do counts from the export differ from the GA4 reports?

The interface estimates user and session counts with an approximation technique, can add modelled data under consent mode, applies data thresholds, and uses the property's reporting identity to decide who counts as one user. The export contains the raw events with a pseudonymous identifier and none of those adjustments. Small gaps are normal; a large one usually means the two queries are not counting the same thing.

Do I need the streaming export?

Only if someone acts on today's data. The daily export is enough for reporting, attribution work and most analysis, and it avoids the provisional intraday tables and their extra storage and query cost. Enable streaming when a use case such as same-day alerting or near-real-time personalisation actually exists, and be aware of the documented differences in what intraday rows contain.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. [GA4] Set up BigQuery Export — Google Analytics Help
  2. Google Analytics Help — Google Analytics Help

Related