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

Published: 22 September 2023

Updated: 25 September 2023

Author Image
Max is an analytics expert @VIDEN, specializing in Looker Studio, Google BigQuery, Google Tag Manager, Google Analytics 4 and more, with years of experience in analytics strategy, identifying new trends, challenges, and opportunities for brands across digital.
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:

Looker Studio low speed

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?
  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

ApproachProsCons
Native GA4 connector for Looker Studio
  • Easy to connect
  • Free
  • Pre-built metrics
Third-party GA4 connector for Looker Studio
  • Easy to connect
  • There are some additional metrics and dimensions that are not available for the native GA4 connector (optional, depends on the connector)
  • All data API limitations applied to native GA4 connector
  • Paid
GA4 export to BigQuery + native BigQuery connector for Looker Studio
  • Fast (could be improved by BI Engine)
  • Reliable
  • Data ownership
  • Data transformations without native connector limitations (in BigQuery as well as in Looker Studio)
  • Realtime reporting
  • Further opportunities to use exported GA4 data: data quality, machine learning, advanced analytics requests
  • Time-consuming
  • Basic familiarity with Google Cloud Platform and SQL is needed
  • Marketing attribution at the session level should be recreated
  • Paid (BigQuery has free quotas for each month)

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

Comparison table - GA4 and Looker Studio connection options

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.
  • BigQuery cost

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:

Before switching from custom query connection

After switching from custom query connection

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
    • On-demand scheduled query

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

    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.
    Run transfer now

    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)
    • Schedule options

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

    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.

    Run transfer now

    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
  6. Connect table to Looker Studio

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.

Get in touch

Got a question? We'd love to hear from you. Send us a message and we'll respond as soon as possible.



    By clicking submit, you agree to our Privacy Policy

    Latest Insights

    Get the tips from our experts to optimize and scale your campaigns

    30 November 2023

    Google Analytics 4 Data Retention – a Few Simple Steps to Maximize

    Learn how to manage data storage and protect privacy with GA4 data retention settings.

    Learn more

    24 November 2023

    How to Read and Interpret Google Analytics 4 Reports to Drive Business Success

    Get to know how to read Google Analytics 4 reports to uncover customer insights, optimize marketing strategies, and drive business growth.

    Learn more

    13 November 2023

    How Much Do Google Ads Cost in 2023?

    In 2023, Google Ads costs are expected to rise by 20%-30%, up from a 5%-19% increase in 2022. This article breaks down Google Ads pricing, helping you budget effectively and understand key cost factors.

    Learn more

    11 November 2023

    How to Preserve Google Analytics’ Historical Data?

    Fearing to lose your Google Analytics’ historical data? Don't worry! This guide will show you four ways to save data during the UA-to-GA4 migration.

    Learn more

    9 November 2023

    The Ultimate GA4 Handbook: Your Complete Guide to Understanding GA4

    Discover the ultimate guide to Google Analytics 4. Seamlessly transition from Universal Analytics to GA4 and unlock its full potential. Download the free handbook now!

    Learn more

    5 November 2023

    How to Create a Viable First-Party Data Strategy 2023?

    Are you ready to leverage first-party data in your marketing strategy? Find out how with this guide, and get tips on creating a successful first-party data strategy for 2023.

    Learn more

    4 November 2023

    How Marketers Can Use GA4 to Improve Campaign Performance

    Study our guide to GA4 for marketers and learn how to track your marketing performance, measure your ROI, and make better decisions.

    Learn more

    3 November 2023

    Healthcare Paid Search: Is It Worth the Investment?

    Use the power of paid search to boost your medical practice's online presence. Explore 6 tips for using adwords in healthcare for remarkable results!

    Learn more