kirillrez

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

Дополнительная задача – создать простую и прозрачную систему формирования планов продаж для ОП.

Решение: RFM-анализ активной клиентской базы (АКБ) по критериям:

R – давность последней покупки

F – частота покупок

M – деньги, которые принес контрагент

БЛОК 1 – создание RFM-анализа активной клиентской базы

За основу данных я взял выгрузку отчета по продажам из 1С. Т.к. формат данных 1С имеет свои особенности, первое, что нам нужно сделать, это привести данные в плоскую таблицу с следующими заголовками:

На основе таблицы с данными по продажам мы построим RFM-анализ активной клиентской базы.

Для формирования списка клиентов АКБ я использовал функцию QUERY которая на выходе создает список ИНН контрагентов и сортирует его от Я до А по сумме покупок. На этом этапе мы уже можем сделать ABC-анализ клиентской массы если присвоим категории A, B и C каждому клиенту в зависимости от суммы его покупок. В своем примере я сделал следующую разбивку:

Категория A – клиенты, на которых в сумме приходится 60% всей выручки компании.

Категория B – клиенты, на которых в сумме приходится 30% всей выручки компании.

Категория C – клиенты, на которых в сумме приходится 10% всей выручки компании.

Проведенный ABC-анализ по представленной матрице уже дает четкое стратегическое направление для работы с клиентской базой:

• Фокус на удержании — на ключевых клиентах (Категория A), которые генерируют основную выручку.

• Фокус на развитии — на клиентах с высоким потенциалом (Категория B), которых можно вырастить до статуса ключевых.

• Фокус на оптимизации — на работе с массовым сегментом (Категория C), где важно снижать затраты на обслуживание, не теряя при этом в выручке.

После определения ценности клиента (Категория A, B, C) мы анализируем его активность и стабильность — количество месяцев, в которых клиент совершал покупки за рассматриваемый период (например, за последние 12 месяцев).

Количество месяцев с покупками я вывел с помощью функции COUNTA, которая считает количество заполненных ячеек в диапазоне. Чтобы отобразить месяц последней покупки я использовал следующий алгоритм:

1. Смотрим строку с покупками клиента (месяцы).

2. Ищем последнюю ячейку, где есть данные (значит, была покупка).

3. Берем заголовок этого столбца (название месяца) из верхней строки.

4. Если у клиента нет покупок — оставляем пустую строку.

Проще говоря: отвечаем на вопрос "В какой месяц клиент покупал у нас в последний раз?"

Теперь мы можем строить стратегию, опираясь не на два, а на три ключевых параметра:

1. Value (Ценность) – ABC-категория (сколько приносит).

2. Activity (Активность) – количество месяцев с покупками (как регулярно покупал ранее).

3. Recency (Свежесть) – месяц последней покупки (активен ли он сейчас).

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

1. Сначала находим «активное окно» для каждого клиента: от первого до последнего месяца, когда была покупка.

2. Считаем, сколько месяцев в этом окне (общее количество месяцев между первой и последней покупкой).

3. Делим это число на общее время работы с клиентом (сколько всего месяцев прошло с начала отношений, берётся из столбца «Количество покупок» и уменьшается на 1, чтобы исключить текущий или стартовый месяц).

4. Результат округляется — это и есть средняя частота покупок в месяцах.

Проще говоря: мы видим, покупал ли клиент раз в месяц, раз в квартал или реже, относительно всего времени, что он с вами.

На основе рассчитанной частоты покупок мы можем разделить всех клиентов на группы по регулярности их покупок. Присвоим категории по матрице:

Зная месяц последней покупки, мы автоматически можем классифицировать клиента по шкале давности, которая напрямую связана с риском его потери. Присвоим следующие категории давности:

Следующим шагом мы вычислим средний чек для каждого клиента. Для этого общую сумму, которую он принёс, разделим на количество месяцев, в течение которых он делал покупки. Этот расчёт покажет, в каком среднем объёме клиент обычно совершает транзакции, что дополнит картину о его платёжном поведении.

Для завершения RFM-анализа с помощью функции XLOOKUP и дополнительного справочника закрепления я добавил к каждому клиенту ответственного менеджера (по ИНН) и полное название компании.

RFM-анализ — не статичный PDF-отчёт, а интерактивная дашборд-система. Всего пара кликов фильтрами превращают массив данных в готовый план действий, позволяя гибко реагировать на изменения и фокусировать усилия там, где это даёт максимальную отдачу. Экономия времени на рутинных запросах и построении отчётов составляет до 80%, освобождая ресурсы для самой работы с клиентами.

БЛОК 2 – формирование плана продаж на основании данных активной клиентской базы (АКБ)

Мы провели глубокую диагностику клиентской базы. Теперь у нас есть четкая картина: кто наши ключевые клиенты, как часто они покупают и когда были в последний раз. Но самый важный вопрос остаётся открытым:

