How to calculate median, percentile, average with third-party connectors in Looker Studio
Average, median and percentile are not working for you in Looker Studio? No worries, we’ve got solution for you so they’ll work like a…
Working with third-party connectors can sometimes be difficult. Indeed, data is not aggregated yet, it’s raw data available at date X campaign X ad X device X … level. This is very convenient when you want to run deep analysis. A bit less when you want to calculate meaningful metrics such as median, percentile and such. Therefore, in this article, we’ll show you how to properly calculate median, percentile, average and more with third party connectors in Looker Studio.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
First, let’s understand the issue
As mentionned in the introduction, third-party connectors tend to provide raw data that is then aggregated in Looker Studio. Thing is, it makes some calculations impossible. Here’s a quick example. We’ve ran 22 ads for a Total Spend of 35k€. If you use the Spend metric and use the Average function, it won’t work. There is a workaround, by creating the following formula:
SUM(Cost)
/
COUNT_DISTINCT(Ad)Does work here (thanks Lord), but what about the Median, or Percentile? In Looker Studio mind, there is only one Ad that is summed, and hence it won’t be able to properly calculate Median nor Percentiles as seen below, always returns total. But good news, we’ve got a workaround for you:
Create a fake blended data to re-aggregate by Ads
Here, we’ll create a blended data to re-aggregate by Ads. So now, in our new data source, one line = one Ad. This way, Looker Studio will understand when we ask it to calculate median and percentiles.
To do so, add a new Blended data; first, add your data source with the level you’re interested in as a dimension (in our case, we want data at Ad level). Then, Cross join to the same data source, but only put a dummy metric, NO DIMENSION. Here we pust Costs, but you could just put 1, we won’t use it. Also, don’t add date in dimensions, stick to the Auto Date Range:
And that’s it! In your KPIs, replace with your newly created Blended data source, display your metric and choose Average or Median as aggregate: it works!
As per the percentile, just use the function, and it will work smoothly.
PERCENTILE(Cost,25)In this quick article, we’ve reviewed how to properly calculate median, percentile, average and more with third-party connectors in Looker Studio.
PROBLEM SOLVED
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):