How to Set up GA4 Reporting in Looker Studio Using BigQuery Export

Since the Universal Analytics (GA UA) deprecation in July 2023, GA4 has become the go-to analytics tool for many businesses. And if you are a GA 360 user, mark your calendar for July 2024, as GA4 will be your main analytics tool starting then. But let’s address the elephant in the room: you have probably noticed that Looker Studio can be painfully slow, especially when handling the large data sets typically pulled from GA UA. You are not alone; we know how frustrating it can be to watch a spinning wheel instead of your much-needed insights. According to a recent survey, 65% of businesses have experienced delays in data analysis due to slow-loading platforms, affecting timely decision-making. Just look at the lag yourself:

In this article, we will guide you step-by-step on how to set up Looker Studio reporting to work seamlessly — and quickly — with GA4 data through BigQuery export. Say goodbye to endless loading screens and hello to fast, efficient analytics.

Table of Contents:

1. Why Build Reporting on BigQuery Data?
   • Exploring the Pros and Cons of the BigQuery Approach
   • Two Ways to Connect BigQuery Data to Looker Studio
2. How to Build Reporting on GA4 Data Exported to BigQuery
3. Summing up

Why Build Reporting on BigQuery Data?

Deciding how to link Google Analytics 4 (GA4) to Looker Studio can be challenging. Let’s simplify it by looking at three common approaches. Each method offers unique benefits and drawbacks influencing data granularity, setup ease, and performance speed.

Three ways to connect GA4 to Looker Studio

Below is a sample table comparing the three approaches across five key criteria:

Now, let’s take a look at the advantages and disadvantages of the BigQuery approach.

Exploring the Pros and Cons of the BigQuery Approach

Pros:

• Reliability

  GA4 limitations and quotas are not imposed on BigQuery raw event data, ensuring a more consistent and dependable data source.

• Data ownership

  With BigQuery, you can store data as long as you want; just make sure billing is enabled.

• Data transformations

  Thanks to SQL support, BigQuery allows you to transform data in any way you can imagine, offering a level of customization that is hard to beat.

• Realtime reporting of almost all exported data

Note: Limited realtime data could be shown in GA4 Web UI.

• Further wide opportunities for advanced analytics use cases, machine learning, data quality, A/B tests and experiments, data blending, and other applications inside GCP.

Cons:

• Time-consuming as data must be retrieved and stored
• Marketing attribution at the session level must be recreated by yourself since the export schema does not contain such fields
• Basic familiarity with GCP and SQL is needed to retrieve and store data used for the reporting
• This is a paid solution. However, every month, Google provides free resources, and the costs are pretty low for most businesses. Of course, it depends on business size, but, for example, for a company with 100K daily events, it costs only $1 per month.

Two Ways to Connect BigQuery Data to Looker Studio

When integrating BigQuery with Looker Studio, the connection options you choose can significantly impact both your workflow and budget. To optimize your analytics, you have two main pathways to explore:

1. Custom query gives access to BigQuery GA4 data directly without setting up intermediate tables. However, it is cost-inefficient since computationally heavy queries should be executed with every report page load or action.
2. Connecting to a precalculated table is a much more efficient way to use BigQuery data for reporting in Looker Studio, and it could be cheaper up to 10 times compared to a custom query connection.

Just look at this example: monthly billings before switching from custom query connection to table connection and after:

How to Build Reporting on GA4 Data Exported to BigQuery

Now, let’s see what key steps are required for building reports based on GA4 data in BigQuery.

1. Link GA4 property to BigQuery. Here is a detailed instruction created by Google and a schema of exported data. In addition to event data, user data is also available for export.
2. After successful linking, check the exported data. It is essential to explore data before building a report because it may turn out that important reporting fields are not pulled, missing, incorrectly tracked, or have different incompatible formats, etc. Here are some examples:
   • Duplicated transaction IDs
   • Missing ID fields (transaction ID, session ID, client ID, user ID)
   • Client IDs have different formats
   • An eCommerce event does not have all eCommerce fields
3. Configure on-demand scheduled query for creating reporting tables with the following settings:
   • Query code can be found on GitHub
   • Schedule options — on-demand

   

   • Location type: Specify a location that preferably matches the region where GA4 exported data is stored

   

   After creating the initialization query, move to the scheduled query page and select the query that you created. Then click on RUN TRANSFER NOW and select Run one-time transfer.
   

   This query creates reporting tables, which will be upgraded by an update query configured in the next step.

4. Configure a scheduled query for daily updating reporting tables with the following settings:
   • Query code can be found on GitHub
   • Schedule options: Repeat frequency (days, at 13:00 or later local time converted to UTC)

   

   • Location type: Specify a location that preferably matches the region where GA4 exported data is stored

   

   After creating the update query, move to the scheduled query page and select the query that you created. Then click on Run transfer now and select Run one time transfer.

   

   A manual run of the update query allows you to check if everything is pulled correctly and debug your query if needed. After that, this update query will run daily, so no manual intervention is needed.

   Please note that both initialization and update scripts are sample scripts; before using them, you must adapt them to your data and needs.

5. Connect the table to the Looker Studio as a data source

Summing up

Connecting GA4 data with BigQuery provides businesses with flexibility and scalability for their data analytics needs. BigQuery’s robust features enable more than just enhanced reporting and visualization; they offer a comprehensive solution for data management. You can clean data for accuracy, employ predictive models for future-facing strategies, and even consolidate multiple data sources like advertising platforms and CRM systems for a unified business view. Additionally, BigQuery’s architecture allows for secure data ownership and unlimited retention, aligning with long-term business goals. Should you require assistance in optimizing this powerful toolset, do not hesitate to contact us for expert support tailored to your business needs.