Why this matters
The advanced marketing analytics level forecast spend from response curves and scenarios. Here the outcome is a contract signed months after the enquiry, and the cost includes a fee, tools and people that serve every channel. The guide to valuing a lead by pipeline stage turns the contract into a value today; the guide to allocating shared marketing costs shares the overheads out. On a channel report the two sit side by side and do opposite jobs: a lead value can move money, an allocation only describes a burden.
The pest control firm and its pipeline
Tazquorin Pest Control is an invented firm selling annual pest control contracts to food businesses. An enquiry becomes a site survey, then a proposal and, sometimes, a contract. The finance director wants every channel report to show a full cost and a value per lead that bids can use. The agency wants paid social's budget moved into paid search, because social barely breaks even once shared costs are added.
Tazquorin Pest Control: closed cohorts, last year's channels and shared costs
Leads created two years ago, all now won or lost. Contribution is the contract price less technician visits, materials and travel, as agreed with finance.
| Segment | Enquiries | Surveys | Proposals | Contracts | First-year contribution per contract |
|---|---|---|---|---|---|
| Restaurants and cafés | 600 | 300 | 200 | 90 | £1,500 |
| Manufacturers and warehouses | 120 | 84 | 60 | 24 | £6,000 |
Restaurants usually sign within 6 weeks, manufacturers within 16. Inside each segment, channels closed at similar rates. This year's manufacturer proposals are converting more slowly than the closed cohort's did.
| Channel last year | Direct cost | Restaurant enquiries | Manufacturer enquiries |
|---|---|---|---|
| Paid search | £42,000 media | 380 | 40 |
| Paid social | £18,000 media | 160 | 10 |
| Trade press | £12,000 advertising | 20 | 40 |
| Trade shows | £15,000 stands and travel | 10 | 30 |
| Shared cost | Amount | Evidence available |
|---|---|---|
| Agency fee, search and social | £24,000 | Timesheets: search 60%, social 40% |
| Call tracking and reporting tools | £9,600 | Tracked lines: search 4, social 2, press 4, shows 2 |
| Marketing manager and coordinator | £60,000 | Time logs: search 20%, social 20%, press 20%, shows 40% |
| Photo and video shoot | £12,000 | Assets used: social 50%, press 25%, shows 25% |
Values that decide, allocations that report
A stage value is the contract's margin times the proportion of leads reaching that stage that go on to sign, read from cohorts old enough to have finished. From the enquiry that share is the Lead-to-Customer RateShare of leads created in a period that became paying customers.; from the proposal it is the Win RateShare of closed sales opportunities that were won.. Each enquiry carries the source captured on its form; leads touching several channels would need a rule-based model like the one in the tutorial on building an attribution model in SQL.
Allocated costs differ in kind: they do not change when one channel's budget does, so they belong in the report and never in the comparison that moves money.
Task
- Value each stage for each segment from Contribution MarginRevenue left after every cost that varies with each sale. and the closed cohorts, and compare with one blended value.
- Say how lag and this year's open cohort are handled, and write the refresh rule.
- Choose a base for each shared cost, with a reason, and allocate last year's shared costs.
- Build last year's channel report with expected contribution, direct cost and allocated cost apart.
- Answer the agency and the finance director: which figure decides spend and bids, and which only reports.
Worked answer
Tazquorin's stage values and channel report
| Stage | Restaurants and cafés | Manufacturers and warehouses |
|---|---|---|
| Enquiry | 90 ÷ 600 = 15%, so £225 | 24 ÷ 120 = 20%, so £1,200 |
| Survey | 90 ÷ 300 = 30%, so £450 | 24 ÷ 84 ≈ 28.6%, so £1,714 |
| Proposal | 90 ÷ 200 = 45%, so £675 | 24 ÷ 60 = 40%, so £2,400 |
| Contract | £1,500 | £6,000 |
One blended enquiry value would be £279,000 ÷ 720 = £387.50: too high for restaurants, under a third of a manufacturer enquiry's worth.
| Channel | Expected contribution | Direct cost | After direct | Allocated | After allocated |
|---|---|---|---|---|---|
| Paid search | £133,500 | £42,000 | £91,500 | £29,600 | £61,900 |
| Paid social | £48,000 | £18,000 | £30,000 | £29,200 | £800 |
| Trade press | £52,500 | £12,000 | £40,500 | £18,200 | £22,300 |
| Trade shows | £38,250 | £15,000 | £23,250 | £28,600 | −£5,350 |
Allocated (fee, tools, people, shoot): search £14,400, £3,200, £12,000; social £9,600, £1,600, £12,000, £6,000; press £3,200, £12,000, £3,000; shows £1,600, £24,000, £3,000. Total £105,600, tying to the ledger.
Values. Two segments, because margins and close rates differ widely and the counts allow it; channels are pooled inside each, having closed alike. Manufacturer values rest on few contracts, so they are labelled provisional.
Lag and refresh. The cohort is old because manufacturers take months to sign. This year's slower proposals come from an open cohort, so they only flag that the value may be moving, checked once the cohort closes. Values are refreshed each January from the latest closed cohort, and whenever prices, margin or stage definitions change, with the inputs recorded and old and new values compared before any bid changes.
Bases. The fee follows the agency's timesheets, because it buys effort; tools follow tracked lines; people follow time logs; the shoot follows assets used. Share of spend would make trade shows look cheap although they take the largest share of the team's time; leads as a base would load cost onto paid search for succeeding. The method goes into the marketing P&L template with each cost's Cost CentreA code finance uses to group costs by the part of the business responsible for them; marketing's total is what has been booked to its cost centres., agreed with finance and kept stable.
Which figure decides. Bids receive the segment stage values from the CRM. Budget moves compare the next pound's expected contribution with its direct cost, not allocations. The agency's case rests on paid social's allocated column, yet those costs stay wherever the money goes and social earns well above its own costs; a move needs evidence on marginal returns. Trade shows are negative only after allocation: cut them and their contribution goes while the team's time lands elsewhere. Whether that time is well spent is separate. The finance director gets a fully loaded cost per lead in reports, labelled, beside the direct figure, and never in bids or targets.
Common mistakes
- One value for every enquiry. Manufacturers underpriced, restaurants overpriced.
- Close rates from open cohorts. Unfinished deals make every rate look low.
- Revenue instead of contribution. Every ceiling becomes too generous.
- Spend share for people costs. Time-hungry channels look cheap.
- Dropping a channel because of its allocated column. The fee, tools and team remain; only its contribution goes.
- Fully loaded cost in bids. Bids react to costs no bid can change.
The level's capstone sets these rules beside a data model, a reviewed mix model and attribution in SQL.
