Differentiation in Excel

Question

Based on the table below, identify top four groups of customers who are more likely to churn and which of the seven other categorical variables can be considered as a good differentiator? Choose all that apply

Please note that the first 4 sets includes two category with Yes and No. The second three sets (Internet Service, Contract, Payment method) has its own three categories like internet service has : DSL, Fiber optic, and No.

Senior Citizen: No (0) 23.43% , Yes (1) 41.00%

Partner: No 32.79%, Yes 19.19%

Dependents: No 30.81% , Yes 15.36%

Phone Service: No 24.95%, Yes 26.37%

Internet Service:

DSL: 18.82%

Fiber optic: 41.47%

No: 6.92%

Contract:

Month-to-month: 42.21%

One year: 11.08%

Two year: 2.34%

Payment Method:

Bank Transfer: 16.38%

Credit Card:14.38%

Electronic Check: 45.62%

Mailed Check: 18.77%

Thank you!!

Answer 1

Calculate the rate as:

x% = x*100% = x*1/100 = x

Note that:

Two propositions collectively gives 100%

So we have:

 

Churn                                 No                        Yes                        Rate

Senior Citizen                   76.57%                23.43%                41.00

Partner                                67.21%                32.79%                32.79

Dependents                     69.19%                30.81%                30.81

Phone Service                 75.05%                24.95%                24.95

DSL                                       81.18%                18.82%                18.82

Fiber optic                         58.53%                41.47%                41.47

No Internet                      93.08%                6.92%                  6.92

Month-to-month            57.79%                42.21%                42.21

One year                            88.92%                11.08%                11.08

Two year                           97.66%                2.34%                  2.34

Bank Transfer                  83.62%                16.38%                16.38

Credit Card                       85.62%                14.38%                14.38

Electronic Check              54.38%                45.62%                45.62

Mailed Check                   81.23%                18.77%                18.77

You can see that the top four groups of customers who are more likely to churn are those who are Senior Citizens and have subscribed to the service, those on a month to month contract, those who have Fiber optic internet service, and those who pay using Electronic Check.

You can also see that Internet Service can be considered a good differentiator as there is a significant difference in churn rates between DSL, Fiber optic, and No internet service.