What is the GA4 BigQuery export

The first thing worth clarifying is what travels across that link. What gets exported is neither the Analytics reports nor their finished metrics. What gets exported are the events exactly as they were collected, one per row, with their parameters nested inside the same row. The key event rate, the engaged session or the active user do not arrive as a number: they arrive as raw material from which you recalculate them if you need them.

The dataset created in BigQuery carries the property identifier in its name and holds one table per day. Each row brings the event name, the timestamp in microseconds, the pseudonymous identifier of the browser or the installation, the event parameters, the user properties, the ecommerce items and the traffic source data.

That nested structure is the practical difference from a report export into a spreadsheet. It forces you to write SQL with unnesting, and in return it allows questions the interface does not accept, because in the interface every report has already decided for you how the data is grouped.

The link is configured in the property admin and requires editor rights in Analytics and owner rights on the Google Cloud project. There are two modes and both can run at once. The daily batch export sends all of the previous day's events once a day, unsampled, usually in the mid afternoon in the property time zone. The streaming export sends the current day's events within a few minutes, as a best effort service with no completeness guarantee. 360 properties additionally have a faster daily variant.

Each mode writes into its own tables. The daily one produces tables with a date suffix; the streaming one produces intraday tables that are deleted at the end of the day, once the daily table is complete. The intraday tables lack some fields, among them the traffic source. Analytics also keeps updating the daily table for up to three days after the event, to pick up data arriving late from offline devices.

One limit decides many projects. A standard property allows up to one million events per day in the batch export; if that is exceeded, the daily export is paused and previous days are not reprocessed. The streaming export has no such ceiling. The cost is not charged by Analytics: BigQuery charges it, for storage and for the processing of each query, with a monthly free tier, and streaming is billed on top by volume sent. Exporting into the BigQuery sandbox is also possible at no cost, with that environment's limitations.

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

The export solves four things the interface does not. The first is aggregation: reports show already condensed values and, when a dimension has too many distinct values, they collapse the tail under a row called (other). In BigQuery that tail is still there, row by row. The second is attribution: if you want your own model, your own window or your own session definition, you need the original events rather than an already attributed metric.

The third is joining. The order identifier travelling inside the purchase event can be joined with the order table of the back office system, and then the conversation stops being about transactions and becomes one about paid orders, returns and margin. The fourth is retention: the raw data stays in your project, under your retention policy rather than the interface's.

It is equally worth saying what it does not do. It brings no data from before the link was created, and there is no retroactive backfill. It also does not reproduce the modelled metrics of the interface, so a figure from BigQuery and one from Analytics can differ without either being wrong. And it does not replace daily monitoring: for a quick look at a trend, the interface remains the cheapest route.

• Switch the export on when the property is created, even if you are not going to query it yet, because data from before the link cannot be recovered.
• Enable the daily and the streaming export together if you need the current day, and use each table for what it is good at.
• Watch the daily event volume on a standard property and cut superfluous events before you approach the batch export ceiling.
• Always filter by table suffix or by date partition, so a single query does not scan entire years.
• Requery the last three days before calling a figure final, because daily tables keep receiving late events.
• Keep the queries in a version controlled repository and document which metric definition each one implements.

• Expecting the link to bring the history that predates its activation, and finding out only when the number is needed.
• Comparing a BigQuery figure with the interface one at face value and calling it an error, when the gap comes from modelling, attribution or the session definition.
• Ignoring the limit warning email and discovering weeks later that the daily export is paused and those days are gone.
• Treating intraday tables as final, when they are deleted at the close of the day and do not carry every field.
• Running queries without a date filter across the whole dataset and discovering the processing cost at the end of the month.