Analysis Brief & Metric Definitions — Data Analyst Capstone — SkilsMVP

What you'll produce

A one-page analysis brief that turns the Head of Operations' vague Slack message — "Something feels off with retention — can you look into it?" — into a sharp, answerable question before you touch a single row of data. It names the decision the analysis serves, the deadline, the precise metric definitions (active subscriber, churn rate, MRR) written so two analysts would compute identical numbers, the time frame, the segments you'll break the data down by, and the single riskiest assumption you must validate. This is the contract for the whole capstone — every later deliverable (the SQL pull, the cleaning log, the findings memo, the dashboard, the readout) is judged against the question and definitions you lock here. It proves the core Topic 6 skill: good analysis starts with a sharp question and unambiguous metrics, not with the data. Get this right and the investigation is half done; get it wrong and even flawless SQL produces a confident, useless answer.

Instructions

Name the decision, not just the topic. Write the one sentence that states what someone will decide differently depending on your answer. "Look into retention" is a topic; "Decide where to focus next quarter's retention budget" is a decision. Add the deadline (the quarterly planning meeting) and the owner (Head of Operations) — a question with no decision and no date is a research project, not an analysis.
Sharpen the vague ask into one answerable question. Rewrite "something feels off with retention" into a single question that names a metric, a time frame, and a breakdown (re-read Topic 6 Lesson 6.1). It should be specific enough that you already know which numbers would answer it.
Define every metric precisely — pin down the edge cases. For active subscriber, churn rate, and MRR, write a definition exact enough that two analysts querying the same warehouse get the same number. For each, force yourself to answer the ambiguities that cause conflicting reports: active as of when? Churn measured over which window, and is the denominator the start-of-period base or the average base? Is MRR gross or net of discounts; do paused subscriptions count? Name the source table(s) and columns (customers, subscriptions, orders, cancellations) each metric comes from.
- Separate the two dimensions that everyone confuses — plan tier and billing cadence. A subscriber chooses two independent things: which product they get (the plan tier) and how often they're billed (the billing cadence). These are orthogonal — a Premium subscriber can be billed Monthly or Annual. Define both vocabularies in the brief, list the allowed values for each, and forbid mixing them. This is the single most common way a meal-kit retention analysis goes incoherent: someone writes "the Monthly plan churns worst" when Monthly is a billing cadence, not a plan, and then the next analyst breaks down by Premium and the two reports can never be reconciled. Lock the vocabulary here so every later deliverable carries it verbatim.
State the time frame explicitly. Give the analysis window (the two quarters where MRR flattened) and the grain (monthly). Note any data you'll deliberately exclude and why (e.g., the current partial month, which would understate churn).
Choose 3–5 segments tied to the decision. Pick breakdowns that could change where leadership spends — e.g., tenure cohort, plan tier, billing cadence, acquisition channel, region. For each, write one sentence on the hypothesis it tests. Treat plan tier and billing cadence as two separate segments if you break down by both — never collapse them into one column called "plan," because the conclusions differ (a tier problem is a price/value problem; a cadence problem is a commitment/renewal problem). Skip vanity splits that wouldn't move the decision (Topic 6 Lesson 6.2).
Add the Simpson's-paradox guardrail. State in one line that you'll check the overall churn number and the same number within each segment, because an overall average can hide opposite trends (Topic 6 Lesson 6.3). This is the habit that separates a real analysis from a misleading one.
Name the single riskiest assumption. Identify the one thing that, if false, breaks the analysis or the decision — and how you'll validate it cheaply before presenting. Be honest: this is usually a data-trust assumption (e.g., that cancellation dates are reliably recorded) or a framing assumption (that the softening is churn, not pricing).
Keep it to one page. A brief is a decision tool, not an essay. If a stakeholder can't read it in two minutes and know exactly what you're about to measure, tighten it.

Worked example

(Company: FreshBox, a meal-kit subscription business, ~40,000 active subscribers. MRR has been flat for two quarters despite steady new-customer acquisition. Source: a warehouse with four tables — customers, subscriptions, orders, cancellations — plus a messy support-ticket CSV.)

