Skip to content

Calculate Customer Churn Metrics

Churn results when a customer cancels their subscription or relationship with a company. Preventing customer churn is critical, and some estimates suggest that acquiring a new customer can be as much as 25 times as costly as keeping an existing one.

The first step to reducing churn is understanding the extent to which your customers are churning. This template will cover three churn metrics you can use to know how many users and how much revenue you are losing to churn. It is appropriate for subscription data or other forms of recurring revenue data.

Imports and data preparation

The following cell imports the packages necessary to import and manipulate user payment data. It also loads and prepares the example data.

The example data in this template contains the payment date, user id, and price paid for a subscription service.

👇  To use your data, you can:

  • Upload a file and update the path variable. You will then need to perform the following steps:
    • Set the column that contains the date/time the payment was made.
    • Update the date offset below if you want to use a period other than monthly.
  • Connect to a database using a custom integration and perform the required preprocessing to convert payment or activity dates to your period of choice.
# Import packages
import numpy as np
import pandas as pd
import plotly.express as px

# Set the path to your file
path = "data/payments.csv"

# Set your date column
date_col = "payment_date"

# Import churn data
payments = pd.read_csv(path, parse_dates=[date_col])

# Categorize payment periods (optional)
payments["payment_period"] = pd.to_datetime(payments[date_col]) + pd.offsets.MonthBegin(-1)

# Preview the data
payments

Net retention rate

Calculating the net retention rate

The first churn metric that we will calculate is the net retention rate. The net retention rate is the percentage of recurring revenue that comes from customers who were subscribed at the start of a period.

Note that this calculation does not include any revenue from new subscribers. It does include the effect of churns, upsells (e.g., customers upgrading their subscription), and down sells (e.g., customers reducing their subscription tier). Because it includes upsells, the net retention rate is a less specific measure of churn than other measures. It is, however, a good overall metric that captures churn, upsells, and down sells that can be easily interpreted by stakeholders (such as investors).

💡  In this and the following cells that define the metric functions, you will need to update the DateOffset() if you wish to use a period other than monthly.

# Define a function to calculate the net retention rate
def nrr(x, dataframe):
    # Identify current and previous period based on group
    current_period = x["payment_period"].max()
    previous_period = current_period - pd.DateOffset(months=1)
    # Calculate the previous period's recurring revenue
    previous_period_rr = dataframe[dataframe["payment_period"] == previous_period]["price"].sum()
    # Identify the users in the previous period
    previous_period_users = dataframe[dataframe["payment_period"] == previous_period]["userid"].unique()
    # Calculate the current period's recurring revenue that comes from the previous periods's users
    current_period_retained_rr = dataframe[
        (dataframe["userid"].isin(previous_period_users))
        & (dataframe["payment_period"] == current_period)
    ]["price"].sum()
    # Caculate and return the net retention rate
    try:
        nrr = current_period_retained_rr / previous_period_rr
        return nrr
    except:
        return None

    
# Group the data by payment period and calculate the net retention rate
nrr_data = (
    payments.groupby("payment_period")
    .apply(nrr, dataframe=payments)
    .reset_index(name="nrr")
)

# Review the data
nrr_data

Note: You may notice low retention and high churn rates in the final month of data (February 2017). This is because the data is incomplete for this final period.

Plotting the net retention rate

We can plot the net retention rate using a line chart from Plotly, which provides an interactive visualization of each period.

fig = px.line(
    nrr_data, 
    x="payment_period", 
    y="nrr", 
    title="Net Retention Rate by Period"
)

fig.show()

Standard account churn

Calculating standard account churn

The next churn metric we will calculate is "standard account churn". Standard account churn is the proportion of customers who cancel to the total number of customers at the start of a period. A customer is only considered a churn if they cancel all subscriptions, and therefore a down sell or reduction in the number of subscriptions a customer holds does not constitute churn.

This metric does not consider upsells and down sells and provides an easy-to-interpret measure of churn. Standard account churn is a good metric to use when you have a free service or when all customers pay the same price.

