Skip to main content
Millennial Business Academy
All practice projects

Business Analytics Project · Advanced · 4 to 6 hours

Scope and prepare a subscription churn analysis

Before a single chart, do the hard part: turn a vague 'why are we losing subscribers' into a precise question, then join and prepare two messy exports into an analysis-ready table.

Get the datasetTake the skills check

The brief

Handa Box is a monthly Filipino snack-box subscription. The founder is worried: 'We are losing too many subscribers. Find out why.' You are the business analyst brought in for the Ask, Collect, and Prepare phase, the work that decides whether the later analysis is even valid. You get two exports: a subscriber master and a payments log. Your job is not to jump to answers. It is to define what churn means here, separate the customers who quit from the ones the billing system dropped, join the two files honestly, quarantine what cannot be trusted, and state plainly what data is still missing to answer why. Get this phase wrong and every dashboard after it lies.

Your role

You are the analyst scoping and preparing the churn study. Deliverable: a one-page analysis plan (question, metrics, assumptions), a clean customer-level table, a quarantine list, and a note on the data gaps.

Translating a vague ask into measurable questions and metricsVoluntary vs involuntary churn, and why they need different fixesJoining two files on a key and auditing join integrityQuarantining orphans, duplicates, and never-billed trialsChoosing a defensible churn denominatorNaming the data you do not yet have

The datasets

600 Handa Box subscribers (Jan 2025 to Jun 2026), with plan, region, channel, status, and cancel reason

ba-churn-subscribers.csv · 600 rows

Columns: customer_id, signup_date, plan, monthly_price, region, acquisition_channel, status, cancel_date, cancel_reason

Monthly billing attempts for those subscribers, carrying a few real merge artifacts

ba-churn-payments.csv · 4,660 rows

Columns: payment_id, customer_id, billing_date, amount, status

Setup

  • Download both files and open them side by side. Keep the raw copies untouched.
  • Add a data dictionary tab and a prep-log tab: record every join, drop, and quarantine with a count.
  • Write your analysis question and metric definitions before you touch the data.

Your tasks

Work through these in order, the way the engagement would actually run.

  1. 1Write the sharp question: define churn (cancelled subscription), the period, and the metric before analyzing.
  2. 2Split cancellations into involuntary (cancel_reason is Payment failed) and voluntary, and count each.
  3. 3Choose and defend a churn denominator: all subscribers active at period start, not just those with payments.
  4. 4Join payments to subscribers on customer_id and count the rows that do not match.
  5. 5Quarantine the 4 orphan payments and the exact duplicate payment_id rows instead of deleting or force-mapping.
  6. 6Flag the subscribers who signed up but were never billed, and decide how the churn metric treats them.
  7. 7Build one clean customer-level table: one row per subscriber with plan, tenure, status, churn type, and payments-to-date.
  8. 8Compute churn overall and by plan, and read whether price tier tracks churn.
  9. 9Write the data-gap note: list what you would need to collect to answer why voluntary churners actually left.

Work like an AI-powered analyst

The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.

  • Give ChatGPT or Claude your one-line ask and have it help you write 5 SMART sub-questions and the exact metric each one needs.
  • Describe your join and its 4 unmatched rows to the AI and ask it to argue quarantine vs drop vs impute, then decide yourself.
  • Ask the AI to list the behavioral and service data a churn root-cause study normally needs, then compare it to what these two files contain.

Expected output

  • A one-page analysis plan: the question, churn definition, metrics, and stated assumptions.
  • A clean customer-level table plus a quarantine list with a one-line reason per row.
  • Churn overall and by plan, with involuntary separated from voluntary.
  • A short data-gap note naming what must be collected to answer why.

Check your numbers

Your results should match these. If they do not, that is the real learning: find out why.

  • 158 of 600 subscribers have cancelled, a 26.3 percent churn rate.
  • Of the 158 cancellations, 53 are involuntary (payment failed) and 105 are voluntary, so 33.5 percent of churn is a billing problem, not a loyalty one.
  • Churn by plan is Lite 34.3 percent, Classic 22.8 percent, Family 21.4 percent, so the cheapest tier leaves the most.
  • The payments file has 4,660 rows: after removing 6 duplicate payment_ids and quarantining 4 orphan payments, 4,650 valid payments remain.
  • 7 subscribers signed up but were never billed (trials), so whether you include them changes the churn denominator.
  • Only 53 payments failed out of 4,650 (1.1 percent), yet failed payments still cause a third of all churn, which is the insight.

Skills check

6 advanced questions on the judgement this project demands, not just its steps. Your score saves to your dashboard, and you can retry for a better one.

Finished it? Put it in your portfolio.

This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.

Explore the BootcampStart Free Training