Telecom Customer Churn Analysis

import pandas as pd
import sqlite3

%reload_ext sql

%sql sqlite:///custchurndb.db

%%sql

SELECT * FROM churndata LIMIT 5;

*  sqlite:///custchurndb.db
Done.

Customer_ID	Gender	Age	Married	Number_of_Dependents	City	Zip_Code	Latitude	Longitude	Number_of_Referrals	Tenure_in_Months	Offer	Phone_Service	Avg_Monthly_Long_Distance_Charges	Multiple_Lines	Internet_Service	Internet_Type	Avg_Monthly_GB_Download	Online_Security	Online_Backup	Device_Protection_Plan	Premium_Tech_Support	Streaming_TV	Streaming_Movies	Streaming_Music	Unlimited_Data	Contract	Paperless_Billing	Payment_Method	Monthly_Charge	Total_Charges	Total_Refunds	Total_Extra_Data_Charges	Total_Long_Distance_Charges	Total_Revenue	Customer_Status	Churn_Category	Churn_Reason
0002-ORFBO	Female	37	Yes	0	Frazier Park	93225	34.827662	-118.999073	2	9	None	Yes	42.39	No	Yes	Cable	16	No	Yes	No	Yes	Yes	No	No	Yes	One Year	Yes	Credit Card	65.6	593.3	0	0	381.51	974.81	Stayed	None	None
0003-MKNFE	Male	46	No	0	Glendale	91206	34.162515	-118.203869	0	9	None	Yes	10.69	Yes	Yes	Cable	10	No	No	No	No	No	Yes	Yes	No	Month-to-Month	No	Credit Card	-4.0	542.4	38.33	10	96.21	610.28	Stayed	None	None
0004-TLHLJ	Male	50	No	0	Costa Mesa	92627	33.645672	-117.922613	0	4	Offer E	Yes	33.65	No	Yes	Fiber Optic	30	No	No	Yes	No	No	No	No	Yes	Month-to-Month	Yes	Bank Withdrawal	73.9	280.85	0	0	134.6	415.45	Churned	Competitor	Competitor had better devices
0011-IGKFF	Male	78	Yes	0	Martinez	94553	38.014457	-122.115432	1	13	Offer D	Yes	27.82	No	Yes	Fiber Optic	4	No	Yes	Yes	No	Yes	Yes	No	Yes	Month-to-Month	Yes	Bank Withdrawal	98.0	1237.85	0	0	361.66	1599.51	Churned	Dissatisfaction	Product dissatisfaction
0013-EXCHZ	Female	75	Yes	0	Camarillo	93010	34.227846	-119.079903	3	3	None	Yes	7.38	No	Yes	Fiber Optic	11	No	No	No	Yes	Yes	No	No	Yes	Month-to-Month	Yes	Credit Card	83.9	267.4	0	0	22.14	289.54	Churned	Dissatisfaction	Network reliability

Customer Analysis

Total Customers in the dataset

%%sql

SELECT count(*) as 'Total Customers' FROM churndata;

*  sqlite:///custchurndb.db
Done.

Total Customers
7043

How many customers have churned, joined, and stayed?

%%sql

SELECT customer_status,count(*) as 'Total Customers' 
FROM churndata 
GROUP BY customer_status;

*  sqlite:///custchurndb.db
Done.

Customer_Status	Total Customers
Churned	1869
Joined	454
Stayed	4720

What is the average montly charge for customers who churned, joined, and stayed?

%%sql

SELECT customer_status as 'Status',
FORMAT("$%.2f",avg(Monthly_Charge)) as 'Average Charge for Churned Customers'
FROM churndata 
GROUP BY customer_status;

*  sqlite:///custchurndb.db
Done.

Status	Average Charge for Churned Customers
Churned	$73.35
Joined	$42.78
Stayed	$61.74

What is the average charge based on internet type?

%%sql

SELECT internet_type as 'Internet Type',
FORMAT("$%.2f",avg(Monthly_Charge)) as 'Average Charge for Customers'
FROM churndata
WHERE internet_type != 'None' 
GROUP BY Internet_type;

*  sqlite:///custchurndb.db
Done.

Internet Type	Average Charge for Customers
Cable	$59.36
DSL	$56.79
Fiber Optic	$90.07

What is the total revenue generated by the company?

%%sql

SELECT FORMAT("$%.2f",sum(Total_Revenue)) as 'Total Revenue' 
FROM churndata;

*  sqlite:///custchurndb.db
Done.

Total Revenue
$21371131.69

What are the top ten cities with the most customers? Show how many customers stayed and churned from these cities.

%%sql

