ROAS in Google Sheets
Return on ad spend (ROAS) tells you how much revenue your ads generated for every pound spent on them. The formula is simple. Most of the mistakes happen in what goes into it, and in how it's totalled.
The formula
ROAS = revenue from ads ÷ ad spend
In Google Sheets, with spend in column B and revenue in column C:
=IF(B2=0, "", C2/B2)
The IF stops a campaign with no spend returning a #DIV/0! error. Format the column as a number (4.5) or a percentage (450%); they're the same figure.
A worked example
| Campaign | Cost | Conversion value | ROAS |
|---|---|---|---|
| Brand | 120.00 | 540.00 | 4.50 |
| Shopping | 800.00 | 2,000.00 | 2.50 |
| Prospecting | 300.00 | 150.00 | 0.50 |
| All campaigns | 1,220.00 | 2,690.00 | 2.20 |
The total row is:
=SUM(C2:C4)/SUM(B2:B4)
Never average ROAS
=AVERAGE(D2:D4) returns 2.50. The correct figure is 2.20.
An average of ratios gives every campaign equal weight, so the £120 campaign counts as much as the £800 one. Depending on which campaigns do well, that can make your return look better or worse than it really is. Divide total revenue by total spend instead.
Why platform ROAS won't match your store
Your ad platform and your store are measuring different things, so the numbers were never going to line up.
Each platform attributes sales to its own ads, using its own rules and time window. A customer who saw ads on two platforms before buying can be counted by both.
The value can differ as well. Depending on how tracking is set up, a platform's conversion value may include tax, shipping or discounts that your store's revenue doesn't, or the reverse. And some conversions are modelled rather than observed.
Platform ROAS is useful for comparing campaigns within one platform. To judge the business as a whole, compare your store's revenue with your total ad spend. That's blended ROAS, covered in its own guide.
Keeping the inputs current
Rather than copying spend and revenue out of Google Ads each week, a scheduled Fresh Sheets report can keep Cost and Conversion value up to date in its own tab, for example one called Google Ads.
Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading, so it still works if you add a field and the columns move:
=SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Conversion value", 'Google Ads'!1:1, 0))) / SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0)))