Analytics Case Study — Customer Churn from Question to Dashboard

Take a churn question from stakeholder brief to SQL, analysis, a dashboard and a recommendation memo.

Est. 50 min Updated Suggest a fix
// Part 01 — The brief

Turn a Vague Churn Request into a Decision

A subscription product lead says, "Churn is up. Find out why and tell us what to do." That request contains a symptom, not a complete analysis plan. Start by asking what decision is pending, who will make it, which customer relationship is in scope, and what time period triggered concern.

For this case, the fictional company sells a monthly learning subscription. A customer is active when a paid plan covers the reporting date. Monthly logo churn means customers active at the start of a calendar month whose paid subscription ended in that month, divided by customers active at the start. We exclude trials and count each customer once. The result is descriptive: the analysis can locate patterns, but it cannot establish a cause on its own.

Brief elementWorking definitionWhy it matters
DecisionWhich retention intervention should be tested next?Keeps analysis tied to an action
PopulationPaid customers active at month startMakes numerator and denominator compatible
OutcomePaid subscription ended during the monthAvoids treating any quiet period as churn
ComparisonMonth, plan, acquisition source and tenure bandsSurfaces patterns while controlling the question scope
LimitObservational data, not a causal experimentPrevents an association from becoming a promise
💡 Note
Write the metric contract before running a query. Confirm how cancellations, pauses, refunds, reactivations and mid-month starts are treated with the business owner and billing source.
// Part 02 — Plan the data

Map the Grain, Sources and Failure Modes

Assume two source tables: subscriptions contains customer, plan, start, end and status dates; and customer_attributes contains one current acquisition source per customer. In production, attributes can change and subscription history may contain overlaps. Confirm the source-of-truth system, timezone, late-arriving updates and customer identity rules before treating the sample query as final.

  1. Agree the churn event and denominator with the product and finance owners.
  2. Inspect source keys, date coverage, duplicate records, status values and null rates.
  3. Build a customer-month dataset with one record per customer per reporting month.
  4. Reconcile a sample of customer-month rows with billing records and record known limitations.
  5. Publish only segments that have adequate data and a clear operational interpretation.

This case uses monthly calendar periods. If the business contract uses a rolling 30-day window or a different fiscal calendar, change the definition and validate it rather than relabeling the same calculation.

⚠️ Important
Subscription status fields can be overwritten by the latest state. If historical snapshots are not available, a current-state table cannot reliably reconstruct past churn. Find a history or event source before presenting trends.
// Part 03 — Build the metric

Calculate Churn from Customer-Months

The SQL below is PostgreSQL-style. It expands each subscription across the months it covers, selects customers active on the first day of a month and identifies an end date within that month. The example assumes one subscription per customer at a time; production handling for overlaps and pauses must follow the agreed rules.

customer_month_churn.sql
sql
WITH month_starts AS (
    SELECT generate_series(
        DATE '2025-01-01',
        DATE '2025-12-01',
        INTERVAL '1 month'
    )::date AS month_start
), active_at_start AS (
    SELECT DISTINCT
        m.month_start,
        s.customer_id,
        s.plan_id,
        s.acquisition_source,
        s.end_date
    FROM month_starts m
    JOIN subscriptions s
      ON s.start_date < m.month_start + INTERVAL '1 day'
     AND (s.end_date IS NULL OR s.end_date >= m.month_start)
    WHERE s.status IN ('active', 'ended')
), monthly AS (
    SELECT
        a.month_start,
        a.plan_id,
        a.acquisition_source,
        COUNT(DISTINCT a.customer_id) AS customers_at_start,
        COUNT(DISTINCT a.customer_id) FILTER (
            WHERE a.end_date >= a.month_start
              AND a.end_date < a.month_start + INTERVAL '1 month'
        ) AS churned_customers
    FROM active_at_start a
    GROUP BY a.month_start, a.plan_id, a.acquisition_source
)
SELECT
    month_start,
    plan_id,
    acquisition_source,
    customers_at_start,
    churned_customers,
    churned_customers::numeric / NULLIF(customers_at_start, 0) AS monthly_logo_churn
