Re-aggregating Auto Aggregate in Google Looker Studio
Loading...
https://www.jellyfish.com/jfcmsnull
InformInform

Re-aggregating Auto Aggregate in Google Looker Studio

Yinyee Wong, Senior Business Intelligence Manager at Jellyfish, looks at blending data sets in Google Looker Studio as a way around default auto aggregation

Updated December 2023

Hi everyone!

I had to solve a very interesting tricky question recently and thought it would be fun to share what I’ve learnt. Have you ever connected Universal Analytics, or Google Analytics 4 data to Looker Studio only to find that:

  • One of your goals or conversion events is having collection issues, which allows it to record more than once and leads you to think;
  • “Could I fix this in Looker Studio while the issue is addressed during collection point?”

And – bonus – have you come across the following warning while building your dashboard in Data/Looker Studio?

‘re-aggregating metrics is not supported!’

If so, this post is for you!

The tricky question:

“Some clients are submitting multiple times, and this should not be possible. Is there a way to force any metric above 1 to be 1, in Looker Studio? This needs to be aggregated correctly too.”

tricky-question.jpg

The solution:

Writing a CASE statement!

This CASE statement forces a specified metric that is greater than 1 to be 1.

goal-case-statement.jpg

Pull in your newly created custom field into your table, and this is what you’ll get. Seems to fix the issue, right? Well, not quite.

table-with-custom-field.jpg

This works fine if you don’t need to aggregate your data. However, it won’t aggregate correctly if you choose to show the summary row.

“You told me to force any metric above 1 to be 1, didn’t you!” – Looker Studio.

grand-total.jpg

Yes we did!

Ideally, the default aggregation here would be ‘SUM' instead of ‘AUTO'.

default-auto.jpg

The next step here is to blend your data source to work around the auto aggregate.

In Google Analytics, metrics data is aggregated automatically upon collection and processing. If you hit ‘edit data source’ and scroll to where the metrics are, usually you will see blue fields (metrics) and the default aggregation is set to Auto.

There’s little we can do about default aggregation when using Google Analytics data. It’s the three things certain in life, right? Death, taxes, and auto aggregation in Google Analytics data.

auto-aggregation-GA-data.jpg

But! Doing a blended data source turns Google Analytics data into lawless land, where regular rules do not apply.

blend-data.jpg

Set up your first data source; whether to rename or pull in necessary filters. Ensure the join keys for both data sources are the same. It’s not necessary to pull in metrics for the second data source because we’re only focusing on these metrics.

data-sources.jpg

This gives us the freedom to set aggregation to SUM on chart level, which is what we need. Voila! Now the Goal Case Statement is aggregating correctly.

aggregate-sum.jpg

 
If not SUM, then other types of aggregation:

other-aggregation.jpg

 
As for this warning saying ‘Re-aggregating metrics is not supported’, I hope this blog post has given you more insight and context as to why this happens on chart or data source level. The 'AUT' before Goal Completions hints that you cannot re-aggregate metrics.

 
re-aggregating-metrics-not-suported.jpg

Other data sources like BigQuery or Google Sheets don’t have the default aggregation applied. Fields are imported as dimensions (green) and are flexible to be used as either dimensions or metrics. You might want to consider using BigQuery if you have a lot of reports that rely on this fix.

Blending data sets isn’t a perfect solution, but it’s nice to know that there’s a way around the default auto aggregation. Or as I like to call it, Re-aggregate Aggregate Aggravation!

 

If you're new to Looker Studio, or want to up your reporting game, check out our Looker Studio course.

This post first appeared on the Data Runs Deep blog. Data Runs Deep joined the Jellyfish global family in 2020 and the knowledge-sharing continues under the Jellyfish banner in Australia and around the world.

About the author

Yinyee Wong

Senior Business Intelligence Manager
Connect:
Loading...