Six ways to bring your GA4 data into Looker Studio

Since the new Google Analytics 4 API quotas are in place, Looker Studio reports using the native Google Analytics 4 connector are likely to break frequently. Fortunately, there are many alternatives available. Keep reading to discover how these alternatives are different, exploring six routes to create stable reports adapted to your reporting needs.

Do you prefer fast-loading reports over easier data exploration? How many GA4 properties do you manage, and how big are they? Do you want to up your game and jump onto the data warehouse train? Based on your reporting requirements, different techniques will be your best choice.

There was significant chatter on social media channels that 2023 will mark the end of free analytics data. There are still ways to get your GA4 data into Looker Studio without spending a dime, but you’re facing an ever-growing data volume, the multiplication of micro-services that you want to track, and require sophisticated levels of data blending. If on top of that you want to have historical data at your fingertips, 2023 may be the year when you want to review your current data pipeline.

Skip ahead >>

• API quotas are everywhere
• Should you do data manipulation in Looker Studio?
• Why you should still use the Supermetrics GA4 connector
• Building a data warehouse using spreadsheets
• Which setup fits your needs?

Want to learn more about GA4? Check our GA4 Knowledge Center for tutorial videos, templates, support articles, and more.

API quotas are everywhere

Before we review the different options to bring data into Looker Studio, let’s review how most marketers use Looker Studio today.

When you create a new data source in Looker Studio, you can choose from over 700 connectors. Most of these connectors communicate directly with the API of the service, and we never asked the question if this is the smartest way to do it.

When talking to our database experts, it became clear that there isn’t a single API of a SaaS provider that doesn’t enforce quotas. As Valery Khudoborodov, Engineering Manager at Supermetrics, puts it, “Storing a load of stuff is cheap, but moving is expensive and often complex.”

And when asked why data warehouses are so much more performant compared to APIs, Kurre Ståhlberg provided the following explanation:

“One thing to remember is that API resources are generally shared between all the API users and, thus, are provisioned for the average user. In contrast, a database, in general, is just for you and is usually over-provisioned. The API handles overuse by enforcing rate limits and quotas—the database handles overuse by crashing.”

In the end, it was probably more a question of ‘when’ and not ‘if’ Google Analytics will enforce a quota at one point.

Should you do data manipulation in Looker Studio?

If you log out from Google and access the Looker Studio homepage, you’ll see the following product description.

You’ve probably read these lines already a few times—there’s nothing new here. But focus on the missing keywords: data cleansing, data preparation, data manipulation, and data blending.

No mention of them, and yet, we’re using Looker Studio functions for data cleaning and preparation, and we were ecstatic when Looker Studio released more advanced data blending capabilities at the beginning of the year.

Himanshu Sharma calls it the “rookie mistakes that 99% of Looker Studio users make”. And he continues, “Looker Studio isn’t meant for data manipulation. It isn’t a spreadsheet or data warehouse.”

While I don’t completely agree with Himanshu, I picked two arguments from his manifest that are worth being discussed further.

1. When you manipulate data in Looker Studio, it slows the report down, especially when using large datasets.
2. Manipulating data in Looker Studio makes it unnecessarily hard to use.

From my experience, I can’t say that data manipulation slows down a report, but I may have never worked with large enough datasets. What slows down a report, though, is blending data from multiple sources. The reason, however, is less the blending than the fact that you have to wait for both APIs to load the data.

If manipulating data in a spreadsheet is easier than in Looker Studio might be a question of preference. I agree that it’s not always easy to get your data in the required format. 

But there are other reasons to talk about spreadsheets and data warehouses.

Spreadsheets and data warehouses

When you pull your data into a spreadsheet or a data warehouse first, you can have separate data manipulation from data visualization.

Google Sheets, for example, offers you a similar set of functions to Looker Studio. This means you can clean, manipulate, or even blend data before you import it to Looker Studio. In a data warehouse, you usually use SQL for these tasks, but even here, things are evolving, and you have more and more access to visual tools.

Speed

Speedy reports are the most obvious result when importing clean data into Looker Studio. Have you ever tried to blend two data sources from slow APIs? The result can be unusable. Pulling the data first into a spreadsheet already makes a huge difference.  

Using data warehouses, like BigQuery, will even be fast with huge datasets. BigQuery uses its BI Engine, a fast, in-memory analysis service that accelerates many SQL queries by intelligently caching the data you use most frequently.

Historical data

If you want to make Year-over-Year (YoY) comparisons, you need at least 2 years’ worth of data. And if the previous year had a pandemic, you would want to go back even further to evaluate your progress properly. Historical data is crucial for benchmarking previous performances. 

