My company collects daily tenant metrics that include variables like Payments, Total Amount Invoiced, Tenant Status. Metrics are collected daily for all tenants, and metrics history is kept indefinitely.
Can we use tenant metrics to predict which tenants will churn next?
High-Level Approach
Divide the metrics into two parts:
- Metrics for tenants that churned (Churned Dataset)
- Metrics for tenants that didn’t churn (Active Datase
Use Churned Dataset to train the model about churn.
AWS ML automatically subdivides the dataset into two parts, by default:
- 70% for training
- 30% for validation
Use Active Dataset to predict churn.
Detailed Approach
Obtain Tenant Metrics
We obtained a sample of these metrics (500M) from production.
Dataset contains daily metrics about:
614 tenants
116 active
145 churned
352 ignored (e.g. trial, employee test tenants)
Prepare Data
Write a program to divide the dataset in 2:
- Churned tenants
- Active tenants
Same program also
- Converts date into account length, and into “days to churn”
- Normalizes all metrics by converting them “percentage growth from yesterday” times 1,000
- Limits growth or decline to +/- 100,000
- Removes trailing records for churned tenants only keeping records until day of churn
- Removes tenant name from churn dataset
- Remove “days to churn” from active dataset
Create AWS Machine Learning Datasource
Target attribute (the one we’re trying to predict) is “days to churn” (named Date in the model).
Create “Churned Tenants” datasource and use Date as the target attribute (i.e. predict days to churn).
Create AWS Machine Learning Model
Create “Churned Tenants” ML Model
Train AWS Machine Learning Model
We trained the model on the churned tenants dataset.
AWS uses 70% of the data to train, and 30% to validate the model.
Recipe
Model had a fairly high error: RMSE of 583 days, which means individual predictions can be off by that many days. However the thought is, for a given tenant, given many records, average prediction would be a little more accurate.
AWS ML chose linear regression with multiple variables to predict days to churn. The below is the “recipe” it came up with, showing which columns were used, and which had higher weight.
{
"groups" : {
"NUMERIC_VARS_QB_50" : "group('Total_Payments_Received_converted')",
"NUMERIC_VARS_QB_500" : "group('Orders','Products','Total_Electronic_Payments_Received_converted','Subscriptions','Active_Accounts','Amendments','Electronic_Payments','Adjustments','Invoices','Users','Active_Subscriptions','Payment_Methods','Bill_Runs','Usage_Record_Uploads','Cancelled_Subscriptions','Total_Amount_Invoiced_converted','Total_Accounts','Data_Sources_Exports','Active_Payment_Gateways','Refunds')",
"NUMERIC_VARS_QB_200" : "group('Rate_Plans','Payments','Payment_Gateways','Total_Amount_Refunded_converted','Total_Account_Balance_converted','Currencies')",
"NUMERIC_VARS_QB_10" : "group('Edition')"
},
"assignments" : { },
"outputs" : [ "ALL_CATEGORICAL", "quantile_bin(NUMERIC_VARS_QB_50,50)", "quantile_bin(NUMERIC_VARS_QB_500,500)", "quantile_bin(NUMERIC_VARS_QB_200,200)", "quantile_bin(NUMERIC_VARS_QB_10,10)" ]
}
Generate Predictions for Active Tenants
We passed metrics for active tenants into the model, and computed the days to churn for each daily metric of every active tenant.
Then we averaged the predictions for each tenant.
Summary: what was learned
It remains to be understood what is the best approach to transform tenant metrics into best shape for predicting churn. The weakness of our approach was assumption that days_to_churn has some correlation to today’s changes in tenant metrics (even though smoothed out and scaled). While there is some correlation (e.g. less money going through the system, less usage) the key challenge is how to build a machine learning model that is accurate.
We ran out of time allotted for the hackathon, and didn’t try out these potentially promising ideas:
- Normalize days to churn so it has the same range for all tenants (would require us to scale data for tenants)
- Smooth out metrics using approaches like moving averages or other
- Brainstorm other ideas for analyzing time series
- Consider taking only last N days before churn for churned tenants, and active tenants
- Consider other machine learning approaches beside linear regression
- Clustering (e.g. find similarity to churned tenants rather than predict days)
- Neural networks
Tools Used
Java for cleaning and preparing data
Excel (pivot table)
AWS Machine Learning
Csvkit (command line csv tools)
Unix command line (cut, paste, grep)
