2 Measuring Churn

This chapter covers

The precise definition of the Churn Rate
How to identify churned accounts and calculate the churn rate using SQL
What the Net Retention rate means
How to calculate Net Retention Rate with SQL
What Monthly Recurring Revenue (MRR) Churn means
How to calculate MRR Churn Rate with SQL
How to convert churn rates between monthly and annual measurements

You already have learned that the churn rate is a measurement of the proportion of customers that quit every month or year. If you don't measure churn correctly, it's going to be that much harder to do anything about it. This chapter teaches you multiple definitions of churn that are suitable to different business scenarios and how to calculate them efficiently from a subscription database. In relation to the overall book scenario introduced in chapter 1, this chapter focuses on the process highlighted in Figure 2.1.

Figure 2.1 The subjects of this chapter in the process of fighting churn with data.

Calculating the churn rate is really not rocket science, but you do need to know some intermediate level SQL and a couple of algebraic equations. There are a few things that make calculating churn rates non trivial - part of the challenge is complexity, and another challenge is logistical. The complexity in calculating churn is that an account can have multiple subscriptions over its lifetime, including:

2.1 The Definition of the Churn Rate

2.1.1 Calculating The Churn Rate and Retention Rate

2.1.2 The Relationship Between the Churn Rate and Retention Rate

2.2 Subscription Databases

2.3 Basic Churn Calculation: Net Retention

2.3.1 Net Retention Calculation

2.3.2 Net Retention Calculation SQL

2.3.3 Interpreting Net Retention

2.4 Standard Account Based Churn

2.4.1 Standard Churn Rate Definition

2.4.2 Outer Joins for Churn Calculation

2.5 Activity (Event) Based Churn for Non-Subscription products