ROAS vs MER Reconciliation Sheet
The spreadsheet that closes the gap your CFO keeps asking about between channel ROAS and total marketing efficiency.
Who this is for
- Founders with a real finance team
- Multi-channel operators (Google + Meta + others)
- Brands above $500K/mo total ad spend
Why this exists
Channel ROAS and blended MER tell two different stories. When they diverge, attribution is broken or incrementality is collapsing. This sheet reconciles both numbers so the founder and the CFO are looking at the same picture.
Read this first
If your Google dashboard says 4.2x ROAS, your Meta dashboard says 2.8x, your Shopify says total revenue is X, and the four numbers don't reconcile in any sheet anywhere in the business, this is the audit that closes the gap. The reconciliation isn't bookkeeping; it's the only honest way to know whether last month's spend produced incremental revenue or recycled organic demand.
How to build the reconciliation
Pull the four primary numbers for the same trailing 30-day window
Total revenue from Shopify (or whatever source-of-truth ecommerce platform you run). Total marketing spend from your finance system, including the platforms plus tools and creative. Channel-attributed revenue from each ad platform's dashboard (Google, Meta, TikTok, etc.). GA4 ecommerce revenue split by source/medium. Same window across all four; don't mix 30-day Google with 7-day GA4.
Compute MER and channel ROAS side by side
MER is total revenue divided by total marketing spend. Channel ROAS is platform-attributed revenue divided by platform spend per channel. The first row of the sheet has MER in one cell and the sum of channel-attributed revenues in the other. The second tells you the over-attribution factor: if channels report $1.5M attributed and your total revenue is only $1.2M, paid is double-counting by 25%.
Layer POAS using the contribution-margin haircut
Multiply attributed revenue by gross margin, then by (1 minus refund rate), then by (1 minus return rate). That's contribution. Divide by spend. POAS sits on the same axis as ROAS but it's the number your CFO already runs. The sheet has a POAS column for every channel and a blended POAS row at the bottom.
Add the AOV-impact model
Average order value drives ROAS as much as bid efficiency. A 12% AOV move offsets a 0.5x channel ROAS shift. The sheet projects: if AOV climbs from $84 to $95, what does each channel's required ROAS target need to be to maintain the same blended MER? This is the chart that makes upsell + bundle work readable as a marketing decision, not just a merchandising one.
Build the two CFO charts
Chart one: blended MER vs blended POAS over time, weekly. The two should track. When they diverge, channel mix is shifting toward channels that look efficient but aren't. Chart two: incremental-revenue-per-channel-dollar from your most recent geo-holdout test (or a defended estimate if you haven't run one yet). These two charts replace whatever channel-ROAS spreadsheet you've been showing the CFO.
Reconciliation numbers, illustrative
Example numbers showing how the same account reads under three different lenses. Use this as the shape; populate the right column with your real data.
| Metric | Looks like | What it actually means |
|---|---|---|
| Channel ROAS (Google + Meta) | 4.2x and 2.8x reported by each platform | Sum of attributed revenue divided by sum of platform spend. Likely double-counts overlap. |
| Sum of channel-attributed revenue | $1.5M | If total revenue is $1.2M, paid is 25% over-attributed. The gap is overlap and brand bleed. |
| MER (total revenue / total marketing spend) | 3.1x | The honest number. No double-count. The CFO's primary view. |
| Blended POAS (MER * margin * (1-refund) * (1-return)) | 1.4x | What the business actually earned. Below 1.0x means paid is losing money even before fixed costs. |
| Incremental revenue (geo-holdout) | 78% of paid attributed revenue | What would have not happened without paid. The 22% gap is brand cannibalisation + organic overlap. |
POAS calculator (sheet formula)
The same formula from the POAS over ROAS pillar, condensed for the reconciliation sheet. Inputs read from your Shopify and finance exports.
# Inputs
revenue = attributed_revenue_for_window
ad_spend = platform_spend_for_window
gross_margin_pct = (revenue - cogs) / revenue
refund_rate_pct = refunds / orders
return_rate_pct = returns / orders
# Adjusted contribution
contribution = revenue * gross_margin_pct
contribution = contribution * (1 - refund_rate_pct)
contribution = contribution * (1 - return_rate_pct)
# Outputs
poas = contribution / ad_spend
roas = revenue / ad_spend
gap_factor = poas / roas
# gap_factor near 0.30 is normal for ecom in 2026.
# gap_factor below 0.18 means margin is too thin for paid acquisition
# at the current efficiency. Above 0.45 means you have room to scale.What to put in front of the CFO
- Blended MER trended weekly for the last 90 days, single line chart
- Blended POAS on the same axis, second line, paired with MER for divergence read
- Channel mix percentage by week, stacked area chart, so MER drift is traceable to channel shift
- AOV trended weekly with the AOV-impact projection if you're testing bundles or upsells
- Most recent geo-holdout test result with the incremental-revenue percentage clearly labelled
- POAS by channel as a sanity table, not as the headline (it's diagnostic, not strategic)
- Notes section explaining one-time events that distort the trend (Black Friday, BFCM hangover, supplier outage)
- Sources tab listing where each number was pulled from with a refreshed-on date
What good looks like at the end of this work
MER and POAS are the headline numbers in every reporting cycle. Channel ROAS sits in a sub-tab as diagnostic. The CFO stops asking what the platform dashboards mean because the sheet reconciles them. Marketing decisions are now bounded by contribution per dollar of spend rather than platform-reported ratios. Geo-holdout cycles are scheduled quarterly so the incremental-revenue number stays defensible.
External resources
Authoritative references we link to alongside the template. Read them before running the audit.
- Google Ads, attribution model basicsRequired reading before debating channel ROAS interpretation with anyone.
- Google Analytics 4, advertising attributionHow GA4 splits credit. Useful for the source/medium split in step 1.
- Google Ads, conversion value rulesLayer category-level value multipliers when product-level margin data isn't available.
- Northbeam, MER vs ROAS frameworkVendor-published library covering MER, blended attribution, and incrementality patterns.
- Search Engine Land, ecommerce measurement coverageTopic index. Useful for tracking attribution-model changes year over year.
Want this run for you?