![\"Flow](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/ga4-bigquery-export-en.png\")
Step 1: Switching the Export On<\/h2>\n
The link is created in GA4 under Admin, in the Product links section, via BigQuery links. Creating it requires edit rights on the GA4 property and owner rights on the destination Google Cloud project, with the BigQuery API enabled. The configuration asks for the Cloud project, a data location, which data streams to include, and an export frequency.<\/p>\n
Two frequencies are offered, and they behave differently. Daily<\/strong> writes one table per day, Two constraints matter before relying on any of this. The export is not retroactive<\/strong> \u2014 it begins on the day the link is created, and no history is backfilled, which makes switching it on early worthwhile even without an immediate use for the data. And standard properties carry a documented daily export limit of one million events per day; a property exceeding it can have the export suspended, so the current volume is worth checking against the limit in Google’s documentation before the export becomes load-bearing.<\/p>\n On the BigQuery side, the free tier covers 10 GiB of storage and 1 TiB of query processing per month, which is generous for a single property being explored by hand.<\/p>\n Avoiding sampling in the export is not a technique \u2014 it follows from the export being the unaggregated event stream rather than a report. Three separate reductions the interface applies are simply absent:<\/p>\n None of the three applies to the exported tables. The trade is that everything the interface computes on top of the raw data \u2014 modelled conversions, data-driven attribution, its own session definitions \u2014 is also absent, which is why totals from BigQuery and totals from the interface will not match exactly.<\/p>\n Each row is one event. What surprises anyone arriving from a conventional SQL background is that the interesting values are not columns: they sit inside A session has no column of its own. It is reconstructed by pairing Before anything analytical, one query establishes that data is arriving and that the table reference is correct:<\/p>\n The The query the interface cannot produce is a full page sequence per session. Reading a value out of Each row is one session’s journey in order, with the domain and query string stripped so that identical pages group together. From here the same structure answers more specific questions: filtering to sessions that contain a particular event, counting how often one page precedes another, or measuring how many steps precede a purchase.<\/p>\n Two expectations are worth setting before this data reaches a report. Numbers from the export will not reconcile exactly with the GA4 interface, and that is by design rather than a fault to be chased: the interface adds modelling and its own session logic that the raw rows do not contain. And the export carries event-level data covering identifiable devices, so the same consent basis and retention discipline that applies to analytics data applies to the dataset in Cloud \u2014 including who is granted access to the project.<\/p>\n With the suffix filter from step 4, no; without it, possibly. The wildcard events_* also matches the events_intraday_YYYYMMDD tables, because their names start with events_ as well. For them, _TABLE_SUFFIX is then not 20260926 but intraday_20260926.<\/p>\n The condition A query can only count twice if, without a suffix filter or with a pattern that is too broad, it picks up both kinds of table and both exist for the same day. A query that mixes the two sources should therefore count only one of them per day.<\/p>\n No. BigQuery bills by the bytes a query has to read, and LIMIT only caps the output after the data has been read. Because BigQuery stores data by column, two other measures do cut costs: selecting only the columns that are needed instead of SELECT *, and the filter on _TABLE_SUFFIX, which takes whole daily tables out of the bill.<\/p>\n No. The retention setting in GA4 covers the data in GA4 itself. Whatever has been exported to BigQuery sits there as a separate copy in the Cloud project and stays until it is deleted there. Left alone, the dataset therefore grows day by day, and with it the storage, which can eventually exceed the free 10 GiB.<\/p>\n The retention discipline the article also calls for on the dataset therefore has to be set up in BigQuery. A default expiration for tables can be set on the dataset for this (in the API, the field defaultTableExpirationMs); new daily tables are then deleted automatically once that period has passed. It only applies to tables created after the change; older tables need an expiration of their own or have to be removed by hand.<\/p>\n Yes; BigQuery then runs as a sandbox, but with two restrictions that matter for GA4. Tables in the sandbox expire after 60 days, so the daily export never holds more than about two months. And the streaming export is not available there. That is enough for getting to know the data; building a long time series needs a billing account, even if usage stays within the free tier.<\/p>\n<\/div>\nevents_YYYYMMDD<\/code>, usually available the following day; it is included at no cost for standard properties. Streaming<\/strong> writes to events_intraday_YYYYMMDD<\/code> continuously and is billed at BigQuery’s streaming insert rates. For learning the data and for most analysis, daily alone is enough.<\/p>\nStep 2: What \u201cUnsampled\u201d Actually Means<\/h2>\n
\n
(other)<\/code> row.<\/strong> When a dimension produces more distinct values than a report can hold, the remainder is collapsed into a single (other)<\/code> bucket \u2014 a real problem for page paths, product IDs, or search terms.<\/li>\nStep 3: The Shape of the Data<\/h2>\n
event_params<\/code>, a repeated record of key\/value pairs, where the value itself is a struct with a separate field per type.<\/p>\n\n\n
\n \nField<\/th>\n Type<\/th>\n What it holds<\/th>\n<\/tr>\n<\/thead>\n \n event_date<\/code><\/td>\nSTRING<\/td>\n The day as YYYYMMDD<\/code>, matching the table suffix.<\/td>\n<\/tr>\n\n event_timestamp<\/code><\/td>\nINT64<\/td>\n Microseconds since epoch \u2014 the ordering key within a session.<\/td>\n<\/tr>\n \n event_name<\/code><\/td>\nSTRING<\/td>\n page_view<\/code>, session_start<\/code>, purchase<\/code>, and custom events.<\/td>\n<\/tr>\n\n user_pseudo_id<\/code><\/td>\nSTRING<\/td>\n The client identifier \u2014 a device or browser, not a person.<\/td>\n<\/tr>\n \n event_params<\/code><\/td>\nREPEATED RECORD<\/td>\n Key\/value pairs: ga_session_id<\/code>, page_location<\/code>, page_title<\/code> and the rest.<\/td>\n<\/tr>\n\n items<\/code><\/td>\nREPEATED RECORD<\/td>\n E-commerce products attached to the event.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n user_pseudo_id<\/code> with the ga_session_id<\/code> parameter, since session ids are only unique within a single client.<\/p>\nStep 4: A First Query to Confirm the Export Works<\/h2>\n
SELECT\n event_date,\n COUNT(*) AS events,\n COUNT(DISTINCT user_pseudo_id) AS users\nFROM `project.analytics_XXXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260920' AND '20260926'\nGROUP BY event_date\nORDER BY event_date;<\/code><\/pre>\n_TABLE_SUFFIX<\/code> filter is not optional housekeeping. The wildcard events_*<\/code> addresses every daily table in the dataset at once, and without a suffix filter the query scans the entire export history \u2014 which is billed by bytes read.<\/p>\nStep 5: Extracting Raw User Paths<\/h2>\n
event_params<\/code> is done with a scalar subquery over UNNEST<\/code>, which is the idiom worth memorising \u2014 it recurs in every GA4 query:<\/p>\nWITH page_views AS (\n SELECT\n user_pseudo_id,\n (SELECT value.int_value FROM UNNEST(event_params)\n WHERE key = 'ga_session_id') AS session_id,\n event_timestamp,\n (SELECT value.string_value FROM UNNEST(event_params)\n WHERE key = 'page_location') AS page_location\n FROM `project.analytics_XXXXXXXXX.events_*`\n WHERE _TABLE_SUFFIX BETWEEN '20260920' AND '20260926'\n AND event_name = 'page_view'\n)\nSELECT\n CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING)) AS session_key,\n COUNT(*) AS page_views,\n STRING_AGG(\n REGEXP_EXTRACT(page_location, r'^https?:\/\/[^\/]+([^?#]*)'),\n ' > ' ORDER BY event_timestamp\n ) AS path\nFROM page_views\nWHERE session_id IS NOT NULL\nGROUP BY session_key\nHAVING page_views > 1\nORDER BY page_views DESC\nLIMIT 100;<\/code><\/pre>\nStaying Inside the Guardrails<\/h2>\n
Questions and answers<\/h2>\n
Does events_* count events twice when the streaming export is switched on?<\/h3>\n
_TABLE_SUFFIX BETWEEN '20260920' AND '20260926'<\/code> excludes those tables, because the comparison is made character by character and the letter i sorts after every digit. Anyone who needs the current day’s data from the intraday table therefore has to include it explicitly, for example with an additional condition _TABLE_SUFFIX = 'intraday_20260927'<\/code>.<\/p>\nDoes LIMIT 100 reduce the cost of a query?<\/h3>\n
Does the retention period set in GA4 also apply to the tables in BigQuery?<\/h3>\n
Does the export also work without a billing account in Google Cloud?<\/h3>\n