Introduction.

This project pretends to explore and make a diagnostic analysis of the customer churn of a bank and extract relevant and valuable insights for detecting and preventing future customer churn. It's broadly known that bringing new customers is more expensive than retaining the current ones, so this analysis will be crucial for improving the company's profit.

The main tool will be SQL, and just a few DataViz inside the same notebook to bring more clarity.

The dataset was taken from Kaggle: https://www.kaggle.com/datasets/radheshyamkollipara/bank-customer-churn

These are the 3 main questions that it pretends to answer:

- ¿What was the churn rate for the data given?

- ¿What could be the reasons for customers to decide to quit?

- ¿Is there anything the bank can do to avoid customer churn?

In the Key insights and recommendations, you can find the key takeaways of this analysis without any process, it's a quick view and valuable summary of the findings.

In the Queries and process, you will find the complete process and queries used to find the insights from scratch and a more detailed explanation.

In the Good to know, you can see other interesting but not much relevant findings and measures taken from the dataset.

Here is a snapshot of the dataset used:

SELECT * FROM 'Customer-Churn-Records.csv';

Key insights and recommendations.

What does NOT have an impact:

Customers with different credit scores had similar percentages of staying or quitting. A high score doesn't guarantee higher odds of staying, and a low score doesn't mean more odds of quitting.

The churn rate was not significantly different between different tenures. The distribution of churn percentages was similar across all tenures, except for two outliers, which is expected and not a significant factor in the decision to quit.

The account balance didn't influence the churn rate. There were no specific account balances where customers quit at a higher or lower rate.

What does have an impact:

Customers with one product represent the majority (69.14%) of the total churn. Focus on understanding their needs and finding solutions to retain them.

Customers with four products had a 100% churn rate, and those with three products had an 82.71% churn rate. Although they represent less than 14% of the churn, it's essential to investigate and address the reasons behind this trend.

63.94% of churned customers were inactive members, while 36.06% were active members. Keep customers engaged to reduce churn.

Complaints play a significant role in churn. Almost all churned customers (99.80%) filed a complaint, whereas almost all staying customers (99.87%) have never complained.

Recommendations

Develop a strategy to build stronger relationships with customers with only one product. Identify their pain points and create solutions to improve retention.

Analyze the fees and perks offered to customers with three and four products compared to competitors. Consider adjusting benefits to promote retention in these groups.

Implement actions to prevent customers from becoming inactive. Regularly communicate the benefits and advantages of staying active.

Conduct a detailed analysis of complaint handling and its impact on customer retention. Focus on turning complaints into positive experiences.

Queries and process

¿What is the churn rate for the data given?

Churn rate = 20.38%

To get this, I counted the customers grouped by the “Exited” field, which says “True” for the customers who ended the relationship with the company and “False” for customers who didn’t, and calculated the percentage of each one.

SELECT "Exited", COUNT("CustomerId") AS count, CAST(COUNT("CustomerId") * 100.0 / SUM(COUNT("CustomerId")) OVER () AS decimal (4, 2)) AS percentage FROM 'Customer-Churn-Records.csv' GROUP BY "Exited";

¿What can be the reason customers decide to quit?

To address and answer this question it’s necessary to start by creating a hypothesis and check the data to see if the hypothesis is true or not. Below it’s listed the facts that may influence the customer’s loyalty and that can be taken into account to generate the hypothesis:

- Credit score - Tenure - Balance - Number of products - Credit card owning - Activeness - Estimated salary - Complaint - Satisfaction score - Card type - Points earned

Then will be necessary to analyze each field compared with the churn to see if there is any pattern or relationship.

To answer this question, I’m going to compare the field “Exited” with each field that can affect customer loyalty.

Are the customers with a high credit score more likely to stay with us and the ones with a low score more likely to quit? Or vice-versa?

No, the customers have gone and have stayed by very similar percentages according to their credit scores. A high score doesn't imply higher odds of staying or quitting; on the flip side, a lower score doesn't mean more odds of quitting or staying.

To get a snapshot of the scores:

SELECT "Exited", AVG("CreditScore") AS "Average credit score", MAX("CreditScore") AS "Maximun credit score", MIN("CreditScore") AS "Minimun credit score" FROM 'Customer-Churn-Records.csv' GROUP BY "Exited";

As we can see, the average credit score doesn't strongly relate to the churn. The 20.38% of customers who decided to quit had similar average credit scores to those who didn't. The max and min credit scores for both were also similar, but I will dive deeper into this:

Now, I will rank the credit scores by FICO Score Ratings:

Exceptional: 800 to 850 Very Good: 740 to 799 Good: 670 to 739 Fair: 580 to 669 Very Poor: 300 to 579

