Data Analysis with pandas · Capstone

Capstone: Analyse Subscription Churn

Plan and carry out an end-to-end pandas analysis of subscription churn: loading, cleaning, joining, metrics, cohorts and presenting results.

Create a free account to save this page, take notes and track your progress. Sign in

Scenario

A SaaS company asks why customers cancel. You get three CSV exports: customers.csv (signup date, plan, country, acquisition channel), subscriptions.csv (one row per monthly period with status and price, including cancellations) and support_tickets.csv (ticket date, customer, category). The exports are messy: mixed date formats, duplicate customers, prices with currency symbols, and tickets for customers missing from the customer file.

Requirements

Monthly churn rate and revenue over the last 12 months.
Churn by plan, country and acquisition channel.
Cohort retention by signup month.
Whether support tickets relate to churn.

Constraints

pandas 3 in a notebook; about 2 million subscription rows.
Results go to a product manager as a short report with charts.

Deliverables

Loading and cleaning plan.
Data model and joins.
Metrics: definitions and pandas approach.
Cohort retention.
Presenting results and pitfalls.

Hints

Define churn precisely before computing it.
Check joins for duplicate keys and unmatched rows.

Draft each deliverable below before revealing the model answer and rubric.

Deliverables

Describe the loading and cleaning plan.

Model answer

read_csv with usecols, explicit dtype (IDs as str, plan/country/channel as category), parse_dates with explicit formats; coerce bad dates and prices (to_numeric(errors="coerce")) and count what became missing.
Normalise text (strip/lower emails and codes) before drop_duplicates(subset="customer_id"), keeping the earliest signup.
Log every cleaning decision (rows dropped, values coerced) so results are reproducible.

Rubric

Look for types fixed at load time, explicit date formats, coercion with checks, and normalisation before deduplication.

Describe the data model and joins.

Model answer

Subscriptions (many) to customers (one): merge(how="left", validate="many_to_one", indicator=True); report and exclude unmatched rows.
Tickets aggregated per customer and month (groupby + size) before joining, to avoid multiplying subscription rows.
One tidy table: customer, month, status, price, plan, country, channel, tickets_this_month.

Rubric

Strong answers aggregate before joining, validate key relationships and track unmatched records.

Define the metrics and how you compute them.

Model answer

Churn rate per month = customers whose subscription ended in the month / customers active at the start of the month.
Revenue = sum of price for active periods (groupby("month")).
Segment churn: the same calculation with groupby(["month", "plan"]), then pivot_table for a plan-by-month view.
Ticket relationship: churn rate for customers with 0, 1, 2+ tickets in the previous month (pd.cut on counts).

Rubric

Look for a precise churn definition with the right denominator, grouped calculations and a careful (correlational) ticket analysis.

How do you compute cohort retention?

Model answer

Cohort = signup month (dt.to_period("M")); period number = months since signup.
Active customers per cohort and period (groupby(["cohort", "period"])["customer_id"].nunique()), then pivot_table cohorts by periods.
Divide each row by its period-0 size to get retention percentages; display as a heatmap.

Rubric

Strong answers build cohort and period columns, count unique active customers and normalise by cohort size.

How do you present the results, and what pitfalls do you mention?

Model answer

A short report: churn trend chart, churn by segment table, cohort heatmap, ticket relationship chart, each with one-sentence takeaways.
Pitfalls: correlation is not causation (tickets may signal problems rather than cause churn); small segments are noisy (show counts); data quality issues and excluded rows; definition of churn (pauses, downgrades).

Rubric

Look for clear visuals with takeaways and honest caveats about causality, sample sizes and data quality.

Open in the app to mark lessons complete, take notes and track your progress.