Last Updated: May 20, 2022

What is Google BigQuery?

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

The bigquery is an enterprise-level data warehouse from Google which is used to provide business intelligence in the form of reports and dashboards. 

It is a data storage and management system which is 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, certain dimensions and metrics combinations can not be queried together whether you use the Google Analytics user interface or Google Analytics API. 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. 

To learn more about the BigQuery advantages, check out this article: Advantages of using Google BigQuery for Google Analytics

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

Disadvantages of using Google BigQuery

Following are the main disadvantages of using Google BigQuery:

#1 You need to very careful about how you query data esp. big data to avoid high query cost. If you don’t construct your queries properly or pull too much data too frequently, you could end paying dearly at the end of each month.

#2 You need a good working knowledge of SQL in order to use BigQuery for data analysis.

#3 You can not use BigQuery outside of 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:

1. The cost of connecting your Google Analytics account to BigQuery
2. The amount of data you stored in BigQuery (i.e the storage cost)
3. The amount of data you processed by each query you run (i.e. the query cost)

However, there is a good news. The first 10 GB of active storage and the first 1 terabyte of data processed is free each month.

To learn more about BigQuery pricing, check out this article: Cost of using BigQuery for Google Analytics

If you want to learn to control the cost of using Google BigQuery then check out this article: Guide to BigQuery Cost optimization

Prerequisites for using BigQuery

Following are the prerequisites for using Google BigQuery:

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

#2 You need a Google Cloud Platform account with billing enabled. In order to enable the billing, you would need a valid credit card.

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

Introduction to Google BigQuery Sandbox

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 comes with 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.

To learn more about the BigQuery Sandbox, check out this article: What is Google BigQuery Sandbox and how to use it

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 for 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 got ‘Schema’, ‘Details’ and ‘Preview’ tabs.

Schema is the structure of your data table. It shows you how the table has been set up. What type of values it accepts.

Through the ‘Details’ tab you can get the following information about your data table: Table ID, Table size, Number of rows in the table etc.

Through the preview tab you can preview your table without running a single query.

If you want to get a visual walkthrough of the BigQuery UI then check out this article: Understanding the BigQuery User Interface

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 would need to use a third party paid solution in order 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 in order to automatically send Google Analytics data to our BigQuery project on a regular basis.

#4 Query the Google Analytics data you need in BigQuery.

To learn more about connecting and sending GA data to BigQuery, check out this article: Sending data from Google Analytics to BigQuery without 360

Get this 70 page detailed checklist containing screenshots, step-by-step instructions and links to articles

Do you want to set up Google Analytics 4 (GA4) fast and correctly?

Yes I want the ebook

Sending Google Analytics 4 data to 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 are going to 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

Follow the steps mentioned in this article in order to connect and then send your Google Analytics 4 data to BigQuery: How to connect GA4 (Google Analytics 4) with 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 learn more about these two data tables then check out this article: events_& events_intraday_ tables in BigQuery for GA4 (Google Analytics 4)

Google Cloud pricing calculator for BigQuery

If you want to estimate the storage cost and/or the cost of running your desired query in BigQuery before you actually run it then you can use the Google Cloud pricing calculator.

However, this calculator works only when you are planning to query the data in terabytes or petabytes.

To learn more about this calculator, check out this article: Using Google Cloud pricing calculator for BigQuery

BigQuery Public Data Sets

BigQuery Public Data Sets are the data sets which are made available to the general public through the Google Cloud Public Dataset Program.

If you do not have access to big data then public data sets provide a great way to play with big data without the need to pay any storage cost.

You can also analyze public datasets while using the BigQuery Sandbox.

If you want to learn how to find and use the public data sets then check out this article: How to access BigQuery Public Data Sets

Google Analytics sample dataset for BigQuery

Once you have access to the BigQuery public data sets, you can use it to find and analyze the samples data sets specially meant for Google Analytics.

If you do not have access to Google Analytics data and you want to learn to analyze GA data in BigQuery then the GA sample dataset can be used for practice purpose.

Learn more about the GA sample dataset from this article: How to use Google Analytics sample dataset for BigQuery

Connecting Google Sheets to BigQuery

You can connect to your Google Sheets document from your BigQuery account. But the connection is not so apparent and straightforward.

However, once the connection has been made, you can then easily analyze the data stored in Google Sheets in BigQuery.

To learn more about connecting Google sheets to BigQuery, check out this article: Connect and transfer data from Google Sheets to BigQuery

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

Querying Google Analytics data in BigQuery

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 by using SQL(Structured Query Language). SQL is one of the most popular and widely used database query language.

To learn more about querying GA data in BigQuery, check out this article: How to query Google Analytics data in BigQuery

BigQuery Export Schema for GA4

The BigQuery Export schema for GA4 defines the format of the Google Analytics 4 property data (and the Google Analytics for Firebase data) that is 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 do 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) that is 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 do they accept, you can always refer to the BigQuery export schema Universal Analytics document: 

Note: This schema document is designed for Google Analytics 360 users. 

Importing Google Ads data into BigQuery

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 then 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 are going to 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.

#4 Query the Google Ads data you need in BigQuery

To learn more about importing Google Ads data into BigQuery, check out this article: How to send data from Google Ads to BigQuery

What is BigQuery Data Transfer Service & how it works

If you want to automatically send data from one or more data sources to a BigQuery project on a regular basis 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.

To learn more about this data transfer service, check out this article: What is BigQuery Data Transfer Service & how it works.

Importing Facebook Ads data into BigQuery

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 then 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 are going to 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.

Follow the steps mentioned in this article to send Facebook ads data to BigQuery: How to send data from Facebook ads to BigQuery

Importing Google Search Console data into BigQuery

Just like Google Analytics, Facebook Ads and Google Ads, you can also send data from Google Search Console into your BigQuery project.

Following is the 10,000 foot view of sending Google Search Console data to BigQuery:

#1 You first need to create a BigQuery project and the corresponding dataset where you are going to store the Google Search Console data in BigQuery.

#2 Use a third-party solution (connector) for connecting your Google Search Console Account with your BigQuery project.

#3 Configure and save your Google Search Console data transfer in BigQuery.

#4 Query the Google Search Console data you need in BigQuery.

To learn more about Google Search Console and BigQuery integration, checkout this article: How to send data from Google Search Console to BigQuery

Sending Custom Google Analytics data to 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 and/or dataset if you want.

#2 Figure out the overall layout and format (wireframe) of how your data table should look like in the BigQuery.

#3 Based on your wireframe, create 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 data transfer in order to automatically send Google Analytics data to our BigQuery project on a regular basis.

#5 Backfill Google Analytics data in BigQuery.

#6 Query the Google Analytics data you need in BigQuery.

To learn more about sending Custom GA data to your BigQuery project, follow the steps mentioned in this article: How to pull custom data from Google Analytics to BigQuery

Are you looking for a Supermetrics alternative?

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 a lot of things which Supermetrics can do esp. for BigQuery but at the fraction of the price. I did a lot of research and actually used this tool for weeks before recommending it.

We do not have any business relationship with Dataddo. Nor they asked us to recommend them. My recommendation is based purely on my own research and personal usage.

You can learn more about this tool from the article: Best Supermetrics Alternative – Dataddo

Backfilling Google Analytics data in BigQuery

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. Through this feature, you can backfill Google Analytics data in your BigQuery data table(s).

To learn more about such backfilling, check out the article: How to backfill Google Analytics data in BigQuery

Frequently Asked Questions About Google Analytics BigQuery