Re-aggregating Auto Aggregate in Google Data Studio
Read Time: 7 Minutes

Re-aggregating Auto Aggregate in Google Data Studio

16 Nov, 2021 Read Time: 7 Minutes
Published by
Yinyee Wong
Senior Business Intelligence Manager
Inspire Inform Ignite

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 Google Analytics data to Data Studio only to find that one of your goals is having collection issues, which allows it to record more than once?

If so, you may have wondered “could I fix this in Data Studio while the issue is addressed during collection point?” And, have you also come across this while building your dashboard?

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 Data Studio? This needs to aggregate correctly too.”

It is possible to force data to be something else, and this is easily done with dimensions using a case statement. One thing to keep in mind, forcing metrics is a bit more tricky because of how data aggregates in Google Analytics than in Data Studio.

The solution:

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 will 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!” - Data 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 do not have the default aggregation applied. Fields are imported as dimensions (green) and are flexible to be used as either dimensions or metrics. You may want to consider using BigQuery if you do have many reports that rely on this fix.

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

Share this article
Further learning
Blog Posts
24 Dec, 2021
In the many years we have been setting up tracking for our clients, the standout and one of the most requested tasks has to be form submits or, as we like to...
Read More
Blog Posts
01 Dec, 2021
If you have worked with a website that uses iframes then it's likely you have felt the tracking pain points that arise. Generally, you will encounter one or ...
Read More
Blog Posts
25 Nov, 2021
If there’s been one good thing to come out of the last couple of years of Covid, it’s that the restrictions imposed on all our lives have been a boon for...
Read More
Blog Posts
25 Nov, 2021
Lately, I’ve found myself using a number of window functions in BigQuery. They are really handy functions but can be difficult to wrap your head around. In...
Read More
We have trained thousands of people all over the world, helping them learn more and achieve more.