GA4 BigQuery – Connect Google Analytics 4 with BigQuery

Google Analytics 4, Google BigQuery

Last Updated: November 15, 2022

What is GA4 BigQuery?

GA4 BigQuery is the use of BigQuery for retrieving, storing and manipulating GA4 data. 

Google BigQuery is one of the products of the Google Cloud Platform. 

BigQuery is an enterprise-level data warehouse from Google that is used to provide business intelligence through reports and dashboards.

It is a data storage and management system used to bring data from several data sources (like Google Analytics, Google Ads, Facebook Ads, shopping cart etc.) for the purpose of reporting and analysis.

What are the advantages of using BigQuery for GA4 (Google Analytics 4)?

Following are the key advantages of using BigQuery with GA4:

  1. GA4 provides a free connection to BigQuery
  2. BigQuery makes advanced data manipulation possible
  3. BigQuery is an excellent tool for fixing data integration issues and cross-platform data analysis
  4. You get access to the raw GA4 event data
  5. Access to 100% unsampled GA4 data
  6. You can work retroactively on GA4 data
  7. Easily filter out or modify incorrect GA4 data
  8. Integration with data visualization tools
bigquery thumb

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

#1 GA4 provides a free connection to BigQuery

Unlike GA3, GA4 comes with a free connection to BigQuery. 

That means that you don’t need to pay a monthly fee just to connect your GA4 property to your BigQuery project.

You can easily connect to a BigQuery project from within the user interface of your GA4 property:

ga4 bigquery

In the case of GA3 (unless you are using GA 360), you would need to use a third-party tool (often a paid subscription) to connect your GA3 property to your BigQuery project.

#2 BigQuery makes advanced data manipulation possible

Through BigQuery, you can manipulate GA4 data in a way that is many times simply not possible by using the GA4 user interface. 

For example, 

Certain dimensions and metrics combinations cannot be used/queried together whether you use the GA4 user interface or GA4 API. 

BigQuery has no such limitations. This is one of the biggest advantages of using BigQuery. 

BigQuery makes advanced data segmentation and analysis possible. It removes most of the limitations which come when you use the GA4 user interface or API for querying analytics data. 

There are companies/analysts out there who use BigQuery a lot more than the GA4 user interfaces for querying analytics data. 

#3 BigQuery is an excellent tool for fixing data integration issues and cross-platform data analysis

Since BigQuery is a data warehouse, it is an excellent tool for combining data from several data sources (like Google Analytics, Google Ads, Facebook Ads, etc.) for the purpose of advanced reporting and analysis. 

Thus the use of BigQuery helps fix data integration issues and enables robust cross-device and cross-platform data analysis and reporting.

#4 You get access to the raw GA4 event data

You can access all raw events from your GA4 property in BigQuery.

This is something which is not possible when you use the GA4 user interface. 

#5 Access to 100% unsampled GA4 data

You can access 100% unsampled GA4 data via BigQuery even if you are not a GA4 360 customer.

#6 You can work retroactively on GA4 data

When you use the GA4 user interface, the conversions and filters do not work retroactively. 

The conversions and filter data are collected and reported only from when you first set up your tracking. 

So if you want to calculate conversions based on historical data, then it is not possible. 

But BigQuery has no such limitations. 

#7 Easily filter out or modify incorrect GA4 data

BigQuery lets you easily filter out or modify incorrect GA4 data from your analysis and reports. 

But… 

When you use the GA4 user interface to query data, you can not easily filter out incorrect data, and you cannot modify incorrect data. 

Data, once skewed, is skewed for good in the GA4 user interface.

#8 Integration with data visualization tools

You can easily integrate BigQuery with data visualization tools like Google Data Studio to visualize GA4 data.

Other advantages of using BigQuery

#9 When you use BigQuery, you can easily store and re-run queries. Thus you can save a lot of time in data retrieval. 

#10 BigQuery allows you to query even terabytes of data within seconds. Since BigQuery can execute SQL statements very fast, it can be used for real-time analytics. 

#11 You can store a massive amount of data in BigQuery for relatively low prices.

#12 BigQuery is easy to learn and use, though it may look scary if you have never used it before.

#13 You don’t need to install or set up anything to use BigQuery. You can be up and running in a few minutes.

