|
|
This guide assumes that you are comfortable navigating the schema documentation. |
One of the key metrics to measure the efficiency of your recruiting process is the time it takes to hire a candidate. There are a variety of ways to measure this depending on the specific business case. Regardless of the specifics of the metric, measuring time to hire helps provide insight into the impacts of changes to your recruiting process. This guide makes the assumption that time to hire is measured from the moment the opportunity is created until the moment the opportunity is marked as hired.
To build a model that will allow you to visualize time to hire, start by finding the appropriate '_events' table. In this case, the appropriate table is the 'opportunity_progress_events' table. This table contains columns that record how the opportunity progressed as well as keys to join this table with other tables that will provide the necessary context to slice the data. Using these keys to join the events with other tables can provide context on the associated posting, requisition, agency (if applicable), department, or location (and more) associated with this opportunity. To build the model, we will need to include all opportunities that were hired, when the opportunity was created, and when the opportunity was hired. The 'opportunity_progress_events' table contains all of this information. The query might look something like this. Notes are included after the double dashes:
select
opportunity_id as "Opportunity ID", -- Identify the Opportunity
min(event_at) as "Apply Date", -- Select the earliest event
max(event_at) as "Hire Date", -- Select the latest event
age(max(event_at), min(event_at)) as "Time Apply To Hire" -- Perform the calculation
from
opportunity_progress_events opv
where
stage_milestone_id in ('applicant', 'hire') -- Filter the data so that we are only including events that represent applications or hires
group by
opportunity_id
having
count(distinct stage_milestone_id) > 1 -- Ensure that the candidate was hired
This query returns a table with a single row for each hired opportunity and includes the opportunity ID, the application date, the hired date, and the calculated difference between the values. Using your data visualization tool, you can further group these rows by time interval - perhaps by month or by quarter - and display the average time to hire for each time interval. Below is a diagram of what a schema of this kind might look like.
