
Power BI · DAX · Python (data generation)
Overview. A two-page Power BI dashboard on marketing spend, revenue and return across six channels.
Business problem. Lumen Outdoor Co (a fictional outdoor-gear brand) spent $2.66M a year across six channels. Every Monday the team stitched together reports from three ad platforms by hand, so it could not see which campaigns made money.
Dataset. Synthetic data: 8,030 daily records for January to December 2024, by date, channel and campaign (spend, impressions, clicks, conversions, revenue).
Method. I built a date table and DAX measures for spend, revenue, blended ROAS and CAC. Page 1 is an overview: 5 KPI cards, spend against revenue over time, spend by channel and ROAS by channel. Page 2 is a channel deep dive: a campaign table colored by ROAS, a CAC trend and a treemap of top campaigns. I added a custom sidebar for navigation.
Key findings
- Revenue was $8.71M on $2.66M of spend: a blended ROAS of 3.27 and a blended CAC of $28.03.
- Organic (ROAS 11.93) and Email (8.49) were the most efficient channels.
- TikTok was the weakest at 1.81, with 9.2% of the budget (about $250K).
- CAC spiked in December.
- The “Direct Traffic” campaign earned $190K from $16K of spend.
Recommendations
- Move budget from TikTok to Email and Organic. The case study estimates about $1.06M of extra revenue if half of TikTok’s budget (about $125K) moved to Email.
Result. One dashboard shows spend, return and CAC by channel and campaign.