Decision this serves: The Head of Operations needs to decide, before the next quarterly planning meeting (in ~3 weeks), where to concentrate next quarter's retention budget — which one customer segment to target with a save initiative. The answer must point to a specific segment, not "improve retention generally."
Sharp question: "Over the last two quarters (2025-10 through 2026-03), what is FreshBox's monthly subscriber churn rate and net MRR trend, and which customer segment — by tenure cohort, plan tier (Basic/Family/Premium), or billing cadence (Monthly/Annual) — is churning materially worse than the overall rate?" This names a metric (churn rate, MRR), a time frame (six months, monthly grain), and breakdowns drawn from the locked vocabulary above.
The two plan dimensions (locked vocabulary — every later deliverable carries these verbatim):
- Plan tier (which product the subscriber gets) — exactly three values: Basic, Family, Premium. These map to box size / recipe count and drive plan_price. Source column: subscriptions.plan_tier. The raw data arrives dirty ("basic", "BASIC plan", "2-person", "Two Person" all appear); Deliverable 3 standardizes every variant to one of these three. No fourth tier exists; anything else is a data-quality error to be mapped or quarantined, never a new bucket.
- Billing cadence (how often the subscriber is billed) — exactly two values: Monthly and Annual. Source column: subscriptions.billing_cadence. This is orthogonal to plan tier — every (tier × cadence) combination is legal, so the full plan space is 3 × 2 = 6 combinations (e.g. Premium/Annual). Never write "the Monthly plan" or "the Premium plan churns worst" as if these were the same axis: Monthly is a cadence, Premium is a tier, and conflating them is the exact error this brief exists to prevent.
Metric definitions (locked):
- Active subscriber — a customer with a row in subscriptions whose status = 'active' (not paused, not cancelled) as of the last day of the month being measured. Paused subscriptions are excluded from the active base because they generate no revenue. Source: subscriptions.status, subscriptions.start_date, subscriptions.end_date. Decision recorded here so it isn't re-litigated later: a customer who cancels and resubscribes counts as active again from their new start_date.
- Monthly churn rate — (active subscribers at start of month who are cancelled by end of that same month) / (active subscribers at start of month), expressed as a percentage, computed per calendar month. The denominator is the start-of-month active base, not the average base — chosen for reproducibility. Source: subscriptions joined to cancellations on subscription_id, using cancellations.cancel_date. A pause does not count as churn (tracked separately). Cadence caveat (load-bearing for later deliverables): a churn event means different things by billing_cadence. For Monthly subscribers it is a mid-term cancellation in that month. For Annual subscribers, mid-term cancels are rare; their churn is overwhelmingly renewal-window non-renewal — they lapse at the end of their 12-month term. So Monthly and Annual churn rates are not directly comparable and must be reported on separate rows, never blended into one cadence-blind number. Plan-tier churn (Basic/Family/Premium) is directly comparable and may be blended.
- MRR (Monthly Recurring Revenue) — the sum of each active subscriber's monthly-equivalent plan price net of active discounts as of month-end. Net of promo discounts, excludes one-off order add-ons and taxes (those live in orders and aren't recurring). Cadence normalization: Monthly subscribers contribute their monthly plan_price; Annual subscribers contribute plan_price ÷ 12 so the two cadences are on one comparable monthly scale. Source: subscriptions.plan_price, subscriptions.discount_pct, subscriptions.billing_cadence. We report net MRR; if leadership wants gross, that's a separate line, not a redefinition.
Time frame: Six full calendar months, 2025-10-01 to 2026-03-31, at monthly grain. Excluded: the current partial month (2026-04), which would understate churn because cancellations are still accruing, and any subscription with start_date after 2026-03-31.
Segments (4, each tied to the decision, drawn from the locked vocabulary):
- Tenure cohort (0–3 months, 4–12 months, 12+ months) — tests whether new customers churn out before the habit forms vs. long-tenured customers leaving. Derived from subscriptions.start_date.
- Plan tier (Basic, Family, Premium) — tests whether the cheapest or priciest product retains worst, which would point the save budget at a price/value problem. From subscriptions.plan_tier. (Directly comparable across tiers.)
- Billing cadence (Monthly, Annual) — tests whether the short-commitment cadence churns faster than the annual one, which would point the budget at a commitment/onboarding problem rather than a product one. From subscriptions.billing_cadence. Reported on separate rows from tier and never blended with it (per the cadence caveat above): Monthly churn is mid-term cancellation, Annual churn is renewal non-renewal.
- Acquisition channel (paid social, referral, organic) — tests whether marketing is buying low-retention customers, which would change who we acquire, not just who we save. From customers.acquisition_channel.
Simpson's-paradox guardrail: I will report the blended company-wide churn rate and churn within every segment above, because a flat overall number can hide one segment surging while another collapses (Topic 6 Lesson 6.3). If overall churn looks stable but a tenure cohort has doubled, the overall number is the misleading one. Concrete trap to watch — tier vs. cadence mix: a plan-tier churn comparison can be distorted by cadence mix, because tiers don't carry cadence evenly (e.g., Basic skews Monthly, Premium skews Annual). If Basic looks worse than Premium overall, I'll check whether that's really a tier effect or just Basic carrying more high-churn Monthly subscribers — by re-cutting tier churn within each cadence. Keeping the two dimensions separate (never one merged "plan" column) is exactly what makes this check possible.
Riskiest assumption: That cancellation dates in the cancellations table are reliably and completely recorded. If cancellations are missing cancel_dates or are logged late, every churn number — overall and by segment — is wrong, and the whole brief collapses. Validation before presenting: count cancellations rows with a null or implausible cancel_date, and reconcile total cancellations against the drop in active subscriptions month-over-month. If they don't roughly match, I fix the data (Deliverable 3) before trusting any rate. Secondary assumption to flag: that the softening is churn-driven at all and not a pricing/downgrade story — I'll sanity-check by confirming MRR is falling faster than the active count.

