Table of contents

    BigQuery is a service available in the cloud i.e. Google Cloud Platform, which is used to process a huge amount of data sets. In practice, this means that 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 BigQuery will 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. When it comes to the data retention period, we have two choices: 2 months or 14 months. You can also find an article on our blog, 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 doesn’t seem to be 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 simply click the blue „Connect” button. Keep in mind, however, that at this stage you are connecting a specific project, which you must have already created in BigQuery. You also need to be the owner or administrator of both services to see the connectable projects. Once you have completed the various steps, at the end you just need to save the changes. A few clicks and your services are 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 will need to accept the terms of service before continuing.

    1. 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 the service right away).

    1. Create a new project

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

    1. Create a dataset (dataset)

    Before you start loading data, create a dataset that will serve 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). 

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

    1. SQL queries and data analysis

    When the data is loaded into a table, you can start analyzing it using SQL query language. In BigQuery Console, you will find a window for typing SQL queries, which allows you to filter, aggregate and combine data, as well as 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)

    Project in BigQuery is a container that contains datasets, tables and other resources related to data analysis.

    • 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 used to select 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 are going to retrieve data from.

    • WHERE

    WHERE is used to filter data based on certain conditions.

    • LIMIT

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

    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. In my opinion, 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 is offering all new users $300 to use the Google Cloud Platform to start. In addition, you will receive free 10 GB of 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 🙂 It is worth mentioning here that if you already hook 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?

    I assume that if you’ve read the article, 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, which allows you to dynamically scale resources as needed. Regardless of the size of data sets, BigQuery is able to 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 behavior is critical.
    • Friendly query language: BigQuery supports the standard SQL query language, making it easy to use for those with database experience. This allows you to quickly create complex queries and get the results you need without having to learn new tools or complex programming languages. In addition, if you are interested in web analytics, learning this language can 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 is compliant with the regulations of the RODO on the collection of personal data.

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

    Good luck!

    Let's talk!

    Dominika Andrejko
    Dominika Andrejko

    Hi, my name is Dominika Andrejko, and in the digital marketing industry I work in Google Ads and analytics. At UpMore, I joined the SEM team and would be happy to explain the intricacies of the latest GA4 and run campaigns on Google.