Customer Analytics: Telephone subscribers churn analytics

This project involves creating an ETL pipeline and Power BI dashboard to analyze customer data and identify areas for marketing campaigns and predict future churners.

Project Goals

• Analyze Customer Data at the following levels:
  • Demographic
  • Geographic
  • Payment and Account Information
  • Services
• Study Churner Profile and identify areas for implementing marketing campaigns
• Predict Future Churners

Metrics Required

• Total Customer
• Total Churn and Churn Rate
• New Joiners

SQL - Database Creation and Data Exploration

1. Database Creation:

CREATE DATABASE db_Churn;

2. Data Exploration:

• Check distinct values:

  SELECT * FROM stg_Churn;
  

• Gender distribution:

  SELECT 
    Gender,
    COUNT(Gender) AS TotalCount,
    COUNT(Gender) * 1.0 / (SELECT COUNT(*) FROM stg_Churn) AS Percentage
  FROM stg_Churn
  GROUP BY Gender;
  

• Contract type distribution:

  SELECT 
    Contract, 
    COUNT(Contract) AS TotalCount,
    COUNT(Contract) * 1.0 / (SELECT COUNT(*) FROM stg_Churn) AS Percentage
  FROM stg_Churn
  GROUP BY Contract;
  

• Customer status and revenue impact:

  SELECT 
    Customer_Status,
    COUNT(Customer_Status) AS TotalCount, 
    SUM(Total_Revenue) AS TotalRev,
    SUM(Total_Revenue) / (SELECT SUM(Total_Revenue) FROM stg_Churn) * 100 AS RevPercentage
  FROM stg_Churn
  GROUP BY Customer_Status;
  

• State distribution:

  SELECT 
    State, 
    COUNT(State) AS TotalCount,
    COUNT(State) * 100.0 / (SELECT COUNT(*) FROM stg_Churn) AS Percentage
  FROM stg_Churn
  GROUP BY State
  ORDER BY Percentage DESC;
  

3. Checking for NULL values:

SELECT 
    SUM(CASE WHEN Customer_ID IS NULL THEN 1 ELSE 0 END) AS Customer_ID_Null_Count,
    ...
FROM stg_Churn;

4. Data Transformation and Insertion into Production Table:

SELECT 
    Customer_ID, Gender, Age, Married, State, 
    ISNULL(Value_Deal, 'None') AS Value_Deal, 
    ...
INTO [db_Churn].[dbo].[prod_Churn]
FROM [db_Churn].[dbo].[stg_Churn];

5. Creating Views for Power BI:

CREATE VIEW vw_ChurnData AS
    SELECT * 
    FROM prod_Churn 
    WHERE Customer_Status IN ('Churned', 'Stayed');

CREATE VIEW vw_JoinData AS
    SELECT * 
    FROM prod_Churn 
    WHERE Customer_Status IN ('Joined');

link to full query » SQL

Power BI

1. Data Transformation in Power BI:

• Import the data using Power Query from the prod_Churn table.
• Create additional columns:
  • Churn Status
  • Monthly Charge Range

2. Measures:

• Total customers:

  Total customers = COUNT(prod_Churn[Customer_ID]);
  

• Total churn:

  Total Churn = SUM(prod_Churn[Churn Status]);
  

• New Joiners:

  New Joiners = CALCULATE(COUNT(prod_Churn[Customer_ID]), prod_Churn[Customer_Status] = "Joined");
  

• Churn Rate:

  Churn Rate = [Total Churn] / [Total customers];
  

3. Visualizations:

• Card Visuals for Total customers, Total churn, New joiners, and Churn rate.
• Donut Chart for Gender distribution.
• Line/Stacked Column Chart for churn rate by Age group.
• Bar Chart for churn rate by Contract type and Payment method.
• Matrix Visual for churn by service.

4. Enhancing the Report:

• Adding drop-downs for interaction and tooltips for churn reasons.
• Customizing the background using PowerPoint for a polished look. link to ppt » PowerPoint
• Using Narrative Visual for AI-based report summary.

Analysis

• Key Insights:
  • Most churners are female (64%).
  • Services with the highest churn rates include Device Protection Plan, Online Backup, and Premium Support.
  • Fiber Optic users have the highest churn rate.
  • Month-to-Month contracts have the highest churn rate.
• Recommended Actions:
  • Improve Device Protection Plan, Online Backup, and Premium Support offerings.
  • Address pain points in internet, phone, and unlimited data services. Interactive Report

Predicting Future Churners

• We can predict future churners using the Random Forest algorithm, which can be integrated into the Power BI report. link to notebook » Jupyter Notebook

Conclusion

This project provides a comprehensive analysis of customer churn, allowing businesses to better understand customer behavior and implement strategies to reduce churn and improve customer retention. Project thought process can be seen here Word Document