In one of our articles, we already mentioned the advantages of using BigQuery to get complete access to your data compared with the GA4 interface.
In this article, we propose to touch on another case in which BigQuery gives odds to GA4 – this is the use of data to build dashboards. It is well known that access to correct, timely, and understandable data directly affects the speed and quality of decision-making and hence the growth of the business as a result.
In GA4, displaying data has taken a significant step forward compared to UA. But simultaneously, the possibilities of both standard and custom reports are limited. This is where Looker Studio comes to the rescue with the ability to directly connect to GA4 data, and more robust data visualization and display capabilities, as in full-fledged paid BI tools.
If everything was so simple, then this article could end there. However, not long ago, Google introduced a limit on the number of requests to data using the GA4 API. Unfortunately, that is the only way Looker Studio received the data from GA4 to build the dashboards. Given that the remaining time of UA before it is shut down is only a few months away, building dashboards on data that will soon lose its usefulness is redundant. Therefore, there is only one reliable and easy-to-connect data source for dashboards: BigQuery.
Even if, one way or another, Google did not push us to use this source by introducing restrictions on using other data sources – BigQuery would still deserve attention. This article describes the main ways of using BigQuery as a data source for building dashboards using Looker Studio. We also share some optimization tips that will allow you not to spend the entire budget on one dashboard. Be aware, however, that BQ charges based on the volume of data stored and processed.
To begin with, to build a high-quality dashboard, we need data. GA4 can export all raw and unsampled data regarding user behavior on the website to BigQuery daily. Since we are considering the Looker Studio – BigQuery link here as a more advanced option and a replacement for the standard GA4 report, we will take the data for building the report from the standard GA4 export.
Once we have decided on the data, there are several ways to connect Looker Studio to BigQuery:
- The most accessible, least efficient, and economical way, in our case, is to connect directly to the tables with GA4 data in BigQuery. This data is collected in partition tables with a standardized structure and daily partitions. We can connect to a table for a specific day and all existing tables simultaneously or tables containing data for a pre-defined period. The downside of this method is that with such a connection, Looker Studio will query BQ for all the data collected over the specified period, regardless of whether we use them to build the dashboard. This increases the amount of processed data and, as a result, the bills for BigQuery because the data is requested each time users manually update the dashboard. This method is optimal for building quick dashboards on a small amount of data or the basis of pre-prepared tables.
- The second way is to use the built-in SQL editor in Looker Studio. This method is similar to the previous one. The only difference is that you can more flexibly customize the data used for plotting. But the amount of information processed will not be much less than in the case of a direct connection to the tables, since all the same, every time the dashboard is updated, a new request to BigQuery will be launched.
- The third way is to use the BigQuery functionality to create views containing only the necessary data and directly connect Looker Studio to them. The difference here is that the request is saved not on the Looker Studio side, but on the BigQuery side, with all the same questions about optimizing the volume of processed data with each dashboard update.
- The fourth way (the most optimal, in our opinion) is to create intermediate tables in BigQuery, followed by a direct connection with Looker Studio. In this case, you must first determine the required minimum data for building a dashboard, create intermediate tables containing only this data, and connect Looker Studio directly to them.
You can make these tables using SQL queries, with the difference that the queries will be executed with a predetermined regularity (BigQuery even has a native ‘scheduled query’ functionality for this) and does not depend on the frequency of updating dashboards. When updating the dashboard, the query will be sent to an intermediate table much smaller in volume than the original tables, which will positively affect the amount you are charged for using BQ.
The only disadvantage of this method is the difficulty of making changes to intermediate tables. If it becomes necessary to add additional fields at some point, then it will be required to overwrite all historical data in the table. However, this is quite rare.
- The last way is to create intermediate tables in BigQuery and connect them to the SQL editor built into Looker Studio. This method is often redundant since part of the data processing logic remains on the BigQuery side. Some are transferred to Looker Studio, which immediately complicates the search for errors in the code and control of changes. We recommend using this method cautiously and only if you need to make minimal edits/additional calculations to already prepared data tables. For this, it would be inappropriate to overwrite all historical data in the BigQuery intermediate table.
If your dashboards are built on initially small tables, if flexibility is essential, if the ability to quickly make data changes, and if the cost issue is not decisive, consider any of the first three methods described. In more complex setups, t is always optional to use the technique with intermediate tables.
In any case, at the moment, the BigQuery and Looker Studio bundle is the most optimal for those who want to get a complete picture of what is happening on the product (website, application), as it is ideal for building dashboards on exclusive and non-sampled data in terms of optimizing cost, completeness, and speed getting a result.
