Employee Attrition

In today’s highly competitive business environment, informed decision-making is essential for long-term success. Employee retention and job satisfaction are critical issues for any organization as they directly impact productivity, morale, and profitability.

 With the aim of reducing employee turnover and enhancing job satisfaction, ABC Corporation has hired us to undertake a powerful data analysis and A/B testing project. Our mission is to identify key factors influencing job satisfaction and, ultimately, employee retention.

 In this project, we will present the results of our exploratory data analysis, design an A/B experiment to test critical hypotheses, and analyze the results to provide ABC Corporation with valuable insights informing their strategic decisions.

Transforming Talent – Data Analysis to Retain and Empower Employees at ABC Corp

Logo de Github (sólo el contorno del gato)

Scenario

Company Overview

ABC Corporation, founded in 1980 in California, is a technology consultancy specializing in providing artificial intelligence (AI) and machine learning solutions to businesses across various sectors. The company’s primary focus is on automating and optimizing business processes through cutting-edge technologies.

The company distinguishes itself by having a multidisciplinary team comprising experts in UX/UI, marketing, analysts and other relevant fields. This diversity allows for a unique synergy between specialized technical knowledge and varied perspectives, enabling them to deliver customized solutions tailored to the individual needs of each client.

The latest project undertaken by the company involved the optimization of personnel selection processes. They developed an intelligent selection platform where employees can automatically analyze CVs of potential candidates, identify their key skills, and finally classify candidates based on their suitability for specific roles. Additionally, they created a recommendation system to suggest the best candidates to recruiters.

Data Source

The data has been collected internally by the company and shared with us in a CSV file.

Structure

The file comprises 1614 rows of information about each employee of the company, including those who left, and 41 columns with the following information:

  1. Age: The age of the employee.
  2. Attrition: Indicates whether the employee has left the company.
  3. BusinessTravel: Describes the frequency of work-related travel for the employee (e.g., “Travel_Rarely” for rarely).
  4. DailyRate: The daily rate of the employee.
  5. Department: The department in which the employee works (e.g., “Research & Development,” “Sales,” etc.).
  6. DistanceFromHome: The distance from the employee’s home to their workplace.
  7. Education: The education level of the employee (usually on a scale from 1 to 5).
  8. EducationField: The field of education of the employee.
  9. EmployeeCount: A counter usually set to 1 and used to count employees.
  10. EmployeeNumber: A unique identification number for the employee.
  11. EnvironmentSatisfaction: The employee’s satisfaction level regarding their work environment, with values ranging from 1 to 4, where 4 is the highest level of satisfaction.
  12. Gender: The gender of the employee, where 0 corresponds to “male” and 1 corresponds to “female”.
  13. HourlyRate: The hourly rate of the employee.
  14. JobInvolvement: The level of involvement of the employee in their work.
  15. JobLevel: The hierarchical level of the employee in the company.
  16. JobRole: The role or job position of the employee.
  17. JobSatisfaction: The employee’s satisfaction level with their job.
  18. MaritalStatus: The marital status of the employee (e.g., “Single,” “Married,” etc.).
  19. MonthlyIncome: The monthly income of the employee.
  20. MonthlyRate: The monthly rate of the employee.
  21. NumCompaniesWorked: The number of companies the employee has worked for.
  22. Over18: Indicates whether the employee is over 18 years old.
  23. OverTime: Indicates whether the employee works overtime.
  24. PercentSalaryHike: The percentage of salary increase for the employee.
  25. PerformanceRating: The performance rating of the employee.
  26. RelationshipSatisfaction: The level of satisfaction in the employee’s interpersonal relationships.
  27. StandardHours: The standard working hours.
  28. StockOptionLevel: The level of stock options for the employee.
  29. TotalWorkingYears: The total number of years of work experience for the employee.
  30. TrainingTimesLastYear: The number of times the employee received training in the last year.
  31. WorkLifeBalance: The balance between work and personal life for the employee.
  32. YearsAtCompany: The number of years the employee has worked in the current company.
  33. YearsInCurrentRole: The number of years the employee has been in their current role.
  34. YearsSinceLastPromotion: The number of years since the employee’s last promotion.
  35. YearsWithCurrManager: The number of years the employee has been under the supervision of the current manager.
  36. SameAsMonthlyIncome: The monthly income of the employee.
  37. DateBirth: The year of birth of the employee (considering that the data was collected in 2023).
  38. Salary: The salary of the employees.
  39. RoleDepartament: The department and role of the employee.
  40. NumberChildren: The number of children of the employees.
  41. RemoteWork: Whether the employee can work remotely or not.

