BI For Beginners

How to measure employee retention

There aren't too many ways to calculate the quality of a company's culture but one of the best indicators is employee retention.

A company that struggles to keep its people is hemorrhaging vital resources and will be less likely to raise funding, attract quality talent and reach key business milestones.

In this post I'm going to cover the step-by-step process that any company can follow to quickly measure employee retention.

What exactly is employee retention?

I define employee retention as the average time that individuals spend in their respective roles within the organization. Employee retention can be broken down across many dimensions, namely gender, department, geography, office, team, age and role.

The main challenge we face is benchmarking time spent in a certain position within a market. You will soon see that measuring retention isn't the problem. The problem is once we have our retention rates how do we know if our rates are good or bad?

I will share a few ideas on how to address this problem.

How to measure employee retention?

Before we can start our analysis we need to get our hands on a data set. I'd bet that the vast majority of companies aren't actively recording the changes in human resources.

If you fall into this category then you have two options. The first is to start recording this information in a spreadsheet or some kind of online CRM from this point on and then revisit this post in 6 months.

The second option is to ask someone in the company to go through whatever records you have and to prepare this data set.

The best data set you can create is a slowly changing dimension like the example I show below.

(Click the image to enlarge it)

employee retention

I want you to notice that John Smith (the first person listed in the table) appears twice. The reason for this is because we are not only tracking each individual that joins our company but also the roles these individuals occupy.

Each row in our table represents a position in the company. Each time someone changes their position in the company we will add a new row.

The role starting and ending date columns helps us track the time period that the individual was in the position. The is Main Row column indicates the most up to date row for each individual.

I know this type of data set is complicating and a bit of a nightmare to maintain but it will give you the most bang for your buck in terms of insights that can be drawn from it.

I don't want to track every single role change, what is the best alternative?

The best alternative to the data set I shared above is the example below.

How to measure employee retention data set example

In this example we have 1 row of data per hire and are recording their first position, first team, first department and standard dimensions like name, gender, date of birth, etc.

Once an employee leaves the company or HR update the spreadsheet, the current position should change from their first position to the position the employee held before he left.

The reason I believe it is important to track current position is because employee retention should be done on the department, team and position levels and you need last position to do this accurately.

I'd also advice to add first and current columns for department and team for the same reasoning as the point above.

The less granular your data the less you can calculate.

My data set is ready, what do I do next?

The next step is to add some helper columns to our data set. I'd take care of this as part of the analysis and not something I'd add to the source spreadsheet. I don't expect HR or whoever is managing the spreadsheet or CRM to add this info.

The fields I'd add would be:

  • Difference in time between joining the company date and exit date in months
  • Difference in time between role starting date and exit date in months
  • Time passed since joining company
  • Number of roles

Now that I have these helper fields I can start answering some of my key questions by building pivot tables in Excel or Tableau. The "time passed since joining company" field can help you normalize the data so you aren't including new hires in your calculations.

In the end you may have a bunch of tables like the one shown below.

employee retention table

Other useful insights you can gather from this data set include:

  • How many employees do I currently have in department A, B and C?
  • How often do employees that started in the product team stay in their first position before changing?
  • Which team has lost the most employees in the last 12 months?
  • Which department has added the most new hires in the last 6 months?
  • Which department has received the most new employees organically in the last 12 months?

Even though I recommend Tableau for doing more advanced manipulations of this data, and to build dashboards to measure this KPI, most of the questions above can be answered in Excel.


Measuring employee retention is in my opinion critical for the success of the company.

I'd argue that companies with more than 500 employees are putting areas of the business at risk if they don't have at least a sense of their employee retention rate.

The biggest take away from this post is to invest in generating a comprehensive data set of your employees. Once you have that in place and a good process to maintain it's accuracy, the rest comes quite easily.

Good luck and let me know if you'd add anything to this post in the comments section below.