Learn
Calculating Churn
Single Month I

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 subscriptions table available in the following format:

  • id - the customer id
  • subscription_start - the subscribe date
  • subscription_end - the cancel date

When customers have a NULL value for their subscription_end, that’s a good thing. It means they haven’t canceled!

Remember from the previous exercise that churn rate is:

Churn

For the numerator, we only want the portion of the customers who cancelled during December:

SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( subscription_end BETWEEN '2016-12-01' AND '2016-12-31' );

For the denominator, we only want to be considering customers who were active at the beginning of December:

SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) );

You might’ve noticed there are quite a few parentheses in these two queries.

When there are multiple conditions in a WHERE clause using AND and OR, it’s the best practice to always use the parentheses to enforce the order of execution. It reduces confusion and will make the code easier to understand. The condition within the brackets/parenthesis will always be executed first.

Anyways, now that we have the users who canceled during December, and total subscribers, let’s divide the two to get the churn rate.

When dividing, we need to be sure to multiply by 1.0 to cast the result as a float:

SELECT 1.0 * ( SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( subscription_end BETWEEN '2016-12-01' AND '2016-12-31' ) ) / ( SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) ) ) AS result;

Here, we have the numerator divided by the denominator, and then multiplying the answer by 1.0. At the very end, we are renaming the final answer to result using AS.

Instructions

1.

We’ve imported 4 months of data for a company from when they began selling subscriptions. This company has a minimum commitment of 1 month, so there are no cancellations in the first month.

The subscriptions table contains:

  • id
  • subscription_start
  • subscription_end

Use the methodology provided in the narrative to calculate the churn for January 2017.

Folder Icon

Sign up to start coding

Already have an account?