User Churn
Analyze churn for two groups of Codeflix users and recommend which segment represents a better long-term user base.
Start
Calculating Churn
Lesson 1 of 1
- 1A common revenue model for SaaS (Software as a service) companies is to charge a monthly subscription fee for access to their product. Frequently, these companies aim to continually increase the…
- 2Now that we’ve gone over what churn is, let’s see how we can calculate it using SQL. In this example, we’ll calculate churn for the month of December 2016. Typically, there will be data in a subsc…
- 3The previous method worked, but you may have noticed we selected the same group of customers twice for the same month and repeated a number of conditional statements. Companies typically look at c…
- 4Our single month calculation is now in a form that we can extend to a multiple month result. But first, we need months! Some SQL table schemes will contain a prebuilt table of months. Ours doesn’t…
- 5Now that we have a table of months, we will join it to the subscriptions table. This will result in a table containing every combination of month and subscription. Ultimately, this table will be u…
- 6We now have a cross joined table that looks something like: | id | subscription_start | subscription_end | month | | — | — | — | — | | 1 | 2016-12-03 | 2017-02-15 | 2016-12-01…
- 7For our calculation, we’ll need one more column on the status temporary table: is_canceled This column will be 1 only during the month that the user cancels. From the last exercise, the sample us…
- 8Now that we have an active and canceled status for each subscription for each month, we can aggregate them. We will GROUP BY month and create a SUM() of the two columns from the status table, is_a…
- 9Now comes the moment we’ve been waiting for - the actual churn rate. We use the number of canceled and active subscriptions to calculate churn for each month: churn_rate = canceled / active
- 10You can now calculate a churn rate over time for a company: | month | churn_rate | | — | — | | 2017-01-01 | 0.127 | | 2017-02-01 | 0.125 | | 2017-03-01 | 0.237 | It looks like o…
What you'll create
Portfolio projects that showcase your new skills