Marketing is usually the last big spend line in the enterprise to leave spreadsheets. Finance gets a three-statement model, sales gets territory, quota and commissions, supply chain gets S&OP — and marketing gets a workbook with one tab per region, a pivot table nobody can refresh, and an agency invoice queue that reconciles to the GL by luck. When the CFO asks for a 15% in-quarter cut with the pipeline impact quantified, the answer takes two weeks and nobody trusts it.
This is a good Anaplan problem. Marketing planning is a sparse, multi-dimensional allocation problem sitting on top of a conversion funnel, with a monthly actualization cycle against the general ledger. That is precisely what the platform is built for. This tutorial walks the build end to end.
It assumes you are comfortable with lists, line item subsets, SUM/LOOKUP, and time ranges, and that a revenue or bookings plan already exists somewhere in your estate to hand pipeline targets down from.
What the model has to do
Write these five jobs down before you create a single list. Every design decision below traces back to one of them.
- Hold the budget — a top-down envelope by region, business unit, and channel that finance owns and marketing cannot silently exceed.
- Plan the campaigns — bottom-up campaign records with timing, cost type, channel, and audience, rolling up to that envelope.
- Drive the funnel — convert spend into leads, MQLs, SQLs, pipeline and closed-won using per-channel conversion and cost assumptions.
- Reallocate under pressure — move money mid-quarter, respecting what is already committed, and show the pipeline consequence.
- Actualize and explain — bring in GL actuals and agency commitments, accrue the gap, and report plan vs. commitment vs. actual with an ROI view.
Most marketing models get 1, 2 and 3 and then die on 4 and 5. The reallocation and actualization layers are where the credibility lives.
Step 1: The dimensional skeleton
Resist the urge to make one giant campaign list dimensioned by everything. Campaign attributes belong in properties on a flat list; only the dimensions you actually slice and aggregate on belong in the module.
| List | Type | Notes |
|---|---|---|
Campaigns | Flat numbered list | One record per campaign or per campaign-wave. Attributes as properties: owner, channel, region, BU, audience, start, end, cost type. |
Channels | Flat list | Paid search, paid social, events, field marketing, content, ABM, partner, PR, web, tooling. Keep it under ~20. |
Regions | Hierarchy | Territory > Country > Region > Global, aligned to the sales hierarchy you already have. |
Business Units | Hierarchy | Match the GL cost center rollup, not marketing's org chart. |
Funnel Stages | Flat list | Impressions, Leads, MQL, SQL, Opportunity, Closed-Won. Ordered, and used for the stage-to-stage cascade. |
Versions | Native versions | Budget, Forecast, Actual, Prior Forecast. |
Cost Types | Flat list | Media, agency fees, production, events, software, headcount-adjacent. Drives accrual treatment. |
Time | Months, 2 years fwd | Use a time range. Marketing does not need your 10-year LRP horizon. |
Two rules that save you later:
- Region and BU are dimensions, not properties. Finance re-slices by both constantly, and a property forces
SUMgymnastics on every report. - Channel is a dimension. Your conversion assumptions live at channel level and you want them to apply without a lookup through the campaign list.
Campaign is a numbered list because marketing will create and kill hundreds of records a year and you do not want code-managed names. Give it an Active? boolean and filter on it everywhere.
Step 2: The budget envelope (DAT and top-down)
Build INP01 Budget Envelope dimensioned by Regions, Business Units, Channels, Time, Versions:
Budget Envelope -- number, input, finance-owned
Envelope Locked? -- boolean, input
Allocated to Campaigns -- number, formula
Unallocated -- Budget Envelope - Allocated to Campaigns
Over-allocated? -- Unallocated < 0
Allocated to Campaigns pulls up from the campaign module:
Allocated to Campaigns =
'CAL01 Campaign Plan'.Planned Spend[SUM: 'SYS01 Campaign Details'.Region,
SUM: 'SYS01 Campaign Details'.BU,
SUM: 'SYS01 Campaign Details'.Channel]
Apply Dynamic Cell Access so Budget Envelope is only writable when Envelope Locked? is false and the user's role is finance. Marketing plans inside the envelope; it does not edit the envelope. That single control is the difference between a model finance trusts and another spreadsheet.
The Over-allocated? boolean drives a red conditional format on every page. Do not build a hard validation that blocks input — planners need to over-allocate temporarily while they shuffle. Make it visible, not impossible.
Step 3: Campaign phasing without a bloated module
The classic mistake is a Campaigns x Time module with forty line items. Split it.
SYS01 Campaign Details — dimensioned by Campaigns only, no time:
Channel, Region, BU, Cost Type, Owner -- list-formatted inputs
Start Date, End Date -- date inputs
Total Planned Cost -- number input
Phasing Profile -- list-formatted (Even, Front-loaded, Event-date, S-curve, Manual)
Start Period = PERIOD(Start Date)
End Period = PERIOD(End Date)
Active? = boolean input
CAL01 Campaign Plan — dimensioned by Campaigns x Time, and kept deliberately thin:
In Window? = ITEM(Time) >= 'SYS01'.Start Period AND ITEM(Time) <= 'SYS01'.End Period
Phasing Weight = IF NOT In Window? THEN 0 ELSE LOOKUP into 'INP02 Phasing Curves'
Weight Total = Phasing Weight[SUM: ...] -- or use a subsidiary total by campaign
Planned Spend = IF Weight Total = 0 THEN 0 ELSE 'SYS01'.Total Planned Cost * Phasing Weight / Weight Total
Manual Override = number input
Final Spend = IF Manual Override <> 0 THEN Manual Override ELSE Planned Spend
Normalising by Weight Total rather than assuming the curve sums to 1 means a planner can shorten a campaign by two months and the money redistributes instead of leaking. That behaviour alone removes most of the "the total changed and I don't know why" support tickets.
Keep the Campaigns x Time module to under ten line items. Everything that does not vary by month belongs in SYS01. On a 2,000-campaign list over 24 months you will feel the difference immediately.
Step 4: Funnel drivers — spend to pipeline
This is the part that earns marketing a seat in the planning cycle, and the part most teams overreach on. You are not building a media mix model in Anaplan. You are building a transparent, auditable driver cascade whose coefficients can be informed by an MMM or an attribution tool sitting outside the platform.
INP03 Channel Assumptions, dimensioned by Channels x Regions x Time x Versions:
Cost per Lead -- input
Lead -> MQL % -- input
MQL -> SQL % -- input
SQL -> Opp % -- input
Opp -> Won % -- input
Average Deal Size -- input
Lag: Spend to Lead (mths)
Lag: Lead to Won (mths)
Saturation Threshold -- monthly spend beyond which CPL degrades
Saturation Penalty % -- CPL uplift applied above threshold
Then CAL02 Funnel Output, dimensioned by Channels x Regions x Business Units x Time:
Spend = 'CAL01'.Final Spend[SUM: ...]
Effective CPL = IF Spend <= Saturation Threshold THEN Cost per Lead
ELSE Cost per Lead * (1 + Saturation Penalty %)
Leads Generated = IF Effective CPL = 0 THEN 0 ELSE Spend / Effective CPL
Leads (lagged) = LAG(Leads Generated, Lag: Spend to Lead, 0)
MQL = Leads (lagged) * Lead -> MQL %
SQL = MQL * MQL -> SQL %
Opportunities = SQL * SQL -> Opp %
Pipeline Value = Opportunities * Average Deal Size
Won Value = LAG(Pipeline Value * Opp -> Won %, Lag: Lead to Won, 0)
Marketing ROI = IF Spend = 0 THEN 0 ELSE Won Value / Spend
Three notes on the modelling choices:
The saturation step is a step function, not a curve. A smooth diminishing-returns curve in Anaplan is easy to write and impossible for a marketing director to defend in a budget meeting. A threshold and a penalty are explainable: "above £400k a month in paid search our blended CPL rises about 20%." If you genuinely need a curve, build it as a lookup table of spend bands rather than a logarithm — it is faster to calculate and far easier to argue with.
Lags belong in the assumption module, not hard-coded. Events convert over five months; paid search converts in three weeks. A single global lag makes the whole funnel wrong for half the channels.
Won Value is marketing-sourced pipeline, not revenue. Reconcile it to the sales plan as a contribution percentage, never as the whole number. The fastest way to lose finance's trust is for marketing's model to claim 140% of the bookings plan.
Step 5: Reallocation and commitment tracking
The question that actually gets asked is: take 15% out of Q3, where does it come from and what does it cost us in pipeline?
You cannot answer that without knowing what is already committed. Add to SYS01:
Committed Amount -- input, from PO/agency contract feed
Commitment Type -- Contracted, PO Raised, Soft Commit, Uncommitted
Cancellation Cost % -- input (event deposits, media cancellation penalties)
Then CAL03 Reallocation Scenario:
Target Reduction % -- input by Region x BU x Time
Protected? -- boolean by Campaign (brand-critical, do not touch)
Available to Cut = IF Protected? THEN 0
ELSE MAX(0, Final Spend - Committed Amount)
Proposed Cut = number input
Cut Penalty = Proposed Cut * Cancellation Cost %
Net Saving = Proposed Cut - Cut Penalty
Pipeline Foregone = Proposed Cut / Effective CPL * (blended conversion) * Average Deal Size
Cost per Pipeline £ Lost = IF Pipeline Foregone = 0 THEN 0 ELSE Net Saving / Pipeline Foregone
That last line item is the one the CFO reads. Sort the campaign grid descending by it and you have a defensible cut list: the money that buys the least pipeline goes first. Build it as a UX page with a card for the target, a grid sorted by that ratio, and a chart of pipeline before and after.
Run this in a scenario, not in the live forecast. Either use a dedicated version or a small Scenarios list — three or four slots is plenty. Do not add a scenario dimension across the whole campaign model just for this.
Step 6: Actuals, commitments and accruals
Marketing spend actualizes late. An event in March gets invoiced in May. If your model only shows GL actuals, marketing looks 30% underspent all year and then blows the budget in Q4.
Load two feeds into the data hub and expose both. ACT01 Actuals and Commitments, by Campaigns x Time:
GL Actual -- imported, cost center + campaign tag
PO Committed -- imported from procurement
Invoiced Not Paid -- imported
Accrual = MAX(0, Expected to Date - GL Actual - Invoiced Not Paid)
Expected to Date = cumulative Final Spend up to the closed period
Total Recognised = GL Actual + Invoiced Not Paid + Accrual
Variance to Plan = Total Recognised - Expected to Date
Untagged Spend -- GL rows that arrived with no campaign ID
The Untagged Spend line item matters more than it looks. In every implementation I have seen, 5–15% of marketing GL rows arrive without a usable campaign identifier. Give it a visible home with an owner and a weekly clean-up page, or it silently pollutes every ROI number in the model. A simple UX page listing untagged rows with a campaign picker and an import-back action closes the loop in minutes a week.
For the forward periods, blend: use Total Recognised for closed months and Final Spend for open ones, driven off a single Current Period in a versions-and-time SYS module. Never let a planner edit the past.
Step 7: Reporting pages that get used
Three pages, and resist adding a fourth until someone asks.
- Budget Control (finance) — envelope vs. allocated vs. recognised by Region and Channel, with the
Over-allocated?flag, a waterfall from budget to current forecast, and drill-through to campaign. - Campaign Workbench (marketing planner) — the campaign grid filtered to the user's region via a selective-access-driven filter, with phasing, funnel output and commitment status side by side. This is where 90% of the input happens, so put everything a planner needs on one page and nothing they do not.
- Pipeline Contribution (CMO / CRO) — spend, pipeline, won value and ROI by channel, with prior forecast and actual overlaid, plus the marginal-ROI ranking from step 5.
Use Workflow for the monthly cycle: planner submits, marketing ops reviews, finance approves, model locks. The lock is the Envelope Locked? boolean and a DCA-driven read-only state on Manual Override — a real control, not a status field.
Testing before UAT
Build a small regression harness before you hand the model over. A few checks catch nearly everything:
- Total
Final Spendby Region equals totalAllocated to Campaignsin the envelope module, every period. - Sum of
Phasing Weightnormalised output equalsTotal Planned Costfor every active campaign, including ones whose window falls entirely outside the time range (should be zero, not an error). - Zero
Effective CPLand zeroAverage Deal Sizeproduce zeros, notDIV/0. - Shortening a campaign window redistributes rather than loses spend.
Total Recognisedin a closed period ties to the GL trial balance for the marketing cost centers, to the pound.
That last one is the one that gets checked in month three by someone who was not in the design workshops. Automate it.
Common design mistakes
- Dimensioning the campaign module by channel, region and BU as well as campaign. Campaign already implies all three. You have just built a sparse cube several orders of magnitude larger than the data in it. Put them in
SYS01as properties and aggregate withSUM. - Modelling attribution inside Anaplan. Multi-touch attribution belongs in your analytics stack. Anaplan consumes the resulting coefficients as inputs and plans with them. Keep the boundary clean.
- One global conversion rate. Channel-level rates are the minimum viable granularity; channel-by-region is usually right.
- No commitment data. Without it, every reallocation exercise is fiction, because half the money you "saved" was already contractually spent.
- Letting marketing's ROI number claim total bookings. Report contribution, reconcile to the sales plan, and say clearly what is sourced versus influenced.
Where this fits
A marketing spend model is a natural second or third spoke in an Anaplan estate: it consumes the revenue targets that already exist, feeds opex into the budgeting model, and contributes pipeline into the sales forecast. Built to the structure above, a first release is typically six to ten weeks depending on how clean the GL tagging is — and the GL tagging, not the modelling, is usually the critical path.
If you are scoping a marketing planning build, or you have a spreadsheet-bound marketing budget cycle you want to retire, get in touch. Our Anaplan model designing and building team has built this pattern across several industries and can tell you fairly quickly whether your data is ready.