Guided project · 6 steps Cellwise Plus
Marketing campaign ROI in Excel
Measure cost per lead and ROI by channel, pick the winner, and list the channels losing money.
You will use: ROUND, IFERROR, SUM, MAX, XLOOKUP, SUMIF, FILTER
Starting data
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Channel | Spend | Leads | Customers | Revenue | Cost/lead | ROI | Overall ROI | |
| 2 | Google Ads | 4200 | 168 | 21 | 9870 | Best channel | |||
| 3 | 3100 | 205 | 14 | 5320 | Spend at ROI > 1 | ||||
| 4 | 600 | 92 | 18 | 7380 | |||||
| 5 | 2800 | 41 | 5 | 2450 | Losing money | ||||
| 6 | TikTok test | 900 | 0 | 0 | 0 | ||||
| 7 | Referral | 1200 | 60 | 15 | 8100 |
Build it step by step
Step 1
Cost per lead is spend divided by leads, rounded to cents. One channel got no leads, so show "No leads" instead of an error. Write it in F2, fill to F7.
Step 2
ROI is revenue minus spend, divided by spend. Round to 3 decimals (1.35 means 135%). Write it in G2, fill to G7.
Step 3
What is the ROI of the whole campaign? Use total revenue and total spend, rounded to 3 decimals, in J1.
Step 4
Which channel has the best ROI? Put its name in J2.
Step 5
How much was spent on channels with ROI above 1 (more than doubling their money)? Put it in J3.
Step 6
List the channels losing money (ROI below 0), starting in I6.