Telecom Churn Case Study - Part 1

Photo by Mike Kononov on Unsplash

This analysis is the combined effort of Umaer and me.

Telecom Churn Case Study

Analysis Approach :

• Telecommunications industry experiences an average of 15 - 25% annual churn rate. Given the fact that it costs 5-10 times more to acquire a new customer than to retain an existing one, customer retention has become even more important than customer acquisition.

• Here we are given with 4 months of data related to customer usage. In this case study, we analyse customer-level data of a leading telecom firm, build predictive models to identify customers at high risk of churn and identify the main indicators of churn.

• Churn is predicted using two approaches. Usage based churn and Revenue based churn. Usage based churn:

• Customers who have zero usage, either incoming or outgoing - in terms of calls, internet etc. over a period of time.

• This case study only considers usage based churn.

• In the Indian and the southeast Asian market, approximately 80% of revenue comes from the top 20% customers (called high-value customers). Thus, if we can reduce churn of the high-value customers, we will be able to reduce significant revenue leakage. Hence, this case study focuses on high value customers only.

• The dataset contains customer-level information for a span of four consecutive months - June, July, August and September. The months are encoded as 6, 7, 8 and 9, respectively.

• The business objective is to predict the churn in the last (i.e. the ninth) month using the data (features) from the first three months.

• This is a classification problem, where we need to predict whether the customers is about to churn or not. We have carried out Baseline Logistic Regression, then Logistic Regression with PCA, PCA + Random Forest, PCA + XGBoost.

Analysis Steps

Data Cleaning and EDA

1. We have started with importing Necessary packages and libraries.
2. We have loaded the dataset into a dataframe.
3. We have checked the number of columns, their data types, Null count and unique value_value_count to get some understanding about data and to check if the columns are under correct data-type.
4. Checking for duplicate records (rows) in the data. There were no duplicates.
5. Since ‘mobile_number’ is the unique identifier available, we have made it our index to retain the identity.
6. Have found some columns that donot follow the naming standard, we have renamed those columns to make sure all the variables follow the same naming convention.
7. Follwing with column renaming, we have dealt with converting the columns into their respective data types. Here, we have evaluated all the columns which are having less than or equal to 29 unique values as catrgorical columns and rest as contineous columns.
8. The date columns were having ‘object’ as their data type, we have converted to the proper datetime format.
9. Since, our analysis is focused on the HVC(High value customers), we have filtered for high value customers to carryout the further analysis. The metric of this filtering of HVC is such that all the customers whose ‘Average_rech_amt’ of months 6 and 7 greater than or equal to 70th percentile of the ‘Average_rech_amt’ are considered as High Value Customers.
10. Checked for missing values.
11. Dropped all the columns with missing values greater than 50%.
12. We have been given 4 months data. Since each months revenue and usage data is not related to other, we did month-wise drill down on missing values.
13. Some columns had similar range of missing values. So, we have looked at their related columns and checked if these might be imputed with zero.
14. We have found that ‘last_date_of_the_month’ had some misisng values, so this is very meaningful and we have imputed the last date based on the month.
15. We have found some columns with only one unique value, so it is of no use for the analysis, hence we have dropped those columns.
16. Once after checking all the data preparation tasks, tagged the Churn variable(which is our target variable).
17. After imputing, we have dropped churn phase columns (Columns belonging to month - 9).
18. After all the above processing, we have retained 30,011 rows and 126 columns.
19. Exploratory Data Analysis

