A channel valued at $22,500 was producing $5.07m
A retailer with $42.8m of closed-won revenue in the CRM wanted to know which marketing channels earned it. The dashboard that was supposed to answer that was understating one channel by a factor of 225 and overstating others at the same time.
- The goal
- Be able to move advertising budget between channels on evidence — which means knowing, per channel, what was spent and what came back.
- What it was costing
- Paid social was carrying $5.07m of closed-won revenue and being appraised at $22,500. On the dashboard's version it was the worst channel in the business, and the conversation about cutting it had already started.
Client anonymised — a home improvement retailer, delivered under another company. Company names, campaign names, platforms and record identifiers are withheld or changed. Every figure below is real.
- Channel revenue recovered
- $5.07m, reported as $22,500
- Understated by
- 225 times
- Revenue base audited
- $42.8m closed-won, all time
- Blockers to ROAS identified
- 5 structural, ranked cheapest-first
The decision that was about to be made on a wrong number
The marketing team had a dashboard and the dashboard had an answer: paid social produced almost nothing. Twenty-two and a half thousand dollars of closed-won revenue, against spend that made it comfortably the weakest line in the mix.
That is a budget decision waiting to happen and it would have been defensible. Nobody keeps funding a channel that the reporting says is dead. The only thing holding it up was a nagging sense among the people running the channel that the number did not match what they saw in the pipeline.
The report was grouping revenue by a campaign field that is properly populated on deals but had been effectively empty on contact records since around May. Grouped that way, nearly every deal fell into one unnamed bucket and the tile reported that bucket as the channel. Read against the field that actually carries campaign names, paid social had produced $5.07m.
The opposite error, on the same screen
While one tile understated by two orders of magnitude, others were overstating. Several reports were built across contacts and deals together, and in that structure a deal with two contacts attached is counted twice.
At this scale that is not a rounding difference, and it flatters exactly the channels that generate multi-contact households — which in home improvement is most of them.
So the team had a dashboard simultaneously too pessimistic about one channel and too optimistic about others, with no way to tell which tile was which. That is worse than no dashboard, because it is used.
Reconciled rather than replaced
Four reports were rebuilt as single-object reports, which removes the double-counting structurally rather than filtering around it.
The step that mattered came next. A rebuilt report producing a different number is just a second opinion, and a team that has been misled once has no reason to trust the replacement. So old and new were reconciled against each other: the old report was missing revenue the contact join had dropped and carrying revenue the fan-out had duplicated, and netting those two effects closed the gap exactly, to the dollar.
Every headline figure was then confirmed against an independent second source before going live — lead volume checked three ways, landing at 4,993, 4,993 and 4,992, and won-deal volume reconciled across all eleven channel buckets against a report the team already trusted and which was deliberately kept rather than replaced.
Three further tiles were making claims the CRM could not support — spend by channel while only one ad platform was connected, revenue by campaign where only keywords had ever been captured. Those were retitled to state exactly what they measure. A tile labelled honestly is still useful; one labelled aspirationally teaches a team to distrust every tile beside it.
The actual deliverable: why ROAS is not yet possible
Fixing the reports was the easy half, and stopping there would have left the client with prettier numbers and the same inability to allocate budget. The substantive output was a ranked list of the structural reasons return on ad spend cannot be calculated in this CRM at all, cheapest high-value item first.
The largest and cheapest unlock: one ad platform is not connected to the CRM, so there is no spend figure against a channel producing five million dollars of revenue. No connection, no ROAS, however good the revenue reporting becomes.
Close dates are an import artefact — roughly 8,800 deals carry a close date stamped inside a single sixteen-second window during a migration. Every trend, velocity and days-to-close measurement built on that field is meaningless, and no amount of report-building repairs it.
The closed-lost stage is never used, so conversion rate has no denominator. Deals carry no owner, so every by-consultant leaderboard renders as unassigned. And two fields the dashboards depend on have been dead since May — one report was running on a dead field and the corrupt close date at the same time, which is how a number can be confidently wrong twice over.
None of that is flattering to report and all of it changes what the marketing team should do next far more than another rebuilt tile would have.
What changed
- Paid social was correctly valued at $5.07m rather than $22,500, before the budget decision was taken against it.
- Double-counted revenue was removed from four reports, so channels are compared on a consistent basis instead of one that quietly favoured multi-contact deals.
- The correction was reconciled against the previous figures to the dollar, so the team could see why the old numbers were wrong rather than being asked to take the new ones on trust.
- Three tiles that implied data the business does not collect now state what they actually measure, and the reporting surface came down from 52 reports to 46.
- The client holds a ranked list of the five structural blockers to measuring return on ad spend, with the cheapest high-value fix named — connecting the missing ad platform — rather than a vague recommendation to improve data quality.
Where it stands
Delivered. The reporting corrections are live and verified. The five structural blockers are the client's to action — connecting the second ad platform, establishing a real closed-lost practice, assigning deal ownership and repairing the migrated close dates — and none of them are things a reporting engagement can fix on the client's behalf.
Questions people ask about this
Why does a marketing channel show almost no revenue in the CRM?
Check which field the report groups on before concluding the channel is weak. A campaign field populated on deals but empty on contacts will push nearly every deal into one unnamed bucket, and the report will show that bucket as the channel. On this engagement that error valued a channel at $22,500 when it had produced $5.07m.
What is report fan-out and how do you spot it?
It is what happens when a report is built across two related objects: a deal associated with two contacts gets counted twice. It inflates quietly and it flatters whichever channels produce multiple contacts per sale. The fix is to build the report on a single object rather than to filter around the duplication.
How do you prove a corrected report is right?
Reconcile it against the old one rather than simply replacing it. Show what the previous version was missing and what it was double-counting, and net those two effects against each other. If the difference closes exactly, the correction is a finding rather than a second opinion — which matters when a team has already been misled once.
Why can't we calculate return on ad spend in our CRM?
Usually a small number of structural reasons rather than one. The common set: an ad platform not connected so there is no spend figure at all, close dates that are import artefacts rather than real dates, a closed-lost stage nobody uses so conversion has no denominator, and deals with no owner so per-person reporting is empty. None of those are fixed by rebuilding reports.
What should you do with a dashboard tile that promises data you do not collect?
Retitle it to say what it actually measures. Deleting it hides a gap the business should know about, and filling it with a proxy is worse — one aspirational tile teaches a team to distrust every honest tile beside it.
If a channel in your reporting looks inexplicably dead, check which field the report groups on before you cut the budget. Then find out whether the number you want is measurable at all.
RevOps Diagnostic Audit