Blog · Finance metrics and formulas · Sales teams
Calculate paid-search ROI from campaign spending and matched financial outcomes. Reconcile clicks, orders, refunds, direct costs, and campaign fees before comparing returns.
PPC ROI compares the contribution from paid-search sales with the campaign investment needed to acquire them. Match the advertising export to orders and finance records, subtract refunds and direct costs, then deduct the campaign costs inside a documented boundary. Revenue reported by an ad platform is only the starting point.
For this workflow, investment means media spending plus assigned campaign-management fees. Direct product and fulfillment costs reduce the contribution earned on the sales; they are not included again in the campaign-investment denominator.
Net revenue = Attributed gross revenue − Matched refunds
Contribution before campaign costs = Net revenue − Direct sales costs
Campaign PPC ROI = (Contribution before campaign costs − Campaign investment) / Campaign investment
Label this definition wherever the result appears. Google Ads describes ROI as a profit-to-cost ratio and notes that the exact calculation depends on campaign goals. A return measured against campaign investment will differ from one measured against all production and advertising costs.
The marketing ROI versus ROAS comparison owns the broader distinction. This page follows the paid-search files through the calculation so the campaign result can be rebuilt and reviewed.
Export campaign ID, campaign name, interaction date, clicks, and spending from the advertising account. From the CRM or order file, bring the recorded attribution reference, order ID, order date, payment status, and assigned campaign. Finance supplies recognized revenue, matched refunds, and direct costs for those orders. The fee register supplies the assigned campaign-management expense.
Choose one acquisition cohort and one reporting cutoff. State which conversion actions count as paid orders. A form submission and a completed sale must not share a denominator labeled orders.
Google defines a conversion window as the time after an ad interaction during which a conversion is recorded. Your financial reconciliation also needs to identify later refunds and the reporting basis for completed sales. A window setting alone does not align those records.
Keep each order once in the attribution table. If credit is split, use the same approved shares for its revenue, refunds, and direct costs, and check that those shares exhaust the order's credited amount. Summarize media costs separately before matching them to outcomes; joining every click to every order can multiply both.
For platform revenue reconciliation, use the Google Ads ROAS workflow. Here the additional work is carrying each matched sale through to contribution and campaign investment.
An Ohio commercial-supplies distributor compares three paid-search campaign groups in a completed cohort. Brand means searches associated with its own name; category means general product searches; competitor means searches associated with competing brands. These are internal group labels, not comparisons of software vendors.
The illustrative exports reconcile to these inputs:
| Campaign group | Clicks | Media spend (USD) | Paid orders | Gross revenue (USD) | Refunds (USD) | Direct costs (USD) | Campaign fees (USD) |
|---|---|---|---|---|---|---|---|
| Brand | 2,000 | 4,000 | 40 | 20,000 | 2,000 | 7,200 | 600 |
| Category | 3,000 | 12,000 | 60 | 60,000 | 6,000 | 21,600 | 1,200 |
| Competitor | 1,000 | 5,000 | 10 | 10,000 | 1,000 | 3,600 | 500 |
| Total | 6,000 | 21,000 | 110 | 90,000 | 9,000 | 32,400 | 2,300 |
Put these columns in A:H, with the first campaign in row 2. If the separate Orders sheet holds campaign in A and matched net revenue in B, aggregate it with:
=SUMIFS(Orders!$B:$B,Orders!$A:$A,$A2)
Microsoft's SUMIFS documentation describes how to sum amounts meeting the selected criteria. Filter the source rows to the approved cohort and cutoff before this single-criterion summary, or add those criteria explicitly.
Calculate the campaign outputs in I:M:
I2 net revenue: =E2-F2
J2 contribution: =I2-G2
K2 campaign investment: =C2+H2
L2 contribution after campaign costs: =J2-K2
M2 PPC ROI: =IF(K2=0,"",L2/K2)
Format M as a percentage. Fill the formulas down, then calculate the total from summed amounts rather than averaging campaign percentages.
| Campaign group | Net revenue (USD) | Contribution (USD) | Campaign investment (USD) | Contribution after campaign costs (USD) | PPC ROI |
|---|---|---|---|---|---|
| Brand | 18,000 | 10,800 | 4,600 | 6,200 | 134.8% |
| Category | 54,000 | 32,400 | 13,200 | 19,200 | 145.5% |
| Competitor | 9,000 | 5,400 | 5,500 | -100 | -1.8% |
| Total | 81,000 | 48,600 | 23,300 | 25,300 | 108.6% |
Percentages are rounded to one decimal place; underlying calculations are unrounded. Total ROI is 25,300 / 23,300 = 108.6%. This is contribution after the defined campaign costs, before shared overhead, financing, and income taxes.
Use the same table to calculate average click cost and the paid-order conversion rate:
Average click cost: =IF(B2=0,"",C2/B2)
Paid-order conversion rate: =IF(B2=0,"",D2/B2)
Brand has a $2.00 click cost and 2.0% conversion rate. Category has a $4.00 click cost and the same 2.0% rate. Competitor has a $5.00 click cost and 1.0% conversion rate. These figures explain where to investigate; the contribution table determines the financial result.
The competitor group still records positive revenue, but its $5,400 contribution does not cover $5,500 of campaign investment. Review the matched orders and direct costs before adding budget. The cost-per-acquisition method provides the separate view of spending per completed outcome.
The first tie is 90,000 − 9,000 = $81,000 of net revenue. Then 81,000 − 32,400 = $48,600 of contribution. Campaign investment is 21,000 + 2,300 = $23,300, leaving 48,600 − 23,300 = $25,300. The three campaign results also sum to 6,200 + 19,200 − 100 = $25,300.
Keep a reconciliation list for unmatched orders, unassigned fees, and refunds awaiting an original order. A balanced summary cannot prove that omitted records are harmless. Finance should approve the direct-cost treatment; the gross-margin and contribution-margin comparison explains why those cost boundaries differ.
Bring campaign, order, refund, direct-cost, and assigned-fee exports, or a Google Sheet with the matched rows. Covirage maps the columns, and its tools compute and check the campaign figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account in about a minute and bring a sample of your own data.
See customer profitability for the contribution view supporting the campaign decision. Covirage is not affiliated with Google or Microsoft.
Include media spending and the campaign costs inside your stated investment boundary, such as assigned management fees. Deduct the direct costs of the matched sales when calculating contribution. Shared corporate overhead belongs only if your agreed measure explicitly allocates it; keep the treatment consistent.
No. Conversion rate counts outcomes per click but does not establish their financial value. Expensive clicks, low-margin orders, refunds, and campaign fees can leave little contribution even when the conversion rate looks strong. Reconcile paid orders to finance results before deciding.
You can report an estimated return using an approved expected contribution per qualified lead, but label it as an estimate. Keep that report separate from realized ROI based on completed sales. Replace estimates with matched financial outcomes when the acquisition cohort matures.
Attribution assigns sales credit under a chosen rule; it does not prove that advertising caused every credited sale. Treat attributed ROI as a reporting measure. A causal claim needs a separate evaluation of what would have happened without the campaign.