Bank-Customer-Churn-Analysis

BANK CUSTOMER CHURN ANALYSIS USING SQL & Power BI

Live Power bi Dashboard Link

What is Bank Churn?

Bank customer churn, also known as customer attrition, refers to the phenomenon where customers stop doing business with a bank or switch to another bank. Churn is a critical metric for banks as it directly impacts their customer base and revenue. Customer churn can occur due to various reasons, such as dissatisfaction with services, better offers from competitors, changes in financial needs, or poor customer experiences. Understanding and predicting bank customer churn is crucial for banks to proactively manage customer relationships, improve customer satisfaction, and reduce revenue loss. By identifying customers who are at a higher risk of churning, banks can implement targeted retention strategies, personalised marketing campaigns, and tailored customer service to mitigate churn and enhance customer loyalty. Customer-Churn

ABOUT DATA:-

In this dataset, we have 12 columns with 10000 records. This dataset has the following columns :
customer_id, credit_score, country, gender, age, tenure, balance, products_number, credit_card, active_member, estimated_salary, churn

AIM:-

The aim of the data will be to predict Customer Churn.

ANALYSIS:-

create database portfolio;
use portfolio;

create table bank_customer_churn 
( customer_id int,
  credit_score int,
  country varchar(15),
  gender varchar(8),
  age int,
  tenure int,
  balance float,
  products_number int,
  credit_card int,
  active_member int,
  estimated_salary float,
  churn int);

A] Overview of the dataset:

select count(*) as Total_records from bank_customer_churn;

image

Que] Are there any missing values in the dataset?

 select count(distinct customer_id) AS TOTAL_UNIQUE_RECORDS
 from bank_customer_churn;

image NO, THERE ARE NO MISSING ITEMS

In this case, you’re aiming to enrich the bank_customer_churn table by incorporating a new column named credit_score_category.
This code snippet enhances the data by adding a new dimension (credit score category). This WILL be beneficial for further analysis and data visualisation tasks.

alter table bank_customer_churn
add column credit_score_category varchar(10);

update bank_customer_churn
set credit_score_category = CASE
    WHEN credit_score >= 800 THEN 'Excellent'
    WHEN credit_score >= 740 THEN 'Very Good'
    WHEN credit_score >= 670 THEN 'Good'
    WHEN credit_score >= 580 THEN 'Fair'
    ELSE 'Poor'
  END ;

  -- Modify the data type of the age_category column in the bank_customer_churn table to varchar(20). This allows for
     storing more descriptive category labels.
  -- Update the age_category values by categorising customers based on their age ranges using a CASE statement. This enhances
     data organisation and facilitates further analysis.
  
alter table bank_customer_churn
modify age_category varchar(20);

update bank_customer_churn
set age_category=CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 30 THEN 'Young Adult'
    WHEN age >= 30 AND age < 50 THEN 'Middle-aged Adult'
    WHEN age >= 50 AND age < 65 THEN 'Mature Adult'
    ELSE 'Senior'
  END ;

B] Exploratory Data Analysis (EDA):

Que] What is the distribution of the target variable (churn)?

select churn,count(churn) as total_count ,
concat(round(COUNT(churn) * 100.0 / SUM(COUNT(churn)) OVER (),2),'%') AS churn_percentage
 FROM bank_customer_churn 
group by churn;

image

Que] How do the input variables (credit score, age, balance, etc.) vary with respect to churn?

select credit_score_category,count(case when churn=1 then 1 end ) as total_churn_customer
from bank_customer_churn
group by credit_score_category
order by total_churn_customer desc;

image

select age_category,count(case when churn=1 then 1 end ) as total_customer_churn
from bank_customer_churn
group by age_category
order by total_customer_churn desc;

image

select age_category,count(case when churn=1 then 1 end ) as total_customer_churn
from bank_customer_churn
group by age_category
order by tot_customer_churn desc;

image

select country,count(case when churn=1 then 1 end ) as total_churn_customer
from bank_customer_churn
group by country
order by total_churn_customer desc;

image

Que] Are there any correlations between the input variables and churn?

C] Customer demographics analysis:

Que] What is the distribution of customers by country?

select country,count(*) as TOTAL_CUSTOMER
from bank_customer_churn
group by country
ORDER BY TOTAL_CUSTOMER DESC;

image

Que] How does churn vary across different demographic groups?

select country,count(case when churn=1 then 1 end ) as total_churn_customer
from bank_customer_churn
group by country
order by total_churn_customer desc;

image

What is the distribution of customers by the number of products they have?

select products_number,count(*) as total_customer
from bank_customer_churn
group by products_number
order by total_customer desc;

image

How does churn vary based on the number of products a customer has?

select products_number,total_churn_customer,total_customer,
concat(round((total_churn_customer / total_customer) * 100,1),' %') as churn_rate from
(select products_number,count(case when churn=1 then 1 end ) as total_churn_customer,count(*) as total_customer
from bank_customer_churn
group by products_number
order by total_churn_customer desc) as x;

image

E] Customer behaviour analysis:

Que] How does tenure (length of time as a customer) vary with churn?

SELECT tenure,count(case when churn=1 then 1 end ) as tot_churn_customer
from bank_customer_churn
group by tenure
order by tot_churn_customer desc;

Que] Do active members have lower churn rates compared to inactive members?

select active_member,CONCAT(round(total_churn_customer/total,2) * 100,'%') as churn_rates from(
SELECT active_member,count(case when churn=1  then 1 end ) as total_churn_customer,
count(*) total
from bank_customer_churn
group by active_member
order by active_member desc) x;

image

     WITH churn_data AS (
  SELECT credit_score_category,
         COUNT(CASE WHEN churn = 0 and active_member=0 THEN 1 END) AS total
  FROM bank_customer_churn
  GROUP BY credit_score_category
)
SELECT
  credit_score_category,total,
  round(total * 100.0 / SUM(total) OVER (),2) AS per
FROM churn_data
ORDER BY total DESC;

image

If you find this resource helpful, please give it a star ⭐️ and share it with others!