• The telecom company has many users with negative average revenues in both phases. These users are likely to churn.
• Most customers prefer the plans of ‘0’ category.
• The customers with lesser ‘aon’ are more likely to Churn when compared to the Customers with higer ‘aon’.
• Revenue generated by the Customers who are about to churn is very unstable.
• The Customers whose arpu decreases in 7th month are more likely to churn when compared to ones with increase in arpu.
• The Customers with high total_og_mou in 6th month and lower total_og_mou in 7th month are more likely to churn compared to the rest.
• The Customers with decrease in rate of total_ic_mou in 7th month are more likely to churn, compared to the rest.
• Customers with stable usage of 2g volume throughout 6 and 7 months are less likely to churn.
• Customers with fall in usage of 2g volume in 7th month are more likely to Churn.
• Customers with stable usage of 3g volume throughout 6 and 7 months are less likely to churn.
• Customers with fall in consumption of 3g volume in 7th month are more likely to Churn.
• The customers with lower total_og_mou in 6th and 8th months are more likely to Churn compared to the ones with higher total_og_mou.
• The customers with lesser total_og_mou_8 and aon are more likely to churn compared to the one with higher total_og_mou_8 and aon.
• The customers with less total_ic_mou_8 are more likely to churn irrespective of aon.
• The customers with total_ic_mou_8 > 2000 are very less likely to churn.

21. Correlation analysis has been performed.
22. We have created the derived variables and then removed the variables that were used to derive new ones.
23. Outlier treatment has been performed. We have looked at the quantiles to understand the spread of Data.
24. We have capped the upper outliers to 99th percentile.
25. We have checked categorical variables and contribution of classes in those variables. The classes with less ccontribution are grouped into ‘Others’.
26. Dummy Variables were created.

Pre-processing Steps

1. Train-Test Split has been performed.
2. The data has high class-imbalance with the ratio of 0.095 (class 1 : class 0).
3. SMOTE technique has been used to overcome class-imbalance.
4. Predictor columns have been standardized to mean - 0 and standard_deviation- 1.

Modelling

Model 1 : Logistic Regression with RFE & Manual Elimination ( Interpretable Model )
Most important predictors of Churn , in order of importance and their coefficients are as follows :

• loc_ic_t2f_mou_8 -1.2736
• total_rech_num_8 -1.2033
• total_rech_num_6 0.6053
• monthly_3g_8_0 0.3994
• monthly_2g_8_0 0.3666
• std_ic_t2f_mou_8 -0.3363
• std_og_t2f_mou_8 -0.2474
• const -0.2336
• monthly_3g_7_0 -0.2099
• std_ic_t2f_mou_7 0.1532
• sachet_2g_6_0 -0.1108
• sachet_2g_7_0 -0.0987
• sachet_2g_8_0 0.0488
• sachet_3g_6_0 -0.0399

PCA: PCA : 95% of variance in the train set can be explained by first 16 principal components and 100% of variance is explained by the first 45 principal components.

Model 2 : PCA + Logistic Regression

Copy 
1Train Performance :  
2
3      Accuracy : 0.627
4      Sensitivity / True Positive Rate / Recall : 0.918
5      Specificity / True Negative Rate :  0.599
6      Precision / Positive Predictive Value : 0.179
7      F1-score : 0.3
8
9      Test Performance :
10
11      Accuracy : 0.086
12      Sensitivity / True Positive Rate / Recall : 1.0
13      Specificity / True Negative Rate :  0.0
14      Precision / Positive Predictive Value : 0.086
15      F1-score : 0.158

Model 3 : PCA + Random Forest Classifier

Copy 
1Train Performance :  
2
3      Accuracy : 0.882
4      Sensitivity / True Positive Rate / Recall : 0.816
5      Specificity / True Negative Rate :  0.888
6      Precision / Positive Predictive Value : 0.408
7      F1-score : 0.544
8
9      Test Performance :
10
11      Accuracy : 0.86
12      Sensitivity / True Positive Rate / Recall : 0.80
13      Specificity / True Negative Rate :  0.78
14      Precision / Positive Predictive Value :0.37
15      F1-score :0.51

Model 4 : PCA + XGBoost