#14 BigQuery is easy to use with multiple users and teams. 

What is the cost of using BigQuery for GA4?

Your monthly cost of using BigQuery for GA4 will depend upon the following two factors:

  1. The amount of data you stored in BigQuery (i.e. the storage cost)
  2. The amount of data you processed by each query you run (i.e. the query cost)

The first 10 GB of active storage is free each month. After that, you would be charged $0.020 per GB of active storage.

The first one terabyte of data processed is free each month. After that, you would be charged $5 per terabyte (TB) of data processed. 

Google Analytics 4 comes with a free connection to BigQuery. 

So you won’t need a third-party solution just to connect your GA4 property with your BigQuery project. 

However, you could still be charged based on your data storage and processing. 

As long as you remain within 10 GB of data storage and one terabyte of queries per month limit, your credit card will not be charged. 

Once you exceed this free usage limit, only then your credit card will be charged.

Note: If you start querying gigabytes or terabytes of data daily in BigQuery, you need to be mentally and financially prepared to pay a considerable amount of storage and/or processing fees each month.

This is what we were paying when we were querying a lot of data in BigQuery:

bigquery cost

How much did using BigQuery for GA4 cost us every month on average?

It cost us less than $1:

bigquery cost average

And we get over a million visitors a year. 

For more details on BigQuery pricing, check out the official resource: https://cloud.google.com/bigquery/pricing 

Prerequisites for using GA4 with BigQuery

Following are the prerequisites for using GA4 with BigQuery:

#1 You would need a Google Cloud Platform account with billing enabled. To enable the billing, you would need a valid credit card.

#2 You would need a BigQuery project where you are going to store the GA4 data.

#3 You would need a good working knowledge of SQL so that you can query data in BigQuery. This is the primary requirement for using BigQuery.

Overview of connecting GA4 with BigQuery

Following is the 10,000-foot view of connecting and sending GA4 data to BigQuery:

  1. Create a Google Cloud Platform account (if you already don’t have one) with billing enabled.
  2. Create a new BigQuery project (to store GA4 data).
  3. Upgrade from the BigQuery Sandbox
  4. Link your GA4 property to your BigQuery project.
  5. Enable and configure the BigQuery API.
  6. Find your GA4 data in BigQuery.
  7. Query the GA4 data you need in BigQuery via SQL.

Create a new Google Cloud Platform account

If you don’t already have a Google Cloud account, then you can create one by following the instructions in this article: How to create a new Google Cloud Platform account with billing enabled.

Create a new BigQuery project

Create a new project in Google Cloud Console (with billing enabled) to store GA4 data.

Name this project ‘GA4 BigQuery’.

For step by step instructions on creating a new project in GA4 with billing enabled, check out this article: How to create a new BigQuery project

How to connect Google Analytics 4 with BigQuery

Follow the steps below to link your GA4 property to your BigQuery project for GA4:

Step-1: Log in to your GA4 property and then click on ‘Admin‘ under the left-hand reporting menu.

admin

Step-2: Click on ‘BigQuery Links‘ under ‘Product Links‘:

product links

Step-3: Click on the ‘Link‘ button:

link project

Step-4: Click on the button ‘Choose a BigQuery project‘:

choose link project

Step-5: Select the BigQuery project where you want to send your GA4 data, and then click on the ‘Confirm‘ button.

In our case, that project is ‘GA4 BigQuery’:

select project to link

Note: You can link only one BigQuery project at a time to a GA4 property.

Step-6: Select your data location from the drop-down menu and click on the ‘Next’ button.

Data location is the cloud region where your data is stored.

data location

Step-7: Click on ‘Configure data streams and events‘ if you want to export only particular data streams and events (optional):

configure data streams
data streams config

Note: By default, all data streams and events are selected for export to BigQuery.

Step-8: Click on the checkbox ‘Include advertising identifiers for mobile app streams’ (optional) 

Use this option if you have a mobile app and you want to export mobile advertiser identifiers to your BigQuery project:

include advertising identifiers

Step-9: Select the frequency of your data import to BigQuery and then click on the ‘Next‘ button:

Select both ‘Daily‘ and ‘Streaming‘ frequencies:

export frequency
GA4 BigQuery - Connect Google Analytics 4 with BigQuery 40

Step-10: Review your GA4 BigQuery integration setup and then click on the ‘Submit’ button:

