In this post I’m going to explain why you need to use what I call “advanced” customer metrics in your fight against churn. If you are trained in data science, you would call this feature engineering because we are talking about designing the data (features) for an analysis. I’m going to make some examples from the Versature case study that I mentioned in my previous post on understanding churn with metric cohorts. (Versature is a provider of integrated cloud communication services.) In that post I showed how to use metric cohort analysis to understand engagement and churn. The metrics can also be use to segment customers for interventions to increase engagement, like email campaigns or webinars. This post also contains examples of customer behavior correlation analysis described in my last post. (This is the post where it all comes together!)
In those previous posts I demonstrated what I call simple customer metrics. Simple customer metrics are counts or sums of what a customer does in a recent time period. For example, the number of calls a customer makes per month is a simple metric for a Telco product. The number of documents a customer makes per month is a simple metric for a SaaS product. And the number of shows a customer streams in a category is a simple metric for a streaming service.
Simple Customer metrics
The image below illustrates metric cohort churn analysis with simple customer metrics.
- Local calls per month – This has the expected relationship to churn in the metric cohort plot: The more calls, the less churn.
- Monthly Recurring Revenue paid by the customer per month – This one is probably not expected: The more customers pay, the less they churn. How does that make sense? If you haven’t done a lot of churn studies this may surprise you. Read on to find out why!
Note: the metric cohort figures show the cohort average metrics as a score (normalized.) Also the churn rates are shown on a relative scale (with the bottom of the figure fixed at zero churn.)
At the same time, the scatter plot (above) shows that paying more is strongly correlated with making more calls. And of course, customers who make a lot of calls churn a lot less than customers that don’t, and this is a stronger relationship with churn than MRR. So that explains why it looks like customers that pay more churn less – they also usually make more calls. That may be true but that relationship is not useful for understanding customer price sensitivity. Something is missing from this picture…
Customer churn results for advanced metrics
Advanced customer metrics for churn are combinations of simple customer metrics that allow you to understand the interaction. The best way of combining two metrics is by making a ratio of one metric to another. The example in the last section is a common scenario where you want to use a metric made from a ratio of two other metrics: Something which is probably disengaging and ought to cause customers to churn (like paying a lot) is correlated with something good that is engaging and makes customers stay (like making a lot of calls with telco service.)
If you take the ratio of the monthly cost to the monthly calls the resulting metric is the cost per call which is a The relationship of the cost per call metric to customer churn is shown in the picture below. The cost per call metric has a relationship to churn that is easy to understand: The more the customer pays (per call) the more they churn. And the relationship is very strong! A unit cost metric like this is excellent way to segment your customers according to the value they receive.
Code for the ratio metric
When I said the metric is a ratio, I meant it! Below is the SQL that I use to calculate it. Literally I calculate the ratio of two other metrics. The only fancy part is the case statement to check for zeros in the denominator. (If you are not familiar with this SQL style for calculating metrics, check out my post on Churn Feature Engineering which goes over the basics of calculating Metrics with SQL.)
with num_metric as ( select account_id, metric_time, metric_value as num_value from metric m inner join metric_name n on n.metric_name_id=m.metric_name_id and n.metric_name = 'MRR' and metric_time between '2020-01-01' and '2020-01-31' ), den_metric as ( select account_id, metric_time, metric_value as den_value from metric m inner join metric_name n on n.metric_name_id=m.metric_name_id and n.metric_name = 'Local_Calls' and metric_time between '2020-01-01' and '2020-01-31' ) insert into metric (account_id,metric_time,metric_name_id,metric_value) select d.account_id, d.metric_time, %new_metric_id, case when den_value > 0 then coalesce(num_value,0.0)/den_value else 0 end as metric_value from den_metric d left outer join num_metric n on n.account_id=d.account_id and n.metric_time=d.metric_time
I think thats all I can fit in a post! To learn more details about the subject, you have to wait for the release of chapter 7 in the e-book of Fighting Churn with Data. (At the time of this writing that chapter is scheduled to be released in e-book form in February 2020…)