Time to Feedback Completion

One of the key activities during the interview process is feedback. In order to decrease the amount of time it takes to hire a candidate, it is important to consider how long it is taking interviewers to complete that feedback.

To build a model that will allow a visualization of time to feedback completion, start with finding the appropriate '_events' table. In this case, the appropriate table is 'opportunity_feedback_events'. This table contains columns that record when the interview occurred as well as when the feedback was recorded. To fully contextualize the interview, it is important to join this '_events' table with other tables to allow the data to be sliced in various ways that can help provide insights. This information can be joined with tables that will provide context on the team, department, requisition, feedback form, stage, or location (and more) associated with this feedback event. 

In this example, the goal is to create a model that measures time to feedback completion as the difference between when the interview panel started and when feedback was submitted. The 'interview_panels', 'interview_panel_interviews', and 'interview_panel_interviewers' tables contain information on when the interview panel started, who performed the interview, and when feedback was submitted. This means that it is not necessary to include the 'opportunity_feedback_events' table in the query that will create the model. This information must also be joined with the 'users' table in order to identify the interviewers. It is also possible to build a de-identified report by omitting this join.

The query might look something like this. Notes are included after the double dashes:

-- Tables:
-- interview_panels (ivp) 
-- interview_panel_interviews (ivw) 
-- interview_panel_interviewers (ivs) 
SELECT
 ivs.feedback_completed_at,
 ivp.start_at as "Interview Panel Start",
 age(ivs.feedback_completed_at, ivp.start_at) as "Time from Panel Start to Feedback",
 ivp.interview_panel_id as "Interview panel Id",
 ivw.interview_panel_interview_id as "The interview ID",
 ivs.interview_panel_interviewer_id as "The interviewer ID" 
 usr.name as "Interviewer Name" 
from
 interview_panels ivp
 join interview_panel_interviews ivw on ivp.interview_panel_id = ivw.interview_panel_id
 join interview_panel_interviewers ivs on ivw.interview_panel_interview_id = ivs.interview_panel_interview_id 
 join users usr on ivs.user_id = usr.user_id 
where
 ivp.completed_at is not null -- Panel is complete

This query returns a table that includes when the feedback was completed, how long it took to complete the feedback (from the start time of the interview), as well as the relevant IDs for the interview panel, the interview event, and the interviewer. It also includes a join to display the interviewer's name.

Schema diagram