review link
submit data config

You should now see a screen like the one below:

link created

Congratulations!

Your GA4 property is now successfully linked to your BigQuery project.

How to enable and configure the BigQuery API

Follow the steps below to enable and configure your BigQuery API:

Step-1: Navigate to https://console.cloud.google.com/apis/dashboard

Step-2: Make sure your BigQuery project for GA4 is selected:

select bigquery project

Step-3: Click on the ‘+ENABLE APIS AND SERVICES’ button:

enable apis

You should now see a screen like the one below:

api library

Step-4: Search for ‘bigquery’ and then click on ‘BigQuery API’ auto-suggestion:

search api library

Step-5: Click on the ‘BigQuery API’ search result to enable the API:

select bigquery api

Step-6: Click on the ‘Manage’ button:

manage bigquery api

Step-7: Click on the ‘Create Credentials’ button at the top right-hand side of your screen to use the API:

create credentials

Step-8: Make sure that ‘BigQuery API‘ is selected under the ‘Select an API‘ drop-down menu:

select api

Step-9: Select ‘Application Data’ to create a firebase service account:

The firebase service account would be used to export GA4 data to BigQuery. 

application data

Step-10: Scroll down, select ‘No, I am not using them‘ and then click on the ‘Next’ button:

not using platforms

Step-11: Enter [email protected] as the service account name:

service account details

Step-12: Click on the ‘Create and Continue’ button:

create service account

Step-13: Grant this service account access to the project and then click on the ‘Continue‘ button (optional):

The default role is ‘owner’. But if you want to give some other permission, then use the drop-down menu.

grant service acc credentials

Step-14: Grant access to users or groups that need to perform actions as this service account and then click on the ‘Done’ (optional)

If you do not want to grant access, then leave the text fields blank and then click on the ‘Done’ button:

service account done

You should now see a screen like the one below, which shows the listing of your new service account:

service account list

Congratulations!

You have now successfully enabled and configured the BigQuery API

bigquery thumb

My Step-By-Step Blueprint For Learning Google Analytics BigQuery

Get the only ebook on Google Analytics BigQuery ever published (60+ Pages)

Yes I want the FREE ebook

How to find GA4 data in BigQuery

Once you have linked your GA4 property to your BigQuery project and enabled and configured the BigQuery API, it usually takes around 24 hrs for your GA4 data to be available in your BigQuery project.

Follow the steps below to find the GA4 data you need in BigQuery:

Step-1: Navigate to your BigQuery account: https://console.cloud.google.com/bigquery

Step-2: Make sure that the project used for collecting GA4 data is selected:

In our case, that project would be ‘GA4 BigQuery’:

select project dropdown

Step-3: Click on the project ID of the project which collects GA4 data:

select project id

You should now be able to see a data set named in the following format: “analytics_<property_id>“. 

data set

For each Google Analytics 4 property linked to BigQuery, a single dataset named “analytics_<property_id>” is added to your BigQuery project.

Property ID refers to your Analytics Property ID, which you can find in the property settings for your GA4 property:

project settings

In my case, the property ID is 298851313

That’s why the name of my data set is ‘analytics_298851313‘

data set name

This data set contains the following two data tables, which contain your GA4 data:

#1 events_(<number of days>)

     #2 events_intraday_<current date>

data tables

If you want to learn more about these two data tables, then check out this article: events_ & events_intraday_ tables in BigQuery for GA4 (Google Analytics 4)

Conclusion

You can use a Google Analytics 4 property and BigQuery to evaluate the limitless possibility of new insights and define your own advanced analytics capabilities.

Furthermore, you can also connect BigQuery with third-party tools using native connectors to integrate more data.

BigQuery also supports most visualization tools, and you can use them to build dashboards and insights to enhance your business growth.

If you want to send GA3 data to Google BigQuery, and that too without using GA 360, then check out this article: Sending Google Analytics data to BigQuery without 360

#1 Google Analytics 4 Intro

  1. What is GA4 (Google Analytics 4) – The Apps + Web Property?
  2. Key Benefits of Using Google Analytics 4 (GA4)
  3. Setup GA4 – Upgrade to GA4 – Implementation Guide
  4. Google Analytics 4 (GA4) vs Universal Analytics – What is the Difference?
  5. GA4 vs GA4 360 – Pricing, Limits, Billing and More
  6. Google Analytics 4 Training & Tutorial with FREE GA4 ebook

