Воронка подбора. Три способа построения

Мало кто в HR не знает, что такое воронка подбора. Это хороший визуальный инструмент для первичного определения проблем в процессе подбора. Она позволяет понять, как протекает процесс, состоящий из нескольких стадий. Если ваш вопрос в определении самой «узкой» части в этом процессе, то воронка как раз то, что надо. В сочетании с метриками, позволяющими еще и время оценить на каждом этапе, у вас появляется реально рабочий инструмент для контроля.

Мы сейчас настраиваем на Power BI такой дашборд. Он может выглядеть так (данные фейковые 😊 )

Но и без Power BI вы можете построить воронку подбора, используя обычный excel, и excel «усиленный» надстройкой Power Pivot.

Этот пост как раз про эти три способа построения воронки подбора, которые может применить каждый в своих рабочих или учебных целях.

В качестве примера использован датасет «данные» из 4840 наблюдений за 2018 год, содержащий следующие переменные:

1. Дата открытия вакансии;
2. Дата закрытия вакансии;
3. Количество кандидатов;
4. Количество телефонных интервью;
5. Количество рассмотренных кандидатов;
6. Количество проверок СБ;
7. Количество предложений кандидатам;
8. Количество приёмов;
9. Структурное подразделение.

Мы предлагаем пройти решение вместе с нами, для этого скачать файл с учебными данными «Пример построения воронки подбора.xlsx» (в файле доступны решения для Способов 1 и 2 с расчётом всех формул.) Формат файла оказывает влияние на последующий расчет, и где-то такой формат удобен, а где-то придется писать много формул. Формат можно конечно поменять, но оставим все как есть, чтобы почувствовать разницу 😊. 

Реализация

Способ 1. MS Excel

Без труда в Интернете можно найти большое число статей о построении воронок, как правило в продажах, но принцип везде одинаковый. Мы же решили показать самый простой вариант, который не потребует даже использования диаграмм.

Первое, что следует сделать – это агрегировать имеющиеся данные, для этого воспользуйтесь сводными таблицами на вкладке «Вставка» для имеющегося набора данных. Перенесите поля («Количество кандидатов», «Количество телефонных интервью», «Количество направленных резюме», «Количество интервью с заказчиком», «Количество проверок СБ», «Джоб оффер выставлен», «Количество приемов») в область значений сводной таблицы, а сами значения отправьте в строки.

В начале логика: мы хотим вывести на экран некий текстовый символ определенное количество раз пропорционально значению в строке, для этого количество событий на каждом этапе воронки надо умножить на некий множитель. Нам понадобится создать этот множитель - вспомогательную величину, которая потребуется для визуализации результатов. Максимальное количество символов (в нашем случае черточек), которое помещается в ячейку excel в этом примере выбрали равным 60, но оно может быть любым числом. Далее считаем по следующей формуле:

=60/ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма по полю Количество кандидатов";$A$2)

Теперь можно визуализировать нашу воронку и для этого напишем ряд простых формул, потом воспользуемся простым форматированием. Используем следующую формулу (здесь нам и пригодится тот множитель, который был рассчитан на предыдущем шаге).

