Formulas

Marketing ROI formula: how to calculate marketing ROI

Marketing ROI is the return on marketing spend after the spend itself has been taken off.

About the Shopify connectorAbout the Google Ads connectorAbout the Meta Ads connector

ROAS tells you how much came back per pound; ROI tells you how much you made. The two use the same inputs and are often confused, so both appear below on the same numbers. The mistake is in what "return" means.

The formula

Marketing ROI = (revenue from marketing − marketing cost) ÷ marketing cost

It is expressed as a percentage. ROAS, for comparison, is revenue ÷ cost, so ROI is always ROAS minus one: a ROAS of 2.50 is an ROI of 150%.

In Google Sheets, with cost in column B and revenue in column C:

=IF(B2=0, "", (C2-B2)/B2)

Format as a percentage. A negative result means the campaign lost money on the revenue figure, before anything else is counted.

A worked example

The total row uses totals, not averages: =(C5-B5)/B5, which is 4,700 ÷ 4,000 = 117.5% (the ROAS is 2.175 before rounding). Prospecting has a ROAS below 1 and an ROI below zero; they are the same fact written two ways.

The common mistake

Treating revenue as the return.

Revenue is not what you keep. If the gross margin on those sales is 40%, the £8,700.00 of revenue is £3,480.00 of gross profit, and the ROI on that is (3,480 − 4,000) ÷ 4,000 = −13.0%. The revenue version of the same quarter says 117.5%. One says marketing is paying for itself twice over; the other says it is losing money. Only the second is about money you have. Report ROI on gross profit, or label the revenue version clearly so nobody mistakes it. Gross margin and contribution margin cover the inputs.

Whichever version you use, keep the cost side complete. Ad spend alone is the ROAS cost. Marketing ROI usually also carries agency fees, tools, creative and people, and leaving them out makes a modest campaign look like a triumph.

What counts as good

Positive, on gross profit, after all the costs, is the bar. Beyond that it depends on what else the business could do with the money, which no benchmark can know. A campaign with a lower ROI that brings in customers who stay is often worth more than one with a higher ROI that does not, which is the argument for LTV to CAC as a companion figure.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Google Ads Cost in a tab called Google Ads and Shopify sales in a tab called Shopify. Gross margin and any costs that are not platform spend go in cells on your formulas tab.

Fresh Sheets replaces each 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. Net payment is what the store received after refunds:

=(SUM(INDEX('Shopify'!A:Z, 0, MATCH("Net payment", 'Shopify'!1:1, 0))) * B2 - SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0)))) / SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0)))

where B2 holds gross margin as a decimal. Change the quoted headings to match the ones in your tabs.

If you also advertise on Meta Ads, a Meta Ads tab adds its spend the same way, with the heading "Amount spent", in both places the Google Ads cost appears.

Work out your ROI with the free calculator