#2 Google Analytics 4 Property

  1. Google Analytics Account Hierarchy (Structure Explained)
  2. Understanding Google Analytics Measurement ID (GA4)
  3. Google Signals GA4 – See Demographics (Gender, Age) in Google Analytics 4
  4. Using the GA4 (Google Analytics 4) Test Property
  5. Google Analytics 4 Sub Properties Tutorial
  6. Roll up Property in Google Analytics 4 (GA4) – Tutorial

#3 Google Analytics 4 Integrations

  1. How to connect GA4 (Google Analytics 4) with Google Data Studio
  2. How to link GA4 (Google Analytics 4) with Google Ads
  3. How to link Google Search Console to Google Analytics 4 (GA4)
  4. How to Install Google Analytics 4 on Shopify
  5. GA4 Firebase Integration – Correctly Add App Data Streams to GA4 Property

#4 Google Analytics 4 Events

  1. GA4 (Google Analytics 4) Event Tracking Setup Tutorial
  2. Understanding Event Parameters in Google Analytics 4 (GA4)
  3. Recommended Events in Google Analytics 4 (GA4)
  4. Enhanced Measurement Events in Google Analytics 4 (GA4)
  5. Automatically Collected Events in Google Analytics 4 (GA4)
  6. How to Set Up GA4 Custom Events via Google Tag Manager
  7. Events Report in Google Analytics 4 (GA4)
  8. How to Rename Events in Google Analytics 4 (GA4)
  9. How to Use Google Analytics 4 Event Builder
  10. GA4 Form Interactions Tracking – Enhanced Measurement

#5 Google Analytics 4 Conversions

  1. Google Analytics 4 Conversion Tracking Guide – GA4 Goals
  2. How to Import Conversions from GA4 Property to Your Google Ads account
  3. GA4 Conversion Rate – How to find it and use it

#6 Google Analytics 4 Dimensions

  1. GA4 (Google Analytics 4) Dimensions Tutorial
  2. GA4 (Google Analytics 4) Custom Dimensions Tutorial
  3. GA4 User Properties (User Scoped Custom Dimensions) – Tutorial
  4. Event Scoped Custom Dimensions in GA4 – Tutorial

#7 Google Analytics 4 Metrics

  1. GA4 (Google Analytics 4) Metrics Tutorial with Free Google Analytics 4 Ebook
  2. GA4 (Google Analytics 4) Custom Metrics Tutorial
  3. What are Predictive Metrics in Google Analytics 4 (GA4)

#8 Google Analytics 4 Ecommerce

  1. GA4 (Google Analytics 4) Ecommerce Tracking via GTM – Tutorial

#9 Google Analytics 4 Specialized Tracking

  1. GA4 (Google Analytics 4) Enhanced Measurement Tracking Tutorial
  2. Cross Domain Tracking in GA4 (Google Analytics 4) Setup Guide
  3. GA4 Site Search – Tracking Site Search in Google Analytics 4
  4. GA4 (Google Analytics 4) Scroll Tracking Tutorial
  5. Self-referral Google Analytics 4 – Referral exclusion GA4
  6. GA4 (Google Analytics 4) Data Import Tutorial
  7. Google Analytics 4 Content Grouping – Create Content Groups in GA4
  8. How to Track Single Page Apps in Google Analytics 4 (GA4)
  9. utm_source, utm_medium, utm_campaign Parameters – GA4 (Google Analytics 4)

#10 Google Analytics 4 filters

  1. GA4 filters – Understanding Data Filters in Google Analytics 4
  2. How to Create and Test Filters in Google Analytics 4 (GA4)?
  3. Exclude Internal Traffic in GA4 (Google Analytics 4) via IP Filter

