This page introduces users to the Sourcing tab of Eightfold (EF) Talent Acquisition (TA) Feature Usage & Outcomes Dashboard. The target audience is users of the dashboard, including the Head of Talent Acquisition, Talent Ops, Eightfold Admins, Recruiting Managers, Recruiters, and Sourcers.

Eightfold Sourcing Features covered

Following features are covered under Sourcing tab: Chrome Extension, Referrals, Import Resume, Recruiting Events

Filter controls

Dashboard filter controls can be used to refine or subset the data shown in the dashboard. For example, to select a certain date range or select a particular feature.

Filter controls are located at the top of the dashboard. Filter controls have to be read with context based on the type of tab.

Sections

• Overview: This section highlights the top level adoption and outcome metrics associated with all the 4 Eightfold Sourcing features.
• User Activity: This section highlights the average weekly and monthly active users, their respective retention percent and how that trends with time
• Hiring Outcomes: This section highlights the funnel conversion from Source to Hire related metrics associated with the candidates who were sourced via the EF sourcing features
• Hiring Efficiency: This section highlights the time taken between stages related as well as diversity related metrics associated with the candidates who were sourced via the EF sourcing features
• Wall of Fame: This highlights the best sourcers in terms of sourced candidates, hiring outcomes and efficiency
• Raw Data: This sections contains the candidate level data of all candidates sourced via the EF sourcing features

Metric Definitions

Overview

Active Sourcing Employees

• Description: The unique number of employees who has done atleast one sourcing activity associated with one of these sourcing features: Chrome Extension, Referrals, Import Resume, Recruiting Events.
  • (Note: All employees who have clicked on any of the options within the Chrome extension which resulted in contacting a candidate or adding a candidate to the talent pool will also be considered here - in the iterative versions of the dashboard, we will make this definition even more precise)

• Formula in Quicksight & Dataset calculation:

  • Quicksight: distinct_count(active_user_id)

  • Analytics table sources:
    

    • Recruiting events: planned_event_activity (any activity on the candidate in the Recruiting Events UI)

    • Import User: user_analytics (event = ‘import_profile’)

    • Chrome Extension: user_analytics (event = ‘extension_click’)

    • Referrals: user_analytics (event = ‘refer_to_job’)

• Dataset(s):

  • ta_feature_sourcing_efficiency_and_outcomes

Candidates Sourced

• Description: The unique number of profile_ids (candidates) sourced via the Eightfold Sourcing Features.(Note: If a candidate’s profile was viewed with Chrome Extension tab visible and if the employee had performed any action on the candidate profile using the Chrome Extension, even those profiles will be considered as sourced - in the iterative versions of the dashboard, we will make this definition even more precise)
• Formula in Quicksight & Dataset calculation:
  1. Quicksight: distinct_count(profile_id)
  2. Analytics table sources:
     1. Recruiting events: planned_event_activity (any activity on the candidate in the Recruiting Events UI)
     2. Import User: user_analytics (event = ‘import_profile’)
     3. Chrome Extension: user_analytics (event = ‘extension_click’)
     4. Referrals: user_analytics (event = ‘refer_to_job’)
• Dataset(s): ta_feature_sourcing_efficiency_and_outcomes

Applied

• Description: The ratio of candidates who got converted into applicants and the candidates sourced via the Eightfold Sourcing Features
• Formula in Quicksight & Dataset calculation:

  Quicksight: {Total Applicants}/{Total Candidates Sourced}

  where,

  1. {Total Applicants} = distinct_count({applicant_profile_id})
     1. Analytics table: ta_leaders_hiring_outcomes
  2. {Total Candidates Sourced} = distinct_count({candidate_sourced_profile_id})
     1. Analytics table:
        1. planned_event_activity
        2. user_analytics
• Dataset(s): ta_feature_sourcing_efficiency_and_outcomes

Hired