Copy 
1Train Performance :  
2
3  Accuracy : 0.873
4  Sensitivity / True Positive Rate / Recall : 0.887
5  Specificity / True Negative Rate :  0.872
6  Precision / Positive Predictive Value : 0.396
7  F1-score : 0.548
8
9  Test Performance :
10
11  Accuracy : 0.086
12  Sensitivity / True Positive Rate / Recall : 1.0
13  Specificity / True Negative Rate :  0.0
14  Precision / Positive Predictive Value : 0.086
15  F1-score : 0.158

Recommendations :

Following are the strongest indicators of churn

Customers who churn show lower average monthly local incoming calls from fixed line in the action period by 1.27 standard deviations , compared to users who don’t churn , when all other factors are held constant. This is the strongest indicator of churn. Customers who churn show lower number of recharges done in action period by 1.20 standard deviations, when all other factors are held constant. This is the second strongest indicator of churn. Further customers who churn have done 0.6 standard deviations higher recharge than non-churn customers. This factor when coupled with above factors is a good indicator of churn. Customers who churn are more likely to be users of ‘monthly 2g package-0 / monthly 3g package-0’ in action period (approximately 0.3 std deviations higher than other packages), when all other factors are held constant.

Based on the above indicators the recommendations to the telecom company are :

Concentrate on users with 1.27 std devations lower than average incoming calls from fixed line. They are most likely to churn. Concentrate on users who recharge less number of times ( less than 1.2 std deviations compared to avg) in the 8th month. They are second most likely to churn. Models with high sensitivity are the best for predicting churn. Use the PCA + Logistic Regression model to predict churn. It has an ROC score of 0.87, test sensitivity of 100%.

Analysis

Data Understanding

Copy 
1# Importing Necessary Libraries.
2import numpy as np, pandas as pd, matplotlib.pyplot as plt, seaborn as sns
3import warnings 
4warnings.filterwarnings('ignore')
5
6# Setting max display columns and rows.
7pd.set_option('display.max_rows', 500)
8pd.set_option('display.max_columns', 500)

Copy 
1# Reading Dataset into a DataFrame.
2data=pd.read_csv('telecom_churn_data.csv')
3data.head()

Copy 
1# Checking information about data.
2print(data.info())
3def metadata_matrix(data) : 
4    return pd.DataFrame({
5                'Datatype' : data.dtypes.astype(str), 
6                'Non_Null_Count': data.count(axis = 0).astype(int), 
7                'Null_Count': data.isnull().sum().astype(int), 
8                'Null_Percentage': round(data.isnull().sum()/len(data) * 100 , 2), 
9                'Unique_Values_Count': data.nunique().astype(int) 
10                 }).sort_values(by='Null_Percentage', ascending=False)
11
12metadata_matrix(data)

Copy 
1<class 'pandas.core.frame.DataFrame'>
2RangeIndex: 99999 entries, 0 to 99998
3Columns: 226 entries, mobile_number to sep_vbc_3g
4dtypes: float64(179), int64(35), object(12)
5memory usage: 172.4+ MB
6None

Data Cleaning

Copy 
1# Checking if there are any duplicate records.
2data['mobile_number'].value_counts().sum()

Copy 
199999

• Since number of rows is same as distinct mobile numbers, there is no duplicate data

Copy 
1# mobile_number is a unique identifier 
2# Setting mobile_number as the index 
3data = data.set_index('mobile_number')

Copy 
1# Renaming columns 
2data = data.rename({'jun_vbc_3g' : 'vbc_3g_6', 'jul_vbc_3g' : 'vbc_3g_7', 'aug_vbc_3g' : 'vbc_3g_8', 'sep_vbc_3g' : 'vbc_3g_9'}, axis=1)

Copy 
1#Converting columns into appropriate data types and extracting singe value columns.
2# Columns with unique values < 29 are considered as categorical variables. 
3# The number 30 is arrived at, by looking at the above metadata_matrix output. 
4
5columns=data.columns
6change_to_cat=[]
7single_value_col=[]
8for column in columns:
9    unique_value_count=data[column].nunique()
10    if unique_value_count==1:
11        single_value_col.append(column)
12    if unique_value_count<=29 and unique_value_count!=0 and data[column].dtype in ['int','float']:
13        change_to_cat.append(column)
14print( ' Columns to change to categorical data type : \n' ,pd.DataFrame(change_to_cat), '\n')

