FreshBox Retention Dashboard — Data Analyst Capstone — SkilsMVP

What you'll produce

A focused, one-screen dashboard spec for FreshBox leadership — a chart-by-chart wireframe where every element earns its place: one headline number, an MRR trend line, a churn-by-segment bar chart, and the exact metric definition and SQL source behind each. This is the artifact that lets the Head of Operations and the rest of leadership self-serve the four retention numbers that matter every Monday morning, instead of pinging you for a fresh export. It proves the Topic 9 skills that separate an analyst from a chart-maker: matching the chart to the question (line for time, bar for categories, big number for a single metric), keeping every axis honest and zero-based, ruthlessly cutting clutter, and ordering the screen so the most important answer is at the top-left where the eye lands first. A dashboard is also where your earlier work becomes durable: the metric definitions from your brief (Deliverable 1), the cleaned data (Deliverable 3), and the segment story you uncovered (Deliverable 4) get wired into something leadership uses long after your investigation ends. A cluttered dashboard with fifty charts gets ignored; the clean one with the five numbers leadership actually watches becomes indispensable — and that is a deliverable worth paying for.

Instructions

Re-state the one decision the dashboard serves. Write it at the top of your spec in one sentence (e.g. "Should we fund a retention fix before next quarter's planning, and for which segment?"). A dashboard with no decision behind it becomes a wall of charts. Everything you add must help answer this; if a chart doesn't, cut it.
Pick the single headline number. Decide the one metric leadership should see first, biggest, top-left. For a retention investigation this is almost always monthly churn rate (or its mirror, retention rate). Write the metric, its current value, its prior-period value, and the direction arrow. Resist the urge to make three numbers "headline" — pick one.
Lock each metric to a precise definition and a source query. For every tile, write three lines: Metric (the plain-English number), Definition (the exact formula — carry it verbatim from your Deliverable 1 brief so the dashboard and brief never disagree), and Source (which Deliverable 2 query or cleaned table feeds it). This is what makes the dashboard trustworthy and reproducible — anyone can trace a number back to its SQL. "Verbatim" means copy-paste, not paraphrase. The sneakiest dashboard bug is a definition that reads plausible but quietly adds, drops, or rewords a clause the brief never had — e.g. tightening "active = status active, no cancellation as of month-end" into "active = status active and ordered in the last 30 days," dropping the brief's "net of discounts" clause from MRR, or labeling a tile "churn by plan" and then mixing the Monthly/Annual cadence with the Basic/Family/Premium tier — two dimensions the brief deliberately keeps separate — into one bar chart that can't be reconciled. The number on the tile then no longer matches the SQL that produced it, and the one tile whose job is traceability becomes the one nobody can trace. Before you ship, diff each Definition line against the brief word-for-word, and confirm the Source query's WHERE/CASE logic actually enforces that exact wording — if the SQL and your Definition disagree, the Definition is wrong.
Match each chart to its question, using the Topic 9 rules:
- Change over time (MRR by month, churn by month) → line chart, time on the x-axis.
- Comparing categories (churn by billing cadence, by plan tier, by tenure, by country) → bar chart, sorted worst-to-best so the problem segment reads first.
- A single key number (overall churn, MRR) → big number tile, no chart.
- Avoid pie charts; never use a second y-axis or a 3-D effect. When unsure, a bar or line chart is the honest default.
Draw the wireframe as a labeled grid. Sketch the actual screen layout in text or boxes — a 2-column, 2–3 row grid is plenty. Place the headline top-left, the trend that gives it context top-right, the segment breakdown that explains where the problem lives on the second row. State the reading order explicitly: top-left first, because that's where the eye lands.
Make every axis honest. Explicitly note: bar charts start at zero (a truncated axis is the most common way a chart lies). Label every chart with a title, axis labels, and units (%, $, count). Add a one-line "what good looks like" or target line where it helps leadership judge a number without you in the room.
Add the small print that keeps it honest: a "Data through [date]" timestamp, a refresh cadence (e.g. weekly), the definition of any non-obvious segment, and a one-line caveat carried from your findings (e.g. "churn excludes involuntary payment-failure cancels — tracked separately"). A dashboard without a freshness date silently rots.
Cut until it hurts, then stop. Aim for 4–6 tiles on one screen. For every chart you're tempted to add, ask "which decision does this change?" If the answer is "none," it's a vanity chart — leave it out. The discipline of subtraction is the skill being graded.

Worked example

