Hit Enter to Search or X to close
Pros and cons of using BigQuery for data analytics
With Google Analytics 4 (GA4), CRMs, Google Search Console, Google Ads… do you ever feel like you have data overload? Do you wish you could go deeper in your data analysis and extraction? Are you frustrated that you can’t get the answers you are looking for while analyzing your data? If that’s the case, you are not alone. As marketers, we are always looking for ways to improve our ways to deal with data.
In this journey, Google BigQuery is a formidable tool that could help you overcome many of your issues and limitations and better leverage your data. It allows you to take ownership of your data and access it with more control, granularity and creativity. It is however not an easy solution to use. So before you decide to invest time and money in Google BigQuery, get to know its pros and cons.
Let’s start with the basics.
Google BigQuery is part of Google Cloud, a platform which offers many cloud services and solutions. BigQuery, specifically, allows you to store data (it is what we call a data warehouse) in the cloud. And this data can be accessed and/or transformed with SQL queries.
BigQuery has an API that can be used to send or access data from what we call a “project”, an environment that you created and set up with specific datasets. The tool also offers a UI which allows you to set up, query and access your datasets and tables from projects.
The problem with data stored in solutions like Google Analytics, Google Search Console (GSC), Google Ads or your CRM, for instance, is that it is limited by what those solutions allow you to do. The limitations are diverse, but taking your data out, by storing it in a data warehouse, allows you to overcome most of those limitations.
Of course, the data itself and the means to export it to BigQuery might have their own limitations, but with this process, you take ownership of your data so that you can use it as you wish and need.
We will mention some of the most common limitations later in this article.
One big limitation of the Google Search Console, for instance, is that the data it provides is limited to 16 months back. This limits your capacity to see evolutions over multiple years and better understand, analyze progressions.
BigQuery being a storage solution, it allows you to stock your data for as long as you wish.
Another very annoying limitation when using the GSC UI, for instance, is the inability to load as many data rows as you wish. This means that, quite often, the tools don’t provide all the available data.
If you are able to export all your raw data to BigQuery, you don’t have these limitations anymore.
Again, it all depends on the data analytics tools you use. But GA4 and GSC, for instance, have big limitations when it comes to the data you can access in their UI (like the row limits, for example).
In the case of GA4, BigQuery allows you to access raw data, with no processing, which is a huge advantage if you want to conduct more precise analysis. The data you can access in GA4’s UI can present thresholding, sampling and/or cardinality issues. You won’t have these limitations in BigQuery (at least if you use the built in data export).
Still talking about GA4, BigQuery is also not subject to custom dimensions, metrics or user properties quotas. If you use the builtin data export, it sends all the raw data to BigQuery.
Even though a solution such as Google Analytics allows you to import some data from tools like Google Ads, Google AdSense and more, it greatly limits your ability to use data from external sources and to mix data coming from multiple sources.
As long as your data sources have common columns (data types), BigQuery allows you to freely blend data from multiple sources. You can, for instance, blend data from GA4 and your CRM in order to have more meaningful insights.
A solution such as BigQuery allows you to query your data in order to run complex calculations and operations to drive your analysis deeper. Using SQL, you can segment, filter, run your own formulas.
BigQuery is therefore a powerful option towards a more data scientific approach.
BigQuery is also an interesting solution to integrate in your process to store your data in a centralized solution such as your own data warehouse. For instance, the tool can easily, automatically, with no cost, receive data from GA4 and the Google Search Console. Therefore, it can be used as a data silo before using pipelines (dedicated tools which allow you to export in a controlled manner) to send this data in your own data storage solution.
We will talk about it later with the disadvantages, but BigQuery is generally very cheap. Of course, it all depends on what you do with it!
If you read all the points above, you might already see why this might not be for you. Bear with us though, because even though BigQuery can be used for all the reasons mentioned here, it can be very useful just to conduct better analysis and access more precise data with quite simple SQL queries.
If you have good SQL skills or access to resources who do, you could probably benefit from it.
Check the cons to better assess if it’s worth the hassle:
Like any tool, BigQuery, and Google Cloud in general, works in a very particular way. You need to learn how to handle it, which might be frustrating and time consuming.
If you already use Google Cloud, this is not going to be an issue for you.
To keep it simple, Google BigQuery is a storage solution. To access, transform and/or use the data that it stores, you need to use SQL queries.
If you don’t know SQL, you can always learn. But it takes time and effort.
If you do, you will love BigQuery.
Keep in mind that even Google BigQuery’s API requires the use of SQL.
BigQuery being a data warehouse, it requires strategies and processes in order to access and transform data, especially if you want to run complex calculations or processes for data export, for instance. This can require a lot of work and maintenance.
Moreover, if you rely on BigQuery for your analytics, this is an additional element in your setup.. It adds complexity and potential issues, as well as cost.
This is the price to pay for more efficiency and granularity though…
A Google BigQuery project is free. However, based on the data volume you store and process (through queries), Google might charge you. Therefore, over time, the solution might start to cost you each month or each time you conduct analysis.
The solution might be especially costly if you play around and try a lot of heavy queries. It might especially be the case if you don’t apply the best practices when it comes to SQL. For instance, if you query all your dataset, it might be very costly based on the amount of data it stores.
As mentioned before, BigQuery can be very cheap (if not free) though. It all depends on how you use it.
Of course, it all depends on the analytics solutions you use.
But let’s talk about GA4 and GSC. They both offer builtin bulk exports features. This is great because it is easy to use (no need to code scripts using their APIs, for instance), reliable and free (you don’t need to pay for pipelines or other tools).
However, they’re not perfect: they only export data starting on the day you set up the exports. The historical data is not going to be sent to BigQuery.
Limitations like that are an example of added complexity inherent to the use of BigQuery, because overcoming them can be complex and costly.
Want more? Here are other articles for you