Why export raw events
Four things justify the export for most marketing teams. The first is exactness: the interface estimates user and sessionA group of one user's interactions with a site or app in one visit, ended by inactivity. 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 KPIA metric tied directly to an objective. needs a multi-year view. The fourth is freedom from the interface's shape: a custom AttributionAssigning conversion credit to touchpoints. 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 group | Shape | What it holds | How to read it |
|---|---|---|---|
| Event core | Flat columns | Name, date string, timestamp, bundle and batch identifiers | Filter and group directly |
event_params | Repeated record of key and typed value | Every parameter the event carried, including ga_session_id, page_location, engagement_time_msec | Extract one key with a sub-select over UNNEST |
user_properties | Repeated record of key and typed value with a set timestamp | User-scoped attributes set by the site | Same extraction pattern as parameters |
user_pseudo_id, user_id | Flat columns | Device-level identifier and the site's own user identifier | Count distinct for users; combine with the session parameter for sessions |
traffic_source | Record | The user's first-touch source, medium and campaign | User acquisition, not session attribution |
collected_traffic_source | Record | Source fields collected with the event, including manual campaign values and click identifiers | Event-level raw source |
session_traffic_source_last_click | Record | The session's last-click source across manual and Google campaign fields | Nearest match to the interface's session dimensions |
device, geo, app_info, platform | Records and flat columns | Device category, browser, operating system, country and city, app version | Group and filter directly |
ecommerce | Record | Transaction identifier, purchase and refund revenue, tax, shipping, item totals | Revenue per transaction without touching items |
items | Repeated record | One entry per product line with the documented item fields and item revenue | UNNEST 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.
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;
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.