Как превратить эти данные в конкретный план, который менеджеры будут выполнять, а руководитель — контролировать?

Блок 1 «RFM-анализ АКБ» дал нам понимание. Блок 2 «Проект плана продаж» даёт результат.

В этом разделе мы создадим не просто отчёт, а динамический механизм планирования, который:

• Автоматически определяет, с кем из клиентов нужно связаться в следующем месяце.

• Рассчитывает реалистичный плановый показатель для каждого контакта.

• Распределяет нагрузку между менеджерами.

• Позволяет гибко корректировать прогноз с учётом бизнес-реалий.

Это переход от ответа на вопрос «Что произошло?» к ответу на вопросы «Что делать?» и «Какой результат мы планируем получить?».

Проект плана продаж строится на принципе прогнозирования следующей покупки на основе паттернов поведения клиента. Алгоритм использует три ключевых параметра:

1. Частота покупок (F) — средний интервал между покупками клиента в месяцах.

2. Дата последней покупки (R) — месяц последней транзакции.

3. Средний чек (M) — средняя сумма одной транзакции клиента.

Алгоритм прогнозирования:

Дата следующей покупки = Дата последней покупки + Частота (F)
Ожидаемая сумма следующей покупки = Средний чек (M)

• Частота (F) = 2 месяца

• Последняя покупка (R) = Октябрь

• Средний чек (M) = 50 000 руб.

• Прогноз: Следующая покупка ожидается в Декабре на сумму ~50 000 руб.

Таким образом, в плане продаж на декабрь этот клиент автоматически попадает в список с четкой задачей: контакт и ожидаемая сделка на 50 000 руб.

В вкладке «Настройка плана продаж» я сделал возможность изменять параметры проекта плана продаж. Достаточно выбрать месяц, на который формируется проект плана, указать корректирующие коэффициенты и отметить галочками категории клиентов по частоте, давности покупки и выбрать настройки клиентов, которые попадут в список на реанимацию.

В результате мы получим список клиентов, которые по плану должны сработать в следующем месяце с учетом выставленных настроек (каких клиентов с какими параметрами мы будем брать для проекта плана продаж).

Для повышения точности прогноза в системе реализован ручной механизм исключения клиентов с аномальными, нерегулярными покупками, которые искажают статистику.

Как это работает:

1. Выявление аномалии: В списке клиентов для плана менеджер или аналитик видит клиентов, чьи покупки носят разовый, нетипичный характер (например, крупный единоразовый проект, не связанный с основной деятельностью клиента).

2. Исключение чекбоксом: рядом с таким клиентом появляется чекбокс («Исключить из плана»). При его активации:

   1. Строка клиента визуально выделяется красным цветом (индикация исключения).

   2. Данные этого клиента (исторический средний чек, частота) не участвуют в расчёте общих плановых показателей (сумма плана, средний чек по портфелю и т.д.).

Результат: Итоговая цифра «Проект плана продаж» становится чище и реалистичнее, так как основана только на стабильных, повторяющихся паттернах покупок.

Итоговый результат — прозрачная и понятная система планирования продаж, работающая ежемесячно.

Каждый менеджер видит обоснование своего плана — какие клиенты, с какой ожидаемой суммой и почему попали в его задачи. Это превращает план из абстрактной цифры в понятную дорожную карту действий.

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

Достаточно только добавить новые данные по продажам в отчет – остальное сделают формулы Гугл-таблиц.

Итог: Данная система переводит отдел продаж из режима реагирования на входящие запросы в режим проактивного управления клиентским циклом и денежными потоками. Вы перестаете гадать, кому позвонить завтра — система сама показывает, с кем и о чем говорить, чтобы выполнить план.

Ссылка на таблицу с примером (можно сделать копию)
https://docs.google.com/spreadsheets/d/1QmZL-OcbR2iM-GCa-Y6sCH3VgqAgFJ2mf3uwB-otzI8/edit?usp=sharing

Коммуникация с клиентом - 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С

Данные из 1С хоть и выгружаются в нужном формате Excel, но то, что внутри таблиц это нужно описывать дополнительными словами. Нелегко приводить формат данных 1С к необходимому виду для последующего анализа. Например в Excel файле-выгрузке из 1С для чего-то куча пустых столбцов, некоторые цифры имеют текстовый формат...беда)

Формулы позволяют преобразовать формат данных 1С в простую плоскую таблицу, на базе которой наполняются отчеты по продажам. Также формулы позволяют преобразовать данные из текстовых значений в числовой формат - это необходимо для корректности цифры в отчетах.

1.3. Дополнительные справочники

Для обработки исходных данных 1С используются дополнительные справочники. Справочник сотрудников - содержит в себе ФИО сотрудника полностью, ФИО сотрудника в 1С и CRM-системе Битрикс24 (да, к сожалению формат ФИО в системах разный), должность и отдел. Справочник товарных групп - содержит в себе уникальные значения номенклатурных позиций: товарная группа, толщина, цвет, бренд. Также в этом файле формируется список активной клиентской базы (АКБ), который позволяет оценить динамику роста АКБ месяц к месяцу по каждому отделу продаж.

