Telecom Analysis

Business Questions

1. Average tenure in months?
2. Contract type by Customer status?
3. Total charges by Customer status?
4. Number of referrals by Customer status?
5. How many customers joined the company during the last quarter? How many customers joined?
6. What is the customer profile for a customer that churned, joined, and stayed? Are they different?
7. What seem to be the key drivers of customer churn?
8. Is the company losing high value customers? If so, how can they retain them?
9. What zip code are customers churning in?

Insight

1. Average churn is 32.4 months.
2. 89% of Churn customers are on a month-to-month contract, 90% of Join customers are on a month-to-month contract compared to Stayed customers where only 33% of customer are on a month-to-month contract.
3. Stayed Customer spent $13.1M, Churned Customer spent $2.8M, Joined Customer spent $35k
4. Stayed Customer referred 12,342 customers, Churned Customer referred 974 customers, Joined Customer referred 431 customers
5. 1051 customers joined last quarter (597 Churned and 454 Joined)
6. Churned customers spend on average $73.35, Stayed customers spend on average $61.73, and Joined customers spend on average $42.77
7. 45% of customer noted "Competitor" as the reason for leaving. Followed by "Dissatisfaction" and "Attitude" with 17% of customers. Any finally "Price" with 11% and "Other" with 10% of customers.
8. Yes. Our most valuable customer have the highest average monthly charge as well as the highest churn. We can look at offering incentives to drive more people to sign 1 to 2 year contracts since most customers have month to month
9. 92126, 92117, 92122, 92109, 92028 these zipcode hold a large number of customer that are churning.

Code

1. Average tenure in months?

Code
SELECT 
    AVG(Tenure_in_Months)
FROM
    telecom_customer_churn;

2. Contract type by Customer status?

SELECT 

    COUNT(DISTINCT Customer_ID), Customer_Status, Contract

FROM

    telecom_customer_churn

GROUP BY 2 , 3;

3. Total charges by Customer status?

SELECT 

    Customer_Status, SUM(Total_Charges) AS TotalCharges

FROM

    telecom_customer_churn

GROUP BY Customer_Status

ORDER BY TotalCharges DESC;

4. Number of referrals by Customer status?

SELECT 

    Customer_status, SUM(Number_of_Referrals) AS TotalReferrals

FROM

    telecom_customer_churn

GROUP BY Customer_Status

ORDER BY TotalReferrals DESC;

5. How many customers joined the company during the last quarter? How many customers joined?

SELECT 

    Customer_Status,

    COUNT(Customer_ID) AS NewCustomerThisQuarter

FROM

    telecom_customer_churn

WHERE

    Tenure_in_Months <= 3

GROUP BY Customer_Status;

6. What is the customer profile for a customer that churned, joined, and stayed? Are they different?

SELECT 

    Customer_Status, AVG(Monthly_Charge) AS AvgMonthlyCharge

FROM

    telecom_customer_churn

GROUP BY Customer_Status

ORDER BY 2 DESC;

7. What seem to be the key drivers of customer churn?

SELECT 

    COUNT(Customer_ID) AS NumberofCustomers,

    Churn_Category,

    Customer_Status

FROM

    telecom_customer_churn

WHERE

    Customer_Status = 'Churned'

GROUP BY 2

ORDER BY 1 DESC;

9. What zip code are customers churning in?

SELECT 

    Customer_Status, tc.Zip_Code, SUM(population)

FROM

    telecom_customer_churn tc

        LEFT JOIN

    telecom_zipcode_population tp ON tc.Zip_Code = tp.Zip_Code

GROUP BY 1 , 2

ORDER BY 3 DESC;