How to calculate marketing ROI, and defend the number

The marketing ROI formula

ROI compares what a marketing activity returned with what it cost.

ROI = (return – cost) / cost × 100%

The formula has two inputs, and each one hides a decision.

• Cost. Ad spend only, or also agency fees, tools and salaries? Ad spend alone gives a higher ROI and a narrower truth.
• Return. Revenue, or the margin left after the cost of goods? Orders placed, or orders kept after cancellations and returns?

Two people can apply the same formula to the same campaign and both be right by their own definition. So write the definition down next to the number.

ROI, ROAS, ROMI and MER: which one you are being asked for

These four get used as if they were one metric. They answer different questions.

ROAS is a ratio, ROI is a percentage of profit. A ROAS of 3.0 means $3 of revenue per $1 of spend. It says nothing about profit: with a 30% margin, a ROAS of 3.0 loses money. More on that in what ROAS is and how to read it.

MER ignores attribution on purpose. It divides all revenue by all spend, so it cannot be argued with and cannot tell you which campaign to cut. See marketing efficiency ratio.

When a board asks for “ROI”, check which of these they mean before you answer. For a longer view than one quarter, the related question is how acquisition cost compares with customer value, covered in the LTV:CAC ratio.

Where ROI numbers break

The formula does not fail. The inputs do, and usually in the same five places.

1. The platform’s own count. Each ad platform reports the conversions it claims. Two platforms can claim the same order, and neither knows about a cancellation.
2. Cancelled and returned orders. Revenue counted at checkout is higher than revenue the business kept.
3. Revenue instead of margin. A campaign that sells low-margin products can show a strong ROI on revenue and a weak one on profit.
4. The attribution rule. Which campaign gets the order: the last click, the first, or a share? Each rule is defensible, and each moves the number. The options are compared in marketing attribution models.
5. The join itself. Spend lives in the ad platforms. Revenue lives in the store or the CRM. Someone has to match them by campaign and date, and that match is where the number is made.

In most companies the fifth step happens once, in a spreadsheet, and is never looked at again.

The SQL behind a defensible ROI

Here is that join written out. It runs in Google BigQuery on the dataset behind the OWOX e-commerce demo project, and it answers one question: what was the ROI of paid search last quarter, by campaign?

Every decision from the list above is visible in it. Spend comes from the ad platforms. An order belongs to the campaign of the session in which it was placed. Return is margin on completed orders: revenue minus product cost, with cancelled and returned orders left out.

WITH spend AS (
  SELECT source, medium, campaign,
         SUM(spend) AS spend
  FROM `owox-demo.ecommerce.ad_performance`
  WHERE date BETWEEN '2026-07-01' AND '2026-09-30'
    AND source IN ('google', 'bing')
    AND medium = 'cpc'
  GROUP BY source, medium, campaign
),
revenue AS (
  SELECT s.source, s.medium, s.campaign,
         SUM(p.line_revenue) AS gross_revenue,
         SUM(IF(o.status = 'Completed',
                p.line_revenue - p.line_cost,
                0)) AS kept_margin
  FROM `owox-demo.ecommerce.orders` AS o
  JOIN `owox-demo.ecommerce.sessions` AS s
    USING (session_id)
  JOIN `owox-demo.ecommerce.purchases` AS p
    USING (order_id)
  WHERE o.order_date
        BETWEEN '2026-07-01' AND '2026-09-30'
  GROUP BY s.source, s.medium, s.campaign
)
SELECT sp.source, sp.campaign,
       ROUND(sp.spend) AS spend,
       ROUND(r.gross_revenue) AS gross_revenue,
       ROUND(r.kept_margin) AS kept_margin,
       ROUND(SAFE_DIVIDE(r.kept_margin - sp.spend,
                         sp.spend) * 100, 1) AS roi_pct
FROM spend AS sp
LEFT JOIN revenue AS r
  USING (source, medium, campaign)
ORDER BY sp.spend DESC

The query is short. What matters is that each choice is a line someone can read and disagree with. Change 'Completed' and the number changes, and so does the definition everyone is working from.

A worked example: one campaign, three ROI figures

One campaign from that result, a Google search campaign on competitor terms, for July to September 2026:

Now apply the same formula three ways.

All three are “the ROI of the campaign”. The first is the one a quick calculation produces. The third is the one that says whether the campaign made money.

Across all seven paid search campaigns the gap is the same shape: 454% on checkout revenue, 198% on margin, from $17,528 of spend. And the platforms claim 255 conversions where the store recorded 198 orders and kept 170.

Nobody in that example made an arithmetic mistake. The figures differ because the definitions differ, and in most reports the definition is not written anywhere.

The same question as a prompt

A marketing leader does not want to write that SQL, and should not have to. What they want to type is this:

Data Model: E-commerce

Prompt: What was our ROI on paid search last quarter, by campaign?

An AI assistant will answer that question whether or not it has what it needs. What it reads decides whether the answer can be trusted.

The left column is not a careless model. Summing line_revenue for every order is a reasonable reading of those tables. Nothing in the raw data says that cancelled orders should be left out or that this company reports ROI on margin. That knowledge is in the analyst’s head until it is written into a query.

This is what OWOX Data Marts is for. The analyst writes the ROI query once and publishes it as a Data Mart in your warehouse. OWOX does not calculate ROI or model attribution for you; it publishes and governs the SQL your analyst wrote. Business users then read that Data Mart in a spreadsheet, a dashboard, or an AI assistant connected through the OWOX MCP server.

The AI connection is only the interface. The guarantee is the Data Mart behind it. When we asked the demo project for paid search spend by campaign for the same quarter, the answer came back with the name of the Data Mart it was read from, a link to its definition, and the time its source tables were last updated. The spend total matched the query above to the cent: $17,527.42.

How that setup works end to end, including the log of every question asked, is described in governed MCP analytics. Why an assistant invents joins on raw tables is covered in connecting Claude to BigQuery through MCP.

Getting the number into the board deck

A defensible ROI figure needs four things written beside it. If any is missing, expect the question.

• The period and the scope. “Paid search, July to September 2026”, not “Q3 marketing”.
• What counts as cost. Ad spend only, or loaded cost.
• What counts as return. Revenue or margin, placed or kept orders, and the attribution rule.
• Where it was computed. One named query, not “the marketing spreadsheet”.

If the same query also feeds the weekly report, the commentary and the number cannot drift apart. AI Insights sends scheduled plain-language summaries built on the same Data Marts to Slack, Microsoft Teams, email or Google Chat.

What a good marketing ROI looks like

There is no universal benchmark, because the figure depends on the definition. A 200% ROI on revenue and a 70% ROI on margin can describe the same campaign, as the example shows.

Two checks are more useful than a benchmark:

• Is it above zero on margin? Below that, the campaign costs more than it brings in, whatever the revenue figure says.
• Is it moving, with the definition held still? A change in ROI means something only if the query did not change with it.

The other metrics that belong beside ROI are collected in marketing metrics and KPIs.

Marketing Strategy25 Marketing Metrics and KPIs to Track, with FormulasOctober 7, 2026→Marketing AnalyticsHow to Use Marketing Mix Modeling to Increase ROIOctober 14, 2026→Google BigQueryHow to Upload Data to BigQuery: CSV, Google Drive and SQLOctober 6, 2026→

See all articles →