От хаоса к системе: как RFM-анализ в Google Таблицах увеличил плановость продаж на 80% — 15.01.26 19:17

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

В B2B-продажах работа вслепую, без понимания структуры клиентской базы, обходится в миллионы рублей упущенной прибыли.

⚠️️P.S.1 — Все данные и клиентские кейсы в статье являются вымышленными и используются исключительно для демонстрации методики. Любые совпадения с реальными компаниями или лицами случайны.

P.S.2 — в конце статьи будет ссылка на пример таблицы с RFM-анализом и проектом плана продаж.

Дано: отдел продаж в производственно-торговой компании. Процесс продажи осуществляется в ЦРМ-системе, результаты продаж фиксируются в 1С.

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

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

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

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

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

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

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

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

Данные по продажам из 1С преобразованные в плоскую таблицу

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

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

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

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

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

ABC-анализ клиентов по выручке

Проведенный 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

Источник