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, etc.) for the purpose of reporting and analysis.
Advantages of using BigQuery for Google Analytics
When you use BigQuery, you can manipulate Google Analytics data in a way which is many times simply not possible by using the Google Analytics user interface.
For example, whether you use the Google Analytics user interface or Google Analytics API, certain dimensions and metrics combinations cannot be queried together. But BigQuery has no such limitations.
This is one of the biggest advantages of using BigQuery. It makes advanced data segmentation and analysis possible.
It removes most of the limitations which come when you use the GA user interface or API for querying analytics data.
You can transfer your GA3 (Universal Analytics) data into GA4 by using BigQuery
Both GA3 and GA4 use different data schemas.
Because of that, it is not possible to transfer your GA3 data into your GA4 property. But there is a workaround.
You can transfer your GA3 and GA4 data into BigQuery and then manipulate it there.
Following are the main disadvantages of using Google BigQuery:
#1 You need to be very careful about how you query data, especially big data, to avoid high query costs. If you don’t construct your queries properly or pull too much data too frequently, you could end up paying dearly at the end of each month.
#2 You need a good working knowledge of SQL to use BigQuery for data analysis.
#3 You cannot use BigQuery outside the Google Cloud Platform or Google ecosystem/infrastructure.
#4 BigQuery is not easy to learn on your own without formal training. The documentation provided by Google is not detailed enough and does not really explain how to use the product.
The cost of using BigQuery for Google Analytics
Your monthly cost of using BigQuery will depend upon the following three factors:
The cost of connecting your Google Analytics account to BigQuery
The amount of data you stored in BigQuery (i.e. the storage cost)
The amount of data you processed by each query you run (i.e. the query cost)
However, there is good news. The first 10 GB of active storage and the first one terabyte of data processed is free each month.
The BigQuery Sandbox is like a free version of BigQuery. It lets you use the Google cloud console for free forever, without creating your billing account or enabling billing for your BigQuery project.
However, the sandbox has certain limitations related to data storage and processing query data. To overcome these limitations, you should upgrade your BigQuery Sandbox account by setting up your billing.
Search and autocomplete (for old BigQuery user interface)
When you opt-in to search and autocomplete features, BigQuery will load your results on-demand for searches in the resource panel and autocomplete in the query editor.
Follow the steps below:
Step-1: Navigate to https://console.cloud.google.com/bigquery and then at the bottom of your screen, click on the ‘Enable’ button next to ‘Try search and autocomplete powered by Data Catalog ‘:
Step-2: Click on the ‘OPT-IN’ button:
BigQuery User Interface
In BigQuery, we create one or more projects. Each project is made up of one or more data sets. Each data set is made up of one or more tables. Each data table has ‘Schema’, ‘Details’ and ‘Preview’ tabs.
Schema is the structure of your data table. It shows you how the table has been set up and what type of values it will accept.
You can get the following information about your data table through the’ Details’ tab: Table ID, Table size, Number of rows in the table, etc.
You can preview your table without running a single query through the preview tab.
Sending Google Analytics data to BigQuery without using Google Analytics 360
All the GA360 users get a free connection to BigQuery. They can easily connect their GA360 property to BigQuery by clicking on the ‘Link BigQuery’ link in the admin area.
But if you do not have access to the GA360 property, then connecting Google Analytics with BigQuery is not straightforward as Google doesn’t provide any in-built connection to BigQuery.
You need to use a third-party paid solution to connect your GA property with BigQuery.
Following is the 10,000-foot view of sending Google Analytics data to BigQuery:
#1 You first need to create a BigQuery project and the corresponding dataset where you are going to store Google Analytics data in BigQuery.
#2 Use a third-party solution (connector) for connecting your Google Analytics property with your BigQuery project. We use this connector to extract Google Analytics data into BigQuery.
#3 Configure and save your data transfer in BigQuery. We create data transfer to automatically send Google Analytics data to our BigQuery project regularly.
#4 Query the Google Analytics data you need in BigQuery.
Google Analytics 4 provides a free connection to BigQuery. So you won’t need a third-party solution for that. You can easily connect your GA4 property with BigQuery by clicking on the ‘BigQuery Linking’ in the admin area:
Following is the 10,000-foot view of sending Google Analytics 4 data to BigQuery:
#1 You first need to create a BigQuery project where you will store Google Analytics 4 data in BigQuery.
#2 Link your Google Analytics 4 property to your BigQuery project.
#3 Enable and configure BigQuery API
#4 Query the Google Analytics 4 data you need in BigQuery
Where can you find Google Analytics 4 data in BigQuery?
Once you have successfully connected your GA4 property with BigQuery and more than 24 hrs have elapsed, you should be able to see your GA4 data in the following two data tables in BigQuery:
#1 events_ data table – This table stores all the GA4 event data from the previous day(s)
#2 events_intraday_ data table – This table stores all the GA4 event data from the current day.
If you want to estimate the storage cost or the cost of running your desired query in BigQuery before running it, then you can use the Google Cloud pricing calculator.
However, this calculator works only when planning to query the data in terabytes or petabytes.
Once you have access to the BigQuery public data sets, you can use it to find and analyze the sample data sets specially meant for Google Analytics.
If you do not have access to Google Analytics data and want to learn to analyze GA data in BigQuery, then the GA sample dataset can be used for practice purposes.
You can connect your Google Analytics account to your BigQuery account and then retrieve and analyze GA data in BigQuery.
The advantage of bringing GA data into BigQuery is that you can integrate Google Analytics data with data from other data sources (like Google Ads, Facebook Ads, etc.) and understand the overall performance of your marketing campaigns.
The recommended method to query Google Analytics data in BigQuery is using SQL(Structured Query Language). SQL is one of the most popular and widely used database query languages.
The BigQuery Export schema for GA4 defines the format of the Google Analytics 4 property data (and the Google Analytics for Firebase data) exported to BigQuery.
When you query Google Analytics 4 data in BigQuery, you could come across various columns/fields which may not make much sense to you.
For example, you may not be aware of what event_params.value.string_value field means:
Whenever you are not sure what a certain column/field (of GA4 data that is imported into BigQuery) means or what type of data they accept, you can always refer to the BigQuery export schema for GA4 document:
From this help document, we now know that the field event_params.value.string_value represents the string value of an event parameter.
BigQuery Export Schema for Universal Analytics
The BigQuery Export schema for Universal Analytics defines the format of the Universal Analytics data (analytics 360) imported into BigQuery.
Whenever you are not sure what a certain column/field (of Universal Analytics data that is imported into BigQuery) means or what type of data they accept, you can always refer to the BigQuery export schema Universal Analytics document:
You can import data from your Google Ads account to BigQuery with the help of a third-party solution/connector. Once the data is imported, it can be integrated with the data from other data sources (like Google Analytics, Facebook ads, etc.) for advanced analysis.
Following is the 10,000-foot view of sending Google Ads data to BigQuery:
#1 You first need to create a BigQuery project and the corresponding dataset where you will store Google Ads data in BigQuery.
#2 Use a third-party solution (connector) for connecting your Google Ads Account with your BigQuery project.
#3 Configure and save your Google Ads data transfer in BigQuery.
What is BigQuery Data Transfer Service & how does it work?
If you want to automatically send data from one or more data sources to a BigQuery project regularly, then you would need to use the Data Transfer service provided by BigQuery.
You can access this service via Cloud Console, bq command-line tool or BigQuery Data Transfer Service API.
You can import data from your Facebook Ads account to your BigQuery project with the help of a connector.
Once the data is imported, it can be integrated with the data from other data sources (like Google Analytics, Google ads, Google Search Console, etc.) for advanced analysis.
Following is the 10,000-foot view of sending Facebook Ads data to BigQuery:
#1 You first need to create a BigQuery project and the corresponding dataset where you will store Facebook Ads data in BigQuery.
#2 Use a third-party solution (connector) for connecting your Facebook Ads Account with your BigQuery project.
#3 Configure and save your Facebook Ads data transfer in BigQuery.
#4 Query the Facebook Ads data you need in BigQuery.
Following is the 10,000-foot view of sending custom Google Analytics data to BigQuery:
#1 You first need to create a BigQuery project and the corresponding dataset where you are going to store Google Analytics data in BigQuery. You can also use an existing project or dataset if you want.
#2 Figure out the overall layout and format (wireframe) of how your data table should look in BigQuery.
#3 Based on your wireframe, create a custom schema via a third-party solution (connector). We use the same connector to extract Google Analytics data into BigQuery.
#4 Create, configure and save your data transfer in BigQuery. We create a data transfer to automatically send Google Analytics data to our BigQuery project regularly.
#5 Backfill Google Analytics data in BigQuery.
#6 Query the Google Analytics data you need in BigQuery.
It could be the case that Supermetrics is too expensive for you, and you are desperately looking for an alternative. If that’s the case, then you are in the right place.
Meet Dataddo. It can do many things that Supermetrics can do, especially for BigQuery but at a fraction of the price. I did a lot of research and used this tool for weeks before recommending it.
I do not have any business relationship with Dataddo, nor did they ask me to recommend them. My recommendation is based purely on my own research and personal usage.
Do you know you can export historical Google Analytics data into BigQuery?
When you edit the data transfer in BigQuery, you get the option to use the ‘SCHEDULE BACKFILL’ feature. This feature can backfill Google Analytics data in your BigQuery data table(s).
When you import GA4 data to a BigQuery project, you use the default data table schema provided by Google.
As a result, you get no control over the number and types of data tables and what data is stored in each table.
You do not get the option to use your own fields in the data table.
If you want to see your data tables with only the fields you want then you need to create your own schema. This user-defined schema is called a custom schema.
If you have been collecting data in your GA4 property for years but have only recently linked your GA4 property to your BigQuery project then you won’t get all the historical GA4 data in your BigQuery project.
This is because, by default, the GA4 data is imported to BigQuery only from the date you first connected your GA4 property to your BigQuery project.
If you want historical GA4 data in your BigQuery project, then you would need to backfill GA4 data in BigQuery.
Frequently Asked Questions About Google Analytics BigQuery
What is Google BigQuery?
Google BigQuery is a data storage and management system used to bring data from several data sources for reporting and analysis. It is used to provide business intelligence.
Is Google BigQuery free to use?
The first 10 GB of active storage and the first one terabyte of data processed is free each month. Afterwards, you will be charged based on the extra data you stored and processed monthly.
How can I use BigQuery?
The primary requirement for using BigQuery is the knowledge of SQL. You would also need a Google Cloud Platform account with billing enabled.
What is the advantage of using BigQuery for Google Analytics?
When you use BigQuery, you can manipulate Google Analytics data in a way that is often not possible by using the Google Analytics user interface.
Where can I find Google Analytics 4 data in BigQuery?
You can see your GA4 data in the following two data tables in BigQuery: ‘events_’ and ‘events_intraday_’.
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
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
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