• Description: The ratio of candidates who got hired and the candidates sourced via the Eightfold Sourcing Features
• Used in Sections:
• Formula in Quicksight & Dataset calculation:

  Quicksight: {Total Hired Candidates}/{Total Candidates Sourced}

  where,

  1. {Total Hired Candidates} = distinct_countIf({applicant_profile_id},isNotNull({min_hired_date}))
     1. Analytics table: ta_leaders_hiring_outcomes
  2. {Total Candidates Sourced} = distinct_count({candidate_sourced_profile_id})
     1. Analytics table:
        1. planned_event_activity
        2. user_analytics
• Dataset(s): ta_feature_sourcing_efficiency_and_outcomes

Female Hires

• Description: The percentage of female hires among the hired candidates (who were sourced via Eightfold Sourcing Features)
• Formula in Quicksight & Dataset calculation:

  Quicksight: {Total Hired Candidates}/{Total Candidates Sourced}

  where,

  1. {Total Hired Candidates} = distinct_countIf({applicant_profile_id},isNotNull({min_hired_date}))
     1. Analytics table: ta_leaders_hiring_outcomes
  2. {Total Candidates Sourced} = distinct_count({candidate_sourced_profile_id})
     1. Analytics table:
        1. planned_event_activity
        2. user_analytics
• Dataset(s): ta_feature_sourcing_efficiency_and_outcomes

% Employees Sourcing

• Description: The share of current employees (excluding alumnis) using the EF sourcing features (this metric will change only with ‘Sourcing Feature Type’ filter and will always show you the value till date)
• Formula in Quicksight & Dataset calculation:
  • Numerator =

    ifelse(${SourcingFeatureType} = 'Recruiting Events' {active_employees_using_recruiting_events}, ${SourcingFeatureType} = 'Chrome Extension', {active_employees_using_chrome_extension}, ${SourcingFeatureType} = 'Referrals', {active_employees_using_referrals}, ${SourcingFeatureType} = 'Import Resume', {active_employees_using_import_resume}, {total_active_sourcing_employees})

  • Denominator = count(distinct t1.employee_email) as total_employees

• Dataset(s): ta_feature_sourcing_breadth_of_usage_across_users

% Sourcers, Recruiters, Event Team Sourcing

