Python · MySQL · pandas · matplotlib · Power BI · Flight Risk Segmentation · Attrition Analysis
Analysed 1,470 IBM employees across 3 departments and 9 job roles to identify attrition drivers, segment active employees by flight risk, and produce a department-level KPI scorecard. Identified that Sales Representatives churn at 39.8% — 2.5x the organisational average — driven by the lowest income ($2,626/month) of any role. Overtime workers are 3x more likely to leave than non-overtime employees (30.5% vs 10.4%). 264 active employees are currently classified as High Risk. Delivered 5 prioritised retention recommendations targeting the highest-impact levers across compensation, overtime, onboarding, and satisfaction.
HR teams managing large employee bases typically have no structured view of which roles carry the highest attrition risk, what drives employees to leave, or which active employees are most likely to churn next. Without this, retention budgets are spread uniformly rather than targeted at the employees and departments where intervention will have the most impact.
The challenge: build an end-to-end people analytics pipeline that identifies attrition drivers across multiple dimensions, segments active employees by composite flight risk, and produces actionable recommendations that an HR director can act on immediately — replicating the analytical workflow of a People Analyst or HR Business Partner at a data-driven organisation.
IBM HR Analytics Employee Attrition — Real IBM internal HR data released publicly (fictional but structurally real). Single CSV, 1,470 employees, 35 columns.
| File | Description | Rows |
|---|---|---|
ibm_hr_attrition.csv |
Employee demographics, compensation, satisfaction, tenure, and attrition status | 1,470 |
Download from Kaggle · IBM (2015) · Open Data · Place in
input/before running.
RAW DATA (1,470 employees, 35 columns)
│
├── Module 1: Data Cleaning & Feature Engineering
│ Drop constant columns · Binary encode Attrition and OverTime
│ Derive: TenureBand, IncomeBand, AgeBand
│ Compute: SatisfactionScore (avg of 4 satisfaction dimensions)
│ Output: cleaned_hr.csv
│
├── Module 2: Attrition Analysis
│ Attrition rate by Department, JobRole, TenureBand
│ Overtime impact: 30.5% vs 10.4% (with vs without overtime)
│ Income band analysis: Low income = 29.3% vs High = 10.3% attrition
│ Satisfaction quartile analysis: Q1 = 25.0% vs Q4 = 10.6%
│ Output: attrition_by_dept.csv + attrition_by_role.csv
│ + attrition_analysis.csv + 5 charts
│
├── Module 3: Employee Flight Risk Segmentation
│ Composite risk score: Engagement (40%) + Tenure (30%) + Compensation (30%)
│ Assign tiers: High Risk / Watch / Stable / Champion
│ 264 High Risk active employees identified across all departments
│ Output: employee_segments.csv + 2 charts
│
└── Module 4: MySQL Analytics (7 queries)
Q1 Attrition by Department and Job Role · RANK() OVER(PARTITION BY)
Q2 Income vs Attrition Benchmarking · AVG() OVER(PARTITION BY), PERCENT_RANK()
Q3 Overtime Impact by Department · Conditional aggregation
Q4 Satisfaction Quartile vs Attrition · NTILE(4), RANK()
Q5 High Risk Employee Profile · RANK() OVER(PARTITION BY dept)
Q6 Tenure Band Attrition Heatmap · Conditional aggregation pivot
Q7 Department KPI Scorecard · SUM() OVER(), RANK() multi-dimension
Output: q1-q7 result CSVs
Built with Power BI — using query result CSVs exported from the MySQL analytics pipeline.
Supporting chart exports (attrition by role, overtime impact, satisfaction quartile, tenure band, risk distribution):
outputs/
| Module | Output | Key Finding |
|---|---|---|
| Attrition Analysis | 9 roles, 3 departments | Sales Rep: 39.8% attrition — highest in the organisation |
| Overtime Impact | Across all departments | Overtime workers churn at 30.5% vs 10.4% without overtime |
| Satisfaction Analysis | 4 quartiles | Q1 satisfaction = 25.0% attrition vs Q4 = 10.6% |
| Flight Risk Segmentation | 1,233 active employees | 264 High Risk (21.4%), 839 Watch (68.0%) |
New employees (0-1 years) churn at 34.9% — more than double the organisational average. Low income band employees churn at 29.3% vs 10.3% for High income band. R&D has the lowest attrition (13.8%) despite the lowest average income, driven by stronger job involvement and career development.
Q1. Which role poses the highest attrition risk and what is the primary driver?
Sales Representative carries a 39.8% attrition rate — the highest of any role — driven by the combination of the lowest average monthly income in the organisation ($2,626), 28.9% overtime exposure, and high-pressure sales targets. The role loses nearly 2 in every 5 employees annually. Addressing the income floor and overtime dependency in this single role would have the most significant impact on overall organisational attrition.
Q2. Is overtime genuinely causing attrition or just correlated with it?
The pattern is consistent across all three departments and all income bands. Overtime workers in Sales churn at 34.2% vs 12.0% without overtime. In R&D, Research Scientists with overtime churn at 22.1% vs 8.4% without. The relationship holds even when controlling for income level — low-income overtime workers churn at 41.3%. This pattern across departments, roles, and income levels is strong evidence that overtime is a direct driver of attrition rather than a coincidental correlation.
Q3. Which active employees are most at risk of leaving in the next 90 days?
264 active employees are classified as High Risk based on a composite score combining low satisfaction, overtime exposure, and below-role-average compensation. The top priority cohort is the 70 High Risk employees in Sales — they combine the three highest-risk factors simultaneously. A targeted manager intervention for the top 50 employees by risk score is the most efficient use of retention budget.
Q4. What is the revenue impact of the current attrition rate?
At a conservative replacement cost of 50% of annual salary per employee, the 237 churned employees at an average income of $6,503/month represent approximately $8.76M in annual replacement cost (237 x $6,503 x 12 x 0.5). Reducing overall attrition from 16.1% to 13% (by addressing overtime and Sales Rep compensation) would save approximately $1.4M annually in replacement costs.
Q5. At what tenure point does attrition stabilise and what does that mean for onboarding investment?
Attrition drops from 34.9% in year 0-1 to 18.4% in years 2-3 and continues declining to 9.4% for veterans. The sharpest drop occurs between the New (0-1 yr) and Early (2-3 yr) bands — a 16.5 percentage point reduction. This confirms that if an employee survives the first 12 months, they are significantly more likely to stay long-term. Investment in structured 90-day onboarding programmes and early career support delivers disproportionate retention return.
Rec 1 — Sales Representative Compensation and Role Redesign Restructure the Sales Representative role with a higher base salary floor and a reduced overtime dependency to address the 39.8% attrition rate driven by the lowest average income ($2,626/month) of any role in the organisation.
Rec 2 — Overtime Monitoring and Cap Policy Implement a 20-hour monthly overtime cap with mandatory manager approval for exceptions and a real-time dashboard flagging employees exceeding 15 overtime hours per month for a manager check-in.
Rec 3 — New Employee 90-Day Retention Programme Launch a structured onboarding programme with assigned mentors and satisfaction pulse surveys at day 45 to reduce new employee attrition from 34.9% to below 25% within 12 months.
Rec 4 — Satisfaction Early Warning System Deploy quarterly satisfaction pulse surveys and automate alerts when any employee's composite score falls below 2.5, enabling early intervention before Q1 satisfaction employees (25.0% attrition rate) make exit decisions.
Rec 5 — High Risk Employee Intervention Prioritise the top 50 High Risk employees by risk score for immediate manager review, with focus on the 70 High Risk Sales employees who combine low income, high overtime, and low satisfaction simultaneously.
| Recommendation | Target | Expected Outcome |
|---|---|---|
| Sales Rep Redesign | 83 Sales Reps | Attrition from 39.8% to below 25% |
| Overtime Cap | 416 overtime workers | Overall attrition from 16.1% to ~13% |
| 90-Day Onboarding | 215 new employees | New employee attrition to below 25% |
| Satisfaction Early Warning | Q1 employees (n=368) | Q1 attrition from 25.0% to below 18% |
| High Risk Intervention | 264 High Risk employees | Retain top 50 priority employees |
For detailed findings behind each recommendation: insights_report.md
Python — multi-feature engineering pipeline, composite satisfaction score from 4 dimensions, normalised multi-component risk scoring (Engagement + Tenure + Compensation), employee tier classification, matplotlib grouped and horizontal bar charts
MySQL — RANK() OVER (PARTITION BY Department) for within-department risk ranking · AVG() OVER (PARTITION BY JobRole) for role-level income benchmarking · PERCENT_RANK() for income percentile positioning · NTILE(4) for satisfaction quartile segmentation · conditional aggregation for Tenure x Department attrition heatmap · SUM() OVER () for headcount share without subquery · multi-dimension RANK() for department KPI scoring
Business Analysis — attrition driver identification across 7 dimensions, composite flight risk segmentation framework, income benchmarking against role averages, tenure-based onboarding intervention design, executive people analytics report with 5 prioritised recommendations and projected cost impact
HR-Attrition-People-Analytics/
├── README.md
├── insights_report.md
│
├── input/
│ └── ibm_hr_attrition.csv <- download from Kaggle, place here
│
├── data/ <- pipeline-generated files only
│ ├── cleaned_hr.csv <- output of 01_data_cleaning.py
│ ├── attrition_by_dept.csv <- output of 02_attrition_analysis.py
│ ├── attrition_by_role.csv <- output of 02_attrition_analysis.py
│ ├── attrition_analysis.csv <- output of 02_attrition_analysis.py
│ └── employee_segments.csv <- output of 03_employee_segmentation.py
│
├── scripts/
│ ├── 00_mysql_setup.sql <- create DB schema + indexes
│ ├── 01_data_cleaning.py
│ ├── 02_attrition_analysis.py
│ ├── 03_employee_segmentation.py
│ ├── 04_load_mysql.py <- batch load into MySQL
│ ├── 05_mysql_analytics.sql <- 7 MySQL business queries
│ ├── 05_run_analytics.py
│ └── 06_executive_dashboard.py
│
└── outputs/
├── power_bi_dashboard.png
├── chart_attrition_by_dept.png
├── chart_attrition_by_role.png
├── chart_overtime_impact.png
├── chart_overtime_by_dept.png
├── chart_income_vs_attrition.png
├── chart_satisfaction_vs_attrition.png
├── chart_satisfaction_quartile.png
├── chart_tenure_attrition.png
├── chart_risk_segments.png
├── chart_risk_by_dept.png
├── chart_role_attrition.png
├── chart_risk_distribution.png
└── q1_attrition_by_role.csv ... q7_dept_scorecard.csv
pip install pandas matplotlib seaborn mysql-connector-python
# Place ibm_hr_attrition.csv in input/ first
python scripts/01_data_cleaning.py
python scripts/02_attrition_analysis.py
python scripts/03_employee_segmentation.py
mysql -u root -p < scripts/00_mysql_setup.sql
python scripts/04_load_mysql.py --host localhost --user root --password yourpassword
mysql -u root -p hr_analytics < scripts/05_mysql_analytics.sql
python scripts/05_run_analytics.py
python scripts/06_executive_dashboard.pyTools: Python 3.x · MySQL 8.0 · pandas · matplotlib · Power BI