Many APIs have promised lifetime access to your data. But the rules changed, and while you still have access for 37 months to your Facebook Ads data, data retention for GA4 is only 14 months.

The more effort you put into your reporting, and the more people are counting on your reports, the more reasons you have to move towards a data warehouse. Protect your assets by taking ownership.

Why you should still use the Supermetrics GA4 connector

Fast reports, even for massive datasets, and owning the data are good arguments for switching to a data warehouse. But they might not apply to all use cases. There are still plenty of reasons to keep using Looker Studio connectors.

Using connectors is still, without any doubt, the quickest way to get access to your data. Within minutes you have the numbers in your report that you can share with the world. 

Having immediate access to all dimensions and metrics in your report helps you discover datasets faster and let you explore metrics that you may have otherwise overseen. Use spreadsheets or data warehouses when you know exactly what metrics and dimensions you want to report on. Until then, use connectors if your data volume is low enough.

But you must use quality connectors that respect API quotas by limiting simultaneous requests and caching to maximize your available resources.

At Supermetrics, we have been closely monitoring API quota errors over the past few weeks. While we’re still trying to improve our GA4 connector, we can share that 92% of our customers have never faced any quota errors, and 94% only see errors in 5% of their queries. 

If you have experienced issues with the native connector and think that your reporting needs are within the 95th tile of the industry, you may want to test the Supermetrics connector.

Spreadsheets vs. data warehouses

Both spreadsheets and data warehouses help you separate data preparation and visualization. Otherwise, these two approaches have little in common.

The low-tech solutions

Google Sheets and the Extract Data connector are valid options, but they lack sufficient storage space. The Extract Data connector has a limit of 100MB, and each tab of a Google spreadsheet is limited to 10 million cells.

For a site with 1000 monthly visits, a YoY comparison with very granular data—9 dimensions and 14 metrics—would not be possible with Extract Data or using Google Sheets.

Data warehouse solutions

If your requirements outgrow spreadsheets, you should consider using a data warehouse. You’ll have almost unlimited storage for a very reasonable price and be able to backfill your data using BigQuery transfer.

There is no other solution to deliver faster reports, but things get slightly more complex. 

If you’re using the free feature from GA4 to export data to BigQuery, you’ll be confronted with a new way of data storage. BigQuery will store the data in a nested format, and before using the data in Looker Studio, you must create flat tables. On the other hand, this requires SQL knowledge and a lot of planning.

The Supermetrics warehouse connector makes things much easier. You can use the standard schema from Supermetrics to access your GA4 data without any preparation. You can also create your custom schema without writing a single line of code—everything is handled with a graphical UI.

To clean your data, even less coding is required than in Looker Studio. On the Supermetrics Teamsite, customers can create custom dimensions and metrics using conditions, functions, and lookups.

“Do I write SQL? Yes. Do I want to write SQL? No, I’m a marketer, and this is what I’m best at.”

JJ Reynolds, Head of Marketing + Analytics, Mediauthentic

There is certainly a learning process, but once you have set up your warehouse for your reporting, you probably don’t want to go back anymore. A good starting point to learn about data warehouses is the webinar “Everything you need to know to build your marketing data warehouse” by Anna Shutko and Evan Kaeding.

Building a data warehouse using spreadsheets

I was discussing the topic with Mehdi Oudjida, one of the best-known Looker Studio experts that frequently surprises the community with clever workarounds.

“It’s possible to pull GA4 data daily into a spreadsheet using the Supermetrics connector. Link the Google Sheet to BigQuery, schedule a query to append the data to your destination table, and get a data warehouse at a reduced cost.”

Mehdi Oudjida, Digital Analytics Expert

This method requires a bit of SQL knowledge, and you’ll have to put some safeguards in place to ensure the integrity of your data in case one of the steps in your chain fail. Setting up such a pipeline isn’t rocket science. It’s a low-risk approach to experience the advantages of a data warehouse.

Which setup fits your needs?

We’ve seen many options to bring our marketing data into Looker Studio. Which setup fits your needs? To make your decision process a bit easier, we’ve created a decision tree that you can use to ask the right questions.

Make GA4 quota limits a story of the past

If you had issues with the new GA4 quota limits, you might need to revise how you bring your marketing data into Looker Studio. There are many options, from more stable connectors, over spreadsheets to data warehouses. Hopefully, this article helps you to get a clear picture of the different possibilities and make the right choice. And if you’re still unsure where to begin, you can book a demo with our team we’re always happy to help.

About the author

Ralph, Head of Data Visualization at Supermetrics, works at implementing the first commercial Looker Studio chart library—a collection of data visualizations that allow you to push the limits of Looker Studio.