Correct ROAS calculation in Google Sheets pivot tables

Do you know that you can (and should!) write custom formulas inside pivot tables in Google Sheets? Calculated fields is, actually, one of my favorite topics, I’ve written a couple of articles about it. By this link you can read “How to calculate correct CTR, CPC and ROAS in Google Sheets and Looker Studio”. Besides, how to calculate metrics in pivot tables? 

Keep reading this article – you will know everything here! ROAS in our case is just an example, because using the same method you can calculate any metric in your pivot table. Let’s take a look at what we have!

If you prefer video format, check out a YouTube tutorial here:

Setting up the pivot table

Basically, we have a tab with basic data like Date, Channel, Sales, Revenue. Having selected all the columns, we simply click Insert – Pivot table. This action creates a pivot table in a new tab by default. Here we can set up the values, rows and columns. So, we select a channel as a row, sales and revenue as values in columns.

To calculate ROAS, you can select a free column, write a formula Revenue/Spend that is in our case will be C2/B2 and apply this formula to all the rows. However, if there are any changes in the pivot table, the calculation of ROAS will be inaccurate. Consequently, it applies to all calculated fields. 

ROAS Calculation

We add a new value as a column – Calculated Field and let’s name it ROAS. Here we have space to write a formula. To avoid all the errors in the table, I write ROAS formula adding iferror, so now the formula looks like this:

=iferror(Revenue/Sales)

If in our initial table we had a slightly different name for a sales column, for instance “Daily Sales”, in the ROAS formula it has to be changed to Revenue/”Daily Sales”.

This way you can calculate different formulas in Google Sheets pivot tables. 

If you want to know more about Looker Studio and start your life changing freelancing data visualization career, you are more than welcome to my Gaille Reports course. Read more about it here. 

Hope you liked this article! Share your impressions in the comments below!

Check out my Medium for more articles.