=ПОВТОР("|";ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма по полю Количество кандидатов";$A$2)*$B$11)

Чтобы результат в строке казался непрерывным выберите шрифт «Britanic Bold» и необходимый размер. Ваш итоговый результат представлен на рисунке далее.

Способ 2. Power Pivot

Excel позволил нам быстро построить воронку подбора кандидатов, не прибегая к сложным расчётам, но у этого подхода есть существенный недостаток – вашу картинку может «сломать» другой пользователь или вы сами захотите расширить исходную сводную таблицу, добавив новый столбец. Вариантов много – исход один – надо всегда проверять, не затерлись ли формулы, не уехали ли ссылки. Можно ли сразу все реализовать в единой сводной таблице и больше не переживать об этом? Как «запихнуть» текст именно как текст в область значений сводной таблицы и реализовать ровно тот же способ, но забыть навсегда о прописывании отдельных формул где-то в стороне от сводной таблицы? Чтобы наша черточка осталась черточкой, а не пересчиталась в число?

Это можно, но использовать придется Power Pivot – надстройку для Excel. Power Pivot подключается отдельно в параметрах Excel, в надстройках COM и доступен  пользователем профессиональных версий Microsoft Office (2010, 2013, 2016). У надстройки много преимуществ, и одно из них (не главное, но очень приятное) – вы можете написать формулы, результатом которых будет именно текст (не числовое значение), и выводя эту формулу (меру языком power pivot) в область значений вы увидите именно текст. Попробуйте кто не знал и создайте меру:

Test for text:="Hellow word"

А теперь выведете ее в сводную таблицу, например, как тут:

Вы видите куда ее «определили» в сводной таблице и что мы получили на выходе. Мы не увидели ни одного числа, мы увидели именно текст. Именно эта особенность работы с power pivot и даст возможность построить более «устойчивую» визуалку для воронки подбора. Логика визуализации та же самая, что и в excel, просто теперь это становится элементом сводной таблицы.

Что делаем:

1.Загружаем исходные данные.

2. Создаем таблицу дат. Очень сильно пригодится, если вы захотите гибко фильтровать отчет по временным периодам. Он может быть создан рядом способов, в нашем примере был использован код в Power Query (ещё одна надстройка Excel, позволяющая осуществлять наиболее эффективную подготовку данных для последующей работы).

Пример создания календаря в Power Query (на английском) можно посмотреть здесь

1. Пишем формулы для расчета мер на языке DAX.

Усложним условия – если вакансия закрылась в январе или осталась активна на 31.01, то выбирая в фильтре январь (или любой другой период), мы должны увидеть в воронке все события по этой вакансии, даже если они случились в прошлом году. То есть дата самого события (интервью, собеседование) здесь уже уходит на задний план. Но тут у кого как поставлена задача, вариантов много.

Задачу позволяют решить следующие формулы для каждого этапа воронки (приведена только первая, но они все есть в прикрепленном файле). Формулы можно переписать гораздо более оптимально, но в таком виде более понятна логика самого расчета. 

Общее количество кандидатов (кол-во):=

--для закрытых вакансий

CALCULATE(

SUM('данные'[Количество кандидатов]);

FILTER('данные';'данные'[close_date]>=MIN('calendare'[RepDate])&&'данные'[close_date]<=MAX('calendare'[RepDate])))

+

--для открытых вакансий

CALCULATE(

SUM('данные'[Количество кандидатов]);

FILTER('данные';ISBLANK('данные'[close_date])&&'данные'[start_date]<=MAX('calendare'[RepDate])))

….

Нам вновь понадобится множитель, для этого создадим меру

Множитель:=60/[Общее количество кандидатов (кол-во)]

Как и в Способе 1 визуализация будет осуществлена с помощью символа «|», приведём первые две формулы:

1. Общее количество кандидатов:

=REPT("|";[Множитель]*[Общее количество кандидатов (кол-во)])

2. Телефонные интервью:

=REPT("|";[Телефонные интервью (кол-во)]*[Множитель])

Нам также хотелось бы знать конверсию кандидатов от общего числа кандидатов на каждом этапе, пример формулы:

Конверсия в рассмотрено кандидатов:=[Рассмотрено кандидатов (кол-во)]/[Общее количество кандидатов (кол-во)])

и конверсию от предыдущего этапа к следующему, пример формулы:

 Конверсия с этапа тел. интервью в рассмотрен. кандидатов:=[Рассмотрено кандидатов (кол-во)]/[Телефонные интервью (кол-во)])

Визуализируем, подключим срезы, временные шкалы на вкладке «Вставка» и получим результат, представленный на рисунке ниже.

Способ 3. Power BI

Шаги идентичны способу 2. Нам потребуется загрузить данные в Power BI и создать календарь. В том случае, если вы выполнили Способ 2, то вы можете открыть ваш «xlsx» файл через Power BI и получить готовую модель со всеми таблицами и мерами, останется только сверстать дашборд. Если нет, то понадобится написать формулы для расчёта метрик по каждому этапу и это будут единственные меры, что нам потребуются. Power BI и Power Pivot использует один и тот же язык DAX, соответственно формулы будут аналогичными.

В Power BI есть готовая диаграмма в виде воронки, в которую мы передаём наши меры, и никаких дополнительных упражнений не требуется.

Ещё раз посмотрим на полученный результат:

Заключение

Несмотря на то, что задачу построения воронки подбора персонала нельзя отнести к глубокой аналитической работе, тем не менее это повседневная необходимость для HR-руководителей, рекрутеров и специалистов ответственных за подготовку отчётов в компании. Возможность самостоятельного построения такой воронки существенно облегчает работу для них, а также может стать первым шагом на пути решения уже более серьёзных аналитических задач, к примеру, поиску ответа на вопрос: почему на данном этапе воронка приобретает такой вид, а на следующем этапе иной?

Нами были представлены три способа построения воронки подбора персонала, которые может использовать каждый HR, в зависимости от удобства и предпочтений. Может кому-то это будет полезно.