• Description: Among all the current employees, what % of sourcers or recruiters or event team sourcing members are using the EF sourcing features (this metric will change only with ‘Sourcing Feature Type’ filter and will always show you the value till date)

  Note: This data will be accurate only if the roles are mapped correctly while the user profile is ingested/imported/created in Eightfold (you can double check the role of a user in the Admin Console > Provisioning > Manage Users

• Formula in Quicksight & Dataset calculation:
  • Numerator =

    ifelse(${SourcingFeatureType} = 'Recruiting Events', {active_potential_sourcers_using_recruiting_events}, ${SourcingFeatureType} = 'Chrome Extension', {active_sourcers_using_chrome_extension}, ${SourcingFeatureType} = 'Referrals', {active_potential_sourcers_using_referrals}, ${SourcingFeatureType} = 'Import Resume', {active_potential_sourcers_using_import_resume}, {total_active_employees_among_potential_sourcers})

  • Denominator = count(distinct t1.employee_email) as total_employees

• Dataset(s): ta_feature_sourcing_breadth_of_usage_across_users

% Sourcers Sourcing

• Description: Among all the current employees, what % of sourcers’ are using the EF sourcing features (this metric will change only with ‘Sourcing Feature Type’ filter and will always show you the value till date).
  Note: This data will be accurate only if the roles are mapped correctly while the user profile is ingested/imported/created in Eightfold (you can double check the role of a user in the Admin Console > Provisioning > Manage Users
• Formula in Quicksight & Dataset calculation:
  • Numerator =

    ifelse(${SourcingFeatureType} = 'Recruiting Events', {active_sourcers_using_recruiting_events}, ${SourcingFeatureType} = 'Chrome Extension', {active_sourcers_using_chrome_extension}, ${SourcingFeatureType} = 'Referrals', {active_sourcers_using_referrals}, ${SourcingFeatureType} = 'Import Resume', {active_sourcers_using_import_resume}, {total_active_sourcers})

  • Denominator = count(distinct t1.employee_email) as total_employees

• Dataset(s): ta_feature_sourcing_breadth_of_usage_across_users

Efficiency & Outcomes

This section gives you a quick peek into how the candidates sourced are being advanced into the stages of hiring as well as the time taken from sourcing to hire and sourcing to phone screen

Recruitment funnel metrics

• Candidates Sourced
• % Applicants = Applicants / Candidates Sourced
• % Phonescreened = Phone Screened candidates / Candidates Sourced
  • where, Phone Screened candidates = The candidates who have been advanced to the ‘phonescreen’ stage group

• % Interviewed = Interviewed candidates / Candidates Sourced

  • where, Interviewed candidates = The candidates who have been advanced to the ‘onsite’ or ‘interview’ stage group

• % Hired = Hired candidates / Candidates Sourced

  • where, Hired candidates = The candidates who have been advanced to the ‘hired’ stage group

Recruitment funnel metrics trend

• The trend lines parallel to the funnel denotes how these metrics vary with time and the x-axis denotes the time at which the candidates where sourced

  Example: If the % Applied = 40% in May 2023, it means that among the candidates who were sourced in May 2023, 40% of them have got converted as Applicants

Hired Candidate Stats

• Female Hires %
• Days between Source to Hire: The difference in days between the source timestamp and the the time at which the candidate is advanced to the ‘hired’ stage (we also make sure the sourcing timestamp takes place before the application is submitted)
  • Formula = ifelse({activity_timestamp} <= {application_ts} AND isNotNull({min_hired_date}), dateDiff({activity_timestamp}, {min_hired_date},'DD'),0)
  • Use case: This will help the sourcers to plan on when to source candidates and how much time does it generally take for the candidate to be advanced to the ‘hired’ stage group

• Days between Source to Phonescreen: The difference in days between the source timestamp and the the time at which the candidate is advanced to the ‘phonescreen’ stage (we also make sure the sourcing timestamp takes place before the application is submitted)

  • Formula = ifelse({activity_timestamp} <= {application_ts} and isNotNull({min_phonescreened_date}), dateDiff({activity_timestamp}, {min_phonescreened_date},'DD'),0)

  • Use case: Since phone screen is the first stage in the hiring funnel and the time taken to hire is not controlled by the sourcer, this will give an idea of how the sourcer can contribute in speeding up the hiring process

Insights

This section will help the sourcers’ answer the following questions and change their sourcing strategy as needed. (this insight can be controlled by all the filters in the controls section)

• how many candidates to source for getting at least 1 hire (based on historical data) so that the sourcer can plan on sourcing ‘x’ candidates to get ‘n’ hires
• Candidates Sourced QoQ: How does the trend in sourcing candidates vary based on the previous calendar quarter and if they have sourced enough candidates.
• % Applied QoQ: How has the applicant conversion changed compared to the previous calendar quarter? If the applicant conversion has gone down, then is there something different that the sourcers should be doing?

User Activity

Weekly Adoption

• Avg Weekly Active users: It is the average number of Weekly active users using one of these Eightfold Sourcing Features in the selected time frame
  • YoY metric: denotes how the Avg Weekly Active Users has changed compared to the previous year.

• Avg Weekly Retention (Repeat Users) %: It denotes the percentage of users in a given week who were active in the previous week as well

  • YoY metric: denotes how the Avg Weekly Repeat users % has changed compared to the previous year.

• Dataset(s): ta_feature_sourcing_adoption_by_week

Monthly Adoption

• Avg monthly Active users: It is the average number of monthly active users using one of these Eightfold Sourcing Features in the selected time frame
  • YoY metric: denotes how the Avg monthly Active Users has changed compared to the previous year.

• Avg monthly Retention (Repeat Users) %: It denotes the percentage of users in a given month who were active in the previous month as well

  • YoY metric: Denotes how the Avg monthly Repeat users % has changed compared to the previous year.

• Dataset(s): ta_feature_sourcing_adoption_by_month

Overall Adoption

• Description: This section gives you the aggregate number of users for a selected period of time, how they trend with time across the sourcing features and user level breakdown
• Dataset(s): ta_feature_sourcing_efficiency_and_outcomes

Hiring Outcomes & Hiring Efficiency

This section contains the breakdown of the metrics that we displayed in the Overview section.

KPI (on the left): Denotes the aggregate value of the metric for a selected period of time

Trended chart (in the middle): Displays the KPI breakdown by feature and how it trends with time. The X-axis always denotes the timestamp associated with the sourcing activity

User level breakdown (on the right): Displays the KPI breakdown by user and how it trends with time. The X-axis always denotes the timestamp associated with the sourcing activity

Wall of Fame

This section highlights all the top performing sourcers in terms of:

• candidates sourced
• sourced to hire ratio %
• time taken between source to phone screen
• Diverse Applicants %
• Hired Candidates
• time taken between source to hire

Datasets used

Note: In the queries below, replace the keyword ‘volkscience’ with the relevant customer name to replicate the data pull through data hub

ta_feature_source_acitivity_log

With extension_referrals_activity_log AS (
SELECT
t0.group_id,
(
case when event = 'extension_click' then 'Chrome Extension' when event = 'refer_to_job' then 'Referrals' when event = 'import_profile' then 'Import Resume' else 'others' end
) as activity_type,
user_id as active_user_id,
concat(
t2.first_name,
concat(' ', t2.last_name)
) as active_user_name,
manager_name,
profile_id,
position_id,
employee_title,
employee_level,
timestamp as activity_timestamp,
employee_seniority,
employee_line_of_business,
employee_business_unit,
is_alumni,
employee_location,
employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race,
count(event) as feature_usage
FROM
public.user_analytics t0
left join (
select
group_id,
employee_email,
manager_email,
manager_name,
first_name,
last_name,
case when title IS NULL then 'Title not assigned' else title end as employee_title,
case when level IS NULL then 'Level not assigned' else level end as employee_level,
case when seniority IS NULL then 'Seniority not assigned' else seniority end as employee_seniority,
case when lob IS NULL then 'Level of Business not assigned' else lob end as employee_line_of_business,
case when business_unit IS NULL then 'Business unit not assigned' else business_unit end as employee_business_unit,
is_alumni,
case when location IS NULL then 'Location not found' else location end as employee_location,
case when location_country IS NULL then 'Location Country not found' else location end as employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race
from
public.employee
) t2 on t0.group_id = t2.group_id
and trim(lower(t0.user_id)) = trim(lower(t2.employee_email))
where
(
event = 'extension_click'
OR event = 'refer_to_job'
OR event = 'import_profile'
)
and timestamp >= '2022-01-01'
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24
having
activity_type != 'others'
),
recruiting_events_activity_log AS (
SELECT
t0.group_id,
'Recruiting Events' as activity_type,
created_by as active_user_id,
concat(
t2.first_name,
concat(' ', t2.last_name)
) as active_user_name,
manager_name,
profile_id,
position_id,
employee_title,
employee_level,
employee_seniority,
employee_line_of_business,
employee_business_unit,
is_alumni,
employee_location,
employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race,
created_at as activity_timestamp,
count(*) as feature_usage
FROM
public.planned_event_activity t0
left join (
select
group_id,
employee_email,
manager_email,
manager_name,
first_name,
last_name,
case when title IS NULL then 'Title not assigned' else title end as employee_title,
case when level IS NULL then 'Level not assigned' else level end as employee_level,
case when seniority IS NULL then 'Seniority not assigned' else seniority end as employee_seniority,
case when lob IS NULL then 'Level of Business not assigned' else lob end as employee_line_of_business,
case when business_unit IS NULL then 'Business unit not assigned' else business_unit end as employee_business_unit,
is_alumni,
case when location IS NULL then 'Location not found' else location end as employee_location,
case when location_country IS NULL then 'Location Country not found' else location end as employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race
from
public.employee
) t2 on t0.group_id = t2.group_id
and trim(lower(t0.created_by)) = trim(lower(t2.employee_email))
where
created_by NOT LIKE 'import%%'
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24
)
select
group_id,
activity_type,
active_user_id,
active_user_name,
manager_name,
profile_id,
position_id,
employee_title,
employee_level,
employee_seniority,
employee_line_of_business,
employee_business_unit,
is_alumni,
employee_location,
employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race,
activity_timestamp,
feature_usage
from
extension_referrals_activity_log
UNION ALL
select
group_id,
activity_type,
active_user_id,
active_user_name,
manager_name,
profile_id,
position_id,
employee_title,
employee_level,
employee_seniority,
employee_line_of_business,
employee_business_unit,
is_alumni,
employee_location,
employee_location_country,
is_recruiter,
is_hm,
is_sourcer,
is_event_team_external,
race,
gender,
ef_gender,
ef_race,
activity_timestamp,
feature_usage
from
recruiting_events_activity_log;

ta_feature_sourcing_breadth_of_usage_across_users

 
SELECT 
-- All the active employees involved in sourcing
count(distinct t1.employee_email) as total_employees,
count(distinct case when (t2.activity_timestamp IS NOT NULL)  then t1.employee_email end) as total_active_sourcing_employees,

-- All the active employees with one of the roles = recruiter, sourcer, event team external and involved in sourcing
count(distinct case when  (is_recruiter OR is_event_team_external OR is_sourcer) then t1.employee_email end) as total_potential_sourcing_employees,
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer)  AND (t2.activity_timestamp IS NOT NULL) then t1.employee_email end) as total_active_employees_among_potential_sourcers,