Copy 
1Columns to change to categorical data type : 
2                    0
30          circle_id
41     loc_og_t2o_mou
52     std_og_t2o_mou
63     loc_ic_t2o_mou
74   std_og_t2c_mou_6
85   std_og_t2c_mou_7
96   std_og_t2c_mou_8
107   std_og_t2c_mou_9
118   std_ic_t2o_mou_6
129   std_ic_t2o_mou_7
1310  std_ic_t2o_mou_8
1411  std_ic_t2o_mou_9
1512   count_rech_3g_6
1613   count_rech_3g_7
1714   count_rech_3g_8
1815   count_rech_3g_9
1916  night_pck_user_6
2017  night_pck_user_7
2118  night_pck_user_8
2219  night_pck_user_9
2320      monthly_2g_6
2421      monthly_2g_7
2522      monthly_2g_8
2623      monthly_2g_9
2724      monthly_3g_6
2825      monthly_3g_7
2926      monthly_3g_8
3027      monthly_3g_9
3128       sachet_3g_6
3229       sachet_3g_7
3330       sachet_3g_8
3431       sachet_3g_9
3532         fb_user_6
3633         fb_user_7
3734         fb_user_8
3835         fb_user_9

Copy 
1# Converting all the above columns having <=29 unique values into categorical data type.
2data[change_to_cat]=data[change_to_cat].astype('category')

Copy 
1# Converting *sachet* variables to categorical data type 
2sachet_columns = data.filter(regex='.*sachet.*', axis=1).columns.values
3data[sachet_columns] = data[sachet_columns].astype('category')

Copy 
1#Changing datatype of date variables to datetime.
2columns=data.columns
3col_with_date=[]
4import re
5for column in columns:
6    x = re.findall("^date", column)
7    if x:
8        col_with_date.append(column)
9data[col_with_date].dtypes

Copy 
1date_of_last_rech_6         object
2date_of_last_rech_7         object
3date_of_last_rech_8         object
4date_of_last_rech_9         object
5date_of_last_rech_data_6    object
6date_of_last_rech_data_7    object
7date_of_last_rech_data_8    object
8date_of_last_rech_data_9    object
9dtype: object

Copy 
1# Checking the date format
2data[col_with_date].head()

• Lets convert the above columns to datetime data type.

Copy 
1for col in col_with_date:
2    data[col]=pd.to_datetime(data[col], format="%m/%d/%Y")
3data[col_with_date].head()

Filtering High Value Customers

• Customers are High Values if their Average recharge amount of june and july is more than or equal to 70th percentile of Average recharge amount.

Copy 
1#Deriving Average recharge amount of June and July.
2data['Average_rech_amt_6n7']=(data['total_rech_amt_6']+data['total_rech_amt_7'])/2

Copy 
1#Filtering based HIGH VALUED CUSTOMERS based on (Average_rech_amt_6n7 >= 70th percentile of Average_rech_amt_6n7)
2data=data[(data['Average_rech_amt_6n7']>= data['Average_rech_amt_6n7'].quantile(0.7))]

Missing Values

Copy 
1#Checking for missing values.
2missing_values = metadata_matrix(data)[['Datatype', 'Null_Percentage']].sort_values(by='Null_Percentage', ascending=False)
3missing_values

Copy 
1# Columns with high missing values , > 50%
2metadata = metadata_matrix(data)
3condition = metadata['Null_Percentage'] > 50 
4high_missing_values = metadata[condition]
5high_missing_values

Copy 
1# Dropping above columns with high missing values 
2high_missing_value_columns = high_missing_values.index 
3data.drop(columns=high_missing_value_columns, inplace=True)

