21.7 Marketing Campaign Performance
The ask. Shahrukh runs local ads for a fictional quick-commerce brand. Before next month's budget he needs CTR, cost per click, cost per order (CPA) and ROAS for each campaign, ranked by ROAS, with a one-line recommendation.
Sheet Campaigns, A1:G5 (fictional):
| A: Campaign | B: City | C: Impressions | D: Clicks | E: Spend (₹) | F: Orders | G: Sales (₹) |
|---|---|---|---|---|---|---|
| Diwali Pune | Pune | 1,20,000 | 3,000 | 24,000 | 300 | 96,000 |
| Monsoon Nashik | Nashik | 60,000 | 1,500 | 9,000 | 150 | 54,000 |
| Ganesh Kolhapur | Kolhapur | 80,000 | 1,200 | 12,000 | 40 | 16,000 |
| Weekend Nagpur | Nagpur | 50,000 | 1,000 | 6,000 | 90 | 27,000 |
What to deliver (one under the other):
- Make the range a Table named
tblAds. - Add CTR = Clicks ÷ Impressions.
- Add CPC = Spend ÷ Clicks.
- Add CPA = Spend ÷ Orders.
- Add ROAS = Sales ÷ Spend (2 decimals).
- Rank by ROAS and write the recommendation: keep, test more, or pause (ROAS below 2).
Key formulas (row 2; copy down or let the Table fill them):
H2: =[@Clicks]/[@Impressions]
I2: =[@[Spend (₹)]]/[@Clicks]
J2: =[@[Spend (₹)]]/[@Orders]
K2: =ROUND([@[Sales (₹)]]/[@[Spend (₹)]],2)
L2: =RANK.EQ([@ROAS],[ROAS],0)
M2: =IF([@ROAS]<2,"Pause",IF([@Rank]=1,"Keep","Test more"))
Expected result (check your numbers):
| Campaign | CTR | CPC (₹) | CPA (₹) | ROAS | Rank | Action |
|---|---|---|---|---|---|---|
| Diwali Pune | 2.50% | 8.00 | 80.00 | 4.00 | 3 | Test more |
| Monsoon Nashik | 2.50% | 6.00 | 60.00 | 6.00 | 1 | Keep |
| Ganesh Kolhapur | 1.50% | 10.00 | 300.00 | 1.33 | 4 | Pause |
| Weekend Nagpur | 2.00% | 6.00 | 66.67 | 4.50 | 2 | Test more |
All campaigns: spend ₹51,000, sales ₹1,93,000, overall ROAS = 3.78. Diwali Pune has the most impressions but not the best ROAS.
Checks before you send it:
- Overall ROAS is total sales ÷ total spend, not the average ROAS.
- CTR is shown as % with 2 decimals (it is small).
- Ganesh Kolhapur is marked Pause, with the reason (ROAS below 2).
- All numbers are labelled fictional on the sheet.
Ravindra Bagale's Tip
Impressions are the easiest number to grow and the easiest to waste. Judge a campaign by CPA and ROAS from the same rows.
Ravindra Bagale's Tip – मराठी
Impressions हा वाढवायला सगळ्यात सोपा आणि वाया घालवायलाही सगळ्यात सोपा आकडा आहे. Campaign ला त्याच rows मधल्या CPA आणि ROAS वरून judge करा.
Ravindra Bagale's Tip – हिंदी
Impressions बढ़ाने में सबसे आसान और बर्बाद करने में भी सबसे आसान आँकड़ा है. Campaign को उन्हीं rows के CPA और ROAS से judge करें.
Loading practice tasks...
- Total clicks (Easy): Add up Clicks in D2:D11.
- Average spend (Easy): Average of E2:E11, rounded to 2 decimals.
- Rows in Google (Easy): How many rows have Platform "Google"?
- Largest clicks (Easy): The largest value in D2:D11.
- Clicks for Google (Medium): Total of D2:D11 where B is "Google".
- Google in Milan (Medium): Total of D2:D11 where B is "Google" and C is "Milan".
- Best in Google (Medium): Largest E2:E11 value where B is "Google".
- Value of row 2 (Medium): D2 times E2.
- Look up Ananya Nair (Medium): The Clicks for "Ananya Nair" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Google (Hard): Share of D2:D11 that is "Google", rounded to 3 decimals.
- Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".