Tabellab

Final Project: A Four-Week Marketing Dashboard with Channel Roll-Up, Blended MER and CAC, Target Status and a Combo Chart

Lesson 20 of 20 Lab 🔒 Locked

Lessons in this course

  1. Blended CAC and MER: Marketing Efficiency Ratio, Blended Customer Acquisition Cost and Acquisition MER from Monthly TotalsLabFree
  2. Channel Roll-Ups from Raw Exports: SUMIFS for Spend and Revenue, ROAS per Channel and Share of SpendLab Locked
  3. Cleaning Raw Exports: Clean Keys with LOWER and TRIM, a Channel Mapping Table with XLOOKUP and the Unmapped ShareLab Locked
  4. Targets on a Dashboard: Variance, Attainment for Higher- and Lower-Is-Better KPIs, Status Bands and a Highlight RuleLab Locked
  5. Pacing Against a Monthly Target: Days in Month with EOMONTH, Expected to Date, Pace, Run-Rate Projection and Required Daily RevenueLab Locked
  6. Week-over-Week Reporting: Conversion Rate, WoW Change, a Trailing 4-Week Average and the Deviation from Recent NormalLab Locked
  7. Sparklines and Conditional Formats: Trend per Channel, a Heat Map of Weekly ROAS and a Rule That Flags Falling ChannelsLab Locked
  8. A Scorecard Driven by One Selector Cell: XLOOKUP by Month, Previous Month with EDATE, MoM Change and Year-to-Date FiguresLab Locked
  9. Platform Numbers vs Reality: Over-Attribution Factor, Proportional Calibration and a Calibrated CAC per ChannelLab Locked
  10. Reading MER Honestly: New vs Returning Revenue, Break-Even MER and Contribution After MarketingLab Locked
  11. The Total Line Done Right: Weighted Conversion Rate and Order Value with SUMPRODUCT Instead of an Average of AveragesLab Locked
  12. Trend Forecasts for a Dashboard: SLOPE, FORECAST.LINEAR, R² and the Projected Attainment of a Quarter TargetLab Locked
  13. Is Today Unusual? Mean, Standard Deviation, z-Scores and a Row-Highlight Rule for Daily CPALab Locked
  14. Explaining the Change: Top Movers with RANK.EQ, LARGE and XLOOKUP and the Net Change of All CampaignsLab Locked
  15. Data Quality Checks: Row Keys with TEXT, Duplicate Highlighting, De-Duplicated Totals and Reconciliation with FinanceLab Locked
  16. Is the CAC Worth It? Cohort Payback Month, Six-Month Margin to CAC and a Payback Staircase with a Formula RuleLab Locked
  17. What-If Grid for a Bigger Budget: New MER by Extra Spend and Marginal ROAS, Mixed References and the Break-Even Marginal ROASLab Locked
  18. Budget Reallocation: Average vs Marginal ROAS, Giving and Receiving Channels with MIN, MAX and XLOOKUP and the New Blended MERLab Locked
  19. The Weekly Report Narrative: Sentences from Numbers with TEXT, a Direction Word with a Flat Band and a Headline CountLab Locked
  20. Final Project: A Four-Week Marketing Dashboard with Channel Roll-Up, Blended MER and CAC, Target Status and a Combo ChartLab Locked