Computing a churn curve in your BI system

Noy Rotbart
 | September 19, 2022 · 3 min
Illustration of people on consecutively higher levels

A churn curve is a handy tool to evaluate your churn in different cohorts/periods. BI tools are not great at collecting time-based data, such as subscription periods, and unifying them. This tutorial will demonstrate how to use a BI tool to arrive at a fully filterable churn curve. We choose PowerBI in this demo.

We begin with the table in which each row contains the subscription ID, start, and end date. If the subscription is still ongoing, the end date is blank. For convenience, we bound each subscription ID with some properties such as frequency, age, inbound channel, country, and marketing allowed.

The first column we need to define for each row is the number of payments made, a function of the current time, subscription start date, and end date. We call num_payments. We also need to compute the number of payments possible with a similar parameter without the end date, which we call num_potential_payments. If no pauses are allowed, and the frequency is identical across the subscription (assume monthly), this we can compute it as

Num_potential_payments=(datediff(date(today),start_date),months)

The churn curve is a function computing the percentile of the sum out of the whole population (0-100%, y-axis) over all months made across time (say five years, which are 60 payments possible, on the x-axis). We create the x-axis external using:

CyclesToAttrition = GENERATESERIES(0, 60, 1)

Conceptually, we want attrition to be:

Attrition = [Made X payments]/[Population  size]

Where Made X payments are the total number of agreements that made at least x payments computed as follows.

Made X payments = var CurrentNumber = MIN ( CyclesToAttrition[Value] )
return  CALCULATE(COUNTROWS(data),data[num_payments]>=CurrentNumber)

Population size is the number of agreements that could have made at least x payments, and we compute it as follows.

Population  size =
var CurrentNumber = MIN (CyclesToAttrition[Value])
return  CALCULATE(
                COUNTROWS( data),data[num_potential_payments]>=CurrentNumber)

At this point, the churn curve is complete, and as we filter the audience based on the country, age, etc. the churn curve will be updated accordingly. Furthermore, we can maintain the filters for one curve, keep another curve without filters, and then cross-evaluate populations against the general churn curve.

  • Churn
  • BI