#11 Google Analytics 4 Explorations

  1. Free Form Report in GA4 (Google Analytics 4) – Exploration Report
  2. How to Use the User Lifetime Report in Google Analytics 4 (GA4)
  3. How to Use Path Exploration Report in GA4 (Google Analytics 4) – Path Analysis
  4. How to Use Segment Overlap Report in Google Analytics 4 (GA4)
  5. How to Use the Funnel Exploration Report in GA4 (Google Analytics 4) – Funnel Analysis
  6. Cohort Exploration Report in Google Analytics 4 (GA4)
  7. How to Create Landing Pages Report in GA4 (Google Analytics 4)
  8. How to Create Google Ads report in GA4 (Google Analytics 4)
  9. How to Segment GA4 Data by Data Stream
  10. Organic Search Traffic Analysis in GA4 – Complete Guide
  11. Google Analytics 4 (GA4) Outbound Links Tracking
  12. How to Track Email Campaigns and Traffic in GA4 

#12 Google Analytics 4 Advanced

  1. Understanding Google Analytics 4 Sessions
  2. GA4 (Google Analytics 4) Measurement Protocol Tutorial
  3. How to Build Comparisons (Advanced Segments) in Google Analytics 4 (GA4)
  4. Understanding Automated Insights in Google Analytics 4 (GA4)
  5. Understanding Channel Groupings in Google Analytics 4 (GA4)
  6. Understanding Data Sampling in Google Analytics 4 (GA4)

#13 Google Analytics 4 Reports

  1. How to Create Custom Insights in Google Analytics 4 (GA4)
  2. How to Use Debug View Report in Google Analytics 4 (GA4)

#14 Google Analytics 4 Attribution

  1. Guide to Attribution Models in GA4 (Google Analytics 4)
  2. How to Change Attribution Models in GA4 (Google Analytics 4)?
  3. GA4 (Google Analytics 4) Conversion Paths Report in Attribution
  4. GA4 (Google Analytics 4) Model Comparison Report in Attribution
  5. Advertising Snapshot in GA4 (Google Analytics 4) Attribution
  6. GA4 Attribution Modelling Tutorial

#15 Google Analytics 4 Audiences

  1. GA4 Audiences – Creating Custom Audience in Google Analytics 4
  2. How to Create a Remarketing Audience in Google Analytics 4 (GA4)
  3. Understanding Audience Triggers in Google Analytics 4 (GA4)
  4. Google Analytics 4 (GA4) Predictive Audiences – Tutorial

#16 Google Analytics 4 BigQuery

  1. GA4 BigQuery – Connect Google Analytics 4 with BigQuery
  2. BigQuery GA4 Schema – Send Custom GA4 Data to BigQuery
  3. How to Backfill GA4 Data in BigQuery
  4. How to Connect and Export Data from GA to BigQuery

Frequently asked questions about Connecting Google Analytics 4 with BigQuery

How do I link Google Analytics 4 to BigQuery?

Follow the below steps to link Google Analytics 4 to BigQuery
1. Click on ‘Admin’ in the GA4 account, and under the ‘Property’ column, click on ‘BigQuery Linking’.
2. Now click on ‘Link’ to create a new connection.
3. Click on ‘Choose a BigQuery Project’.
4. Select your BigQuery Project (You can create a new project if needed).
5. Select your data location.
6. Select the data streams for which you want to export the data.
7. Select the frequency.
8. Review your configuration and click on ‘Submit.

What are the cost involved in GA4 and BigQuery export?

GA4 and BigQuery exports are completely free of cost. You can export your GA4 data to a free instance of BigQuery (BigQuery Sandbox). However, if your export exceeds the limit of BigQuery Sandbox, it will incur charges.

What are the advantages of GA4 and BigQuery export?

Following are the advantages of GA4 and BigQuery export.
1. Store data in BigQuery, and then after processing, you can send it to other data warehouses like AWS or Azure.
2. Perform advanced analysis on the raw data from the GA4 property.
3. Connect multiple data streams from GA4. For example, you can pull data into BigQuery from Android apps, IOS apps, and websites from one central point.
4. Join your data with other marketing or CRM tools.
5. Export to BigQuery is completely free for GA4 properties. Earlier, this was only available in GA360 (Premium Universal Analytics) accounts with a paid subscription.
6. Visualize your data in tools such as Tableau, Qlik Sense, PowerBI, and Data Studio.

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

​#5 The number 1 reason why conversion optimization is not working for your business.

​#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization Master the Essentials of Email Marketing Analytics Attribution Modelling in Google Analytics and Beyond Attribution Modelling in Google Ads and Facebook

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and BeyondSECOND EDITION OUT NOW!
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!