From: Ines, Head of Subscriptions at Lumen Coffee
Subject: Who's cancelling?
Before we change the plans, I'd like the facts from lumen-coffee.shop.subscriptions: how many subscriptions are active, how many we've lost and when, which plans lose people fastest, and how long people stay before they cancel. A subscription is active while cancelled is NULL.
A case study is one question answered in several queries, with a judgment at the end. Each step is checked, so your numbers are right before you draw conclusions from them.
Keep definitions steady: churned means cancelled IS NOT NULL, and the churn share of a group is churned subscriptions divided by all subscriptions in it, rounded to 3.
Choose the population before comparing plans. This extract records subscription history, so a missing cancellation date means the subscription remains active in the supplied snapshot. Cancellations per month are counts, while churn share has a denominator. They answer different questions. The final recommendation must reflect those limits: a small fictional extract can identify a pattern to investigate, but it cannot establish why customers leave or what a new campaign would earn.