How to Create Custom Google Analytics 4 Reports Using BigQuery

Tired of hitting limits with standard Google Analytics 4 reports? You can unlock a far deeper level of analysis by connecting your GA4 property directly to BigQuery. This article guides you step-by-step through setting up the connection, understanding your data, and writing custom queries to build the exact reports your business needs.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use BigQuery for GA4 Reporting?

While the GA4 interface is great for top-level metrics, it has some key limitations for deep-dive analysis. The standard reports rely on pre-aggregated data, which can obscure important details. When you try to build custom explorations, you often run into data sampling, where GA4 uses a subset of your data to estimate the results, sacrificing accuracy for speed. You're also limited to a 14-month data retention period for those explorations.

Connecting GA4 to BigQuery solves all of these problems. Here's what you gain:

• Unsampled, Raw Data: BigQuery receives the raw, hit-level event data from your website or app. Every single event is captured, meaning your reports are built on 100% of your data, not a sample.
• Long-Term Data Storage: You control the data retention period. While there are costs for storage beyond the free tier, you can keep your data for as long as you need, breaking free from GA4's retention limits.
• Ultimate Flexibility: With direct access to raw data, you can ask extremely specific and complex business questions that are impossible to answer in the GA4 UI. You can build custom-defined funnels, perform advanced user segmentation, and create unique attribution models.
• Combine Data Sources: Perhaps the most powerful feature is the ability to join your GA4 data with other datasets. You can import data from your CRM (like Salesforce or HubSpot), advertising platforms, or internal databases to create a single, unified view of the entire customer journey.

Step 1: Link Your GA4 Property to BigQuery

Before you can write any queries, you need to set up the data export from Google Analytics to a BigQuery project. The good news is that the export itself is free for all GA4 properties.

Here’s how to get it done:

Prerequisites:

• You need Administrator or Editor permissions for the Google Analytics property you want to link.
• You must have a Google Cloud Platform (GCP) project with the BigQuery API enabled. If you don't have one, setting it up is quick and comes with a generous free tier.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Linking Instructions:

1. Navigate to the Admin section of your Google Analytics 4 property (click the gear icon in the bottom-left corner).
2. In the Property column, scroll down to the "Product Links" section and click on BigQuery Links.
3. Click the blue Link button. From here, click Choose a BigQuery project to select the GCP project where you want your data sent.
4. Select your project from the list and click Confirm.
5. Next, you'll need to configure your data settings. First, choose a location for your data (e.g., us-central1). Make sure it's the right location for your needs, as it cannot be changed later.
6. Now, configure the data streams and event frequency. You'll see two frequency options for the export:
7. Once you review your settings, click Submit.

That's it! Your GA4 property is now linked. You will begin to see datasets appear in your BigQuery project within 24 hours. They will be named analytics_<property_id>, and inside, you'll find tables named events_YYYYMMDD.

Step 2: Understanding GA4's Data Structure in BigQuery

Once your data starts flowing in, the next step is getting familiar with how it's structured. Universal Analytics data was session-based, but GA4 data is event-based and organized quite differently in BigQuery, using nested fields.

At first glance, the schema can look a little intimidating. You'll see one row for every event collected (like a page_view, session_start, or purchase). Don't worry, you only need to focus on a few key columns to get started.

Here are the important fields:

• event_date: The date the event occurred, in YYYYMMDD format.
• event_timestamp: An exact Unix-style timestamp of when the event happened, down to the microsecond.
• event_name: The name of the event fired, like page_view, add_to_cart, or session_start.
• user_pseudo_id: The unique identifier for the user's device and/or browser.

The Magic of event_params

The most important part of the entire schema is the event_params field. This isn't a simple column, it's a "nested" column that acts like a container holding all the detailed parameters sent with each event.

Inside each event_params container, you'll find bits of information with two main parts:

• key: The name of the parameter (e.g., page_location, page_title, campaign).
• value: A container that holds the actual value of the parameter. It contains even more nested fields like string_value, int_value, and others to store different types of data.

In plain English, this means if you want to find the page URL for a page_view event, you need to look for event_params where the key equals page_location, and then grab the string_value from inside the value container.

This nested structure is extremely efficient for storing data, but it means you need to use SQL functions like UNNEST in your queries to "flatten" this data into a traditional tabular format.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Your First SQL Query - Building a Basic Report

Let's walk through building a simple reporting query that counts sessions by source and medium for the last seven days.

We'll use the UNNEST function to work with the nested event_params fields so we can list individual rows. Then, we'll use SQL functions to extract session and medium values:

SELECT
    COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS STRING))) AS sessions,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium
FROM
    `YOUR_PROJECT.YOUR_DATASET.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP("%Y%m%d", DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
    AND FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP())
GROUP BY 2, 3
ORDER BY sessions DESC

Popular Analytical Queries

Now that you have a grasp on the basics, let's tackle two more popular reports you can build.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Top Landing Pages by Sessions

This query shows you the most popular landing pages on your website, filtered by session_start events and extracting the page_location parameter.

SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    COUNT(DISTINCT user_pseudo_id) AS sessions
FROM
    `YOUR_PROJECT.YOUR_DATASET.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP("%Y%m%d", DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
    AND FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP())
    AND event_name = 'session_start'
GROUP BY page_location
ORDER BY sessions DESC
LIMIT 25

Funnels and Conversion Analysis

This is a powerful use case for the more advanced dimensions of GA4. We build a funnel query to count conversions from page_view to add_to_cart to purchase.

SELECT
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNTIF(event_name = 'page_view') AS Page_Views,
    COUNTIF(event_name = 'add_to_cart') AS Add_To_Cart,
    COUNTIF(event_name = 'purchase') AS Purchases
FROM
    `YOUR_PROJECT.YOUR_DATASET.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP("%Y%m%d", DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
    AND FORMAT_TIMESTAMP("%Y%m%d", CURRENT_TIMESTAMP())
GROUP BY users

Final Thoughts

Once your GA4 data is exported to BigQuery, you unlock a new level of analysis that isn’t possible in the standard interface. You can create advanced reports and dashboards that help drive your business decisions.

If you want all of the insights without having to write SQL queries yourself, try Graphed. It seamlessly integrates with your Google Analytics data, allowing you to ask business questions, build analyses, and produce reports - all without needing to dig into code.