ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Digital Marketing Media Planning & Conversion Model
2
3
PurposeThis workbook plans a multi-channel digital media budget and forecasts the full funnel (impressions -> clicks -> leads -> sales -> revenue -> profit -> ROI) for each channel, so you can compare channel efficiency and re-allocate budget with confidence.
4
5
How to use1) Open 'Assumptions' and enter your Overall Budget, AOV, Gross Margin and channel-level rates (CPM/CPC, CTR, conversion rates) in BLUE cells only.
2) 'Media Plan' converts spend into impressions and clicks automatically.
3) 'Funnel & P&L' converts clicks into leads, sales, revenue, cost, profit and ROI.
4) 'Dashboard' visualizes budget mix, funnel shape and channel ROI/CPA.
5) 'Scenario Compare' lets you test up to 3 budget-mix scenarios side by side.
Only change BLUE cells. Black cells are formulas; do not overwrite them.
6
7
Color codeBLUE text = input you can change. BLACK text = formula (do not edit). GREEN text = link pulled from another sheet. YELLOW fill = key assumption to review.
8
9
Funnel logicMedia Spend -> Impressions (CPM channels) or Clicks (CPC channels) -> Clicks (via CTR) -> Leads (via Lead Conversion Rate) -> Sales (via Sale Conversion Rate) -> Revenue (Sales x AOV) -> Gross Profit (Revenue x Margin) -> Net Profit (Gross Profit - Total Cost) -> ROI (Net Profit / Total Cost).
10
11
Channels modeledPaid Social (CPM), Display Ad Network (CPM), Paid Search (CPC), Organic Search/SEO, Affiliate (CPC), Aggregator/Marketplace (CPC) - edit channel names in Assumptions row 9 if your media mix differs.
12
13
Companion guideSee the accompanying Word document 'Digital_Marketing_Media_Planning_Guideline.docx' for the full methodology, formulas explained, worked example and best practices.
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100