-- All employees with recruiter roles involved in sourcing
count(distinct case when is_recruiter then t1.employee_email end) as total_recruiters,
count(distinct case when is_recruiter AND (t2.activity_timestamp IS NOT NULL) then t1.employee_email end) as total_active_recuiters,

-- All employees with sourcer roles involved in sourcing
count(distinct case when is_sourcer then t1.employee_email end) as total_sourcers,
count(distinct case when is_sourcer AND (t2.activity_timestamp IS NOT NULL) then t1.employee_email end) as total_active_sourcers,

-- All employees who are active in using specific sourcing features
count(distinct case when t2.active_user_id IS NOT NULL and t2.activity_type = 'Chrome Extension' then t1.employee_email end) as active_employees_using_chrome_extension,
count(distinct case when t2.active_user_id IS NOT NULL and t2.activity_type = 'Recruiting Events' then t1.employee_email end) as active_employees_using_recruiting_events,
count(distinct case when t2.active_user_id IS NOT NULL and t2.activity_type = 'Import Resume' then t1.employee_email end) as active_employees_using_import_resume,
count(distinct case when t2.active_user_id IS NOT NULL and t2.activity_type = 'Referrals' then t1.employee_email end) as active_employees_using_referrals,

-- Sourcers using specific sourcing features
count(distinct case when is_sourcer AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Chrome Extension' then t1.employee_email end) as active_sourcers_using_chrome_extension,
count(distinct case when is_sourcer AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Recruiting Events' then t1.employee_email end) as active_sourcers_using_recruiting_events,
count(distinct case when is_sourcer AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Import Resume' then t1.employee_email end) as active_sourcers_using_import_resume,
count(distinct case when is_sourcer AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Referrals' then t1.employee_email end) as active_sourcers_using_referrals,

-- Recruiters, Sourcers, Event team external using specific sourcing features
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer) AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Chrome Extension' then t1.employee_email end) as active_potential_sourcers_using_chrome_extension,
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer) AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Recruiting Events' then t1.employee_email end) as active_spotential_sourcers_using_recruiting_events,
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer) AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Import Resume' then t1.employee_email end) as active_potential_sourcers_using_import_resume,
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer) AND t2.active_user_id IS NOT NULL and t2.activity_type = 'Referrals' then t1.employee_email end) as active_potential_sourcers_using_referrals,

