35. More Real-World Project Briefs
35.7 Marketing Campaign
Business question. Which fictional campaign brought orders, and which city only spent?
Tables. Spend (Date, City, Campaign, Amount). Orders attributed with Campaign ID (Date, City, Campaign, Order ID, Sales Amount). Campaign names you invent as labels, for example "Diwali Pune" or "Monsoon Nagpur". They are labels, not real ads. Date.
Measures.
Ad Spend = SUM(Spend[Amount])
Campaign Sales = SUM(Orders[Sales Amount])
Orders = DISTINCTCOUNT(Orders[Order ID])
Cost per Order = DIVIDE([Ad Spend], [Orders])
There is no honest return-on-ad-spend until Spend and Orders share campaign, city and date grain. If a campaign is missing on the order file, do not allocate spend by guesswork inside DAX.
Pages. A table: Campaign, Spend, Orders, Cost per Order, Sales. A line of spend by week. A slicer for city. No "ROI %" card if orders are not attributed. Leave the card out. That is a better report.
Sample layout — fictional data. The picture uses the worked rows in this brief. It does not add a table.
Worked rows (fictional sample data). Every order below has a campaign. Names are labels, not real ads.
| Campaign | City | Spend | Orders | Sales |
|---|---|---|---|---|
| Diwali Pune | Pune | ₹10,000 | 50 | ₹40,000 |
| Monsoon Nashik | Nashik | ₹4,000 | 40 | ₹20,000 |
| Ganesh Kolhapur | Kolhapur | ₹8,000 | 10 | ₹6,000 |
| Weekend Nagpur | Nagpur | ₹2,000 | 20 | ₹10,000 |
| Clearance Mumbai | Mumbai | ₹1,000 | 10 | ₹5,000 |
| New user Sambhaji | Sambhaji Nagar | ₹6,000 | 5 | ₹2,000 |
Spend is ₹31,000. Orders are 135. Sales are ₹83,000. Cost per order is spend divided by orders. 31,000 ÷ 135 = ₹230 (229.63, nearest rupee ₹230). Sales divided by spend is 83,000 ÷ 31,000 = 2.68. That second number is only fair because every row is attributed. Do not call it ROI if you later add spend with no orders.
Monsoon Nashik: 4,000 ÷ 40 = ₹100 per order, and 20,000 ÷ 4,000 = 5.00 sales per rupee of spend. Ganesh Kolhapur: 8,000 ÷ 10 = ₹800 per order, and 6,000 ÷ 8,000 = 0.75.
Starter file: brief_35_7_campaigns.csv – the worked rows above as a CSV (fictional practice data). Load it with Get data › Text/CSV, build the measures, then check your cards.
Expected values to check
Spend ₹31,000 · Orders 135 · Sales ₹83,000 · Cost per order ₹230 (229.63) · Sales ÷ spend 2.68
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Nashik ₹100 per order | Diwali spend ₹10,000, the largest budget |
| Why | Nashik returned ₹5 of sales per ₹1. | Diwali looks like the main campaign because it spent the most. Ganesh spent ₹8,000 and came back at 75 paise per rupee. |
What the manager does. The Nashik buyer keeps Monsoon. The Pune buyer may keep Diwali (40,000 ÷ 10,000 = 4.00) and still must not judge it by the size of the spend. The Kolhapur buyer pauses Ganesh in the practice file. Sambhaji Nagar is 2,000 ÷ 6,000 = 0.33. Pause that too. Small Mumbai is allowed to win: 5,000 ÷ 1,000 = 5.00.
Ravindra Bagale's Tip
Many students divide sales by spend and call it ROI when half the orders have a blank campaign. Label the blank campaign as Others, and do not join it to spend and call it ROAS. Remember this rule.
Ravindra Bagale's Tip – मराठी
बरेच students निम्म्या orders चा campaign blank असताना sales ला spend ने भागतात आणि त्याला ROI म्हणतात. Blank campaign ला Others असं label करा, आणि त्याला spend शी जोडून ROAS म्हणू नका. हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
बहुत से students आधे orders का campaign blank होने पर भी sales को spend से भाग देकर उसे ROI कहते हैं. Blank campaign को Others label करो, और उसे spend से जोड़कर ROAS मत कहो. यह नियम याद रखो.