Copy 
1# Looking at remaining columns with missing values 
2metadata_matrix(data)

• data contains information of 04 months - 6,7,8,9.
• For the purpose of missing value treatment, each month’s revenue and usage data is not related to the other months.
• hence, missing value treatment could be performed month wise.

Copy 
1# Month 6

Copy 
1sixth_month_columns = []
2for column in data.columns:
3    x = re.search("6$", column)
4    if x:
5        sixth_month_columns.append(column)
6# missing_values.loc[sixth_month_columns].sort_values(by='Null_Percentage', ascending=False)
7metadata = metadata_matrix(data)
8condition = metadata.index.isin(sixth_month_columns)
9sixth_month_metadata = metadata[condition]
10sixth_month_metadata

• Note that all the columns with *_mou have exactly 3.94% rows with missing values.
• This is an indicator of a meaningful missing values.
• Further note that *_mou columns indicate minutes of usage, which are applicable only to customers using calling plans. It is probable that, the 3.94% customers not using calling plans.
• This could confirmed by looking at ‘total_og_mou_6’ and ‘total_ic_mou_6’ related columns where _mou columns have missing values. If these columns are zero for a customer , then all _mou columns should be zero too.

Copy 
1#  columns with meaningful missing in 6th month 
2sixth_month_meaningful_missing_condition = sixth_month_metadata['Null_Percentage'] == 1.05
3sixth_month_meaningful_missing_cols = sixth_month_metadata[sixth_month_meaningful_missing_condition].index.values
4sixth_month_meaningful_missing_cols

Copy 
1array(['std_ic_mou_6', 'offnet_mou_6', 'std_ic_t2f_mou_6', 'isd_ic_mou_6',
2       'ic_others_6', 'onnet_mou_6', 'std_ic_t2m_mou_6',
3       'loc_ic_t2t_mou_6', 'loc_ic_t2m_mou_6', 'loc_ic_t2f_mou_6',
4       'loc_ic_mou_6', 'std_ic_t2t_mou_6', 'og_others_6', 'spl_og_mou_6',
5       'roam_ic_mou_6', 'spl_ic_mou_6', 'std_og_t2t_mou_6',
6       'loc_og_t2c_mou_6', 'std_og_t2m_mou_6', 'loc_og_t2f_mou_6',
7       'std_og_t2f_mou_6', 'loc_og_t2m_mou_6', 'std_ic_t2o_mou_6',
8       'std_og_t2c_mou_6', 'std_og_mou_6', 'loc_og_t2t_mou_6',
9       'isd_og_mou_6', 'roam_og_mou_6', 'loc_og_mou_6'], dtype=object)

Copy 
1# Looking at all sixth month columns where rows of *_mou are null
2condition = data[sixth_month_meaningful_missing_cols].isnull()
3# data.loc[condition, sixth_month_columns]
4
5
6# Rows is null for all the above columns 
7missing_rows = pd.Series([True]*data.shape[0], index = data.index)
8for column in sixth_month_meaningful_missing_cols : 
9    missing_rows = missing_rows & data[column].isnull()
10
11print('Total outgoing mou for each customer with missing *_mou data is ', data.loc[missing_rows,'total_og_mou_6'].unique()[0])
12print('Total incoming mou for each customer with missing *_mou data is ', data.loc[missing_rows,'total_ic_mou_6'].unique()[0])

Copy 
1Total outgoing mou for each customer with missing *_mou data is  0.0
2Total incoming mou for each customer with missing *_mou data is  0.0

• Hence, these could be imputed with 0

Copy 
1# Imputation
2data[sixth_month_meaningful_missing_cols] = data[sixth_month_meaningful_missing_cols].fillna(0)
3
4metadata = metadata_matrix(data)
5
6# Remaining Missing Values
7metadata.iloc[metadata.index.isin(sixth_month_columns)]