Закончил свой проект по управлению отчетностью отдела продаж в торговой компании. Это мой взгляд на базовые показатели работы отдела продаж которые позволяют принимать управленческие решения.
Проект полностью построен на базе Google-sheets. Автоматизация в проекте реализована за счет формул и скриптов GAS, а значит проект абсолютно бесплатен:). Решил поделиться с сообществом своей работой.
Аргументированные пинки и обратная связь приветствуется! Погнали)
Дано:
Торгово-производственная компания, штат коммерческого направления b2b 20 человек. 4 филиала по России: Н. Новгород, Казань, Саратов и Пермь. Оптовое направление структурно состоит из 4х отделов продаж (ОП): РОП + 4 менеджера.
Работа менеджеров происходит в 2х плоскостях:
Коммуникация с клиентом – CRM-система Битрикс24
Работа с заказом клиента – 1С
Задача – Сформировать единый формат управленческой отчетности B2B направления. Автоматизировать процесс формирования отчетности. Организовать раздельный управляемый доступ к рабочим отчетам, содержащим коммерческую тайну.
Архитектура проекта «Система отчетности для отдела продаж»
Архитектура и составляющие проекта “Система отчетности отдела продаж”
Приведенная схема отображает всю архитектуру проекта и включает в себя основные блоки:
Блоки архитектуры проекта “Система отчетности для отдела продаж”
1. База хранения и обработки данных
База данных наполняется вручную с помощью выгрузки отчетов в формате *.xls или *.csv из CRM-системы Битрикс24 и 1С. Это единственное ручное действие, которое пока не автоматизировано. С одной стороны ручные действия это потеря времени, с другой стороны ручные действия на входном этапе позволяют контролировать корректность данных.
1.1. База данных телефонии, по сделкам и задачам из CRM-системы Битрикс24
Выгрузки из CRM-системы Битрикс24 делаются ежедневно. Это позволяет накапливать информацию за прошлые периоды, что в свою очередь дает преимущество при сравнении показателей за период, например динамику месяц к месяцу (M-t-M).
Т.к. в выгрузке из CRM-системы в отчете по телефонии мы получаем данные о продолжительности диалога в текстовом значении «4 мин, 3 сек» для оцифровки они не подойдут. Поэтому с помощью формул мы получаем данные о продолжительности диалога в оцифрованном виде в формате «00:04:03», т.е. hh:mm:ss. Отлично! Также в этой вкладке мы подготавливаем данные для коннектора и добавляем показатель «Месяц, год» в формате YYYY_mm и также в этой вкладке у меня добавлен показатель времени звонка. Пока я его не «прикрутил» к основному отчету. Планирую добавить данные по количеству входящих звонков в течении дня, чтобы РОП смог спланировать график перерывов и обеда для сотрудников без риска пропустить входящий звонок клиента.
Обработанные данные по телефонии для передачи в коннектор
Данные по телефонии готовы к отправке в коннектор. По сделкам данные предварительно не обрабатываются, передаются в «сыром» виде в коннектор. Данные по задачам (текущие и просроченные) передаются с помощью функции IMPORTRANGE, т.к. объем информации небольшой.
1/2
Данные по сделкам и задачам для передачи в коннектор
1.2. База данных по продажам 1С
Данные из 1С хоть и выгружаются в нужном формате Excel, но то, что внутри таблиц это нужно описывать дополнительными словами. Нелегко приводить формат данных 1С к необходимому виду для последующего анализа. Например в Excel файле-выгрузке из 1С для чего-то куча пустых столбцов, некоторые цифры имеют текстовый формат…беда)
Исходная выгрузка по продажам из 1С
Формулы позволяют преобразовать формат данных 1С в простую плоскую таблицу, на базе которой наполняются отчеты по продажам. Также формулы позволяют преобразовать данные из текстовых значений в числовой формат – это необходимо для корректности цифры в отчетах.
Обработанные данные по продажам из 1С в виде плоской таблицы
1.3. Дополнительные справочники
Для обработки исходных данных 1С используются дополнительные справочники. Справочник сотрудников – содержит в себе ФИО сотрудника полностью, ФИО сотрудника в 1С и CRM-системе Битрикс24 (да, к сожалению формат ФИО в системах разный), должность и отдел. Справочник товарных групп – содержит в себе уникальные значения номенклатурных позиций: товарная группа, толщина, цвет, бренд. Также в этом файле формируется список активной клиентской базы (АКБ), который позволяет оценить динамику роста АКБ месяц к месяцу по каждому отделу продаж.
1/2
Справочник товарных групп и данные по АКБ (активная клиентская база) компании
Справочник товарных групп формируется на основе уникальных значений списка номенклатурных позиций. Вручную присваиваются параметры номенклатуры: товарная группа, толщина, цвет и бренд. Справочник сотрудников подгружается в файл автоматически.
2. Коннектор данных
В входных данных я выделю 2 самых емких отчета: отчет по телефонии и отчет по отгрузкам 1С. Первый отчет за 6 месяцев накопил порядка 65к строк, второй более 20к строк. Стандартная функция IMPORTRANGE отказалась работать и выдала ошибку «Слишком большой объем данных».
В первой части статьи, в описании блока «База хранения и обработки данных» я упоминал, что из-за большого объема данных я выделил 3 отдельных файла под базы данных: выгрузки из CRM-системы Битрикс24 (телефония, сделки и задачи); выгрузка по отгрузкам 1С и выгрузка по оплатам 1С.
Решением задачи переноса обработанных данных в управленческие отчеты стал скрипт «СОБИРАТОР» с телеграмм-канала «Google-таблицы». Скрипт собирает заданные данные из одной таблицы и переносит с нужной фильтрацией в другую. Скрипт работает автоматически, обновления у меня настроены каждый день в 09:30. РОПы как раз успевают подготовиться к планерке с сотрудниками, используя актуальные цифры.
Настройки коннектора данных
3. Отчеты отдела продаж
3.1. Отчет РОП – количественные показатели процесса продаж
Начну презентацию своих «базовых» отчетов с количественных показателей основного процесса продаж – телефонные звонки. Отчет называется «отчет РОП» и содержит в себе 3 основных блока: телефония (длительность), звонки (количество), сделки (количество) и дополнительный блок: «Анти ФРОД»
3.1.1. Длительность звонков – позволяет увидеть ежедневную динамику изменения длительности диалогов по каждому менеджеру. Настройки позволяют выбрать необходимые параметры для наполнения отчета: входящий/исходящий вызов, длительность вызова или отсутствие соединения. Установлен ежедневный норматив по длительности диалогов – от 45 мин. Выполнение – зеленая заливка, невыполнение – красная. Дополнительно рассчитывается общее время разговоров, средний трафик (учитываются рабочие дни) и количество звонков исходя из длительности: всего звонков, до 1 мин., от 1-5 мин., от 5-10 мин., свыше 10 мин.
1/2
Отчеты отдела продаж – длительность и количество звонков
3.1.2. Количество звонков – позволяет увидеть ежедневную динамику по созданным сделкам по каждому сотруднику во всех воронках. Таким образом можно определить кто из сотрудников разговаривает «в пустоту». Дополнительно, чтобы выводы из отчета были максимально близки к ситуации, в отчет добавлена декомпозиция по звонкам:
3.1.3. Количество сделок – позволяет увидеть ежедневную динамику по созданию новых сделок каждым менеджером в каждой воронке. CRM-система Битрикс24 работает в связке с 1С. Созданная сделка в Битрикс24 автоматически создает заказ в 1С. Таким образом 1 сделка – 1 заказу. Добавьте к этому отчету цифры из телефонии и звонков и сразу станет понятно, кто разговаривает эффективно, а кто просто «болтает» с клиентами.
Отчет отдела продаж – количество сделок по всем воронкам продаж
3.1.4. Отчет по текущим и просроченным задачам
Отчет отдела продаж – текущие и просроченные задачи
После каждого касания с клиентом поставь задачу или перенеси срок на следующее касание с клиентом. Такое у меня правило в отделах продаж! В этом отчете я и РОПы видят загруженность каждого сотрудника. Можно быстро оценить «закопался» сотрудник в перезвонах или ему уже не хватает клиентов и нужно добавить новых. Цветом выделено количество просроченных задач по каждому сотруднику. Если задача просрочена, значит клиент не дождался звонка, договоренности нарушены? В продажах это ТАБУ!
3.1.5. Ну и мое самое любимое – отчет «Анти ФРОД»
Отчет появился незапланированно. Но как оказалось, он стал очень востребован. Выше я писал, что у сотрудников есть дневной норматив по длительности диалогов. Напомню, это 45 минут. Дополнительно у сотрудников есть еще один норматив – количество звонков. Он составляет от 40 звонков в день. Если сотрудник 80% своего рабочего времени выполняет ежедневные нормативы, по итогу месяца сотрудник получает дополнительную премию.
Что же сделали некоторые мои сотрудники? Вместо того, чтобы набрать клиенту и сделать предложение, от которого он не сможет отказаться, некоторые менеджеры каждый день «набивали» себе цифры. Как они это делали? Очень просто – звонили на номера формата 8-800-… и слушали автоответчик… А я смотрю и не могу нарадоваться, нормативы 2й месяц у ребят выполняются, каждый день в зеленой зоне. А вот плана у ребят почему-то не было. Стал разбираться, поднял детализацию за несколько месяцев и увидел всю картину их «выполнения» нормативов.
Отчет «АнтиФРОД» показывает какое количество исходящих вызовов делает сотрудник на 1 номер телефона. Выводит ТОП-5 номеров с самым большим количеством исходящих. Отчет обновляется автоматически. Есть возможность выбора предыдущих периодов.
Таким образом сотрудники видят количество своих действий. Открыто фродить уже откровенная наглость:)
На этом разбор отчета количественных показателей процесса продаж «отчет РОП» завершен. Можно переходить к следующему управленческому отчету – «отчет Директора по продажам»
4. Отчет Директора по продажам
Отчет Директора по продажам состоит из ключевых показателей деятельности отдела продаж – ПЛАН / ФАКТ. Дополнительно отчет содержит вспомогательную информацию для анализа и планирования деятельности отдела продаж компании.
4.1. ПЛАН / ФАКТ по продажам
Отчет отдела продаж – ПЛАН / ФАКТ продаж
Отчет показывает общие цифры по компании, отделу продаж, сотруднику, процент выполнения, остаток до 100%. Подсветка выполнения плана продаж «светофор»: до 70% – красная заливка; от 70-99,9% – желтая заливка; от 100% и выше -зеленая заливка. Есть возможность выбора периода. Отчет обновляется автоматически. Дополнительно в отчете добавлены показатели количество заказов, шт. и средний чек, руб.
4.2. Отчет по динамике продаж
Отчет отдела продаж – Динамика продаж
Отчет показывает результаты продаж за каждый месяц, по филиалам, по каждой товарной группе. Результаты продаж в отчете: по валовой прибыли, по выручке, по размеру наценки, по количеству.
4.4. Отчет «Продажи M-t-M»
Отчет позволяет сравнить продажи по каждой товарной группе месяц к месяцу. Показывает динамику: рост или падение, а также дельту в процентах. Возможен выбора отдела продаж и показателя сравнения: валовая прибыль, выручка или количество. Отчет будет полезен для планирования и реализации планов продаж.
Отчет отдела продаж – Продажи M-t-M
Отчет позволяет сравнить продажи по каждой товарной группе месяц к месяцу. Показывает динамику: рост или падение, а также дельту в процентах. Возможен выбора отдела продаж и показателя сравнения: валовая прибыль, выручка или количество. Отчет будет полезен для планирования и реализации планов продаж.
4.4. Отчет «Продажи по товарным группам и по сотрудникам”
1/2
Отчеты отдела продаж – Продажи по товарным группам и сотрудникам
Отчеты были созданы по запросу руководителей отдела продаж. Позволяют оперативно формировать списки клиентов по определенным товарным группам. Таким образом РОП может определить приоритет работы с клиентами: от большего к меньшему по сумме покупок. Настройки позволяют выбрать период, сотрудника и товарную группу для анализа.
5. Отчет ABC-анализ. Клиентская масса ОП
Отчет отдела продаж – ABC-анализ клиентской массы
Вся клиентская масса сегментирована по сумме продаж за каждый месяц, выделена категория клиента по ABC-анализу: 0-20; 20-60;60>100. По каждому клиенту добавлен параметр – месяцев с покупкой, а также есть привязка контрагента к ответственному сотруднику.
Информация из отчета помогает эффективно формировать списки клиентов и расставлять приоритет проработки исходя из: категории клиента, сезона, количества и суммы оплат.
Отчет отдела продаж – ABC-анализ клиентской массы по сотруднику
6. Отчет – Расчет бонуса
Отчет отдела продаж – расчет бонусов. Данные по филиалу
Основной отчет отдела продаж – ПЛАН / ФАКТ по оплатам. Соответственно расчет бонусов должен считаться на основании данных по оплатам. Расчет бонусов хоть и делается раз в месяц по итогам отчетного периода, но занимает значительную часть времени. Да и риск ошибки при ручных расчетах сильно выше, чем при автоматизированных. Дополнительным плюсом такого отчета является открытость и динамичность – сотрудники всегда могут посмотреть размер текущих бонусов и не переживают, что их кто-то обманывает. Алгоритм работы с отчетом следующий:
Шаг 1 – РОП вносит информацию о необходимых корректировках и дополнительных расходах по каждому сотруднику, указав отчетный период корректировки. Шаг 2 – Данные о корректировках учитываются при расчете бонусов при помощи формул. Шаг 3 – Проверить цифры: радоваться или делать выводы:)
7. Справочник сотрудников и управление доступами
Как я и писал ранее, к сожалению данные о сотрудниках в CRM-системе Битрикс24 и 1С имеют разные форматы. А одна из основных задач проекта «Сформировать единый формат управленческой отчетности B2B направления». Для решения этой задачи для всей системы реализован единый справочник сотрудников. Дополнительно файл управляет доступами к отчетам для каждого сотрудника полностью в автоматическом режиме. Не нужно тратить свое время, «проваливаться» в каждый отчет и вручную назначать права доступа. Также такой управляемый подход к процессу организации доступов к отчетам, содержащим коммерческую тайну позволяет повысить безопасность информации.
1/2
Справочник сотрудников и управление доступами к отчетам отдела продаж
В справочнике сотрудников содержатся не только данные ФИО для разных систем (CRM и 1С), но и информация по грейду сотрудника. Указанный в справочнике грейд влияет на бюджет премии при расчете бонусной части. Также для удобства и скорости работы отчетности отдела продаж в справочнике у каждого сотрудника есть статус работы: действующий или уволен. Данные уволенных сотрудников остаются в справочнике, но не фигурируют в отчетах.
Для оптимизации работы отчетов отдела продаж и для более эффективного управления информацией данные из справочника сотрудников с помощью функции IMPORTRAGE передаются в файлы отчетов. При изменении данных по сотруднику (добавление или удаление) обновление происходит во всех отчетах автоматически в режиме реального времени.
Функционал управления доступами к отчетам отдела продаж позволяет в несколько кликов предоставить доступ сотруднику по e-mail и права доступа: редактирование (EDIT) или чтение (READ). Добавлена возможность моментального закрытия доступа для сотрудника (например при увольнении сотрудника). Реализовано с помощью скрипта «ДОПУСКАТОР 2», который я подсмотрел у телеграмм-канала «Google-таблицы».
Таким образом параметры доступа сотрудников компании к отчетам отдела продаж управляются в одном месте, что повышает оперативность и эффективность управления.
Итого получилась система управления отчетами отдела продаж, которая полностью реализована на базе Google-sheets, абсолютно бесплатна, требует минимального количества времени (загрузка данных из CRM-системы Битрикс24 и 1С) и позволяет оперативно получать оцифрованные данные как по процессу продаж так и по его результату.
