How to Query Google Analytics Data in BigQuery

Connecting your Google Analytics 4 data to BigQuery unlocks a level of analysis you simply can't achieve in the standard GA4 interface. By getting access to your raw, unsampled event data, you can build custom reports, join GA data with other sources, and overcome the limitations of the GA platform. This article guides you through the process, from linking your accounts to writing your first custom SQL queries to analyze your website traffic events.

Why Send Google Analytics Data to BigQuery?

While the standard GA4 interface is great for a quick overview, serious marketers and analysts often hit a wall. Here’s why a BigQuery connection is a game-changer:

• No More Data Sampling: For high-traffic websites, GA4 often uses data sampling in its reports to deliver them quickly. This means the numbers you're seeing are estimates, not the full picture. BigQuery contains all your raw, unsampled event data, giving you absolute precision.
• Full Data Ownership: The data in BigQuery is yours to keep. GA4 has data retention limits (max 14 months for event-level data on the free version), while your BigQuery data is stored permanently in your own Google Cloud project.
• Deeper, Richer Analysis: The GA4 UI has predefined reports. With BigQuery, you can analyze your data in any way you can imagine using the power of SQL. You can build advanced user funnels, perform complex cohort analyses, or calculate custom multi-touch attribution models.
• Combine Data Sources: This is a massive advantage. You can join your GA website data with data from your CRM (like Salesforce), ad platforms (like Facebook Ads), or payment processors (like Stripe) to get a complete view of your entire customer journey.

Step 1: Set Up the GA4 to BigQuery Integration

Before you can start querying, you need to connect the two platforms. It’s a straightforward process that takes just a few minutes in your Google Analytics admin panel.

Prerequisites:

First, make sure you have the following:

1. You must have Editor or Administrator access to the Google Analytics property you want to link.
2. You need a Google Cloud Project. If you don't have one, you can create one for free.
3. You must enable billing for your Google Cloud Project. While BigQuery has a generous free tier for storage and processing, billing must be enabled to use the service. A new project usually comes with free credits to get you started.

Linking Your Accounts:

1. Navigate to the Admin section of your Google Analytics 4 property (click the gear icon in the bottom-left).
2. In the Product Links column, click on BigQuery Links.
3. Click the blue Link button. A new configuration panel will open.
4. Click Choose a BigQuery project to select the Google Cloud Project where you want to store your GA data. A list of projects you have access to will appear.
5. Select a Data location. This determines where your data is physically stored. Choose a location close to you or wherever makes the most sense for your business.
6. Under Data Streams and Events, select which data streams you want to export. Most of the time, you'll just select your main web stream. You can also exclude specific events if needed, but it's best to export everything to start.
7. For Frequency, you have two options:
8. Review your settings and click Submit.

That's it! Your GA4 property is now linked to BigQuery. It will create a new dataset named "analytics_PROPERTY-ID" in your BigQuery project, and you should see the first data tables appear within 24 hours.

Understanding the GA4 Data Structure in BigQuery

When you start exploring your new dataset, you’ll notice a distinct table structure. Unlike Universal Analytics which had session-based tables, GA4 uses an event-based model.

• events_YYYYMMDD: Each day, GA4 exports a new table named with the corresponding date (e.g., events_20231026). Your query costs depend on how much data you scan, so querying these daily tables directly is great practice for limiting scope.
• events_intraday_YYYYMMDD: If you enabled the streaming export, you'll see tables with this format. This is where real-time events are temporarily stored before being consolidated into the main daily table at the end of the day.

The core concept to grasp is that each row in these tables represents a single event (like a page_view, session_start, or purchase). All the information about that event - who the user was, what device they used, where they came from - is stored within that single row, often in nested fields.

The Key to GA4 Queries: UNNEST()

Looking at the schema, you’ll see fields like event_params, user_properties, and items. These aren’t simple strings or numbers, they are RECORD types that contain an ARRAY of data. Think of it as a list of data points nested within a single cell.

