Using BigQuery with Google Analytics 4: Definitive Guide
Using BigQuery with Google Analytics 4 will set you ahead of the curve in terms of finding new ways to manipulate and utilize data. With data becoming the dominating influence in informing business and marketing decisions, it’s important that you’re making the most of it. So let’s start with some background.
If you’re running a website, chances are you’re using Google Analytics (GA). It’s a tool being utilized by an estimated 30-50 million people. If you aren’t one of these people, well, you probably should be. In today’s world, data is becoming more and more of a sought-after commodity. If made the most of, GA allows us to gain more knowledge about an individual customer than ever.
But all of this data can sometimes be a bit overwhelming. This is especially true if you’re running a big business with various applications and websites to manage. What are you supposed to do when the data is too much to handle?
Luckily, Google have presented us with an answer to this question. They’ve called it BigQuery (BQ), and using BigQuery with Google Analytics 4 can really simplify your data-related conundrums.
BQ opens a world of opportunities that will help you to take advantage of all this data. This minimises the headache and allows you to create more streamlined and effective marketing campaigns.
What is BigQuery?
If you’re running a big business, you’re likely gathering data from all kinds of places. Be it your app, website, email or marketing campaigns. Sometimes, it means you’re gathering lots of data from a variety of locations worldwide. With all these data sets, you need a location to store everything.
This is probably going to blow a hole in your budget; you’re not just paying for location but maintenance and security. There’s also the issue of processing all of this data. Sometimes an analytics report can take days to formulate. That’s time you could have spent improving your marketing campaigns and growing your brand.
Benefits of using BigQuery with Google Analytics 4
With BigQuery, you’re no longer responsible for having to pay for a warehouse and all the elements that come with it. Google provides the space and infrastructure, meaning BQ acts as a fully managed data warehouse. Suddenly you can process data much more quickly, delivering better results for you and your business.
Another issue with having so much data is being able to find the information that you want. There are a whole number of potential headaches related to this. You or your employees could have to spend hours trying to collect relevant pieces of information.
BQ makes this data simple and accessible. All information is stored in a structured table, allowing for the use of the programming language, standard SQL. This allows you to easily query your data. BQ is particularly useful when you’re running thousands of stores. It allows you to break up your revenue by Stock Keeping Unit, region, or per time period. This function saves time and money.
In today’s competitive environment, you need to make the most of your data. Any tool that helps you to understand your customers better gives huge benefits. One that also helps convert visitors to your site into customers will give you a vital business advantage.
The BigQuery application can save you time and improve the overall efficiency of your business. To gain maximum benefit, you might need expert help. We have a great track record in helping businesses to grow their customer base through Conversion Rate Optimisation services.
BigQuery can help you overcome lots of challenges
There are lots of reasons why a big business should start using BigQuery. One of these is the range of obstacles that it can help you to navigate.
BigQuery integrates with Google Clouds Identity and Access Management
When you’re managing swathes of data, it’s inevitable that there will be certain information that you don’t want everyone to see. This is where Google Clouds Identity and Access Management comes in. The tool allows you to control who can access data, allowing you to assign read or write permissions to specific users. This ensures that sensitive information stays private.
BigQuery helps you to avoid the ‘data silo’ problem
This is one nightmare that businesses that deal with lots of data know too well. It happens when individual teams have their own data marts (structures used to recover client-facing data). When data is being analysed across teams, it can cause friction and create issues with data version control. By integrating with Google Clouds Identity and Access Management, BQ allows for continued collaboration between teams, whilst assigning specific teams to different data sets.
BigQuery cuts out red tape
Have you ever been in a situation where you’ve had to manage large structures to deal with data? With BQ, this becomes a thing of the past. There are three parts to BQ, storage, ingestion and querying. Everything else is dealt with by Google handles, meaning that you have to spend less time setting up intricate structures. You don’t even need a database administrator, you can just log in. You’re saving time and money.
How does BigQuery work with Google Analytics 4?
Google Analytics 4 seems to have all the analytical features one could imagine. If used correctly, GA can be a real game-changer.
But what do you gain by integrating it with BigQuery? Whilst it’s true that GA4 has a world of analytical features, there are lots of ways that it can work with BQ to produce more effective analytics reports:
You can perform more advanced analytics
By enabling the export of raw event data from Google Analytics 4 to BigQuery, you can perform even more advanced analytics. This means you will know your audience better, and be able to perform pinpoint accurate marketing, putting you a cut above your competitors.
Making the most of data is something Measure Minds knows a thing or two about, hence our short listing in the UK Growth Awards for 2020.
You can make the most of Data sampling
Data sampling is a handy tool that allows you to analyse subsets of user data. It’s something that is particularly useful when working with larger data sets to gain statistical analysis. Subsampling is only really effective if it’s working with lots of data.
With BigQuery, you can subsample up to 1 terabyte each month, and store up to 10 gigabytes. For all that storage, you don’t even have to burn a hole in your pocket – this is the free version. If you want more storage, you’re looking at $5 per terabyte each month.
You can export custom events to BigQuery with Google Analytics 4
One of the biggest features of GA4 was its new data model. Each specific user interaction is now treated as an individual event. With GA4, a user can create custom events that record specific predefined actions such as views on an individual webpage. By exporting your custom events to BigQuery, you can take advantage of the massive amounts of data collected and get a more in-depth analysis.
Visualise your data
Google Analytics 4 can be linked with a number of data visualisation apps such as Looker Studio. Once linked, you can push your BigQuery data to the data visualisation app. When working with massive amounts of data subsets, visualisations are a must.
Need help setting up Google Analytics 4?
There are numerous steps involved in the setting up of Google Analytics 4 and like with BigQuery, it can be a bit difficult to get your head around. If you need help upgrading or setting up GA, we have created a step-by-step how-to guide. This will take you through setting up, and making the most of GA.
Alternatively, you could use our GA4 migrator app to automatically migrate you to Google Analytics 4.
Setting up, and working with BigQuery
If you’re running a big business and managing lots of data. Now is probably the time to get set up with BigQuery. If you’re already using the tool, and want to integrate it with GA4, head over to the ‘Linking BigQuery with Google Analytics 4’ section.
Step 1: Set up your account
To begin, head to cloud.google.com/bigquery. From here, you can begin the process of setting up your account.
The first thing you’ll probably notice is some text notifying you that, as a new customer, you get $300 worth of free credits to spend on Google Cloud. This applies during the first 90 days of you using the application.
The set-up will ask for your credit card details. Please note, even if you use all of your credits, or time runs out, Google will ask if you want to pay for continued use.
Step 2: Set up your first project in BigQuery
Once you’ve got through the initial setup and finished filling in your details, you’ll be met with the BigQuery interface. It’ll look something like this.
There is a whole world of options here. For now, head to the top right and select ‘My First Project’.
This will bring up a pop-up menu. Normally this is where you’ll see all the projects you’ve got on the go. Since this is our first project, we’ll hit ‘New Project’ in the top right.
And there you have it, you’ve set up BigQuery and started your first project.
From the ‘New Project’ screen you can name your project and input your organisation name. We’ll name our project ‘First Project’ but you can name yours whatever you want. When you’re done, hit ‘create’.
Note: Before proceeding, you’ll want to make sure that billing is properly set up. You can do this from the ‘billing’ section on the main menu.
Linking BigQuery with Google Analytics 4
Now that you’re all set up you can begin the process of linking your BigQuery project with Google Analytics 4. As we’ve already explained, there are lots of reasons for doing this, from more detailed reports to better data visualisations. Luckily, Google have made this process simple. It shouldn’t take more than a couple of minutes.
Step 1: Go over to Google Analytics 4
To begin the process of linking with BigQuery, you first need to head to your Google Analytics 4 property.
From your GA4 interface, select ‘admin’ at the bottom left.
Step 2: Select BigQuery linking
From here you’ll see two columns. Scroll down in the second column until you see ‘BigQuery Linking’. Select it.
Step 3: Proceed through BigQuery linking
This will bring up the ‘BigQuery Linking’ screen. This is where you’ll be able to see all of the projects that you have linked to your Google Analytics 4 property. As this is the first time you’re linking a project, this box will be empty. To link your project, select ‘Link’ in the top right.
Step 4: Choose a BigQuery project
From here you can select the project that you want to link with BigQuery. Select ‘Choose a BigQuery’ project.
Step 5: Confirm the selection of your BigQuery project
You should now see the project you created earlier. Select the tick box to the left of the Project ID and then hit ‘Confirm’ in the top right.
Step 6: Configure the settings for linking your report in BigQuery with Google Analytics 4
This will bring you back to the linking screen. Select a Google Cloud location for your data to be stored and then click ‘Next’.
We’re now configuring the settings for linking your report with GA4.
The first area to focus on is ‘Data Streams’. Here you’re selecting all the data sources that will be feeding into your project. If you’re a domain controlling numerous websites and apps, you need to input this information here.
Next is ‘Frequency’. In this section, you’re choosing how often you’d like to receive data updates from your property. You can choose daily updates or ‘Streaming’ updates – streaming gives you continuous data as it comes in.
What kind of data do these reports give you?
If you’re familiar with GA4, you’ll know that every user action is treated as an individual ‘event’. This includes page scrolls, button clicks, and basically anything you can imagine. All of this information is compiled and sent to your BigQuery reports.
Daily, or streaming reports?
What you choose here is really down to the kind of website that you’re running. If your site is running hour-based content that requires constant analysis (e.g., News updates), then you’ll want to choose ‘Streaming’. Otherwise, select ‘Daily’. If you want the best of both worlds, you can tick both boxes.
Step 7: Review your selections and submit
Once you’ve made your choice, hit done.
Before the linking of your BigQuery project with Google Analytics 4 is complete, Google will ask you to review your selections. If you’re happy with what you’ve chosen, hit ‘Submit’.
You’ll now get a pop-up to notify you that a link has been created.
Need help with Google Analytics?
It’s all well and good linking BigQuery with Google Analytics, but it’s only really useful if you’re getting the right data from your GA. If you feel you could be getting more from your reports, or if you just feel you need brushing up, Measure Minds offers comprehensive Google Analytics and Google Tag Manager training.
All training is provided by industry experts who are globally renowned for their mastery of Google Analytics. You don’t even need to make the trip – We are willing to make the journey to come and see you to provide world-class training in-house.
How to set up an API to send BigQuery data to Google Analytics 4
Step 1: Head to the ‘APIs & Services’ library in BigQuery
Whilst a link has now been created with your project, there is still one more step you need to take before you can send data to Google Analytics. To do this you’ll need to reopen BigQuery.
From the BigQuery interface, go to the toolbar at the right of your screen, hover over ‘APIs & Services’ and select ‘Library’.
Step 2: Select and enable the API to link Bigquery with Google Analytics 4
From the ‘Library’ screen, you’ll see a search box. Select it and type ‘BigQuery API’, then hit enter. You’ll want to select the first option (as seen above). This API will link BigQuery with your Google Analytics 4 property. Once you’ve selected the API, you’ll want to click enable.
There’s a chance that this will be enabled already, if that’s the case – good news. If not, don’t panic! The API is entirely free.
Setting up service accounts to send data between BigQuery and Google Analytics 4
Now that you’re API is installed, there’s one more step you’ll need to take before it’s fully linked, and that’s setting up a service account. This will authorise data to be sent between your project and GA4.
What is a service account?
A service account is an account that helps manage Google Cloud Projects. It represents a nonhuman user and needs authorisation to access data from APIs. Google provides these examples of scenarios where service accounts can come in useful, most commonly running workloads:
- On virtual machines (VMs).
- On on-premises workstations or data centres that call Google APIs.
- That are not tied to the lifecycle of a human user.
If you’d like to know more about service accounts, take a look at this useful guide created by Google themselves.
Adding the Firebase Measurement service account
So that data can be sent from your project, you need to add the ‘Firebase Measurement’ service account. To set this up, head back to your main dashboard and from the toolbar, hover over ‘IAM & Admin’ and select ‘Service Accounts’.
From this screen, you’ll see all service accounts associated with your project. Select ‘Create Service Account’.
This brings up the service account creation screen, which allows you to specify details about your service account. Into the ‘Service account name’ section, paste firstname.lastname@example.org. Select whatever you want, ideally one that best describes the service account. Next, select ‘create and continue’.
On the next screen, you need to select a role for your service account. There’s a variety of roles that you can select.
For this service account, we’ll be selecting the editor role. To access the role, click ‘Basic’ in the quick access section and choose ‘Editor’.
The final screen allows us to choose what permissions we want to give other users the option of deploying jobs to the service account. For now, we’re leaving this screen blank and selecting ‘Done’.
And there you have it – your Service account is successfully set up. You’ll now begin seeing data flowing into your reports – although it may take a day or two.
How can I see data that I’ve received after linking BigQuery with Google Analytics 4?
So you’ve set up your BigQuery account, linked it with your Google Analytics 4 property and activated your API. You’re on your way towards a big step in your digital transformation.
Does that mean data can now start moving between your BigQuery project and GA4? Well not quite. You’ll probably need to wait around a day before linking is complete and you start seeing data. To see your data and your queries, you’ll need to do the following.
Go the ‘Query History’ tab. To navigate to this, go to your BigQuery dashboard. From the toolbar on your right, scroll down until you find ‘BigQuery’ and select it.
This menu provides a complete list of all of your projects and the data related to each one. You may need to click “Disable Editor Tabs” first but then after you’ll be able to click ‘Query History’.
This handy screen is where all your data queries can be found. Once it’s all set up, it will look similar to the image shown below. Before this can happen, however, we need to create a new dataset.
Something called a dataset will be made automatically for you. However, if you’d like to create new datasets manually, here’s how to do it.
Creating a dataset in BigQuery
To create a new dataset, select the small, dotted line to the right of your project name. You’ll need to click “Enable Editor Tabs” first at the top and click ‘Open’.
You’ll now see some additional options at the top of your screen. Select ‘Create Dataset’.
A pop-up window should now appear, enabling you to configure settings for your dataset. Let’s go through each section.
At the top of the window, you’ll see ‘Dataset ID’. This is basically just choosing a name for your dataset. You can choose whatever name you want, although you’ll probably want to choose a name that’s relevant to the data that you’re collecting.
The next option is ‘Data Location’. Here you’re choosing where your data is stored. By selecting ‘Default’ Google will send your data to the US multi-region.
Next, we have ‘Default table expiration’. This option allows you to decide how long your data tables are stored before deletion. You can either specify never – your tables will not be deleted, or a number of days until deletion.
If you’re dealing with temporary data that you won’t need in the long term, it’s recommended you select the latter. If you are managing data that you want to refer to in the months and years ahead, go with the former.
Finally, we have encryption settings allowing you to choose between a Google-managed and customer-managed key.
Once you’ve made your selections for each section, you can hit ‘Create Dataset’.
Creating Tables in BigQuery
Now your dataset is created, you can begin creating tables for your project. A table in BigQuery consists of data records. Each record is composed of a column.
Types of tables in BigQuery
There are three different types of tables in BigQuery:
These are backed by the native BigQuery storage. Native tables allow a user to import full data into BigQuery. These can take longer to create and incur greater costs (you’re storing more data within the cloud).
These are tables that deal with data that is stored externally outside of BigQuery. They are quicker to create than native tables, and don’t incur any extra costs. On the negative side, queries for external tables can take longer than native tables. This is especially the case if your external tables are storing large files.
This is a virtual table that is defined by a SQL query (used to pull important information from databases). With a view, you can set up default settings, so you don’t have to repeat them in future queries. Views are read-only, information cannot be edited, updated or deleted.
How do I create a table?
After creating your dataset, you’ll be brought back to the ‘BigQuery’ screen. Select the white plus symbol to create a table.
Once again, we’re met with a myriad of options. As before, we’ll walk you through each one.
From the top, we have ‘Source’. This is where you choose the basis for your table.
There are a few different source options.
- Empty Table (no data will be inputted).
- Google Cloud Storage
- Upload (add the data yourself)
- Google Drive
- Google Cloud Bigtable
If you’ve already linked GA4 with BigQuery as mentioned earlier in the article, you won’t need to do this. However, if you want to add extra data into BigQuery, you can use any of the above source options but for now, we’ll use the file upload option as an example. By selecting upload, we’re given the option to pick a file and choose the appropriate file format.
Once you’ve selected your file, you can move on to the next section.
Now it’s time to name your table (go with whatever name works best) and pick a dataset. We’ll be selecting the set that we created earlier.
Next, you’ll see the table type. What you choose to select here, all depends on where your data is coming from. If it’s an internal source, go with a native table. If you’re working with external data, choose an external table.
Next, we’re onto ‘Schema’. This is an essential part of the process. Essentially, the schema is all the different values that relate to the file you’re uploading. When a document is made up of columns, data types and key numbers, it’s important that your table correctly reflects this information. The good news is that BigQuery can autodetect a schema, meaning that you don’t have to do it manually.
Partitioning and cluster settings
Finally, we have our ‘Partition and Cluster settings’. With partition settings, we can decide the kind of partitioning we want for a table. We can choose between ‘No Partitioning’ and ‘Partition by ingestion time’. With partitioning, you can divide your tables into smaller segments. This allows for querying using filters based on partitioning columns.
Filters help to make the querying process faster by reducing the amount of data that is scanned. It can also reduce costs and improve querying performance. As nice as all that sounds, for now, we will keep things simple and select ‘No partitioning’.
With ‘Clustering order’ we can decide on the sorting order of data. We’ll leave this empty.
There is also an ‘Advanced options’ section, but this is something for you to come back to once you’re more familiar with BigQuery
Once you’ve gone through the various sections and selected options that you are happy with, you can choose ‘Create table.’
You’ll see that your table has now appeared under your project.
If you select your table, you can see the various aspects of the data that you have uploaded. If the information is transferred correctly, the layout should resemble the images below.
What can you do with this information?
You’ve created a dataset and input some data into a table? But how is this useful? Well for a start, you can query the data.
Querying your Dataset
To test a query, choose a table. Click the three dots and select “Query”. After which, the query syntax is written for you automatically. This will take you to a screen like the one seen below. From here you can input all of your data queries. All queries are based on SQL.
We’ll put an Asterix (*) after ‘select’. This is instructing BigQuery to query the entire table. The ‘Limit 1000’ found at the end of the line refers to the number of items queried.
Done correctly, the line should now look like this. If you’re satisfied, select ‘Run’. Your query will begin processing. You’ll now get your first sense of how lightning-fast BigQuery is. Your query should be completed in a matter of seconds.
Because you have selected the whole table in your query, the report should look identical to the table. If you know SQL, you query whatever information that you want, and get your information back at similar speeds. If you’re not familiar with SQL, it might be time to bring in someone that is.
Google Analytics 4 Related Queries
Sometimes, when you’re managing a property, it can be useful to know about user interactions as they happen. This can help you to gauge the performance of a certain page or get better insights into the kind of behaviour of your users.
With Google Analytics 4, all user interactions are treated as ‘events’ but how can we query GA4 events?
Step 1: Compose a new query in your Google Analytics data stream
To begin, we need to return to your project list and select your GA data stream. As before, we need ‘Compose New Query’.
Step 2: Use a list of commands and paste into your query
You now should be back on the query creation screen. For this, we’ll run a query to let us know when a user first opens a page or an app. To do this you’ll need to paste the list of commands below into your query.
(event_name = ‘first_open’,
0)) AS first_open
_table_suffix = ‘20181003’ — update to your desired start and end date’
Once you’ve pasted this set of commands into your query editor, hit ‘Run’. You’ll now start seeing details about users that have entered your site, alongside the time they arrived.
As with before, you can save this information, add it to a table or create a view. With the power of BQ’s querying feature, you can find out and isolate all sorts of information from your Google Analytics reports.
How do I keep a record of queries?
With all the data that you’re receiving from BigQuery, you’ll undoubtedly want to save your reports so that you can retrieve your data later down the line. There are a few ways you can do this.
Saving your Query Results
The first, is to click ‘Save Results’ below your query.
BigQuery will then prompt you to choose an option for storing your Query. Each option has its own capacity for storage:
- CSV – Allows you to save up 16,000 rows locally.
- CSV (Google Drive) – You can save CSV files on Google drive, there a 1GB limit.
- JSON – As with CSV, you can store 16,000 rows locally.
- JSON (Google Drive) – Once again, there is a 1GB limit.
- BigQuery table – Perhaps one of the most useful options, this allows you to save your query as a table within BQ. The process for this is similar to creating an ordinary table.
- Google Sheets – You can save up to 16,000 rows to Google sheets.
- Copy to Clipboard – you can also copy up to 16,000 rows to your clipboard. These can then be manually pasted into a table.
- So, a great deal of options for how and where you want to save your queries.
But there’s more…
You can also save a query directly. With this option you choose who can access your queries – great when working with larger teams. You can make a query private, public or enable team edits.
Retrieving and editing past queries
You can also head to ‘Query History’ to find a complete list of all your queries. This can be divided into ‘Personal History’ and ‘Project history’.
Once you’ve selected a Query, you can choose to open it in the editor. This allows you to look back on past queries and edit them if you wish.
Saving a query as a View
The third option for saving a query, is for saving as a view. As explained earlier, a view is a table created from a Query. To create a View, hit ‘save’ and select ‘Save View’.
This will bring up the view creation table. Select the project and dataset link the view with. Now you need to name your table. BigQuery does not allow you to include spaces in the name, so if you’d like to separate the words use an underscore (_).
When done, click save.
Your View will become visible beneath your dataset. Select it and you’ll see a table specifying all of the values from your query. This is particularly useful when looking at a specific set of data. For example, you could create a table to display profit from a specific region.
You can even query a view, to go into even more specific detail. To do this simply select ‘Query View’ at the top of your toolbar.
Go big or go home
There’s so much more to BigQuery, that we’ve barely touched the surface. To properly understand the program and all of its features could take weeks. If you’re unsure, there’s no harm in enlisting expert help to make sure you get the most out of your reports.
There’s no doubt that BQ is essential for any large business that’s controlling multiple properties. Think about the time, resources and money that could be spared by using it. Making the most of data can elevate your business to the next level. You can learn more about the buying habits of your customers than ever. Enabling you to create more streamlined and effective marketing campaigns.
The alternative is running multiple warehouses. These require staffing, security, and maintenance. You would also need to manage all your data yourself. The simple truth is that this is never likely to be more effective than the solutions presented by BigQuery.
Don’t be saddled with outdated and underperforming data management. You’ll only spend time lagging behind your competitors. If a business is to survive in the modern world, it must learn to use data to support strategic planning and better decision-making.
Data is the future – stay ahead by using BigQuery with Google Analytics 4
As time goes on, data is only going to become more and more important. New ways of analysing and utilising data are constantly appearing. The only way to get ahead of the curve is to keep up with technology like BigQuery.
It is easy to see how important all of this is. It would be understandable, however, if this still seems quite daunting. We have helped many businesses, in sectors from banking and Local Government through to healthcare and education. With our help, they have made the most of their data and delivered real benefits to their organisations. We would love to help your business to thrive.
Want more? Check out our blog for more tips on Google Analytics, as well as a whole host of other Google packages.