Data Analyst Capstone — The Churn Investigation: Why Are Customers Leaving?

The idea: Knowledge becomes skill only when you do the work. In the capstone you own one real question that leadership actually cares about and carry it through the entire analyst workflow — from a vague executive ask to a decision-driving readout. You produce the real artifacts a Data Analyst makes. By the end you'll have a portfolio that proves — to yourself and to employers — that you can do the full analyst job end to end, not just describe it.

How it works: Each deliverable unlocks after the syllabus topic that teaches its skill. You produce the artifact (a template is provided in ../templates/), submit it, and the app's AI reviews it against a rubric, gives you specific feedback, and lets you revise until it's solid. Finished deliverables assemble into your portfolio.

Your scenario (it's already chosen for you)

Unlike a build-your-own project, this capstone hands you one coherent investigation — because the real test of an analyst isn't picking a topic, it's taking a messy, ambiguous business problem and driving it to an honest answer.

FreshBox is a meal-kit subscription company with ~40,000 active subscribers. In the last two quarters monthly recurring revenue has flattened even though marketing keeps acquiring new customers, and the Head of Operations drops a Slack message: "Something feels off with retention — can you look into it?"

You have access to a simple warehouse with four tables (customers, subscriptions, orders, cancellations) plus a messy CSV export of customer support tickets. Before the next quarterly planning meeting, leadership wants to know: where are customers leaving, which segments are worst, what's likely driving it, and one concrete recommendation they can act on.

You carry FreshBox through every deliverable — the same company, the same data, one investigation. The artifacts stack into a single story, exactly how a real analyst's work hangs together.

One window, locked for the whole capstone. The investigation covers 2025-10-01 through 2026-03-31 — six full calendar months, the two quarters where MRR flattened, at monthly grain. FreshBox's fiscal year starts in October, so this window is Q1–Q2 of FY26 (Q1 = Oct–Dec 2025, Q2 = Jan–Mar 2026), and the quarterly planning meeting you're racing toward is the Q3 FY26 planning meeting (Apr–Jun). Use these exact dates and these quarter labels in every deliverable — the brief, the SQL WHERE clauses, the dashboard axes and footer, and the readout. A coherent investigation is one where the window never silently shifts; the worked examples below all use this window, and so should you. (Working a different dataset for your own portfolio? Pick your own six-month window and one quarter convention, then hold them fixed across all six artifacts — the rubric rewards the consistency, not the specific dates.)

The deliverables (your capstone arc)

#	Deliverable	Unlocks after	The skill it proves
1	Analysis Brief & Metric Definitions	Topic 6 (Defining the question and the metrics)	Turning a vague ask into a sharp, answerable question
2	SQL Data-Pull Workbook	Topic 5 (Databases and SQL basics)	Pulling exactly the right data from a database
3	Data Cleaning Log	Topic 7 (Cleaning and preparing data)	Making the analysis trustworthy and reproducible
4	Findings Memo with Descriptive Stats	Topic 8 (Descriptive statistics that matter)	Summarizing honestly and not being fooled by averages
5	FreshBox Retention Dashboard	Topic 9 (Data visualization and dashboards)	Letting leadership self-serve the numbers that matter
6	Executive Readout & Recommendation	Topic 10 (Telling stories with data)	Driving a decision with an answer-first story

Note on order: the brief comes first in your portfolio because analysis starts before the data — but it unlocks after Topic 6, while the SQL workbook unlocks after Topic 5. In practice you'll have the SQL skills in hand by the time you finalize the brief, so write the brief, then pull the data to serve it. That's the real sequence: define the question, then go get the data.

Each builds on the last: the brief defines the question and metrics → the SQL workbook pulls the data to answer it → the cleaning log makes that data trustworthy → the findings memo analyzes it honestly → the dashboard lets leadership see it → the readout turns it into a decision. Together they tell one coherent investigation about one company.

The deliverables in detail

1. Analysis Brief & Metric Definitions (after Topic 6) A one-page brief that turns the vague "look into retention" ask into a sharp, answerable question — naming the decision it serves, the precise metric definitions (active subscriber, churn rate, MRR), the time frame, and the segments you'll break the data down by. Includes the riskiest assumption you'll need to validate. Template: ../templates/analysis-brief.md (~60 min).

2. SQL Data-Pull Workbook (after Topic 5) A documented set of 4–6 SQL queries against the FreshBox warehouse that pull exactly the data your brief calls for — a JOIN across customers and subscriptions, a GROUP BY churn rate per segment, a monthly MRR trend — each query annotated with the business question it answers and a sanity-check on the row counts it returns. Template: ../templates/sql-data-pull-workbook.md (~75 min).

3. Data Cleaning Log (after Topic 7) A transparent cleaning log for the raw FreshBox extract and the messy support-ticket CSV: every issue found (duplicates, inconsistent country/plan formatting, missing cancellation dates, mistyped numbers, outliers) with the decision you made and why — so the analysis is trustworthy and reproducible. No silent deletions. Template: ../templates/data-cleaning-log.md (~70 min).

4. Findings Memo with Descriptive Stats (after Topic 8) An analysis memo that summarizes the cleaned data with the right statistics — median vs. mean where extremes matter, spread, churn broken down by segment — surfaces the real story hiding under the overall average (a Simpson's-paradox check), and explicitly separates a correlation worth investigating from a claimed cause. Template: ../templates/findings-memo.md (~80 min).

5. FreshBox Retention Dashboard (after Topic 9) A focused dashboard spec — a chart-by-chart wireframe plus the metric behind each — that lets leadership self-serve the few retention numbers that matter: MRR trend line, churn-by-segment bar chart, a single headline number, all honestly labeled with zero-based axes. Built in a BI tool or sketched as a wireframe. Template: ../templates/dashboard-spec.md (~70 min).

6. Executive Readout & Recommendation (after Topic 10) An answer-first executive readout: a one-sentence headline conclusion, the two or three charts that prove it, the dollar impact quantified in leadership's terms, one concrete recommendation, an honest note on uncertainty, and methodology kept to an appendix — the artifact you'd actually present in the quarterly planning meeting. Template: ../templates/executive-readout.md (~90 min).

What "done" looks like

A complete capstone is six artifacts about one investigation, each passing its rubric, assembled into a portfolio you can link from your resume and walk through in interviews. This is exactly the workflow Topic 11 (Landing the data analyst job) tells you to be able to demonstrate. When you reach the interview prep, you'll practice presenting these artifacts the way you would to a hiring manager — the SQL technical screen leans on Deliverable 2, the case study on Deliverables 1, 3, and 4, and the stakeholder round on Deliverables 5 and 6.

What the finished portfolio demonstrates about you

A hiring manager flipping through these six artifacts sees an analyst who can:

• Scope an ambiguous ask. You turned "something feels off with retention" into a defined decision, precise metric definitions, a time frame, and segments — the judgment that separates an analyst from a query-runner.
• Get the data yourself. Your SQL workbook shows you can JOIN, GROUP BY, aggregate, and — crucially — sanity-check your own row counts instead of trusting the first number that comes back.
• Earn trust through transparency. Your cleaning log proves every number is reproducible and that you made no silent deletions — the difference between an analysis people act on and one they quietly distrust.
• Reason honestly with statistics. You chose median over mean where extremes distorted the picture, caught the story hiding under the average, and refused to call a correlation a cause.
• Communicate to a decision. Your dashboard lets leadership self-serve, and your answer-first readout puts the conclusion and the dollar impact in front, methodology in the back — the way real executives consume analysis.

Together they prove the one thing employers actually screen for: that you can take a real business question end to end and produce an answer someone can act on.

Tips

• Keep it real and specific. "Churn is up" is weak; "monthly churn in the new (first-90-day) Monthly-cadence, EU segment rose from 4% to 9% over Q1–Q2, concentrated in customers with an open support ticket" is an analyst thinking. (Use the brief's two locked dimensions precisely: billing cadence = Monthly/Annual, plan tier = Basic/Family/Premium — never collapse them into one "plan.")
• Reuse your earlier work — and never let the window drift. The metrics you define in the brief should be the exact ones your SQL pulls, your cleaning log feeds, your stats summarize, your dashboard displays, and your readout headlines — and they should all cover the same window (2025-10-01 to 2026-03-31, Q1–Q2 FY26) with the same quarter labels. A dashboard footer that says "data through March 2026" while the memo talks about "Q3–Q4" is the kind of silent inconsistency graders (and interviewers) catch instantly. Graders (and employers) love a coherent thread from question to recommendation; the dates are part of that thread.
• Be honest about uncertainty. The strongest deliverable isn't the one with the boldest claim; it's the one that quantifies impact and flags what it can't yet prove. That's what makes leadership trust the next analysis you bring them.
• Use the templates in ../templates/ as starting points, and revise based on the AI feedback rather than aiming for perfect on the first try (that's the whole loop).