Which campaigns actually produced revenue?
The Salesforce and Power BI logic that ties inconsistent campaign records to signed fee, so leadership can compare channels on one definition of revenue.
01 — Problem
Nobody could connect spend to signed revenue.
Marketing ran email, conferences, and paid search. Sales closed clients. Both sides reported diligently, and neither report could answer the only question that mattered at budget time: which of those activities produced signed fee?
Campaign platforms knew about sends, clicks, and booth scans. Salesforce knew about accounts, opportunities, and closed-won revenue. Nothing joined the two, so channel budgets were argued from conviction rather than evidence.
02 — Messy data
The join was the hard part.
- No consistent definition. Campaign records, event lists, and opportunities each captured marketing activity differently, and none agreed on what counted as a touch.
- Conference data arrives as a spreadsheet. Badge scans come back after the event in whatever format the organiser used, with names and companies typed by hand.
- Long, uneven lag. A booth conversation in March can sign in August. Any month-by-month comparison of spend against revenue in the same period is misleading by construction.
- Duplicates and near-duplicates. The same company enters the CRM three times with three spellings, and each has a fragment of the story.
03 — Approach
Map campaigns to one definition, then connect them to fee.
- One consistent definition. I built the logic that maps every campaign onto one consistent list, so the same activity is counted the same way everywhere.
- Connected to outcomes. Each campaign links to the opportunities and signed fee it influenced.
- Conflicts resolved. Where sources disagreed on what a campaign touched, the rule for which one wins is documented in the report.
- Timed to engagement, not close. A deal can sign months after first contact, so credit goes to the campaign that sourced it, not the month it closed.
- ROAS and ROI kept separate. ROAS is signed fee over spend; ROI is profit over spend, each shown with its formula.
04 — Architecture
How it fits together.
05 — The report
What leadership actually opens.
A recreation of the production report, rebuilt with synthetic numbers and the same structure. Switch pages, filter by channel, and note that the Events view is deliberately different — conference spend behaves differently from everything else.
Spend, YTD
$106K
Signed fee, YTD
$1.09M
Blended ROAS
10.3×
signed fee ÷ spend
Engaged → client
4.3%
of accounts engaged
Monthly spend vs signed fee
ROAS by channel
Conversion funnel
Monthly spend vs signed fee — trend
06 — Outcome
Budget conversations changed shape.
[N]
campaigns mapped to one consistent definition
3
channels compared on the same revenue definition
5
events tracked individually, misses kept visible
No single headline number carries the project. What changed is that a claim like “email returned more per dollar than paid search” became something the report could settle, rather than something argued from conviction.
07 — Lessons
What I'd tell myself at the start.
Reconciling the definitions took most of the time. I budgeted for analysis and spent the bulk of it deciding how to map every campaign onto one consistent list; every later number depends on that step.
Next project
The revenue & invoicing engine