FROM monthly
ORDER BY month_start, plan_id, acquisition_source;

Review the interval boundaries carefully: the example uses the first day of the month as the snapshot and counts end dates before the next month begins. If your data stores timestamps, define the timezone before converting to dates. The source joins and active-status rule need adjustment if customers can hold multiple plans or restart service.

📌 Real World Example
A customer who ends a subscription at month end is counted according to the agreed end-date convention. Make this edge case explicit in a test row; boundary disagreements often explain why finance and product reports differ.
// Part 04 — Explore patterns

Check the Trend Before Explaining It

First chart the overall monthly rate and its numerator and denominator. Then segment by plan, acquisition source and customer tenure. Compare equivalent months, inspect segment sizes, and look for data or product changes that coincide with a break in the series. Do not turn every visible fluctuation into a product story.

inspect_churn.py
python
import pandas as pd
import matplotlib.pyplot as plt

# Export one row per month and segment from the validated SQL model.
monthly = pd.read_csv("monthly_churn.csv", parse_dates=["month_start"])
monthly["monthly_logo_churn"] = (
    monthly["churned_customers"] / monthly["customers_at_start"]
)

# Keep the overall trend separate from segment comparisons.
overall = (
    monthly.groupby("month_start", as_index=False)
    .agg(
        churned_customers=("churned_customers", "sum"),
        customers_at_start=("customers_at_start", "sum"),
    )
)
overall["churn_rate"] = overall["churned_customers"] / overall["customers_at_start"]

ax = overall.plot(
    x="month_start", y="churn_rate", marker="o", legend=False,
    figsize=(9, 4), ylim=(0, 1),
)
ax.set_ylabel("Monthly logo churn")
ax.set_xlabel("Month")
ax.set_title("Paid-customer churn; denominator = active at month start")
ax.grid(True, alpha=0.25)
plt.tight_layout()
plt.show()

A segment's rate can be high because its customers differ in tenure or plan mix. Show counts, define the comparison period and check whether each segment is mature. If the rate changed after a billing migration, validate the event pipeline before treating it as customer behavior.

ObservationPossible explanationsValidation before action
Increase across all plansBilling change, product issue or broad experience shiftCheck tracking, release timing and support volume
Increase isolated to new customersOnboarding friction or acquisition mixCompare activation milestones and source composition
Higher rate in one sourceAudience fit, campaign promise or channel mixCheck cohort sizes, tenure and offer terms
Spike in one month onlySeasonality, outage, data correction or genuine eventReview calendar, incidents and source history
// Part 05 — Investigate the journey

Connect Churn to Product Behavior Without Overclaiming

Add leading indicators such as first lesson completion, weekly active days, support contact and time to first useful result. Compare these features among customers who later churned and those who remained, using a clear lookback window that ends before the outcome. A feature measured after cancellation may leak the answer into the analysis.

Use retention curves and funnel analysis to understand where new customers stop progressing. A difference between groups is a hypothesis, not proof: people who complete a lesson may already be more motivated. To estimate an intervention's impact, define a randomized experiment or another defensible causal design with the product team.

🎯 Pro Tip
Write findings as three separate statements: what the data shows, what might explain it, and what evidence would distinguish the explanations. This keeps a useful recommendation honest about uncertainty.
// Part 06 — Dashboard and recommendation

Make the Result Useful in a Review Meeting

Build a compact dashboard around the decision. Put the metric definition, current rate, prior trend and customer counts near the top. Add segment filters only when they answer a likely follow-up question; a page of unrelated charts makes it harder to find the point. Show refresh date and an owner for metric questions.

A short recommendation memo can follow this structure: decision requested, metric and population, key observation, evidence and limitations, proposed next test, owner and success measure. For example, if early activation is associated with later retention, propose a bounded onboarding experiment and monitor activation plus guardrails such as support contacts and refunds. Do not promise that the association guarantees lower churn.

  1. Lead with one sentence that answers the stakeholder question.
  2. Show the denominator and time window alongside the rate.
  3. Separate observed evidence from hypotheses and causal claims.
  4. Recommend one testable next action with an owner and guardrail.
