Find the Retention Rate

image

Summary of the Project

Selected to interview at a fast growing start-up by developing a high level people analytics Tableau dashboard. This dashboard received positive feedback between 2 C-Suite level executive's within the company. It enabled stakeholders valuable insight regarding their people's team; more specifically YoY metrics regarding the following KPI's : retention rate, hiring rate, and productivity.

Problem

Create a dashboard utilizing a BI tool that has the following features:

  • Allows stakeholders to identify, collect, aggregate, and analyze employee data that informs and evaluates business needs and questions about their employee population.
  • Include sophistated predictive and regression models that dive deep into the organization's people data
  • Create interactive dashboards that tell an analytical story, but allows the user to drill down through filters and parameters.
  • Ensure that the dashboard is both scalable and clean

Solution

Please click on the bookmark below to view the dashboard.

Please note:

  • All of the emoji's are clickable buttons

Process

Tools Utilized

  • Figma
  • Kaggle
  • Tableau
  • Excel

Data Set Used:

Steps

  1. Brainstorming - Sketch some dashboards using Figma Jam

Thought Process:

I spent around 4-6 hours brainstorming and thinking about what the end result. I spent some time reading Josh Bersin's 2021 HR report to be up to speed regarding key metrics within the People Analytics space. Reading that report helped me develop 15-20 KPI's that I thought would be important to the stakeholders. I also started to vision board - I had 2-3 dashboards that I looked at for inspiration for this project. Once I had a good picture of what kind of data that I wanted to analyze, I started to sketch some dashboards using figma jam.

image
  1. Create the dashboard using Figma

Thought Process:

Once I felt comfortable with the sketch, I made two dashboards for this project. I ended up scrapping the first dashboard because I thought it didn't look good enough. Creating a vision board earlier really helped. I found 2-3 dashboards that I really liked and modeled my dashboard after that. The main inspiration for the dashboard was an image that I found off the company website. I based my color scheme and design of that image.

Main Inspiration for Dashboard:

image

Prototype Dashboard:

image

Final Dashboard:

image
  1. Executing - Clean the data / Transform the Data / Data Integrity using Excel

Thought Process:

Cleaning The Data

Once I downloaded the dataset from Kaggle, I used the following functions to make sure that there is no duplicates or unwanted null values:

  • Count Distinct
  • Count Blank
  • Count If

If there is any values that turn up null, I would come up with the following solutions:

  • Name the value "Missing" "N/A" or something to categorize the missing value.

I checked out the data using my data cleaning framework below and everything was accurate.

Transforming the Data

As I mentioned earlier, I had to make up the data for production and OKR's. While the values used were random, I wanted to make sure that calculations used were accurate. My goal was to simulate real results and ensure that my tableau calculations were correct. When I made the calculations in in Tableau, I also made sure that these calculations were accurate in Excel.

Data Integrity

The deliverable mentioned that they wanted predictive results regarding Forecasting and Regression. Since I made up the data and it was based on random numbers with a small sample size, I knew that these measures wouldn't provide any meaningful insight.

The next best thing I could do is showcase the following:

  • I understood these statistical measures conceptually
  • I was able to implement them in a real world use case.

Tableau has a cool function that gives you the ability to add trend lines and forecast, so this was an easy deliverable.

image

I used the following framework below to data clean:

  • Data Cleaning:
    • Clean for no data
    • Too little data
    • Wrong Data - Changes for errors
      • Margin of Error
      • Confidence Level
      • Population
  • Account for the following:
    • Duplicate Data
    • Outdated Data
    • Incomplete Data
    • Incorrect Data
    • Inconsistent Data

      • Dummy Data:

      image
  1. Executing - Analyze the data using Tableau

Thought Process:

I really wanted to showcase my knowledge of measuring productivity and HR with this project. While the stakeholders wanted to know how the productivity of each department / employee, I had industry knowledge that FAANG companies don't use the actual productivity calculation as the main measure. Google is famous for using Objective Key Results as a measure and framework for success. I made the decision to keep the productivity calculation in my dashboard, but I included OKR scores as well. If you look at the HR Data, you would notice that there is no information in regards to productivity or OKR's. I created dummy data using the random between function in Excel. The main thought process behind this is to showcase that I can measure these metrics, rather than finding insights. I quickly looked through the columns and thought about interesting things that I could filter.

Here is a list of interesting insights that could be unlocked with BI tools:

  • How much employee's are in each department?
  • What is the average pay for black female?
  • What's the performance of a male white guy in production?

Here is a list of filters / parameters / categories / insights that I wanted to include in the project:

  • Hiring Rate vs PY
  • Engagement Rate vs PY
  • Retention Rate vs PY
  • Production Rate vs PY
  • Number of Employees by Gender
  • Details Tab for Productivity
  • Forecasting
  • Regression
  • Year Filter
  • Department by Position
    • Include Drill Downs
  • Pay by Race

Once I had a list of what I wanted to accomplish, the rest of the project came down to execution and putting it in the right places.

image

Formula for productivity below:

image

Sample OKR:

image

Further Exploration

Diversity and Inclusion

I wanted to include a filter for transwomen / transmen / gay / lesbian / non-binary and other, but the dataset didn't allow for it. This is something that very passionate about because I have close relatives that identify as such.

Feedback Back for Employers

During the interview process, I received feedback from stakeholders. They had some additional asks and asked my thought process on how I would provide that information.

Additional Ask 1:

How would you approach finding the cost-per-hire in our company?

The formula is listed below:

image

Internal Recruiting Costs would be the following:

  • Employee referral bonuses
  • Recruiting Salary
  • Interview Costs

External Recruiting Costs would be the following:

  • Advertising Costs (Job Boards, PPC)
  • Recruiting Software
  • Recruiting Events
  • Non-internal recruiter fees
  • Flights / Relocation / Misc

I should be able to obtain this information through the Applicant Tracking System and other job posting boards.

Discussion topics to involve stakeholders:

  • Why are we measuring cost-per-hire?
  • Cost-per-hire should vary from position to position. What do you think about breaking it down by position / department with drill downs?
  • What action would we preform with this information?
  • Is it beneficial to increase the cost-per-hire to obtain greater candidates?

Additional Ask 2:

Is it possible to come up with a metric that shows if someone pregnant will leave?

Thought Process:

What are the factors that determine showcase pregnancy?

  • Not attending happy hour
  • Stop smoking
  • Food aversions
  • Fatigue
  • Ask with Culture AMP

What are the factors that determine if someone will leave?

  • Num of times checking Linkedin
  • Taking extended PTO
  • Not attending meetings
  • Low performance
  • Low scores on engagement survey
  • Low scores on satisfaction survey
  • Low response on emails

How can we obtain these insights?

  • CultureAmp
  • Survey Monkey
  • Google Survey

How do we calculate these factors to validate:

A / B testing

Further questions for analysis:

How long would it take for us to obtain a sample size to accurately determine this information?

I think getting this information would be a lengthy and time consuming process. The better solution is to go directly to CultureAmp / Pew Data Research / Research Papers to give us insights in regards to this.

Instead of coming up with a predictive method, the more cost-efficient method would be figuring asking reliable sources for the following information:

  • % chance of pregnant people leaving in all organizations.
  • What factors will increase the rate of retention of pregnant people?
  • Provide resources to pregnant people
  • Utilize CultureAmp or other feedback tools to obtain feedback in our procedures.