Phase 1: Exploratory Data Analysis (EDA)

Before conducting the project, A/B testing and formulating hypotheses, it is crucial to better understand the dataset and its characteristics. A detailed exploratory analysis of the dataset was conducted to familiarize ourselves with the information and understand what data we had.

Phase 2: Data Transformation

In this phase, we focused on refining and enhancing the quality of the extracted data through a series of meticulous transformations. The key steps undertaken include:

  1. Column Standardization:
    • Transformed all column names to lowercase and snake case, ensuring uniformity and simplifying further data handling processes.
  2. Data Type Uniformity:
    • Standardized values across columns to ensure consistent data types. For instance, mapping binary values (0 and 1) in the ‘Gender’ column to ‘Male’ and ‘Female’ or converting responses in the ‘remotework’ column to ‘Yes’ or ‘No’ for clarity.
  3. Typographical Error Correction:
    • Addressed typographical errors in categorical columns to maintain accuracy and integrity in the dataset.
  4. Duplicate Removal:
    • Implemented measures to identify and eliminate duplicate records, enhancing data integrity.
  5. Column-Specific Data Type Conversion:
    • Modified data types of specific columns to align with the analysis requirements and improve overall consistency.
  6. Null Value Handling:
    • Evaluated and managed null values using various techniques. Some were replaced with the mode, others with “Unknown” while others were imputed using the k-nearest neighbors (KNN) algorithm, ensuring a comprehensive approach to missing data.
  7. Inconsistent Value Resolution:
    • Identified and addressed inconsistent values, such as negative distances in the ‘DistanceFromHome’ column, to enhance the reliability of the data.

These transformations were implemented using Python functions tailored to the unique characteristics of the dataset. The result is a refined and standardized dataset, laying a solid foundation for subsequent analytical processes.

Phase 3: Database Design and Data Insertion

In this pivotal phase, the objective is to meticulously design the database structure, create the database and insert the refined data. The key components of this phase include:

  1. Database Structure Design:
    • Carefully designed the database structure by identifying essential tables and establishing their relationships. This involved defining primary and foreign keys to ensure data integrity and facilitate efficient querying.
  2. Data Transformation for Database Insertion:
    • Transformed the processed data into a format suitable for database insertion. This included modifying the CSV data into a list of tuples, aligning it with the structure of the designed database tables.
  3. Database Creation:
    • Utilized Python and SQL tools to create the database, establishing connections to facilitate smooth data flow between the application and the database. This step ensures a robust foundation for storing and retrieving information.
  4. Table Creation and Key Definitions:
    • Implemented the designed structure by creating tables within the database. Defined primary and foreign keys to establish relationships and maintain data consistency.
  5. Insertion of Transformed Data:
    • Executed the insertion of meticulously transformed employee data into the respective tables of the database. This step completes the integration of the cleaned and standardized data into the newly created database.

This comprehensive approach not only ensures the establishment of a well-structured database but also guarantees the successful insertion of data, setting the stage for subsequent data analysis and retrieval processes.

With the completion of this ETL process, data has seamlessly transitioned from its source through meticulous transformations, culminating in its insertion into a well-structured database. This organized flow not only ensures the integrity and reliability of the data but also sets the stage for robust analytical processes and insights.

