VIDEN Blog Area Analytics

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

Published: 22 September 2023

Updated: 29 January 2024

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 that 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
    • Location type: Specify a location that preferably matches the region where GA4 exported data is stored

    On-demand scheduled query

    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. This query creates reporting tables, which will be upgraded by an update query configured in the next step.
    Run transfer now

  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

    Schedule options

    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

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

    25 April 2024

    Benefits of Choosing a Google Premier Partner for Your Ad Strategy

    Google introduces new features and updates on a regular basis, which can be tough to track. The smartest way to keep up is to have a partner committed to staying in the loop. When searching for a Google Partner to handle your business needs, we recommend one with Premier status. That way, you’re guaranteed top-tier […]

    Learn more

    19 April 2024

    PPC Management Challenges for SMBs

    Navigating PPC advertising can present many complexities for small and medium-sized businesses. The companies face different challenges when it comes to effectively managing PPC campaigns. In this article, we’ll go through the difficulties that SMBs encounter in PPC management and share tips on how to overcome them, ensuring optimal performance and ROI in a competitive […]

    Learn more

    28 March 2024

    Navigating the Data Deluge: Prioritizing Key Metrics in the Age of Google Analytics 4

    With the advent of Google Analytics 4 (GA4), the key business challenge becomes not collecting data but discerning what truly matters in the sea of data. 64% of marketing executives believe data-driven marketing is crucial to success in a hyper-competitive global economy. By prioritizing key metrics and avoiding the trap of tracking everything, businesses can […]

    Learn more

    10 January 2024

    How Much Do Google Ads Cost in 2024?

    Discover the true cost of Google Ads for your business. Learn how to budget and determine if it's worth it. Average costs per click and more.

    Learn more

    3 January 2024

    How to Create a Viable First-Party Data Strategy in 2024?

    Discover the 9 key steps to build a first-party data strategy that maximizes customer data and enhances your marketing efforts.

    Learn more

    2 January 2024

    How to Boost Your Pet Shop Sales with Effective Marketing Strategies

    Discover how to succeed in the pet industry by leveraging digital marketing. Boost your online presence and sales with these pet eCommerce marketing tips and strategies.

    Learn more

    1 January 2024

    Ramadan Advertising Guide: Crafting Effective Ramadan Ads to Reach Muslim Consumers

    Ramadan spending booms to $2.1 TRILLION! Discover innovative advertising ideas, culturally relevant strategies, and tips for creating promotions that connect with Muslims.

    Learn more

    15 December 2023

    Our Black Friday Marketing Lessons and Strategies for 2024

    How we ran Meta & Google Ads campaigns to turn $1M into Black Friday sales gold. Best Black Friday marketing strategies for eCommerce businesses.

    Learn more