When Users Churn

21 May, 2018

In this analysis we’ll explore the topic of churn. Specifically we’ll explore subscription lengths and the amount of time it takes users to cancel their paid subscriptions. We’ll bucket churned customers into categories: those that cancel their subscription within a month of upgrading, those that cancel in the first 3 months of the plan, and those that churn later on.

These are the key findings of the analysis:

First thing’s first, let’s collect the data we need. The following SQL query retrieves all Stripe subscriptions created since January 1, 2015, their start dates, and, if the subscription has been canceled, the date of the cancelation.

select 
  s.id as subscription_id
  , s.customer_id
  , date(s.created_at) as start_date
  , date(s.canceled_at) as end_date
  , s.plan_id
  , s.simplified_plan_id
  , s.billing_interval
  , s.successful_charges
from dbt.stripe_subscriptions as s
where s.successful_charges > 0
and s.created_at >= '2015-01-01'

We have 161 thousand subscriptions that were created since January 1, 2015 and have successfully paid Buffer.

How Long Does it Take to Churn?

Of these 161 thousand subscriptions, let’s see how many have churned already.

# get proportion that churned
subs %>% 
  group_by(is.na(end_date)) %>% 
  summarise(subs = n_distinct(subscription_id)) %>% 
  mutate(percent = subs / sum(subs))

## # A tibble: 2 x 3
##   `is.na(end_date)`  subs percent
##   <lgl>             <int>   <dbl>
## 1 F                 94682   0.586
## 2 T                 67003   0.414

So far, around 59% of all subscriptions created since the beginning of 2015 have churned. It may be useful to remember here that some subscripitons have had longer than others to churn – those subscriptions created at the beginning of 2015 have had many more opportunities to churn than those created at the beginning of 2018. The data is right-censored.

Of those that did churn, we can visualize the distribution of the number of days it took them to cancel the subscription.

# calculate days to churn
subs <- subs %>% 
  mutate(churned = !is.na(end_date), 
         days_to_churn = as.numeric(end_date - start_date),
         churn_within_30_days = days_to_churn <= 30,
         churn_within_90_days = days_to_churn <= 90,
         churn_within_180_days = days_to_churn <= 180,
         churn_wthin_year = days_to_churn <= 365)

We can see that there are very different distributions for monthly and annual plans. The most common time for a monthly user to churn is right after the first billing period finishes, around 30-40 days into the subscription.

For annual customers, the most common time to churn is right at the end of the first billing period, around 365 days into the plan. There is also a group of users that cancel their annual plan within the first 30 days of it starting.

We can get a little more granular by looking at the cumulative distribution functions. Let’s start with the monthly plans.

This is an inforative graph. Around 14% of all churned monthly subscriptions churned within 30 days of starting the subscription. Around 29% churn within 60 days and 39% churn within 90 days. This means that around 61% of churned customers took more than 90 days to cancel their subscriptions.

Let’s examine annual plans now.

We see a very different looking graph for annual subscriptions. Only around 5% of annual subscriptions churn within the first 60 days. Most of the churn occurs right at the end of the billing period. Between days 360 and 390, around 60% of churned subscriptions canceled their plans.

Let’s now shift gears and look at billing periods

Billing Period

There is a common theme with these monthly and annual subscriptions – many churn after their first billing period. Let’s explore this in some more depth to get a sense of how the billing period affects churn.

For all churned subscriptions, we can look at the number of successful payments they had before churning.

We see a distribution that we might expect. The most common time for users to churn is after the first successful charge – the second charge is never paid. After the first billing period, the likelihood of churning diminishes as each billing period goes by.

This makes intuitive sense. It’s crucial for us to deliver a good experience within early on – otherwise people will cancel.