InformUpdated 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!’
This means that you cannot apply an aggregation function (like SUM, AVG, COUNT) to data that is already aggregated in a data source, as this would essentially be trying to sum up a sum, which is not allowed in Looker Studio.
In this post we'll look at some scenarios in which that might occur!
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.”
The solution:
Writing a CASE statement!
This CASE statement forces a specified metric that is greater than 1 to be 1.
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.
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.
Yes we did!
Ideally, the default aggregation here would be ‘SUM' instead of ‘AUTO'.
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.
But! Doing a blended data source turns Google Analytics data into lawless land, where regular rules do not apply.
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.
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.
If not SUM, then other types of aggregation:
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.
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.
Yinyee Wong
