Figuring out Employee Churn with Power Query [HR Analytics]

Posted on by

Let’s say you are the people manager at ACME Inc. You are looking staff list for the months – January and February 2017. You see that we had 4,000 employees in Jan and 4,200 employees in Feb. So what is the churn?

  • Is it just 200?
  • Or is it the sum of people who left and who joined?
  • What if you want to find out how many people moved to new designations / departments or groups?

You see, churn is tricky to figure out.

So why not invite the pros? ie Power Query. 

employee-churn-analysis-pq

Side note: You could also calculate churn in Excel (using formulas) or in SQL (by using long queries and unions with a boat load of joins)

Setting up your data – Churn analysis

The first step is to set up two sets of data (one for each point in time). Make sure that you include only relevant columns. Let’s say we go with below layout.

employee-churn-analysis-data

For the sake of simplicity, let’s call these tables thismonth and lastmonth. You can include the data date columns, but they are not necessary for the analysis.

Define churn

We can categorize churn in to one of these 6 levels.

  1. New employees
  2. Exits
  3. People who moved to a new group
  4. People who moved to a new branch
  5. People who moved to a new designation
  6. No changes

Figuring out the churn – Power Query

Here is the process to calculate the churn using Power Query.

  1. We create a third query by merging two datasets (thismonth and lastmonth) on employee number as Full Outer join (think of this as A union B in sets – ie any employee present in either months will be included)
  2. We define a custom column in this new query, called status. It will have
    1. New employees if emp number is null in last month’s column
    2. Exit if emp number is null in this month’s column
    3. New group if thismonth.group <> lastmonth.group
    4. New Branch if thismonth.branch <> lastmonth.branch
    5. New Designation if thismonth.designation <> lastmonth.designation
    6. No changes else
  3. We delete all rows with no changes (as we are only interested in churn)
  4. Load this data to Excel

Figuring out employee churn – Power Query lesson video

Since the process is somewhat technical and confusing, I made a video explaining everything in detail. Check it out below.

 

You can watch this video on our youtube channel too.

Download Example Workbook

Click here to download example workbook. You must have Power Query 2013 or Excel 2016 to customize anything.

More on Power Query

Power Query is awesome. If you haven’t explored its power, check out below tutorials and get started.

How do you calculate churn?

As mentioned earlier, we can use either Excel formulas or SQL to calculate churn. If I am only interested in high-level churn (ie entries and exits), I use a simple formula. But for anything more than that, I prefer Power Query or SQL (as PQ is not be available in all versions of Excel)

What about you? How do you calculate and analyze churn? Please share your approach and tips in the comments.