SELECT City, count(Customer_ID) as 'Total Customers', 
count(case when customer_status = 'Churned' then Customer_ID end) as 'Churned Customers', 
count(case when customer_status = 'Stayed' then Customer_ID end) as 'Stayed Customers'
FROM churndata
GROUP BY City
ORDER BY count(Customer_ID) DESC
LIMIT 10;

*  sqlite:///custchurndb.db
Done.

City	Total Customers	Churned Customers	Stayed Customers
Los Angeles	293	78	197
San Diego	285	185	93
San Jose	112	29	81
Sacramento	108	26	76
San Francisco	104	31	66
Fresno	61	13	48
Long Beach	60	15	43
Oakland	52	13	34
Escondido	51	16	32
Stockton	44	12	29

Churn Analysis

What is the leading category for customer churn?

%%sql

SELECT Churn_Category,
count(customer_id) as 'Count'
FROM churndata
WHERE Churn_Category != 'None'
GROUP BY Churn_Category
ORDER BY count(customer_id) DESC;

*  sqlite:///custchurndb.db
Done.

Churn_Category	Count
Competitor	841
Dissatisfaction	321
Attitude	314
Price	211
Other	182

What is the percentage of customers that churned and stayed based on the contract?

%%sql

SELECT 
customer_status,
format('%2d%%',count(case when contract = 'Month-to-Month' then contract end)*100/(select count(case when contract = 'Month-to-Month' then contract end) from churndata where customer_status !='Joined')) as 'Month-to-Month',
format('%2d%%',count(case when contract = 'One Year' then contract end)*100/(select count(case when contract = 'One Year' then contract end) from churndata where customer_status !='Joined')) as 'One Year',
format('%2d%%',count(case when contract = 'Two Year' then contract end)*100/(select count(case when contract = 'Two Year' then contract end) from churndata where customer_status !='Joined')) as 'Two Year'
FROM churndata
WHERE customer_status != 'Joined'
GROUP BY customer_status;

*  sqlite:///custchurndb.db
Done.

Customer_Status	Month-to-Month	One Year	Two Year
Churned	51%	10%	2%
Stayed	48%	89%	97%

Looks like month-to-month contracts have a much higher churn rate than any other contract.

What is the percentage of customers that churned and stayed based on Internet Service?

%%sql

SELECT 
customer_status,
format('%2d%%',count(case when Internet_Type = 'Cable' then Internet_Type end)*100/(select count(case when Internet_Type = 'Cable' then Internet_Type end) from churndata where customer_status !='Joined')) as 'Cable',
format('%2d%%',count(case when Internet_Type = 'DSL' then Internet_Type end)*100/(select count(case when Internet_Type = 'DSL' then Internet_Type end) from churndata where customer_status !='Joined')) as 'DSL',
format('%2d%%',count(case when Internet_Type = 'Fiber Optic' then Internet_Type end)*100/(select count(case when Internet_Type = 'Fiber Optic' then Internet_Type end) from churndata where customer_status !='Joined')) as 'Fiber Optic'
FROM churndata
WHERE customer_status != 'Joined'
GROUP BY customer_status;

*  sqlite:///custchurndb.db
Done.

Customer_Status	Cable	DSL	Fiber Optic
Churned	27%	19%	42%
Stayed	72%	80%	57%

What is the average tenure for customers that have churned and stayed?

%%sql

SELECT customer_status, round(avg(Tenure_in_Months),2) as 'Average Tenure', round(avg(Number_of_Referrals),2) as 'Average Referrals'
FROM churndata
WHERE customer_status != 'Joined'
GROUP BY customer_status;

*  sqlite:///custchurndb.db
Done.

Customer_Status	Average Tenure	Average Referrals
Churned	17.98	0.52
Stayed	41.04	2.61

What are the churn counts based on tenure? What percentage customers churned at those tenure amounts?

%%sql

SELECT Tenure_in_months,
count(case when customer_status = 'Churned' then Customer_ID end) as 'Churn Count',
count(case when customer_status = 'Churned' then customer_id end)*100/(SELECT count(case when customer_status = 'Churned' then customer_id end) FROM churndata) as 'Churn Percent'
FROM churndata
GROUP BY Tenure_in_months
ORDER BY count(case when customer_status = 'Churned' then Customer_ID end) DESC
LIMIT 10;

*  sqlite:///custchurndb.db
Done.

Tenure_in_Months	Churn Count	Churn Percent
1	380	20
2	123	6
3	94	5
4	83	4
5	64	3
7	51	2
9	46	2
10	45	2
8	42	2
6	40	2

Findings:

• Month-To-Month contracts and Fiber Optic internet are the leading causes for customer churn.
• 20% of churned customers left after their first month.
• San Diego saw the most churned customers due to competitors.