Embedded Files
Bank Customer Attrition
A comprehensive Power Query and Excel-based solution focused on predicting and analyzing the primary drivers of customer churn in the banking sector, identifying at-risk segments for targeted retention efforts.
The Business Problem & Goal
The bank faced a critical challenge in retaining profitable customers, leading to revenue loss and high acquisition costs. The goal was to develop a predictive model and an analytical report to clearly identify the demographic (Age, Income, Education) and behavioral (Transaction Count, Service Calls, Tenure) factors most correlated with the risk of customer attrition (Churn_Status).
Advanced Data Modeling (Excel & Power Query)
Advanced Data Modeling (Excel & Power Query)
This project utilized Microsoft Excel as the primary analytical platform, leveraging Power Query for data preparation to analyze customer attrition drivers.
This project utilized Microsoft Excel as the primary analytical platform, leveraging Power Query for data preparation to analyze customer attrition drivers.
Data Consolidation
Data Consolidation
Power Query was essential for merging the three source tables (Customer_Info, Account_Activity, Transaction_Credit) using the Customer_ID key to build a unified dataset.
Data Cleaning:
Missing values (e.g., Age, Income_Range) and inconsistent text entries (e.g., Marital_Status) were systematically cleaned and standardized within Power Query.
Feature Engineering:
New analytical columns were engineered to measure customer engagement, such as the Credit Utilization Ratio and Total Transaction Count, which were critical factors in the attrition prediction model.
The Interactive Solution (Full Dashboard Demo)
The Interactive Solution (Full Dashboard Demo)
Key Insights & Findings
Key Insights & Findings
Low Engagement is High-Risk:
Customers categorized as "Attrited" showed significantly lower Total Transaction Counts and higher Inactive Months compared to existing customers. This confirmed that low engagement is the strongest predictor of churn.
Service & Frustration Correlation:
Customers who utilized Customer Service Calls 4 times or more had an attrition rate that was 50% higher than the average. This suggests that repeated service calls often indicate unresolved frustration, leading to the loss of the customer.
Tenure & Product Risk:
The analysis revealed that customers with Tenure less than 18 months and those holding only one or two Bank Products are the most vulnerable segments, requiring immediate pro-active engagement and tailored retention offers.
Business Impact & Conclusion
This analysis provided the bank with a clear, data-driven understanding of the attrition problem. By identifying the direct correlation between low engagement (transaction count) and service call frustration with the risk of churn, the bank was empowered to:
Business Impact & Conclusion
This analysis provided the bank with a clear, data-driven understanding of the attrition problem. By identifying the direct correlation between low engagement (transaction count) and service call frustration with the risk of churn, the bank was empowered to:
Proactive Retention: Implement targeted retention campaigns focused on the high-risk segment (low-tenure, low-product count customers).
Service Optimization: Rework the customer service protocol for repeated callers to ensure resolution on the first or second call, directly mitigating a key driver of customer loss.
The Excel/Power Query solution effectively converted raw behavioral data into a strategic tool for maximizing customer lifetime value (CLV
Page updated
Google Sites
Report abuse