Blended ROAS and MER in Google Sheets
Blended ROAS and MER (marketing efficiency ratio) are usually two names for the same calculation: total revenue divided by total ad spend, across every channel. Some teams also add other marketing costs to the bottom of MER. Either works, as long as you pick one definition and write it next to the number.
The formula
MER = total revenue ÷ total ad spend
Because the revenue comes from your store rather than the ad platforms, no sale is counted twice. That is the whole point of it.
A worked example
One week, with revenue from Shopify and spend from Google Ads and TikTok Ads:
| Amount | |
|---|---|
| Shopify net payment | 12,400.00 |
| Google Ads cost | 1,850.00 |
| TikTok Ads spend | 650.00 |
| MER | 4.96 |
In Google Sheets:
=B2/(B3+B4)
By day, from three report tabs
With each report in its own tab, and the date in column A of your working tab:
=IFERROR(SUMIFS(INDEX(Shopify!A:Z,0,MATCH("Net payment",Shopify!1:1,0)), Shopify!A:A, A2) / (SUMIFS(INDEX('Google Ads'!A:Z,0,MATCH("Cost",'Google Ads'!1:1,0)), 'Google Ads'!A:A, A2) + SUMIFS(INDEX('TikTok Ads'!A:Z,0,MATCH("Spend",'TikTok Ads'!1:1,0)), 'TikTok Ads'!A:A, A2)), "")
This assumes each report has its date in column A. It will, as long as the date is one of the fields you've chosen.
Where it goes wrong
- Using the platforms' conversion value as revenue. That brings back exactly the double counting MER is meant to remove.
- Not choosing the revenue figure deliberately. In Fresh Sheets' Shopify report, Total includes tax and shipping, while Net payment is the money received minus refunds. Say which one you use.
- Mixing currencies. Keep every account in one currency, or convert before you divide.
- Mismatched days. Each source counts days in its own account's time zone. Check they match before comparing day by day.
- Including today. An unfinished day understates revenue and spend by different amounts, so leave it out.
Keeping the inputs current
Schedule three Fresh Sheets reports, each into its own tab:
- Shopify: orders by day, with Net payment.
- Google Ads: by day, with Cost.
- TikTok Ads: by day, with Spend.
Keep the MER formulas on a separate tab, because each report replaces its own tab on every refresh.