GA4 to BigQuery SQL Guide: Querying Raw Event Data for Advanced Analytics

Unlocking Raw Web Data: How to Query GA4 Data in BigQuery Using SQL

If you rely solely on the Google Analytics 4 (GA4) user interface, you have likely run into standard reporting limitations: thresholding, data sampling, restricted custom funnel logic, or difficulty joining web behavior with CRM records.

Fortunately, GA4 offers a native, free export link to Google BigQuery. Instead of viewing aggregated reports, you get raw, event-level logs delivered directly to your cloud data warehouse.

However, once you open BigQuery, you might notice that querying GA4 data isn't as straightforward as traditional relational databases. GA4 uses a nested JSON-like schema, meaning event parameters and user properties are stored inside arrays.

In this hands-on guide, we will cover how to structure basic and advanced SQL queries to flatten GA4 event data, extract custom parameters, and build production-ready datasets for marketing analytics.

1. Understanding the GA4 BigQuery Schema

Every row in your analytics_XXXXXX.events_* table represents a single event (such as a pageview, click, form submit, or transaction).

Key columns in the table include:

1. event_date: The date of the event (formatted as YYYYMMDD).
2. event_timestamp: UTC timestamp in microseconds.
3. event_name: The name of the event (e.g., page_view, session_start, purchase).
4. event_params: A nested array containing key-value pairs of parameters associated with that event.
5. user_pseudo_id: The client/device identifier assigned by GA4.

Because event_params is an array of records (RECORD repeated type), you must use the UNNEST clause in SQL to unpack key-value pairs into readable columns.

2. Unnesting GA4 Event Parameters in SQL

To extract specific parameter values (like page_location or ga_session_id), you cross-join the main events table with the unnested parameters array.

Basic Query: Extracting Page Views and URLs

SQL

Key Concept: Why Use Scalar Subqueries?

Instead of joining UNNEST(event_params) multiple times—which can multiply row counts and increase query cost—using scalar subqueries like (SELECT value.string_value FROM UNNEST(...) WHERE key = '...') cleanly extracts single parameter values per row.

3. Reconstructing Sessions and Traffic Sources

In the standard GA4 web interface, traffic sources are grouped automatically. In BigQuery, you reconstruct session-level source/medium data by pulling parameters associated with the session_start event or combining first-touch user properties.

SQL

4. Joining GA4 Data with CRM Lead Data

The real power of exporting GA4 to BigQuery is the ability to connect top-of-funnel web analytics with backend CRM operations (e.g., PostgreSQL, HubSpot, or Salesforce data).

How the Pipeline Works:

SQL

5. Cost Optimization Best Practices in BigQuery

Because BigQuery charges based on the amount of data scanned, running unoptimized queries across historical GA4 tables can become expensive.

1. Filter by _TABLE_SUFFIX Early: Always filter date partitions in the WHERE clause (_TABLE_SUFFIX BETWEEN '20260701' AND '20260720') to scan only necessary dates.
2. Select Only Necessary Columns: Avoid SELECT *. Explicitly list required metrics and dimensions.
3. Create Incremental Materialized Tables: Save cleaned session or conversion datasets into staging tables so complex unnesting doesn't run every time a BI dashboard refreshes.

Key Takeaways

1. Raw Ownership: BigQuery export eliminates sampling and quota limits, providing full ownership of raw event data.
2. Master UNNEST: Use scalar subqueries on event_params to flatten key-value pairs without inflating row counts.
3. Connect Web & CRM: Link client IDs or custom lead IDs to bridge top-of-funnel traffic with closed revenue.
4. Optimize Query Costs: Partition your queries using _TABLE_SUFFIX to keep query execution fast and cost-effective.