Most marketing performance reports are built from the ad-platform export outward. Someone connects the platforms, the CRM and a spreadsheet of budgets, flattens them into one wide table, and starts drawing charts. The first version looks convincing. The second meeting is spent explaining why paid search has two different lead counts on two pages.
The visuals are rarely the problem. The problems are definitions: which channel a lead belongs to, which attribution rule applies, and which month the revenue lands in. This is how I would structure the model so those questions are answered once, in the model, instead of in every meeting.
Start from the decision, not the export
Before any table is loaded, name the decision the report exists to support. For marketing it is usually some version of: where does next month's budget go, and what stops being funded. That sentence decides almost everything downstream — the grain you need, how fresh the data has to be, and which of the long list of available platform metrics matter.
A report built to reallocate budget monthly needs spend, qualified leads and revenue by channel and campaign, at a monthly cadence, with trends. It does not need impression-level detail refreshed hourly. Starting from the export gives you every field the platform offers; starting from the decision gives you the few that change what somebody does.
Keep spend, leads and revenue as separate facts
These three arrive at different grains and from different systems. Spend is daily, per campaign, from each ad platform. Leads are per contact, from forms and the CRM. Revenue is per closed deal, often weeks or months later, from the CRM or finance. Flattening them into one table forces a single grain onto data that does not share one, and the result is duplicated spend or lost leads, depending on which way the join went.
Model them as separate fact tables in a star schema, and connect them through shared dimensions:
- Date — one marked date table, used by every fact.
- Channel — the business's channel taxonomy, not any one platform's.
- Campaign — keyed consistently across platforms and the CRM, which is usually the hardest part of the whole build.
Measures then combine the facts at whatever grain the visual asks for. Cost per lead is spend from one fact divided by leads from another, both filtered by the same Channel and Date. Nothing is pre-joined, so nothing is double-counted.
The channel taxonomy is a KPI dictionary problem
"Paid social" means whatever the tracking parameters say it means, and tracking parameters are typed by people. The same campaign turns up as facebook, Facebook, fb and meta. If the model maps those with a nested IF in a calculated column, the mapping lives where nobody in marketing can see or change it.
Put it in a mapping table instead: source and medium in, channel out, maintained by a named person in marketing operations. Treat it like any other entry in the KPI dictionary — an owner, a change history, and a rule for what happens to values nobody has mapped yet.
That last rule matters most. Unmapped values should land in a visible "Unmapped" channel with its spend shown on the page, not be dropped by an inner join. Dropped values make the channel totals look clean and the grand total quietly wrong.
Attribution is a definition, not a chart
Every attribution model is a rule for dividing credit for a sale across the touches that preceded it: all to the first touch, all to the last, split evenly, or weighted by position. None of them is the truth. Each is a definition, and like any other definition it needs to be chosen, written down and applied consistently.
In practice that means three things. Calculate the credit upstream — in the warehouse or in Power Query — as a weight per touchpoint for each model you support, rather than improvising it in DAX at query time. Expose the model as an explicit choice on the page, labelled, so nobody has to guess which rule produced the number. And never put channels calculated under different models side by side. A comparison between last-touch paid search and first-touch organic is not a comparison.
If the organisation cannot yet agree on a model, that is the conversation to have first. A report cannot settle it by picking one quietly.
Time and money need a stated rule
Two rules cause more reconciliation arguments than any chart choice.
Which month. Spend in September can produce a lead in September and a deal in December. Reported by close date, September looks expensive and December looks efficient. Reported by lead-created date, the recent months look poor until their deals close. Both views are legitimate; mixing them without saying so is not. In the model, the revenue fact can hold both dates, with an active relationship to Date on one and an inactive relationship on the other, switched on inside the measures that need it with USERELATIONSHIP. Label which one each page uses.
Which currency, at which rate. Platforms bill in their own currency. Store spend in the original currency alongside a dated exchange-rate table, and convert inside the measure at a rate you have written down — the rate on the transaction date, or a monthly average agreed with finance. If marketing converts at one rate and finance at another, the report and the ledger will never match, and finance's number wins that argument every time.
Define the measures once
Cost per lead, cost of acquisition, return on ad spend and pipeline generated per unit of spend belong in the semantic model as measures, each written once and reused by every visual. When a definition changes, it changes in one place, and the change is reviewable.
Return on ad spend =
DIVIDE (
[Attributed revenue],
[Ad spend]
)DIVIDE rather than the division operator, because a campaign with no spend in the filter context should show a blank, not an error. The measure itself is trivial. What matters is that [Attributed revenue] already carries the stated attribution rule, date rule and currency rule — so the ratio inherits every decision above instead of making its own.
The KPIs worth defining first
Most marketing reports draw on the same short list of measures. None of them is hard to calculate. Each one comes with a trap, and the trap is where two versions of the same number start to circulate.
| KPI | Calculation | What it answers | The usual trap |
|---|---|---|---|
| Spend | Sum of ad spend | What was spent | Drifts from the invoice when recent days are restated and never reloaded |
| Click-through rate | Clicks ÷ impressions | Whether the ad earns attention | Says nothing about revenue; easy to optimise in isolation |
| Conversion rate | Leads ÷ sessions | Whether the landing page works | Sessions and clicks get used interchangeably as the denominator |
| Cost per lead | Spend ÷ leads | What a lead costs | Low-quality leads make it look excellent |
| Lead-to-opportunity rate | Opportunities ÷ leads | Whether the leads are any good | Needs cohorting by lead-created date, or recent months look poor |
| Cost per opportunity | Spend ÷ opportunities | Cost per lead, corrected for quality | Opportunities take time to appear, so the latest month is incomplete |
| Pipeline per spend | Pipeline value ÷ spend | What spend is generating before deals close | Pipeline is not revenue; stage definitions must be agreed |
| Customer acquisition cost | Spend ÷ new customers | What a new customer costs | State whether sales cost is included; two versions of it circulate otherwise |
| Return on ad spend | Attributed revenue ÷ spend | Which channels pay back | Entirely dependent on the attribution rule, so label it |
| Win rate | Won ÷ closed opportunities | How much of the pipeline converts | Open deals do not belong in the denominator |
| Sales cycle length | Average days from lead to won | How long revenue takes to arrive | Explains why the most recent months always look weakest |
Put each of these in the KPI dictionary with an owner, the exact calculation and its date rule before it reaches a page. The table is the easy part; the agreement behind each row is the work.
Refresh for the restatement you actually get
Ad platforms revise recent days as late conversions and billing adjustments arrive. A pipeline that loads only yesterday's data will hold the first, incomplete version of every day permanently, and the report will drift away from what the platform shows.
Reload a trailing window on every run instead of only the latest day, sized to how far back the platform actually restates. In Power BI, an incremental refresh policy does this directly: archive older periods and refresh only the recent window. Then reconcile the model's spend total against the platform's billing total on a schedule, so the drift is caught by a check rather than by the person who owns the budget.
What the model cannot tell you
A well-built marketing model tells you, consistently and under stated rules, what was spent and what followed. It does not tell you what caused what. Attribution assigns credit; it does not measure whether the sale would have happened without the ad.
That question has an honest answer only through a controlled test — a holdout region, a paused channel, a split audience. A model can make such a test easy to read afterwards. It cannot replace it, and a report that implies otherwise will be used to defend budgets it has not earned.
So the page that goes in front of the budget owner should say which attribution rule, which date rule and which currency rule it uses, and should not claim more than those rules support. That is less impressive than a dashboard promising the true return on every channel. It is also the version that is still trusted at the next budget round.