# Define a function to calculate the standard account churn
def std_churn(x, dataframe):
    # Identify current and previous period based on group
    current_period = x["payment_period"].max()
    previous_period = current_period - pd.DateOffset(months=1)
    # Calculate the previous period's total number of users
    previous_period_user_count = dataframe[
        dataframe["payment_period"] == previous_period
    ]["userid"].nunique()
    # Identify the users in the previous and current periods
    previous_period_users = dataframe[dataframe["payment_period"] == previous_period]["userid"].unique()
    current_period_users = dataframe[dataframe["payment_period"] == current_period]["userid"].unique()
    # Calculate the number of churned users
    churns = len(np.setdiff1d(previous_period_users, current_period_users))
    # Caculate and return the standard churn
    try:
        std_churn = churns / previous_period_user_count
        return std_churn
    except:
        return None


# Group the data by payment period and calculate the net retention rate
std_churn_data = (
    payments.groupby("payment_period")
    .apply(std_churn, dataframe=payments)
    .reset_index(name="std_churn")
)

# Review the data
std_churn_data

Plotting the standard account churn

We can can again plot the churn by using a Plotly line chart.

fig = px.line(
    std_churn_data,
    x="payment_period",
    y="std_churn",
    title="Standard Account Churn by Period",
)

fig.show()

Monthly recurring revenue churn

Calculating monthly recurring revenue churn

The final churn metric to cover is "monthly recurring revenue", a variant of net retention rate. Whereas net retention rate includes upsells, monthly recurring revenue does not, as technically, these do not constitute churn.

Monthly recurring revenue churn is a useful metric if you have a variety of different prices (i.e., different pricing tiers). It is considered a more accurate definition of churn because it focuses exclusively on down sells and total churn.

# Define a function to calculate the periodly recurring revenue churn
def mrr(x, dataframe):
    # Identify current and previous period based on group
    current_period = x["payment_period"].max()
    previous_period = current_period - pd.DateOffset(months=1)
    # Identify total recurring revenue from previous period
    previous_period_rr = dataframe[dataframe["payment_period"] == previous_period]["price"].sum()
    # Identify the users in the previous and current periods
    previous_period_users = dataframe[dataframe["payment_period"] == previous_period]["userid"].unique()
    current_period_users = dataframe[dataframe["payment_period"] == current_period]["userid"].unique()
    # Identify retained and churned users
    retained_users = np.intersect1d(previous_period_users, current_period_users)
    churned_users = np.setdiff1d(previous_period_users, current_period_users)
    # Identify revenue of retained users in each period
    current_period_retained_rr = dataframe[
        (dataframe["userid"].isin(retained_users))
        & (dataframe["payment_period"] == current_period)
    ]["price"].values
    previous_period_retained_rr = dataframe[
        (dataframe["userid"].isin(retained_users))
        & (dataframe["payment_period"] == previous_period)
    ]["price"].values
    # Define mask to filter arrays for downsells
    downsells = previous_period_retained_rr > current_period_retained_rr
    # Calculate total downsell loss
    downsell_loss = np.sum(
        previous_period_retained_rr[downsells] - current_period_retained_rr[downsells]
    )
    # Calculate loss to users who churned
    churn_loss = dataframe[
        (dataframe["userid"].isin(churned_users))
        & (dataframe["payment_period"] == previous_period)
    ]["price"].sum()
    # Caculate and return the monthly recurring revenue
    try:
        mrr = (downsell_loss + churn_loss) / previous_period_rr
        return mrr
    except:
        return None


# Group the data by payment period and calculate the monthly recurring revenue churn
mrr_data = (
    payments.groupby("payment_period")
    .apply(mrr, dataframe=payments)
    .reset_index(name="mrr")
)

# Review the data
mrr_data

Plotting monthly recurring revenue churn

As with the previous metrics, we can use a Plotly line chart to plot the monthly recurring revenue churn.

‌
‌
‌