{"id":10122,"date":"2026-09-27T08:05:00","date_gmt":"2026-09-27T06:05:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/blog\/?p=10122"},"modified":"2026-10-26T11:41:34","modified_gmt":"2026-10-26T10:41:34","slug":"tutorial-exporting-ga4-data-to-bigquery-and-writing-the-first-sql-query","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-analytics\/tutorials\/tutorial-exporting-ga4-data-to-bigquery-and-writing-the-first-sql-query\/","title":{"rendered":"Tutorial: Exporting GA4 Data to BigQuery and Writing the First SQL Query"},"content":{"rendered":"

BigQuery makes exported GA4 events available for custom SQL analysis: joins with business data, explicit metric definitions and observed page sequences. GA4 also offers path and funnel explorations; SQL adds control over the calculation and output rather than making all interface analysis obsolete.<\/p>\n

This tutorial starts with daily export, checks whether events arrive and then groups observed web page views by session identifiers. The examples describe the selected export, not every real visit or a guaranteed complete journey.<\/p>\n

\"Export
Export scope, stable identifiers and ordering limits determine the meaning of a reconstructed path.<\/figcaption><\/figure>\n

1. Daily export setup<\/h2>\n

In GA4, the link is created under Admin \u2192 Product links \u2192 BigQuery links<\/strong>. The GA4 account needs Editor or higher access at property level. The Cloud project needs the documented IAM and service permissions; Owner includes those permissions, but a suitable narrower role combination can also provide the required access. The BigQuery API must be enabled.<\/p>\n

The setup selects the Cloud project, data location, streams and any excluded events. For standard properties, Daily and Streaming are available independently. Eligible Analytics 360 properties also offer Fresh Daily. Daily produces events_YYYYMMDD<\/code>; Streaming produces provisional events_intraday_YYYYMMDD<\/code> tables and can have gaps. The daily table is preferable for completed-day analysis, although late events can update recent daily tables.<\/p>\n

The link does not backfill the property’s previous history. Initial data normally starts arriving within 24 hours; a particular daily delivery time is not guaranteed. Standard properties have a daily limit of one million exported events. Exceeding the limit can suspend daily export; stream and event exclusions can reduce volume but also change the scope of subsequent analysis.<\/p>\n

Daily export does not make Cloud usage free: storage and query processing can incur charges. Streaming adds ingestion costs. Before the first query, the editor’s estimate or a dry run, which checks the query without running it, shows expected processing; maximum bytes billed<\/code> provides a per-query limit under on-demand pricing. The examples below use a deliberately bounded date range.<\/p>\n

2. Differences from GA4 reports<\/h2>\n

The event export is unsampled and does not collapse high-cardinality values into an (other)<\/code> row. GA4 reports and explorations can use different source tables and may involve sampling, aggregation limits or privacy thresholds. Thresholds are relevant, for example, to demographic and search-query information; enabling Google signals is not a universal explanation for every missing row.<\/p>\n

BigQuery does not reproduce GA4’s behavioral or key-event modeling or data-driven attribution. This does not mean that all attribution fields are absent: the current schema includes observed last-click session traffic-source fields where available. Differences should be investigated by matching the metric, date range, property timezone, identity method, export filters and data freshness. A discrepancy is not automatically a fault, but also not automatically harmless.<\/p>\n

3. The nested schema<\/h2>\n

Each exported row represents an event. Top-level fields include event_name<\/code>, event_date<\/code>, event_timestamp<\/code> and user_pseudo_id<\/code>. Additional values such as ga_session_id<\/code> and page_location<\/code> sit in event_params<\/code>, a list of name\/value pairs within the event row. UNNEST<\/code> exposes those records; a scalar subquery, which returns a single value, retrieves the value for one key.<\/p>\n

event_date<\/code> follows the configured reporting timezone. event_timestamp<\/code> is measured in microseconds UTC, but several batched events can share a timestamp. Batch fields add ordering information. A pseudo-ID identifies an app instance or browser context, not a verified person; missing or changing identifiers limit reconstruction. The query keeps stream_id<\/code>, pseudo-ID and session ID as separate grouping fields.<\/p>\n

4. Exported event availability<\/h2>\n

The project ID, property dataset and dates below are placeholders to replace with existing daily tables. GoogleSQL is required.<\/p>\n

SQL<\/span>