data base structure onthe SQL worckbench. Star model, with a central table with employee personal data and and tables at the points of the star for valuations, salaries and internal company management
def creacion_bbdd_tablas(query, contraseña, nombre_bbdd=None):<br /> """<br /> Crea una conexión a la base de datos MySQL y ejecuta una consulta para crear una tabla.</p> <p> Args:<br /> - query (str): Consulta SQL para crear la tabla en la base de datos.<br /> - contraseña (str): Contraseña para acceder a la base de datos.<br /> - nombre_bbdd (str): Nombre de la base de datos a la que se conectará.</p> <p> Returns:<br /> - None</p> <p> """<br /> if nombre_bbdd is not None:<br /> cnx = mysql.connector.connect(<br /> user="root",<br /> password=contraseña,<br /> host="127.0.0.1"<br /> )</p> <p> mycursor = cnx.cursor()</p> <p> try:<br /> mycursor.execute(query)<br /> print(mycursor)</p> <p> except mysql.connector.Error as err:<br /> print(err)<br /> print("Error Code:", err.errno)<br /> print("SQLSTATE", err.sqlstate)<br /> print("Message", err.msg)<br /> else:<br /> cnx = mysql.connector.connect(<br /> user="root",<br /> password=contraseña,<br /> host="127.0.0.1",<br /> database=nombre_bbdd
bar charts with the different valuation variables
Chi square result of the A/B testing (p-value = 0.003)

Phase 4: Data Analysis

  1. Data Analysis with Visualizations in PythonIn this section, the goal is to conduct a detailed analysis of the data by leveraging Python visualizations, enhancing the interpretation of patterns and trends in job satisfaction and employee turnover.
    • Utilize Python libraries such as Matplotlib and Seaborn to create a variety of visualizations, in order to better observe differences.
    • Analyze visualizations to explore potential relationships between job satisfaction levels and employee turnover. Look for patterns, trends and potential insights that may inform the subsequent A/B testing phase.
  2. A/B Testing for Job Satisfaction and Employee TurnoverIn this crucial phase, the primary goal is to investigate the relationship between job satisfaction and employee turnover, guided by the hypothesis that lower job satisfaction levels are significantly associated with a higher likelihood of employee departure. The structured steps for this analysis are as follows:
    • Group Division: Categorize employees into two distinct groups – Group A “Satisfied” and Group B “Not Satisfied,” based on predetermined criteria (satisfaction rating >= 3).
    • Turnover Rate Calculation: Independently calculate the employee turnover rate for each group, providing insights into departure patterns among satisfied and not satisfied employees.
    • Statistical Analysis: Perform a robust statistical analysis using the chi-square test to determine if there is a statistically significant difference in turnover rates between Group A and Group B.
    • Results Analysis: Thoroughly analyze the obtained results, focusing on patterns, trends, and statistical significance. Quantify the magnitude of the observed relationship using statistical measures such as the chi-square test, providing a numerical understanding of the substantial impact of job satisfaction on employee turnover.

     

Following the initial analysis of job satisfaction and turnover, the exploration extends to additional factors influencing attrition. The focus shifts to salary, categorizing employees into two groups: Group A “High Salary” ( > $5500) and Group B “Low Salary”. A parallel A/B test is conducted within these groups to assess the statistically significant impact of salary on employee turnover.

This meticulous approach ensures not only a thorough exploration of the relationships but also places a strong emphasis on statistically significant findings, reinforcing the reliability and importance of the results in informing decision-making processes.

Phase 5: Reporting and Dashboard Creation with Power BI

In this reporting phase, the objective is to convey the findings effectively through visualizations and dashboards created using Power BI

  1. Data Preparation: Ensure that the data is formatted appropriately for Power BI, addressing any necessary transformations.
  2. Power BI Dashboard Design: Create a dashboard that encapsulates key insights from the A/B testing and visual analysis, including relevant visualizations to present the data in a compelling and easily understandable manner.
  3. Insightful Storytelling: Craft a narrative within the dashboard, guiding viewers through the story of the analysis. Clearly communicate the significance of the A/B testing results and visual findings.
  4. Sharing and Distribution: Share the dashboard with relevant stakeholders, ensuring accessibility and ease of understanding. Provide any necessary documentation to aid interpretation.

Conclusions

The key findings of the study were:

  • There is overall positive job satisfaction.
  • There is a significant relationship between job dissatisfaction and quit rate. .
  • The quit rate is even higher when salaries are low. .
  • Improve working conditions and pay, particularly for those employees who want to be retained. .