-- % cohorts using one of the Sourcing features
count(distinct case when t2.active_user_id IS NOT NULL then t1.employee_email end) *100.00/count(distinct t1.employee_email) as percent_employees_using_sourcing_features,
count(distinct case when is_sourcer AND t2.active_user_id IS NOT NULL then t1.employee_email end) *100.00/count(distinct case when is_sourcer  then t1.employee_email end) as percent_sourcers_using_sourcing_features,
count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer) AND t2.active_user_id IS NOT NULL then t1.employee_email end) *100.00/count(distinct case when (is_recruiter OR is_event_team_external OR is_sourcer)  then t1.employee_email end) as percent_potential_sourcers_using_sourcing_features

FROM 
(

          select employee_email, profile_id, hiring_date, is_sourcer, is_recruiter, is_event_team_external
          from volkscience_analytics.volkscience_employee 
          WHERE is_alumni = FALSE
          and termination_date IS NULL

)t1
LEFT JOIN
(
SELECT
activity_type,
activity_timestamp,
active_user_id
from volkscience_analytics.volkscience_ta_source_activity_log
)t2
on trim(lower(t1.employee_email)) = trim(lower(t2.active_user_id))
and t1.hiring_date <= t2.activity_timestamp

Ta_feature_sourcing_adoption_by_week

