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
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]
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.