# How to Use Google BigQuery to Analyze Exported Google Analytics 4 (GA4) Data
With the transition from Universal Analytics to Google Analytics 4 (GA4), one of the most powerful features previously reserved for enterprise customers has been democratized: free, native exporting of raw event data to Google BigQuery.
While the GA4 user interface is sufficient for standard reporting, complex analysis, machine learning models, and joining web data with CRM data require querying the raw data directly. BigQuery, Google’s fully managed enterprise data warehouse, is the ideal tool for this.
This guide provides a comprehensive walkthrough on how to link GA4 to BigQuery, understand the exported data schema, and write your first SQL queries to extract meaningful insights.
## Prerequisites
Before beginning, ensure you have:
1. Admin access to your Google Analytics 4 property.
2. A Google Cloud Platform (GCP) project with billing enabled (BigQuery offers a generous free tier, but billing must be linked to create the export).
3. “Owner” or “Editor” permissions on the GCP project.
## Step 1: Link GA4 to Google BigQuery
The first step is establishing the connection so GA4 can stream its event data into your GCP project.
1. Navigate to the **Google Analytics** admin interface.
2. In the Property column, scroll down to **Product Links** and select **BigQuery Links**.
3. Click the **Link** button in the top right corner.
4. Click **Choose a BigQuery project** and select the GCP project where you want the data stored.
5. Select the **Data location**. *Note: This cannot be changed later. Choose a location closest to your users or your business operations for compliance and performance reasons.*
6. Click **Next**.
7. Configure the data streams and events you wish to export. By default, all data streams are selected.
8. Choose your **Frequency**:
– **Daily:** A full export of the previous day’s data (recommended for historical analysis).
– **Streaming:** Data is exported continuously within minutes of the event (incurs higher BigQuery streaming insert costs; useful for real-time dashboards).
9. Click **Submit** to finalize the link.
*Important: It typically takes 24 hours for the first daily export to appear in BigQuery. Historical data prior to the link date will not be exported retroactively.*
## Step 2: Understanding the GA4 BigQuery Schema
Once data begins flowing, navigate to the BigQuery console (`console.cloud.google.com/bigquery`). In the Explorer pane on the left, you will see a new dataset named `analytics_
Inside this dataset, you will find tables named `events_YYYYMMDD` (if using daily export) or `events_intraday_YYYYMMDD` (if using streaming).
The GA4 BigQuery schema is heavily nested, meaning some columns (like `event_params` and `user_properties`) contain arrays of key-value pairs rather than flat data. This requires specific SQL techniques to query effectively.
### Key Columns to Know:
– `event_date`: The date the event occurred (String).
– `event_timestamp`: The time the event occurred in microseconds (Integer).
– `event_name`: The name of the triggered event (e.g., `page_view`, `purchase`, `session_start`).
– `event_params`: A nested array containing custom parameters for the event (e.g., `page_location`, `link_url`).
– `user_pseudo_id`: The GA4 client ID, used to identify unique devices/browsers.
– `user_id`: Your custom user ID (if you have implemented user tracking).
– `items`: A nested array used primarily for ecommerce events.
## Step 3: Writing Your First SQL Queries
Because of the nested structure, querying GA4 data requires the use of the `UNNEST()` function in standard SQL.
Here are three practical examples of querying your exported data.
### Example 1: Count Total Events by Event Name
This is the most basic query to understand what events are occurring on your site over a specific date range.
“`sql
SELECT
event_name,
COUNT(*) AS event_count
FROM
`your-project.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN ‘20231001’ AND ‘20231031’
GROUP BY
event_name
ORDER BY
event_count DESC;
“`
*Note: The `*` wildcard and `_TABLE_SUFFIX` allow you to query multiple daily tables simultaneously.*
### Example 2: Extracting a Specific Event Parameter (Page Views by URL)
To analyze which pages are most viewed, you need to extract the `page_location` from the nested `event_params` array. This is where `UNNEST` is essential.
“`sql
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘page_location’) AS page_url,
COUNT(*) AS page_views
FROM
`your-project.analytics_123456789.events_*`
WHERE
event_name = ‘page_view’
AND _TABLE_SUFFIX BETWEEN ‘20231001’ AND ‘20231031’
GROUP BY
page_url
ORDER BY
page_views DESC
LIMIT 10;
“`
*Explanation: The subquery `(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘page_location’)` flattens the array just for the specific key you need.*
### Example 3: Calculating Daily Active Users (DAU)
Counting unique users requires querying the `user_pseudo_id`.
“`sql
SELECT
PARSE_DATE(‘%Y%m%d’, event_date) AS date,
COUNT(DISTINCT user_pseudo_id) AS daily_active_users
FROM
`your-project.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN ‘20231001’ AND ‘20231031’
GROUP BY
date
ORDER BY
date ASC;
“`
*Explanation: `PARSE_DATE` converts the string date format (YYYYMMDD) into a native BigQuery DATE object, making it easier to visualize in tools like Looker Studio.*
## Cost Management and Best Practices
While querying GA4 data is powerful, BigQuery charges based on the amount of data processed by your queries. To prevent unexpected costs:
1. **Avoid `SELECT *`:** Never use `SELECT *` unless absolutely necessary. Only select the specific columns you need for your analysis.
2. **Always Partition by Date:** Use the `_TABLE_SUFFIX` to restrict your queries to a specific date range, rather than querying the entire dataset.
3. **Use Materialized Views:** If you run complex queries frequently (e.g., for a dashboard), create a materialized view or schedule a query to write the results to a flat table. Querying the flat table will be significantly cheaper and faster than repeatedly running `UNNEST` operations on the raw GA4 tables.
By linking GA4 to BigQuery and mastering these SQL fundamentals, you transition from basic reporting to advanced data science, unlocking the true potential of your website and app analytics.