WITH all_sourcing_activity_log AS
(
select
datepart('year',activity_timestamp) as year,
datepart('week',activity_timestamp) as week,
DATE_TRUNC('week' , activity_timestamp) as start_of_week,
activity_type, 
active_user_id, 
activity_timestamp, 
feature_usage,
LAG(activity_timestamp) OVER (PARTITION BY active_user_id ORDER BY activity_timestamp) AS previous_activity_timestamp_ts
from volkscience_analytics.volkscience_ta_source_activity_log
),

weekly_activity_by_sourcing_event AS (
select
year,
week,
start_of_week,
activity_type,
count(distinct active_user_id) as active_users_this_week,
sum(feature_usage) as feature_usage_count,
sum(feature_usage)/count(distinct active_user_id) as frequency_of_usage_per_user,
count(DISTINCT CASE WHEN DATEDIFF(week, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END) as repeat_users_following_week,
count(DISTINCT CASE WHEN DATEDIFF(week, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END)*1.00 /count(distinct active_user_id) as repeat_users_percent

FROM
all_sourcing_activity_log
group by 1, 2, 3, 4
),


aggregate_sourcing_weekly_activity AS (
select
year,
week,
start_of_week,
'All Sourcing Activity' as activity_type,
count(distinct active_user_id) as active_users_this_week,
sum(feature_usage) as feature_usage_count,
sum(feature_usage)/count(distinct active_user_id) as frequency_of_usage_per_user,
count(DISTINCT CASE WHEN DATEDIFF(week, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END) as repeat_users_following_week,
count(DISTINCT CASE WHEN DATEDIFF(week, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END)*1.00 /count(distinct active_user_id) as repeat_users_percent

FROM
all_sourcing_activity_log
group by 1, 2, 3, 4
)

select year, week, start_of_week, activity_type, active_users_this_week,  feature_usage_count, frequency_of_usage_per_user, repeat_users_following_week,  repeat_users_percent from weekly_activity_by_sourcing_event
UNION 
select year, week, start_of_week, activity_type, active_users_this_week,  feature_usage_count, frequency_of_usage_per_user, repeat_users_following_week,  repeat_users_percent  from aggregate_sourcing_weekly_activity

ta_feature_sourcing_adoption_by_month

EXPLAIN 
WITH all_sourcing_activity_log AS
(
select
datepart('year',activity_timestamp) as year,
datepart('month',activity_timestamp) as month,
DATE_TRUNC('month' , activity_timestamp) as start_of_month,
activity_type, 
active_user_id, 
activity_timestamp, 
feature_usage,
LAG(activity_timestamp) OVER (PARTITION BY active_user_id ORDER BY activity_timestamp) AS previous_activity_timestamp_ts
from volkscience_analytics.volkscience_ta_source_activity_log
),

monthly_activity_by_sourcing_event AS (
select
year,
month,
start_of_month,
activity_type,
count(distinct active_user_id) as active_users_this_month,
sum(feature_usage) as feature_usage_count,
sum(feature_usage)/count(distinct active_user_id) as frequency_of_usage_per_user,
count(DISTINCT CASE WHEN DATEDIFF(month, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END) as repeat_users_following_month,
count(DISTINCT CASE WHEN DATEDIFF(month, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END)*1.00 /count(distinct active_user_id) as repeat_users_percent

FROM
all_sourcing_activity_log
group by 1, 2, 3, 4
),


aggregate_sourcing_monthly_activity AS (
select
year,
month,
start_of_month,
'All Sourcing Activity' as activity_type,
count(distinct active_user_id) as active_users_this_month,
sum(feature_usage) as feature_usage_count,
sum(feature_usage)/count(distinct active_user_id) as frequency_of_usage_per_user,
count(DISTINCT CASE WHEN DATEDIFF(month, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END) as repeat_users_following_month,
count(DISTINCT CASE WHEN DATEDIFF(month, previous_activity_timestamp_ts, activity_timestamp) = 1 THEN active_user_id END)*1.00 /count(distinct active_user_id) as repeat_users_percent

FROM
all_sourcing_activity_log
group by 1, 2, 3, 4
)

select year, month, start_of_month, activity_type, active_users_this_month,  feature_usage_count, frequency_of_usage_per_user, repeat_users_following_month,  repeat_users_percent from monthly_activity_by_sourcing_event
UNION 
select year, month, start_of_month, activity_type, active_users_this_month,  feature_usage_count, frequency_of_usage_per_user, repeat_users_following_month,  repeat_users_percent  from aggregate_sourcing_monthly_activity

ta_feature_sourcing_efficiency_and_outcomes

WITH sourcing_activity_log AS
(
select
datepart('year',activity_timestamp) as year,
datepart('week',activity_timestamp) as week,
DATE_TRUNC('week' , activity_timestamp) as start_of_week,
activity_type, 
active_user_id, 
profile_id, 
position_id, 
activity_timestamp, 
active_user_name,
manager_name,
sum(feature_usage) as feature_usage
from volkscience_analytics.volkscience_ta_source_activity_log
group by 1, 2,3,4,5,6,7,8, 9, 10
),

ta_outcomes_log AS
(
SELECT 
application_id,
profile_id,
application_ts,
position_creation_ts,
position_job_function,
position_business_unit,
position_id,
position_title,
candidate_first_name,
candidate_last_name,
(case when application_inferred_gender = 'female' then 1 else 0 end) as diverse_candidate_indicator,
hired_advanced_ts as min_hired_date,
phonescreen_ts as min_phonescreened_date,
onsite_interview_ts as min_interviewed_date,
application_last_stage_ts as last_updated_stage
FROM
volkscience_analytics.volkscience_ta_leaders_hiring_outcomes
group by 1, 2, 3, 4, 5, 6, 7,8, 9, 10, 11, 12, 13, 14, 15
)



SELECT 
year, 
week,
start_of_week,
activity_type, 
active_user_id, 
active_user_name,
manager_name,
feature_usage,
concat(candidate_first_name,concat(' ',candidate_last_name)) as applicant_name,
t0.profile_id as candidate_sourced_profile_id,
t1.profile_id as applicant_profile_id,
min_phonescreened_date,
min_interviewed_date,
min_hired_date,
diverse_candidate_indicator,
application_ts,
activity_timestamp,
t1.position_id,
position_title,
position_creation_ts,
position_job_function,
position_business_unit
from sourcing_activity_log t0
left join ta_outcomes_log t1
on t0.profile_id = t1.profile_id
and t0.activity_timestamp <= t1.application_ts
and t0.activity_timestamp <= t1.last_updated_stage

Frequently asked questions

Question?

1. Will the employees who refer also be considered as active employees who source?

   1. Yes, they are considered as active employees who source since they are using the referrals feature in the TA platform