For example, for a page_view event, all the parameters associated with it (like the page_location and page_title) live inside the event_params field.

To access this nested data, you have to use the UNNEST() function in your SQL query. This function takes an array and "flattens" it, effectively creating a new copy of the row for each element in the array. This is the single most important function you will use for querying GA4 data in BigQuery.

Writing Your First GA4 BigQuery Queries

Let's move from theory to practice. Here are some common use cases and the SQL you'd use to get the answers from your GA4 data. Remember to replace your_project_id.analytics_123456789.events_* with your actual project, dataset, and table information.

1. Counting Total Events and Users for One Day

Let's start very simple. We just want to count the total events and an approximate number of unique users for October 26th, 2023.

SELECT
  COUNT(*) as total_events,
  COUNT(DISTINCT user_pseudo_id) as total_users
FROM
  `your-project.analytics_123456789.events_20231026`

2. Analysis Across a Date Range using _TABLE_SUFFIX

To analyze a date range without writing a giant FROM clause for each individual day, you can use a wildcard (*) in your table name. Then, you filter the date using the _TABLE_SUFFIX pseudocolumn. This saves time and dramatically reduces query costs.

Here’s how to find the total users for the last 7 days:

SELECT
  COUNT(DISTINCT user_pseudo_id) as unique_users_last_7_days
FROM
  `your-project.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Note: Using _TABLE_SUFFIX is the BigQuery-approved way to work with partitioned tables and is far more efficient than filtering on the event_date field.

3. Finding the Top 10 Most Viewed Pages

Now things get interesting. To find your most popular pages, you need to filter for page_view events and then extract the page URL from the nested event_params record. This is where UNNEST() comes into play.

SELECT
  pages.value.string_value AS page_url,
  COUNT(pages.value.string_value) AS pageview_count
FROM
  `your-project.analytics_123456789.events_*`
CROSS JOIN
  UNNEST(event_params) AS pages -- Flattening the event_params array
WHERE
  _TABLE_SUFFIX BETWEEN '20231001' AND '20231031' -- Filter for October
 AND event_name = 'page_view'
 AND pages.key = 'page_location' -- We only care about the page location parameter
GROUP BY
  1 -- Group by the page_url
ORDER BY
  2 DESC -- Order by pageview_count
LIMIT 10

In this query, CROSS JOIN UNNEST(event_params) AS pages virtually creates a new temporary table where each page_view's parameters get their own row. From there, we can easily filter, group, and count them.

4. Building a Simple Traffic Source Report

Want to see which channels are driving the most users? You can replicate the traffic acquisition report from GA4. The traffic source information is also stored as an event parameter on the session_start event. We'll need to UNNEST() several times to pull out the source, medium, and campaign.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') as traffic_source, 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') as traffic_medium, 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') as traffic_campaign,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNTIF(event_name = 'session_start') as sessions,
  COUNTIF(event_name = 'purchase') as conversions
FROM 
  `your-project.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20231001' AND '20231031'
GROUP BY 1,2,3
ORDER BY users DESC

This query uses a subquery technique within the SELECT statement, which is another common way to access specific values from a nested field without fully duplicating rows.

Final Thoughts

Connecting GA4 to BigQuery lets you tap into your raw, unsampled data to perform analyses far beyond the GA4 interface. By understanding the event-based schema and mastering the UNNEST() function, you can build custom reports, join marketing data with sales outcomes, and own your analytics insights in a way that simply isn't possible otherwise.

While learning SQL to query BigQuery is incredibly powerful, it certainly has a learning curve and isn't a skillset everyone on your team has. That’s why we built Graphed. We connect directly to your data sources like Google Analytics, Shopify, and your ad platforms, so you can skip the SQL entirely. Teams can simply ask questions in plain English, like "Show me a chart of my top ten landing pages by conversions this month," and get live, interactive dashboards built in seconds, enabling everyone to make better, data-driven decisions without a technical background.