BigQuery – what is this tool, and why is it so important in web analytics?

BigQuery is a service available in the cloud, i.e., Google Cloud Platform, which is used to process a huge number of data sets. In practice, this means you can throw data from various sources into this tool and analyze it very efficiently without hardware limitations. This is a great complement to the Google Analytics 4 service, as I hope to convince you of in this article.

Why will BigQuery be useful to you in web analytics?

The advent of Google Analytics 4 has introduced a revolution in the digital marketing industry, as you probably know and have probably even felt first-hand. The leap between Universal Analytics and GA4 is huge and brings with it changes affecting our perception of data One important difference between the two tools that I’d like to focus on today is the retention period of user data. In UA, the matter was quite simple: just one click and the data could stay there without automatic expiration.

In Google Analytics 4, the matter got a little more complicated for us. Regarding the data retention period, we have two choices: 2 months or 14 months. You can also find an article on our blog about why you should change the data retention period after setting up a GA4 service, and I recommend you take a look at it so you don’t make one of the biggest mistakes Google Analytics 4 users make. The 2-month period is definitely too short, but the next solution isn’t the best either. And this is where the BigQuery tool comes to the rescue! Thanks to the connection with GA4, we will ensure that we collect data without time limits unless we introduce such limits ourselves.

Another benefit that comes from integrating the two tools is the ability to collect, manage and export raw (unsampled) data in just seconds. Besides, you also have access to automatic anonymization of IP addresses, so you can be sure to meet all legal requirements in the context of data protection. Integration with BigQuery will also help you bypass any limits imposed by Looker Studio so you can create extensive reports without unnecessarily tearing your hair out in exasperation.   

How to connect BigQuery with Google Analytics 4?

I have good information for you – it’s child’s play! All you need to do is to go to the Administration section in your GA4 account, and then in the column for Services, select „Connected Services” → „Connected Services”. → „BigQuery.” Then click the blue „Connect” button. Keep in mind, however, that you are connecting a specific project at this stage, which you must have already created in BigQuery. You must also be the owner or administrator of both services to see the connectable projects. Once you have completed the various steps, you just need to save the changes. With a few clicks, your services will be connected!

How to create an account and start using BigQuery?

1. Log in to your Google account
2. Open Google Cloud Console

After logging into your Google account, open the Google Cloud console, preferably using the address console.cloud.google.com. If you are a new Google Cloud user, you must accept the terms of service before continuing.

3. Go to the BigQuery service

In the Google Cloud console, go to “Services” and search for “BigQuery”. Click on it to enable this service in your account (it’s also a good idea to use the pin icon to pin it right away).

4. Create a new project

You can do so from the main menu if you don’t already have a project created in Google Cloud Console. A project allows you to logically group various Google Cloud resources and services, including BigQuery.

5. Create a dataset (dataset)

Before you start loading data, create a dataset serving as a container for your tables. In the “Datasets” section, click “Create dataset” and provide a name and, optionally, a description (you can, for example, create data for your GA4 by typing „Google Analytics 4 + account ID). 

6. Load the data into the table

After creating a dataset, you can proceed to load the data into tables. Select the appropriate dataset, and in its “Tables” tab, click the “Create Table” button. Select options for loading the data, such as format, data source (e.g., CSV or JSON file, another Google Cloud resource or SQL query) and location.

7. SQL queries and data analysis

When the data is loaded into a table, you can analyse it using SQL query language. In BigQuery Console, you will find a window for typing SQL queries, allowing you to filter, aggregate, combine data, and generate analysis results.

BigQuery Glossary for Beginners

To make it easier for you to find your way around the seemingly complicated BigQuery interface, familiarize yourself with a few terms to start with:

• Dataset (Dataset)

Dataset is a container that contains the tables and metadata needed to organize and manage data in BigQuery.

• Table (Table)

A table is a data structure in BigQuery consisting of columns and rows in which data is stored.

• Project (Project)

A project in BigQuery is a container that contains datasets, tables, and other data analysis resources.

• SQL Query (SQL Query)

An SQL query is a command that performs operations on data in BigQuery, such as filtering, aggregation, merging and other analytical operations.

• JOIN

JOIN is the merging of data from two or more tables based on a common key, allowing information from different sources to be combined.

• SELECT

SELECT is an SQL command that selects specific columns or fields from a table to analyze and generate results.

• FROM

This is another one of the commands in SQL that tells us what table we will retrieve data from.

• WHERE

WHERE is used to filter data based on certain conditions.

• LIMIT

This SQL clause is used to limit the number of query results.

Learn web analytics under the guidance of experts!

Setting up GA4 and creating good reports keeps you up at night. Take advantage of our training program and learn analytics with practical examples!

Sign up for a free introductory interview!

How much does it cost to use BigQuery?

In the world of web analytics, there are both paid and free solutions. The absolutely unbeatable free tool is, of course, Google Analytics 4, but it’s worth extending it with a paid solution like BigQuery. 

Although BigQuery allows you to handle hundreds of thousands of queries (so-called queries) and analyze data through the use of SQL, the costs associated with maintaining this tool are not as great as you might think.

Google offers all new users $300 to use the Google Cloud Platform. In addition, you will receive 10 GB of free storage space and the ability to make up to 1 TB of queries per month (which is really quite a lot!). Thus, the beginning of your adventure with this tool is completely free? If you have already hooked up the payment, you will pay only for the data you analyze and only when you exceed the above-mentioned 1TB of queries. This gives you full control over your spending because if you don’t use BigQuery in a given month or use only a small number of queries, this analysis will be free for you. 

Why should you use BigQuery?

If you’ve read the article, I assume you could safely list several benefits of using this tool. However, as a summary, I will collect all the advantages in one place to make it easier for you to absorb the knowledge:

• Scalability: BigQuery is built on Google’s cloud infrastructure, allowing you to scale resources as needed dynamically. Regardless of the size of data sets, BigQuery can process them efficiently.
• Real-time data processing: With a real-time processing model, users can analyze and receive results as soon as the data is loaded. This is especially important for web analytics, where rapid response to changing trends and user behaviour is critical.
• Friendly query language: BigQuery supports the standard SQL query language, making it easy to use for those with database experience. This lets you quickly create complex queries and get the desired results without learning new tools or complex programming languages. In addition, if you are interested in web analytics, can learning this language give you many advantages in the future, especially professional ones?
• Integrations with other Google tools: BigQuery works closely with other Google tools, such as Looker Studio and Google Analytics 4, making it easier to analyze data and visualize results.

• Data security: BigQuery provides advanced data security mechanisms, including data encryption, and complies with the RODO regulations on collecting personal data.

After reading this article, I hope you know a little more about this powerful tool, BigQuery. There’s no denying it: in today’s world, data is extremely important, and BigQuery is very important in web analytics, especially if you want to get serious about it. If you have a spare moment and want to learn something new, I suggest reaching for this tool. It could mean long-term benefits for you, as digital agencies and analysts who use BigQuery can draw valuable insights from data collected virtually indefinitely.  

Good luck!