In Looker Studio and other reporting tools, it is easy to make calculations like totals and averages.
There are a few common mistakes made when calculating with percentages (like impression share or click through rate) or other derived metrics like cost per click, or cost per conversion. This support page will highlight how you should handle derived metrics the right way (or avoid handling them at all)
Calculating an average cost per click over a date range
If your data source contains the costs, the clicks and the "cost per click" per day, you might be tempted to use the "cost per click" metric in your (Looker Studio) report. But instead you should create a calculated metric inside Looker Studio (based on the total costs divided by the total clicks). You might think you will get the same results, right? Well, in some cases you will, but in some you don't.
You will get the same results if you only look at the cost per click per day, and do not have any filters in your report. In all other cases, you will see deviations. Here is why:
Suppose you have these numbers in your data source
If you use the CPC from the data source to calculate the average cost per click for the two days, you get a CPC of 6 euro, calculated as (2+10)/2.
But in fact, your average CPC of those 2 days is 4,66 euro. That's because the total costs are 1.400 (400+1000) and the total clicks are 300 (200+100), so the average CPC of those 2 days together is 1.400 / 300 = 4,66 euro.
How to calculate a derived metric in Looker Studio?
In Looker Studio, you should calculate a derived metric (in our example the cost per click) like this.
The Cervinodata connectors have calculated metrics already built in so you do not have to do this. Those calculated metrics are shown in blue inside your data source in Looker Studio. For ease of use, the formula that is used is shown in the description.
What about "impression share" and other special metrics?
Ad platforms have the tendency to create their own special metrics that you cannot calculate yourself. Like impression share or Quality score (or Reach for that matter). For those metrics you should be careful when using them to draw conclusions. In particular in these situations:
- When saying something about a certain period of time (for instance last month)
- When saying something about a group of campaigns or a total of an ad account
Keep this in mind: If you can not calculate the metric yourself, ONLY use the metric directly from the data source, without combining records / grouping results.
So it would be ok to use the CPC metric in the example in the table above if you only use it in a graph that shows day 1 and day 2 on the x-axis and the CPC in a trend line.
Or, if you have a data source that contains the reach per day per campaign, you can create a graph that shows the day on the x-axis (so not months or weeks) and a trend line of this reach for each campaign (but not for all campaigns combined).