Ok so let’s talk data aggregation – it’s important to everyone right? It’s there bobbing away like the blood-painted face of Wilson from Castaway, riding the waves and always in your face looking at you when you’ve messed up.
And if you use Google Looker Studio we often take these things for granted.
Take this scorecard for example:
Yeah it’s very simple. But it’s actually showing the average of all of the days from 1st July – 25th August. This:
AND this continued:
Which gives you this:
So…
You use the same method you applied to your scorecard and show this as a line chart:
AND eyeballing this we can see a rough daily average of at least 1m 13 seconds right?
“OK right yes, well done CLAP CLAP…
…But what’s you’re point?”
If you change the standard date dimension:
AND ACTUALLY…
You want to change this to see this as Date (ISO Year Week):
you’ll end up with something that looks a bit like this:
“OK to the graph didn’t break, it looks normal what wrong with it?”
Look again at the daily graph:
VS the ISO Year Week Graph:
The average was around 1m 13 seconds. But now looking at the ISO Year Week graph we’re seeing an average of well above 9 minutes.
This is to do with how the aggregation of the metric is calculated and applying the different date range comparison on an average value does not return the right result.
“So what’s the solution?”
There are a few method online that suggest things like blending data together etc etc.
AND that might work perfectly well for you…Which is totally fine.
BUT I found a different approach…Which might be less complex and quicker…
The best way to view this is to look at a table view to see the raw numbers clearer:
THEN
1. Create a new calculated field on your graph:
2. In the formula setting copy and paste this:
Average session duration/COUNT(Day)
What this is doing is simply taking the total average it’s giving you and dividing it by the count of days.
3. Call this what you want and make sure to change the Data Type to Duration.
Now let’s look at the table again with the original metric and the recalculated metric side by side:
Looks a bit more like what we would expect right?
Now let’s look at all 3 graphs on top of each other…
Standard Date Graph:
Default ISO Year Week Graph:
Workaround Metric Using ISO Year Week Date Value:
The average is the same as the Standard Date Graph.
AND if you want further proof, lets look at Date (Year Month) Average Session Duration VS the Recalculated Metric using Date (Year Month) alongside the Standard Date graph:
Standard Date Graph:
Date (Year Month) Average Session Duration:
Recalculated Metric using Date (Year Month):
Ok for those of you that are hawk-eyed, YES in the above example it IS missing 6 milliseconds from the monthly graph average.
AND I do love to be precise… But I can’t think of any scenario where 6 milliseconds actually make any decisive difference. So I’m happy with that.
But if you are really bothered about this level of granularity then it might be best to look at Big Query for using a more refined approach. The solution above is just designed to give a quick workaround with Google Looker Studio and the native GA4 connector.
Final Words…
If you’ve got this far, depending on your time zone and beverage preference, it’s probably time to go make a cuppa, a beer or pour yourself a large fruitified juice. And as always thank you reading! Please feel free to share your thoughts and experiences with this below.
One final point, if you want to find out more about data aggregation and how it works and some nuances with the native GA4 integration, you should watch this great video below by the legend that is Siavash Kanani.