Справочник товарных групп формируется на основе уникальных значений списка номенклатурных позиций. Вручную присваиваются параметры номенклатуры: товарная группа, толщина, цвет и бренд. Справочник сотрудников подгружается в файл автоматически.

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 мин.

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»

Отчет позволяет сравнить продажи по каждой товарной группе месяц к месяцу. Показывает динамику: рост или падение, а также дельту в процентах. Возможен выбора отдела продаж и показателя сравнения: валовая прибыль, выручка или количество. Отчет будет полезен для планирования и реализации планов продаж.

Отчет позволяет сравнить продажи по каждой товарной группе месяц к месяцу. Показывает динамику: рост или падение, а также дельту в процентах. Возможен выбора отдела продаж и показателя сравнения: валовая прибыль, выручка или количество. Отчет будет полезен для планирования и реализации планов продаж.

4.4. Отчет «Продажи по товарным группам и по сотрудникам"

Отчеты были созданы по запросу руководителей отдела продаж. Позволяют оперативно формировать списки клиентов по определенным товарным группам. Таким образом РОП может определить приоритет работы с клиентами: от большего к меньшему по сумме покупок. Настройки позволяют выбрать период, сотрудника и товарную группу для анализа.

5. Отчет ABC-анализ. Клиентская масса ОП

Вся клиентская масса сегментирована по сумме продаж за каждый месяц, выделена категория клиента по ABC-анализу: 0-20; 20-60;60>100. По каждому клиенту добавлен параметр - месяцев с покупкой, а также есть привязка контрагента к ответственному сотруднику.

Информация из отчета помогает эффективно формировать списки клиентов и расставлять приоритет проработки исходя из: категории клиента, сезона, количества и суммы оплат.

6. Отчет - Расчет бонуса

Основной отчет отдела продаж - ПЛАН / ФАКТ по оплатам. Соответственно расчет бонусов должен считаться на основании данных по оплатам. Расчет бонусов хоть и делается раз в месяц по итогам отчетного периода, но занимает значительную часть времени. Да и риск ошибки при ручных расчетах сильно выше, чем при автоматизированных. Дополнительным плюсом такого отчета является открытость и динамичность - сотрудники всегда могут посмотреть размер текущих бонусов и не переживают, что их кто-то обманывает. Алгоритм работы с отчетом следующий:

Шаг 1 - РОП вносит информацию о необходимых корректировках и дополнительных расходах по каждому сотруднику, указав отчетный период корректировки. Шаг 2 - Данные о корректировках учитываются при расчете бонусов при помощи формул. Шаг 3 - Проверить цифры: радоваться или делать выводы:)

7. Справочник сотрудников и управление доступами

Как я и писал ранее, к сожалению данные о сотрудниках в CRM-системе Битрикс24 и 1С имеют разные форматы. А одна из основных задач проекта «Сформировать единый формат управленческой отчетности B2B направления». Для решения этой задачи для всей системы реализован единый справочник сотрудников. Дополнительно файл управляет доступами к отчетам для каждого сотрудника полностью в автоматическом режиме. Не нужно тратить свое время, «проваливаться» в каждый отчет и вручную назначать права доступа. Также такой управляемый подход к процессу организации доступов к отчетам, содержащим коммерческую тайну позволяет повысить безопасность информации.

В справочнике сотрудников содержатся не только данные ФИО для разных систем (CRM и 1С), но и информация по грейду сотрудника. Указанный в справочнике грейд влияет на бюджет премии при расчете бонусной части. Также для удобства и скорости работы отчетности отдела продаж в справочнике у каждого сотрудника есть статус работы: действующий или уволен. Данные уволенных сотрудников остаются в справочнике, но не фигурируют в отчетах.

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

Функционал управления доступами к отчетам отдела продаж позволяет в несколько кликов предоставить доступ сотруднику по e-mail и права доступа: редактирование (EDIT) или чтение (READ). Добавлена возможность моментального закрытия доступа для сотрудника (например при увольнении сотрудника). Реализовано с помощью скрипта «ДОПУСКАТОР 2», который я подсмотрел у телеграмм-канала «Google-таблицы».

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

Итого получилась система управления отчетами отдела продаж, которая полностью реализована на базе Google-sheets, абсолютно бесплатна, требует минимального количества времени (загрузка данных из CRM-системы Битрикс24 и 1С) и позволяет оперативно получать оцифрованные данные как по процессу продаж так и по его результату.

Этот главная дорога ЖК. Обычно здесь всегда ездят машины.

Набережная ЖК. Безмолвная тишина

Все спят. Ну или не спят) но никого нет)