A Recruiting Dashboard by M. Firlus

In this article I want to focus on a recruiting dashboard with the following specifications.

1. User group: Recruiter
2. One Recruiter needs to fill one Junior Frontend Developer role
3. S/he already has filled a role with the same profile some weeks ago
4. Data from the previous search is available and will be used as goal component for KPI setup
5. Usages of an artificial data set
6. Location remains the same
7. Focus on following recruiting stages: Lead Generation, Prescreening, Interview, Offer, Signing)
8. Tool: Excel (Power Query, Power Pivot, DAX)

1.  Introduction

Let’s imagine you are a recruiter at…let me think….Cyberdyne Systems (AI & Robotics) and your Hiring Manager – Miles Dyson – approaches you to hire a new Junior Frontend Developer. The new hire shall support in executing routine work regarding the implementation of a new User Interface for Cyberdyne’s cash cow: Skynet.

Lucky you, you already have placed a similar profile some weeks ago and got the chance to build relationship with Miles. At the end you have understood the basics about the product and tech stack as well as where the Frontend team needs additional support.

So, let’s get back to your Hiring Manager. Miles is asking you the following question: 

When can he expect a signing?

Besides this, you ask yourself more detailed questions:

1. What are your most relevant recruiting indicators/events and how many of them you need (Leads, Prescreens, IV’s, Offers & Signings)?
2. How do you distribute (timely) the generation of those recruiting events?
3. Which budget you need to allocate?
4. What is your reference for evaluation?
5. How do you measure success?

To answer those questions you need some data, right?

2.  Where do you get your data from?

Normally each ATS provides at least a CSV file export of your recruiting indicators: Leads, Prescreens, IV’s , Offers & Signing. Export them per recruiting project and needed timeframe. IMPORTANT: If you want to compare recruiting data, you must make sure, that your funnel design is more or less the same and the indicators are used consistently. If this is not the case, start by defining your main indicators and their usage.

2.1. Recruiting Indicators[1]

1.  Leads – CV’s with contact data and interest by Candidate
2. Prescreens – Recruiter Calls
3. IV’s – 1st Interview with Hiring Manager & Candidates
4. Offers – 1st mail with offer details in a written form
5. Signing – Date when the main contract is signed by the new hire

Normally 80 % of all white-collar recruiting projects have those indicators (known as stage steps or funnel stages) no matter if you want to recruit an intern or C-Level role.

2.2. Historical data from your ATS

A pivot table based on your CSV file export/import could provide the data seen in Table 1. Based on that we can give Miles the following answer: We probably will need 4 weeks to get the Signing of our new Junior Frontend Developer. And to answer our own questions we will probably need: 75 Leads, 34 Prescreens, 16 IV’s 3 Offers to generate 1 Signing. To keep it simple we try to copy the past and take the totals and weeklies as goal component of our KPI setup.

We are aware that an exact replication is not possible, also that we do guestimations because we just have this one comparable old recruiting project without statistical significance (To get also Data Scientist on our side, we need to base our forecast calculation on around 50-100 same datasets and stable side conditions (market situation etc.).

3.  KPI Setup

Anyway, let’s set up the KPI’s. We take the historical numbers and calculate measures for benchmarking the search in general as well as recruiting indicators per week. To see immediately the status in the detailed KPI view (Table 2) we choose a traffic light status in Figure 1.

3.1. Weekly KPI’s - Recruiting Indicators per week

Having a look at Table 2 we see the recruiting indicators (2.1) after passing week 1 of the recruiting campaign. The recruiter could generate 8 Leads but having a LeadGoal of 15 which causes a red light on the LeadStatus section.

1. Leads/Week
2. Prescreen/Week
3. IV/Week
4. Offer/Week
5. Signing/week

3.2. Grand Total KPI’s

Besides the weekly status we also should have a view on how we perform towards the Grand Totals in Table 1 shown in Figure 2.

The horizontal column chart shows the current week of search after completed week 1. The donut charts show what we have generated so far measured against the Grand Totals in Table 1. In week 1 we could generate 8 Leads which is way below we had guesstimated for week 1 and which is 11% of the Grand Totals of 75 Leads we assumed to need for 1 Signing. Still 67 Leads to generate in Week2-4. 

4.  The Dashboard - Week 1/4

Besides the above mentioned elements you see some additional dashboard specifications on top of Figure 3. The clients name, hiring manager, targeted Cost per Hire, some forecast data as well as used recruiting channels and a section for relevant recruiting events. Also, you see the navigation pane on the right to navigate threw all of the weeks of search. In Figure 4 you will see the results after we have signed our new Junior Frontend Developer.

5.  The Dashboard - Week 4/4

To interpret the KPI’s in section 4 the right way always have in mind that the perfect recruiting process will show 1 / 100% on Lead-2-Signing KPI (between charts & Navigation Pane) which means you need 1 Lead for 1 Signing which is a conversion rate of 100%. So, it’s not really dramatic if your status is just red on the Lead side, as long the status improves on the following KPI’s like Prescreen in week 2 or 100% fulfillment of IV’s in week 3 or the forecasted Signing in week 4.

6.  Final Remarks

This draft of a recruiting dashboard visualizes some important indicators and KPI’s to steer your recruiting. There is a lot to improve but also a lot you can already work with.

To use your already gained historical data you have to make sure, that your recruiting data/indicators are consistently used and collected. To compare data you need to have a similar recruiting stage/funnel design for each recruiting project.

Talk to your ATS Support, to deep dive how your ATS can(not) support you in terms of reporting.

Improve your technical analytical skills, talk to your internal BI and Marketing team. Start thinking data driven, attend some Excel / Power BI lessons.

7.   Special thanks to the following BI experts

• Christoph Spieles & Artur König and their awesome meetup group: Berlin Power BI Group
• Michael Weiner and his Power Query Workshops
• Marco Russo/ Alberto Ferrari and their mindblowing DAX Guide
• Mynda Tracy and her fabolous Excel Traininghub

8.  Abbreviations

ATS – Application Tracking System

CSV File – Comma-Separated-Value File

9.   Q&A

How do you like the dashboard draft?

What is missing?

[1] This article is a snapshot about what you can track and focuses on Lead-2-Signing. Mail campaigning via e.g. LinkedIn or Career Page Tracking (Google Analytics/Tag Manager) is not part of this article.