I was deputed to work at Lagos, Nigeria in 2011 to work for a telecom giant there. The project in hand was to develop customer analytics modules using SAS on customer’s newly built Oracle data warehouse. We thought about developing following modules.
- Customer Churn Analysis
- Calculating Product Propensities
- Customer Lifetime Value Calculation
- Customer Segmentation
Customer Churn Analysis: A subscriber was defined to have churned if he / she didn’t make any revenue generating usage in past three months. Following this definition around 15% subscribers were found to have churned in the past among the 35 million total subscriber base. Around 30 variables / attributes were extracted at the MSISDN level to analyse customer churn. PROC LOGIT in SAS was used to perform Logistic Regression with the binary YES / NO churn variable as target to find the churn propensities at the MSISDN level. Results were pushed in a Data Mart in the Data Warehouse. Refreshed Business Objects reports were generated every month showing aggregated results of Customer Churn Analytics. Client employees were trained in SAS Enterprise to query out MSISDN level information for churn prevention activities.
Calculating Product Propensities: The telecom client had various bundles of items that they designed and marketed for the subscribers. They wanted to learn about the Up Sell / Cross Sell probabilities. Various bundles / products were selected and the YES / NO target variables indicating whether the subscriber procured the product or not were extracted from the Data Warehouse. For each product and the corresponding target variable PROC LOGIT was used for running the Logistic Regression that generates the Up Sell probabilities of the products. For the Cross Sell probabilities filters were used on one product to find the Cross Sell probabilities of the other products.
Customer Lifetime Value Calculation: Client wanted to score their subscribers that sort of evaluates them from the perspective of their worth over their entire expected life time or tenure with the service provider. An upper-bound of customer life time was assumed and the expected monthly revenues to be generated over time were linearly regressed from past data. Monthly revenues were weighed by Retention Probabilities (1 – Churn Probabilities) exponentiated by time and financial discount factors were introduced in the calculation of the required Life Time Values.
Customer Segmentation: Around the then 35 million subscriber base were clustered by PROC FASTCLASS for the easy of study for marketing purposes.