Rubric

The app's AI scores your submission against these criteria and gives per-criterion feedback. Levels: Needs work (1) / Solid (2) / Excellent (3). Passing = every criterion at Solid or above; revise and resubmit until it passes.

Decision-linked, scoped question — 1: restates "look into retention" with no decision or deadline · 2: a single answerable question naming a metric, time frame, and breakdown · 3: a sharp question explicitly tied to a concrete decision and deadline, so it's obvious what changes based on the answer.
Precise, reproducible metric definitions — 1: vague ("churn = people who leave") or missing source tables · 2: active subscriber, churn rate, and MRR each defined with their source columns · 3: definitions pin down the edge cases (denominator, paused vs. cancelled, net vs. gross, as-of date) so two analysts would compute identical numbers.
Plan tier vs. billing cadence defined as two distinct dimensions — 1: a single "plan" axis that mixes products and billing (e.g., lists Monthly next to Premium), so later deliverables can't reconcile · 2: plan tier (Basic/Family/Premium) and billing cadence (Monthly/Annual) named separately, with their allowed values and source columns · 3: the two dimensions are explicitly orthogonal (3 × 2 space), the cadence churn caveat is stated (Monthly = mid-term cancel vs. Annual = renewal non-renewal, reported on separate rows), and the vocabulary is locked verbatim for downstream use.
Time frame stated, with grain and exclusions — 1: missing or just "recently" · 2: a clear window and monthly grain · 3: window, grain, and an explicit, justified exclusion (e.g., the partial current month) that shows you anticipated a distortion.
Decision-relevant segments — 1: none, or vanity splits that wouldn't move the decision · 2: 3–5 segments drawn from real columns · 3: each segment paired with the hypothesis it tests and why it could change where the budget goes.
Segmentation / Simpson's-paradox guardrail — 1: relies on the overall average only · 2: commits to checking key segments alongside the overall number · 3: explicitly states that an overall figure can hide opposite segment trends and how that would change the conclusion.
Riskiest assumption + validation plan — 1: missing or a trivial assumption · 2: a genuine load-bearing assumption named · 3: the truly decision-breaking assumption, with a concrete, cheap way to validate it before presenting.