¿How many customers with exceptional credit scores have gone?

SELECT COUNT(*) AS customers, CAST((customers * 100.0) / (SELECT COUNT(*) AS total_churned FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1) AS decimal (6,2)) AS "% of total churn" FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1 AND "CreditScore" >= 800 ;

With these numbers, we see that the customers with exceptional credit scores who have gone represent only 6.28% of the total customers who have gone.

¿And the ones with very good credit scores?

SELECT COUNT(*) AS customers, CAST((customers * 100.0) / (SELECT COUNT(*) AS total_churned FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1 AND "CreditScore" BETWEEN 740 AND 799 ;

The participation of customers with very good credit scores in the total churn is 12.41%, double of the customers with exceptional credit scores.

¿Good credit scores who have gone?

-- Count and percentage of the total churn for customer with good credit score who quit. SELECT COUNT(*) AS customers, CAST(customers * 100.0 / (SELECT COUNT(*) AS total_churned FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1 AND "CreditScore" BETWEEN 670 AND 739 ;

The amount of customers continues rising, with this category at 22.18%

¿What about the customers with fair credit scores?

-- Count and percentage of the total churn for customer with fair credit score who quit. SELECT COUNT(*) AS customers, CAST(customers * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1 AND "CreditScore" BETWEEN 580 AND 669 ;

It keeps going up, now with 33.61% of the total churn.

¿And the ones with very poor credit scores, in the total churn?

SELECT COUNT(*) AS customers, CAST(customers * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE "Exited" = 1 AND "CreditScore" BETWEEN 300 AND 579 ;

The customers with the lowest credit category are 25.52% of the total churn.

The customers with fair credit scores represent the more significant portion of the churn with 33.61%, followed by the customers with very poor credit scores with a percentage of 25.52%, the third place is the customers with good credit scores with 22.18%, then the ones with very good credit scores with 12.41% and finally the exceptional credit scores with just a 6.28%.

This shows us a trend, but we need to be mindful of the percental distribution of the credit scores with all the customers and not only with the exited ones because if the trend looks similar to one generated by the customers who still are active, then the trend generated by the churned customers would not meaning anything. Let's see the trend with all the customers.

SELECT CASE WHEN "CreditScore" >= 800 THEN 'Exceptional' WHEN "CreditScore" BETWEEN 740 AND 799 THEN 'Very good' WHEN "CreditScore" BETWEEN 670 AND 739 THEN 'Good' WHEN "CreditScore" BETWEEN 580 AND 669 THEN 'Fair' ELSE 'Very poor' END AS ScoreCategory, COUNT(CustomerId), CAST(COUNT(CustomerId) * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv') AS decimal (6,2)) AS "% of total" FROM 'Customer-Churn-Records.csv' GROUP BY ScoreCategory ORDER BY ANY_VALUE(CreditScore) ;

The number of customers with the same score category who have gone is proportional to the ones who haven't. It means that a high or low credit score is not the main reason for the quit.

¿Are our customers more likely to quit when they are relatively new and more likely to stay with us when they are tenured?

The churn was not significantly different between the tenures; almost all the 10 tenures represent percentages around 10%, which shows a similar distribution; however, there are 2 outliers, and actually, they are the top and the bottom of tenures, but the same is true for the customers who didn't quit so it's just an expected outlier, and we can conclude the tenure does not play an important role in the decision to quit.

To resolve this, I bring the count and percentage of customers who quit segmented by their tenure.

SELECT CASE WHEN Tenure = 0 THEN 'Less than a year' WHEN Tenure = 1 THEN '1 Year' WHEN Tenure = 2 THEN '2 Years' WHEN Tenure = 3 THEN '3 Years' WHEN Tenure = 4 THEN '4 Years' WHEN Tenure = 5 THEN '5 Years' WHEN Tenure = 6 THEN '6 Years' WHEN Tenure = 7 THEN '7 Years' WHEN Tenure = 8 THEN '8 Years' WHEN Tenure = 9 THEN '9 Years' ELSE '10 Years' END AS Tenure_text, COUNT(CustomerId) AS Customer_count, CAST(COUNT(CustomerId) * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY Exited, Tenure ORDER BY Tenure ;

The data shows a reduction in exited customers when they have less than a year and when they have 10 years; now let's see if that reduction is present in the customer who didn't quit.

SELECT CASE WHEN Tenure = 0 THEN 'Less than a year' WHEN Tenure = 1 THEN '1 Year' WHEN Tenure = 2 THEN '2 Years' WHEN Tenure = 3 THEN '3 Years' WHEN Tenure = 4 THEN '4 Years' WHEN Tenure = 5 THEN '5 Years' WHEN Tenure = 6 THEN '6 Years' WHEN Tenure = 7 THEN '7 Years' WHEN Tenure = 8 THEN '8 Years' WHEN Tenure = 9 THEN '9 Years' ELSE '10 Years' END AS Tenure_text, COUNT(*) AS Customer_count, CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 0) AS decimal (6,2)) AS "% of total" FROM 'Customer-Churn-Records.csv' WHERE Exited = 0 GROUP BY Tenure, Exited ORDER BY Tenure ;

The percentages remain slightly similar.

¿Does the balance in the customer's account influence their decision to quit?

The customers left indistinctly of the account balance. There are no certain account balances where the customers quit at a higher or lower rate.

-- To get a snapshot of the balances in the accounts: SELECT Exited, AVG(Balance), MAX(Balance), MIN(Balance) FROM 'Customer-Churn-Records.csv' GROUP BY Exited ;

In the following query, we can see the customer count by account balance. A normal distribution is present in the total count as well as in the churn count:

SELECT CASE WHEN Balance = 0 THEN '$0' WHEN Balance BETWEEN 0 AND 10000 THEN '$0 to $10,000' WHEN Balance BETWEEN 10001 AND 25000 THEN '$10,000 to $25,000' WHEN Balance BETWEEN 25001 AND 50000 THEN '$25,000 to $50,000' WHEN Balance BETWEEN 50001 AND 75000 THEN '$50,000 to $75,000' WHEN Balance BETWEEN 75001 AND 100000 THEN '$75,000 to $100,000' WHEN Balance BETWEEN 100001 AND 125000 THEN '$100,000 to $125,000' WHEN Balance BETWEEN 125001 AND 150000 THEN '$125,000 to $150,000' WHEN Balance BETWEEN 150001 AND 175000 THEN '$150,000 to $175,000' WHEN Balance BETWEEN 175001 AND 200000 THEN '$175,000 to $200,000' ELSE '$200,000 and above' END AS Balance_text, COUNT(*) AS Customer_count FROM 'Customer-Churn-Records.csv' -- Desactive the WHERE clause to see the same for the total customers -- WHERE Exited = 1 GROUP BY Balance_text ORDER BY ANY_VALUE(Balance) ;

¿The more products the customer has, the less likely to quit?

100% of the customers with 4 products quit, and 82.71% of customers with 3 products quit; however, those 2 categories represent less than 14% of the churn, so it would be nice to look into and fix it, but the greatest impact would be the category of 1 product as it represents 69.14% of the total churn.

Count by the number of products of all customers:

SELECT NumOfProducts, COUNT(CustomerId) AS Customer_count FROM 'Customer-Churn-Records.csv' GROUP BY NumOfProducts ORDER BY Customer_count DESC;

Count by the number of products of exited customers, the percentage of the total customers, and the total churn.

WITH subquery AS ( SELECT NumOfProducts, COUNT(CustomerId) AS total_customer_count FROM 'Customer-Churn-Records.csv' GROUP BY NumOfProducts ) SELECT main.NumOfProducts, main.COUNT(CustomerId) AS "Count of customers", CAST((main.COUNT(CustomerId)*100.0/subquery.total_customer_count) AS decimal (6,2)) AS "% of total", CAST((main.COUNT(CustomerId)*100.0/(SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1)) AS decimal (6,2)) AS "% of churned" FROM 'Customer-Churn-Records.csv' AS main INNER JOIN subquery ON main.NumOfProducts = subquery.NumOfProducts WHERE main.Exited = 1 GROUP BY main.NumOfProducts, subquery.total_customer_count ORDER BY main.COUNT(CustomerId) DESC ;

¿Were the exited customer credit card owners?

The exited customers had credit cards in the same proportion as those who didn't quit, keeping the number around 70% with credit cards.

SELECT HasCrCard, COUNT(*) AS "Count of customers", CAST(COUNT(*) * 100.0/(SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (4,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY HasCrCard ORDER BY HasCrCard ;

¿Were the churned customers active members?

63.94% of the churn were inactive members, while the remaining 36.06% were active members.

SELECT IsActiveMember, COUNT(CustomerId) AS Customers, CAST(Customers * 100.0/(SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (4,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY IsActiveMember ;

¿Were the customers with a certain range of salaries more likely to quit?

The churn rate was relatively consistent across the different salary ranges so it does not represent an important fact in this review.

To see the range of salaries in the whole dataset:

SELECT AVG(EstimatedSalary), MAX(EstimatedSalary), MIN(EstimatedSalary) FROM 'Customer-Churn-Records.csv' ;

Creating the salary ranges to analyze the churn:

SELECT CASE WHEN EstimatedSalary BETWEEN 0 AND 25000 THEN '$0 to $25,000' WHEN EstimatedSalary BETWEEN 25001 AND 50000 THEN '$25,000 to $50,000' WHEN EstimatedSalary BETWEEN 50001 AND 75000 THEN '$50,000 to $75,000' WHEN EstimatedSalary BETWEEN 75001 AND 100000 THEN '$75,000 to $100,000' WHEN EstimatedSalary BETWEEN 100001 AND 125000 THEN '$100,000 to $125,000' WHEN EstimatedSalary BETWEEN 125001 AND 150000 THEN '$125,000 to $150,000' WHEN EstimatedSalary BETWEEN 150001 AND 175000 THEN '$150,000 to $175,000' ELSE 'More than $175,000' END AS "SalaryRange", COUNT(CustomerId) FROM 'Customer-Churn-Records.csv' -- Desactive the WHERE clause to see the same for the total customers WHERE Exited = 1 GROUP BY SalaryRange ORDER BY ANY_VALUE(EstimatedSalary) ;

¿Do complaints influence churn?

Complaints play a crucial role in churn. 99.80% of churned customers filed a complaint. Likewise, 99.87% of customers that stayed with us have never complained.

WITH subquery AS ( SELECT Exited, COUNT(*) AS total FROM 'Customer-Churn-Records.csv' GROUP BY Exited ) SELECT main.Complain, main.Exited, CAST(COUNT(main.CustomerId) * 100.0/subquery.total AS DECIMAL(10, 2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' AS main INNER JOIN subquery ON main.Exited = subquery.Exited GROUP BY main.Complain, main.Exited, subquery.total;

¿Did customer satisfaction influence churn?

The churn was equal across the 5 satisfaction scores, so it does not represent a critical factor.

SELECT "Satisfaction Score", CAST(COUNT(CustomerId) * 100.0/(SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (10,2)) AS "% of churned customers", FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY "Satisfaction Score" ORDER BY "satisfaction Score" ;

¿Had the exited customers a certain card type?

No, the customers who have gone had different card types in pretty similar proportions.

SELECT "Card Type", CAST(COUNT(CustomerId) * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (6,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY "Card Type" ;

¿Do customers with a high amount of points not quit?

Not necessarily. The churn was equal across the different ranges of points earned, so it does not play a relevant role in the churn.

To see the whole range of points earned:

SELECT MIN("Point Earned"), AVG("Point Earned"), MAX("Point Earned") FROM 'Customer-Churn-Records.csv' ;

Creating ranges to segment the count:

SELECT CASE WHEN "Point Earned" < 100 THEN '0 - 99' WHEN "Point Earned" < 200 THEN '100 - 199' WHEN "Point Earned" < 300 THEN '200- 299' WHEN "Point Earned" < 400 THEN '300 - 399' WHEN "Point Earned" < 500 THEN '400 - 499' WHEN "Point Earned" < 600 THEN '500 - 599' WHEN "Point Earned" < 700 THEN '600 - 699' WHEN "Point Earned" < 800 THEN '700 - 799' WHEN "Point Earned" < 900 THEN '800 - 899' ELSE '900 - 1000' END AS Points, COUNT(CustomerId), CAST(COUNT(CustomerId) * 100.0 / (SELECT COUNT(*) FROM 'Customer-Churn-Records.csv' WHERE Exited = 1) AS decimal (10,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY Points ORDER BY ANY_VALUE("Point Earned") ;

Good to know

Churn by gender:

Out of the total number of people who left, 55.89% were women and 44.11% were men.

SELECT Gender, COUNT(CustomerId) AS Customers, CAST(COUNT(CustomerId)*100/ SUM(COUNT(CustomerId)) OVER() AS decimal (10,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY Gender ;

Churn by country:

Spain has the lowest churn, while France and Germany have almost 40% with 39.79% and 39.94% respectively.

SELECT Geography, COUNT(CustomerId) AS Customers, CAST(COUNT(CustomerId)*100/ SUM(COUNT(CustomerId)) OVER() AS decimal (10,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' WHERE Exited = 1 GROUP BY Geography ;

Churn by age:

SELECT Age, Exited, COUNT(CustomerId) AS Customers, CAST(COUNT(CustomerId)*100/ SUM(COUNT(CustomerId)) OVER() AS decimal (10,2)) AS "% of churn" FROM 'Customer-Churn-Records.csv' --WHERE Exited = 1 GROUP BY Age, Exited ORDER BY Age ;