(FreshBox — meal-kit subscription, ~40,000 active subscribers. Decision deadline: next quarter's planning meeting. Numbers below are carried from the cleaned dataset and the Deliverable 4 findings memo.)

Decision this serves: Should leadership fund a retention intervention before Q3 planning, and which subscriber segment should it target first?

Audience & cadence: Head of Operations + exec staff; self-serve, refreshed every Monday 8am from the warehouse.

Wireframe (one screen, 2 columns × 3 rows)

┌───────────────────────────────────────┬───────────────────────────────────────┐
│  TILE 1 — HEADLINE (big number)        │  TILE 2 — MRR TREND (line)            │
│                                        │                                        │
│   Monthly churn rate                   │   $  Monthly Recurring Revenue        │
│        4.8%   ▲ +1.3 pts               │   1.04M ┐                             │
│   vs 3.5% six months ago (Sep '25)     │        │‾‾‾‾‾‾‾‾‾\___ flat / soft     │
│   (target: ≤ 3.0%)                     │   0 ───┴──────────────────────► month │
│                                        │        Oct ……………………………… Mar           │
├───────────────────────────────────────┼───────────────────────────────────────┤
│  TILE 3 — CHURN BY TENURE (bar)        │  TILE 4 — CHURN BY CADENCE (bar)      │
│                                        │                                        │
│  0–3 mo  ████████████ 9.1%             │  Monthly  ███████████ 6.2%            │
│  4–6 mo  ██████ 4.4%                   │  Annual   ██ 1.1%                     │
│  7–12 mo ███ 2.6%                      │                                        │
│  12+ mo  ██ 1.9%                       │  (axis starts at 0%)                  │
│  (axis starts at 0%, sorted worst→best)│                                        │
├───────────────────────────────────────┴───────────────────────────────────────┤
│  TILE 5 — CHURN BY MONTH (line, context for the headline)                      │
│   6% ┐                          ___/‾‾  rising since Jan                        │
│   0 ─┴───────────────────────────────────────────────────────► Oct … Mar       │
├─────────────────────────────────────────────────────────────────────────────────┤
│  Data through Mar 31, 2026 · refreshed weekly · churn = voluntary cancels only │
└─────────────────────────────────────────────────────────────────────────────────┘

Reading order (stated for the viewer): start top-left (Tile 1 — the answer), read right for the revenue context (Tile 2), then drop to row 2 to see where the churn lives (Tiles 3–4), and use Tile 5 only if someone asks "since when?"

Tile-by-tile spec

Tile 1 — Headline: Monthly churn rate (big number)

Metric: Monthly churn rate, current month = 4.8%, up from 3.5% six months ago (▲ +1.3 pts). Target line: ≤ 3.0%.
Definition (verbatim from brief): churned subscribers in month ÷ active subscribers at start of month. "Active" = a subscription with status active and no cancellation as of month-end. Counts voluntary cancellations only.
Source: Deliverable 2, Query 3 (GROUP BY month churn rate) on the cleaned subscriptions + cancellations tables.
Chart choice: single key number → big-number tile, no chart. Direction arrow + prior value + target give it meaning at a glance.
Honesty note: show the comparison ("vs 3.5% six months ago") so 4.8% isn't read in a vacuum.

Tile 2 — MRR trend (line chart)

Metric: Monthly Recurring Revenue, last 6 months (Oct 2025 → Mar 2026). Currently $1.04M as of March, essentially flat (peaked $1.05M in Dec, $1.04M in Mar).
Definition (verbatim from brief): sum of each active subscription's monthly-equivalent plan price at month-end — Monthly-cadence subs at their monthly price, Annual-cadence subs at plan_price ÷ 12 — net of discounts. Before refunds.
Source: Deliverable 2, Query 4 (monthly MRR trend).
Chart choice: change over time → line chart, month on x-axis. This is the metric that triggered the investigation ("MRR has flattened even though marketing keeps acquiring") — it belongs top-right as context for the churn headline: acquisition is fine, the leak is retention.
Honesty note: y-axis labeled in $, gridline at $0; do not truncate to make the flattening look like a cliff — the flatness is the story.

Tile 3 — Churn by tenure (bar chart) — the most important tile

Metric: Monthly churn rate split by how long the subscriber has been with FreshBox. 0–3 mo: 9.1%, 4–6 mo: 4.4%, 7–12 mo: 2.6%, 12+ mo: 1.9%.
Definition: same churn formula as Tile 1, segmented by tenure bucket (months since subscription start).
Source: Deliverable 2, Query 2 (JOIN customers ↔ subscriptions, GROUP BY tenure_bucket).
Chart choice: comparing categories → bar chart, sorted worst-to-best so the 9.1% new-subscriber bar reads first. This is the punchline of the whole investigation: the leak is concentrated in the first 90 days.
Honesty note: x-axis starts at 0%; bars labeled with exact %. Caption: "New subscribers churn ~5× faster than tenured ones."

Tile 4 — Churn by billing cadence (bar chart)

Metric: Monthly churn by billing cadence (Monthly vs Annual, the brief's cadence dimension — not plan tier). Monthly cadence: 6.2%, Annual cadence: 1.1%.
Definition: same churn formula as Tile 1, segmented by billing_cadence. Note the brief's cadence caveat: Monthly here is mid-term cancellation, Annual is renewal-window non-renewal — kept as two bars, never blended.
Source: Deliverable 2, Query 3 variant (GROUP BY billing_cadence).
Chart choice: two categories → bar chart (not a pie — even two slices read more precisely as bars).
Honesty / Simpson's-paradox note (carried from Deliverable 4): Monthly-cadence subscribers are disproportionately new, so part of this gap is the tenure effect from Tile 3, not the cadence itself. Caption flags it: "Much of the cadence gap is explained by tenure — see Tile 3. Correlation, not proven cause." This keeps leadership from a quick "kill the monthly cadence" misread. (Plan tier — Basic/Family/Premium — was checked separately and is roughly flat, so it gets no tile.)

Tile 5 — Churn by month (line chart, supporting)

Metric: Overall monthly churn rate over the last 6 months — 3.5% (Oct) rising to 4.8% (Mar), inflection in January.
Source: Deliverable 2, Query 3.
Chart choice: change over time → line chart. Demoted to a full-width supporting row because it answers a follow-up ("since when?"), not the headline question.

Footer: Data through March 31, 2026 (window: 2025-10-01 to 2026-03-31, Q1–Q2 FY26) · refreshed weekly (Mon 8am) · churn = voluntary cancellations only; involuntary payment-failure cancels (~0.6%/mo) tracked on the Billing dashboard.

What I deliberately left off (the subtraction that makes it usable): no country-by-country map (24 countries, no decision rides on it yet), no support-ticket volume chart (interesting but not the retention number), no cohort heatmap (too dense for a self-serve exec screen). Five tiles, one screen, one answer: the leak is new-subscriber churn in the first 90 days, it's been climbing since January, and it — not acquisition — is what's flattening MRR.

Rubric

The app's AI scores the learner's submission against these criteria and gives feedback. Levels: Needs work (1) / Solid (2) / Excellent (3). Passing = every criterion at Solid or above.

Focus & decision-orientation — 1: a wall of charts with no stated decision, or >8 tiles · 2: 4–6 tiles tied to a stated decision · 3: ruthlessly focused, every tile changes a decision, with an explicit note on what was cut and why.
Chart-to-question fit — 1: wrong types (pie for trend, line for categories) · 2: line for time, bar for categories, big number for a single metric · 3: correct types and a clear rationale, with segment bars sorted worst-to-best to surface the problem.
Chart honesty — 1: truncated/non-zero bar axes or missing labels · 2: zero-based bar axes, titles, axis labels, units · 3: fully honest and adds targets/comparisons and an explicit caveat (e.g. correlation-not-cause or definition footnote) that pre-empts a misread.
Metric rigor & traceability — 1: numbers with no definitions or sources, or a definition that silently contradicts the brief (e.g. adds an order-recency clause to "active," or merges the brief's two separate dimensions — billing cadence and plan tier — into one "plan" tile) · 2: each tile names its metric, exact definition, and source query/table, with no clause that the brief or the Source SQL doesn't support, and keeps cadence and tier as distinct breakdowns · 3: definitions carried verbatim from the brief and traced to specific Deliverable 2 queries — every clause in the Definition is one the Source query's WHERE/CASE logic actually enforces — so the dashboard, the brief, and the SQL never disagree.
Layout & hierarchy — 1: random arrangement · 2: a labeled grid with the headline number prominent · 3: deliberate hierarchy (headline top-left, context top-right, segment story below) with the reading order stated and a freshness/refresh footer.
Coherence with the investigation — 1: disconnected from prior deliverables · 2: reflects the brief's metrics and the findings' segment story · 3: the dashboard visibly is the investigation made self-serve — headline, MRR trend, and tenure breakdown together tell the same answer-first story leadership will act on.