Calculating Churn
Lesson 1 of 1
  1. 1
    A 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…
  2. 2
    Now 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…
  3. 3
    The 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…
  4. 4
    Our 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…
  5. 5
    Now 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…
  6. 6
    We 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…
  7. 7
    For 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…
  8. 8
    Now 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…
  9. 9
    Now 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
  10. 10
    You 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

Pro Logo