Re-aggregating Auto Aggregate in Google Data Studio

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!