RFM анализ в Excel

Как сегментировать клиентов на основе частоты, давности и суммы покупок. В предыдущей статье мы расмотрели основные принципы и способы сегментации клиентов, а сейчас проведем RFM анализ в Excel для сегментации клиентов онлайн магазина. Функция RFM анализа уже заложена в ряде CRM-систем, например, Битрикс-24, 1С. Тем не менее, RFM анализ в Excel просто и наглядно демонстрирует механику всего процесса.

1. Для анализа необходимы следующие данные: номер транзакции, ID клиента, дата транзакции, сумма транзакции. Кроме того, понадобится столбец с перечислением всех уникальных клиентов (в нашем случае столбец F).

2. Для удобства мы присвоим каждому столбцу имя. Для этого выделяем весь массив данных вместе с именами столбцов (это можно сделать, выделив ячейки названий A1:D1 и нажав сочетание клавиш Shift+Ctrl+Pgdown). Затем в меню ФОРМУЛЫ выбираем СОЗДАТЬ ИЗ ВЫДЕЛЕННОГО.

В появившемся меню выбираем создание имен В СТРОКЕ ВЫШЕ. В результате мы создадим одновременно четыре имени исходных столбцов с данными. Проверить, что имена заданы, можно нажав на меню ДИСПЕТЧЕР ИМЕН:

3. Логично предположить, что каждый клиент может осуществить несколько покупок. Поэтому мы создадим столбец ЧИСЛО ТРАНЗАКЦИЙ и посчитаем число транзакций, приходящихся на каждого уникального покупателя. Для этого используем формулу СЧЁТЕСЛИ(Клиент;F2). Обратите внимание: значение диапазона сразу задается именем столбца В – «Клиент». После этого проводим формулу вниз (можно навести курсор на ячейку с формулой и дважды кликнуть по черному крестику).

4. Вычисляем первый компонент RFM – давность покупок. Создадим столбец ПОСЛЕДНЯЯ ТРАНЗАКЦИЯ. Нам нужно найти максимальную дату покупки каждого уникального клиента. Используем формулу: МАКС(ЕСЛИ(Клиент=F2;Дата;»»)).

Важно, если у вас версия Excel 2021 или старше! После ввода формулы необходимо нажать Ctrl+Shift+Enter. В запись формулы добавятся фигурные скобки, в противном случае при вводе будет высвечиваться ошибка.

Задаем формат ячейки: «Дата» и проводим формулу вниз.

5. Похожим образом создаем столбец ПЕРВАЯ ТРАНЗАКЦИЯ. Используем формулу: МИН(ЕСЛИ(Клиент=F2;Дата;»»)). Не забываем про фигурные скобки, а также формат ячеек «Дата».

6. Высчитываем, сколько лет каждый покупатель приобретает товары. Для этого добавляем ячейку с текущей датой с помощью функции СЕГОДНЯ().

Затем создаем столбец ПЕРИОД и вводим формулу (($L$1)-I2)/365,25. Значение ячейки L1 в формуле зафиксировано знаками $ (для этого можно на;ать клавишу F4). Чтобы вывести значение в годах, мы делим дни на 365,25 (учитывая, что каждые четыре года добавляется один день високосного года).

Сокращаем количество знаков после запятой до двух.

7. Вычисляем второй компонент RFM – средняя выручка с клиента. Создаем столбец СУММА ПОКУПОК с суммой покупок в среднем за год. Формула: СУММЕСЛИ(Клиент; F2; Сумма)/M2.

8. Вычисляем третий компонент RFM – средняя частота покупок. Для этого делим число транзакций на период: G2/M2.

9. Далее нам нужно присвоить каждому клиенту ранг по критерию давности, частоты и выручки. Для этого сначала присвоим созданным ранее столбцам имена: выделяем столбец и в меню ФОРМУЛЫ выбираем СОЗДАТЬ ИЗ ВЫДЕЛЕННОГО.

Затем создаем три столбца РАНГ R, РАНГ F, РАНГ M. Используем формулу РАНГ(H2;Последняя_транзакция;0) для ранжирования столбца РАНГ R. Мы выбираем порядок ранжирования по убыванию, т.е. [порядок]=1.

Формула для РАНГ F – РАНГ(O2;Частота_покупок;1).

Формула для РАНГ M — РАНГ(N2;Сумма_покупок;1).

10. Далее сформируем значения сегментов. Для этого в отдельных ячейках зададим несколько диапазонов рангов и присвоим каждому диапазона отдельное значение RFM. В данном случае чем выше значение ранга, тем больше индекс, тем лучше проявляет себя клиент.

11. Далее считаем, в каком диапазоне находится клиент по каждому критерию. Для этого создаем три столбца: R, F, M. Используя функцию ВПР (Vlookup, или вертикальный просмотр) пишем формулу ВПР(P2; $T$2:$U$5;2;ИСТИНА) в ячейку V2. Продляем формулу вправо для столбцов F и M, а потом протягиваем вниз.

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

В результате анализа может быть получено очень много сегментов. Например, если вы используете 5 диапазонов, то получите 5*5*5=125 возможных комбинаций сегментов. В таком случае сегменты можно объединять. Например, можно выяснить, какие группы сегментов охотнее реагируют на рекламу, почтовые рассылки, аукционные предложения.