How to calculate ROAS, CTR, CPC correctly in Google Sheets and Looker Studio

If you have ever worked with Looker Studio, you may know that when you connect Google Sheets that contain calculated fields, the values in Looker Studio may be inaccurate. Today I would like to tell you how to calculate ROAS, CTR and CPC correctly and avoid this problem in your report.

I have recorded a video tutorial, check it out:

Let’s start with Google Sheets. How to calculate ROAS, CTR and CPC

I have a Google Sheets table that contains Campaign name, Impressions, Clicks, Cost, Conversions and Revenue.

We can count CPC, CTR and ROAS by formulas:

CPC = Cost / Clicks
CTR = Clicks / Impressions
ROAS = Revenue / Cost

We apply this formula to all the rows and let’s find the total CPC, CTR, ROAS. There are two ways to do it. First one is to find the average of all the rows. The formulas are:

CTR = Average (I:I)
CPC = Average (J:J)
ROAS = Average (K:K)

It may be a logical way but it is not an accurate one. The second one and the only correct way to calculate total CPC, CTR, ROAS or any other calculated field like conversion rate etc. is to use summaries, so that our formulas look like:

CPC = sum(F:F) / sum(E:E)
CTR = sum(E:E) / sum(D:D)
ROAS = sum(H:H) / sum(F:F)

On the image you can see how different the results of the first and the second way of calculating are.

Moving to Looker Studio

Let’s connect our Google Sheets to the report. I do not need the whole table, just certain columns, so I select A:K.

Let’s create a simple table that contains Date, Ads Platform, Campaign Name. As metrics I select Impressions, Clicks and CTR.

Looker Studio has counted the numbers instead of us, using a formula that we actually used to count CTR. If we check the accuracy of it, it’s not correct, it doesn’t coincide with our results in the Sheets. How can we fix it? 
Let’s edit the formula and make it look like this:

Here is the table with two columns “Wrong CTR” and “Correct CTR” so that you can see the difference.

To sum up, my point about it: if you have some calculated metrics like CTR, CPC, conversion rate, cost per something, when you divide something by something, please create calculated fields in Looker Studio directly and in formulas please use some aggregation of actions like average, summary etc. There is only one correct way to calculate the correct CTR.

Hopefully, this report was educational and useful for you! Please, tell in the comments section if you have ever faced incorrectly calculated fields in Looker Studio!

You can find my other articles in my blog!