📌 Real World Example
A decision-ready headline might say: "New paid customers from channel A have lower 60-day lesson completion and higher observed churn; validate onboarding fit with a controlled welcome-flow test." The headline states the pattern and next check without asserting that channel A caused churn.
// Part 07 — Test and ship

Make the Analysis Safe to Refresh

Turn assumptions into checks: one row per customer-month, nonnegative denominators, churned customers no greater than customers at start, allowed status values, and no impossible end dates. Test month boundaries and a customer who cancels, pauses, reactivates or changes plans. Reconcile totals with the billing source and preserve a query/model version.

quality_checks.sql
sql
-- The modeled output should have one row per month and declared segment.
SELECT month_start, plan_id, acquisition_source, COUNT(*) AS row_count
FROM monthly_churn
GROUP BY month_start, plan_id, acquisition_source
HAVING COUNT(*) > 1;

-- Rates must have valid denominators and consistent numerator bounds.
SELECT *
FROM monthly_churn
WHERE customers_at_start <= 0
   OR churned_customers < 0
   OR churned_customers > customers_at_start;

-- Inspect missing segment assignments rather than silently dropping them.
SELECT COUNT(*) AS rows_without_source
FROM monthly_churn
WHERE acquisition_source IS NULL;

The SQL returns rows that need investigation; a clean result is empty for the uniqueness and bounds checks. Decide how null acquisition sources appear in the report. Schedule refreshes only after source readiness, monitor failures, and assign an owner to review metric changes before stakeholder distribution.

Use this rubric to review the case study before sharing it with stakeholders.
Rubric areaStrong submissionNeeds rework
DefinitionPopulation, event, dates and edge cases are written downUses churn without an operational rule
Data qualityGrain, duplicates and reconciliations are checkedAssumes source data is complete
AnalysisShows counts, comparable periods and limitationsExplains a rate change from one chart
CommunicationA decision and next test are explicitLists findings without an owner or action
ProductionRefresh, access, tests and ownership are documentedNo repeatability or privacy review
// Part 08 — Extensions

Where to Take the Case Next

For a stronger portfolio project, add cohort retention by signup month, segment revenue churn separately from logo churn, and quantify uncertainty when comparing small groups. Add a data dictionary and a short dashboard walkthrough. If you build an experiment proposal, specify randomization unit, eligibility, primary metric, guardrails and stopping rules before launch.

⚠️ Important
Customer-level churn data can reveal sensitive behavior. Use only approved datasets, minimize identifiers, restrict access, and aggregate outputs before sharing outside the authorized team.
// Part 09 — Practice

Deliver a Churn Review Pack

Practice

Recreate the workflow with a fictional subscription dataset

  1. Write a one-paragraph metric contract that settles the population, churn event, observation window and edge cases.
  2. Profile source tables, identify the grain, check duplicates and find missing history before querying.
  3. Build monthly customer counts, churn rates and two clearly labeled segment views in SQL.
  4. Add one behavioral indicator measured before the churn outcome and describe why it is only an association.
  5. Deliver a dashboard outline, a five-sentence recommendation memo and tests for key assumptions.

Hint: For every rate, keep its numerator and denominator visible. Ask what decision the chart is supposed to change.

🎯 Key Takeaways

  • ✓A useful churn case starts with a stakeholder decision and a written metric contract.
  • ✓Choose a customer-time grain and verify that the source preserves historical status.
  • ✓Compare equivalent periods, show counts and validate data before interpreting a trend.
  • ✓Behavioral associations can motivate a hypothesis but do not establish causal impact.
  • ✓A decision-ready dashboard combines definitions, trends, denominators, limitations and a next action.
  • ✓Production readiness includes quality tests, refresh ownership, access control and privacy.
  • For warehouse-specific SQL date generation and filtering syntax, consult the official documentation for your database engine.

Next

What comes next

You have followed a question through metrics, SQL, analysis and a recommendation. Next, practice explaining that work in interviews.

Lesson 16 · Data Analyst Interview Prep →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...