Concept
1. Data (resources/marketing) — FY2025-26, online store
| File | Rows | Columns |
|---|---|---|
marketing_channels.csv |
60 | Month, Channel, Spend, Impressions, Clicks, Leads, Customers, Revenue |
marketing_cohorts.csv |
6 | Cohort (month of first purchase), New Customers, M0 … M6 (share still buying) |
marketing_campaigns.csv |
8 | Campaign, Channel, Spend, Leads, Customers, Revenue |
Channels: Google Ads, Meta Ads, Influencers, Email, SEO/Organic (SEO "spend" = agency + content cost). Load as tblMkt, tblCohort, tblCamp.
2. Marketing KPI definitions (put these on README)
| KPI | Formula | Meaning |
|---|---|---|
| CTR | Clicks ÷ Impressions | ad attractiveness |
| CPL | Spend ÷ Leads | cost per lead |
| Conversion | Customers ÷ Leads | sales effectiveness |
| CAC | Spend ÷ New customers | cost to acquire one customer |
| ROAS | Revenue ÷ Spend | ₹ revenue per ₹1 of spend |
| Break-even ROAS | 1 ÷ Gross margin % | below this, ads lose money |
With a 30% gross margin, break-even ROAS = 1 ÷ 0.30 = 3.33. ROAS is revenue-based; a ROAS of 2.5 can still lose money.
3. Inputs and filtered totals (CALC)
inp_Channel (All + list), inp_From, inp_To (month dropdowns), k_Ch = IF(inp_Channel="All","*",inp_Channel).
=LET(f, (tblMkt[Month]>=inp_From) * (tblMkt[Month]<=inp_To) * ((tblMkt[Channel]=inp_Channel)+(inp_Channel="All")>0),
HSTACK(SUM(FILTER(tblMkt[Spend], f, 0)), SUM(FILTER(tblMkt[Impressions], f, 0)), SUM(FILTER(tblMkt[Clicks], f, 0)),
SUM(FILTER(tblMkt[Leads], f, 0)), SUM(FILTER(tblMkt[Customers], f, 0)), SUM(FILTER(tblMkt[Revenue], f, 0))))
One row: Spend, Impressions, Clicks, Leads, Customers, Revenue. KPIs are simple ratios of these cells.
(Month in the CSV is "2025-04" text — convert it to a real month-start date in Power Query and use the same date type for both dropdowns.)
Filter scope: Channel and month-range inputs filter tblMkt KPIs and the funnel. The channel comparison keeps all channels over the selected months and is labelled accordingly. Cohorts have no Channel field: show them as an all-channel cohort panel, separate from these filters. Campaigns have no Month field: Channel can filter the campaign table, but label it “Campaign-period totals — month filter does not apply”. Campaigns are not an additive breakdown of tblMkt; do not force their totals to reconcile. Reject From > To, use consistent real month dates, and display “No data” for empty selections with blank ratios.
4. KPI cards (FY2025-26, all channels)
| Card | Value | vs target |
|---|---|---|
| Spend | ₹264.2 L | — |
| Revenue | ₹1,075.1 L | — |
| ROAS | 4.07 | target 4.0 ✓ · break-even 3.33 |
| CAC | ₹1,159 | target ₹1,200 ✓ |
| New customers | 22,782 | — |
Show ROAS with one decimal and an "x" (4.1x); CAC in rupees without decimals.
5. Funnel chart
| Stage | Count | Step conversion |
|---|---|---|
| Clicks | 35,99,307 | CTR 2.15% of 16.7 cr impressions |
| Leads | 2,68,649 | 7.5% of clicks |
| Customers | 22,782 | 8.5% of leads |
Insert → Funnel (Microsoft 365/2019+) on Stage/Count. Impressions are 46× clicks, so they would squash the funnel — show them as a KPI or subtitle instead. Put step conversions as labels next to each bar (helper column). The biggest leak is click → lead (92.5% drop).
6. Channel comparison
| Channel | Spend (₹ L) | ROAS | CAC (₹) | CPL (₹) |
|---|---|---|---|---|
| SEO/Organic | 18.0 | 9.77 | 522 | 53 |
| 9.0 | 9.62 | 490 | 67 | |
| Google Ads | 78.9 | 6.13 | 845 | 77 |
| Meta Ads | 105.8 | 2.38 | 1,632 | 113 |
| Influencers | 52.5 | 1.47 | 3,126 | 217 |
Chart: ROAS by channel as a sorted bar with a break-even line at 3.33 (Analysis & Visualization, Module 5 Lesson 3 "average line" technique, primary axis). Meta and Influencers sit below the line: they get 60% of spend (158.3 of 264.2 L) but return below break-even. Bubble chart alternative: x = Spend, y = ROAS, bubble = Customers.
Insight title: ="Meta + Influencers: "&TEXT(share,"0%")&" of spend, below break-even ROAS".
7. Cohort retention grid
| Cohort | New | M0 | M1 | M2 | M3 | M4 | M5 | M6 |
|---|---|---|---|---|---|---|---|---|
| Apr-25 | 1,698 | 100% | 31.0% | 25.0% | 20.6% | 18.4% | 15.7% | 15.2% |
| … | ||||||||
| Sep-25 | 1,789 | 100% | 37.8% | 26.9% | 23.2% | 19.7% | 18.2% | 17.1% |
Read it: each row = customers who first bought in that month; each column = months later; value = share who bought again. Format with a white → accent colour scale on M1:M6 (exclude M0). Newer cohorts retain better in month 1 (31% → 38%) — this is consistent with an improvement, but the cohort data alone cannot establish that an email flow caused it.
If you have customer-level data instead, deduplicate to one row per CustomerID and purchase month, then count customers by first-purchase month and MonthsSince. A pivot of Cohort × MonthsSince with Distinct Count in the Data Model also avoids counting repeat orders as different customers.
8. Campaign table
Sorted by ROAS with data bars and an icon rule (green ≥ 4, amber ≥ 3.33, red below):
| Campaign | Channel | Spend (₹ L) | Customers | CAC (₹) | ROAS |
|---|---|---|---|---|---|
| Blog: Buying Guide | SEO/Organic | 4.1 | 660 | 627 | 11.3 |
| VIP Newsletter | 1.3 | 315 | 419 | 8.5 | |
| Diwali Search | Google Ads | 21.8 | 2,490 | 876 | 5.1 |
| Wedding Season | Google Ads | 20.0 | 2,600 | 767 | 5.1 |
| Monsoon Offers | Meta Ads | 23.4 | 1,711 | 1,369 | 2.2 |
| Retargeting Q3 | Meta Ads | 14.6 | 740 | 1,972 | 2.1 |
| Diwali Mega Sale | Meta Ads | 23.2 | 1,368 | 1,693 | 2.1 |
| Influencer Unboxing | Influencers | 7.4 | 189 | 3,905 | 1.5 |
9. Layout
┌ Marketing Dashboard — FY2025-26 | All channels [Channel ▼] [From ▼] [To ▼] ┐
│ [Spend] [Revenue] [ROAS 4.1x] [CAC ₹1,159] [New customers] │
│ [Funnel + step %] │ [ROAS by channel + break-even line] │
│ [Cohort retention grid] │ [Campaign table, sorted by ROAS] │
└──────────────────────────────────────────────────────────────────────────────────┘
The 3.33 break-even line assumes a uniform 30% gross margin and excludes other acquisition, service and overhead costs. Treat budget changes as hypotheses to test; attributed revenue is not proof of incremental revenue.
10. Recommendations the dashboard should support
- Shift budget from Influencers (ROAS 1.5) and the weakest Meta campaigns to Google Search and Email/SEO content.
- Fix the click → lead step (landing pages, offer clarity) — the largest drop in the funnel.
- Test the post-purchase flow hypothesis with a controlled comparison; month-1 retention is about 7 points higher, but cause is unproven.
Common mistakes
Celebrating ROAS > 1 (break-even depends on margin). Funnel with impressions squashing everything. Summing percentages across channels instead of recalculating from totals. Cohort grid coloured including M0 (100% dominates the scale).