Google Analytics BigQuery Tutorial

Table of Contents for Google Analytics BigQuery Tutorial

  1. What is Google BigQuery?
  2. Advantages of using BigQuery for Google Analytics
  3. Disadvantages of using Google BigQuery
  4. The cost of using BigQuery for Google Analytics
  5. Prerequisites for using BigQuery
  6. Introduction to Google BigQuery Sandbox
  7. Search and autocomplete
  8. BigQuery User Interface
  9. Sending Google Analytics data to BigQuery without using Google Analytics 360
  10. Sending Google Analytics 4 data to BigQuery
  11. Where to find Google Analytics 4 data in BigQuery

What is Google BigQuery?

Google Analytics BigQuery Tutorial

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

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

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

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.

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

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:

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 you can 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)


Frequently Asked Questions About Google Analytics BigQuery

What is Google BigQuery?

Google BigQuery is a data storage and management system which is used to bring data from several data sources for the purpose of 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 1 terabyte of data processed is free each month. After that you will be charged based on the amount of extra data you stored and processed each month.

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 which is many times simply not possible by using the Google Analytics user interface.

Where I can 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 Beyond
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 !!