RFM-анализ в Excel и Power BI
В этой статье мы разберем RFM-анализ с помощью Excel и построим простую визуализацию в Power BI. Вы узнаете, как анализировать ваших клиентов и почему клиенты, которые недавно совершили покупку, с большей вероятностью откроют вашу email-рассылку!
Что такое RFM-анализ
Для начала давайте вспомним основные тезисы. RFM-анализ — анализ клиентов по давности, частоте и ценности покупок. Это один из классических и эффективных методов прогнозирования поведения клиентов. Преимущества RFM-анализа:
— не требует особых знаний. Не нужно привлекать аналитиков, математиков, анализ можно сделать на листе Excel;
— не требует временных затрат. За 25 минут можно сделать анализ базы до 50-70 тысяч клиентов, зная несколько формул Excel;
— не требует дополнительных данных. Нужна только выгрузка из CRM-системы с информацией о текущих клиентах;
— не требует дополнительных затрат и даже позволяет экономить на email-рассылках и коллцентре.
— не требует особых знаний. Не нужно привлекать аналитиков, математиков, анализ можно сделать на листе Excel;
— не требует временных затрат. За 25 минут можно сделать анализ базы до 50-70 тысяч клиентов, зная несколько формул Excel;
— не требует дополнительных данных. Нужна только выгрузка из CRM-системы с информацией о текущих клиентах;
— не требует дополнительных затрат и даже позволяет экономить на email-рассылках и коллцентре.
Применение RFM
Один из самых эффективных методов работы с базой клиентов — RFM-анализ. Применяется для эффективной коммуникации со своей базой клиентов посредством email и смс-рассылок, обзвона по базе клиентов. Ранее в директ-мейлинге и каталогах по почте. Эффект достигается за счет персонализации рекламного предложения с помощью самых простых данных о клиенте — датах его покупок.
Не все клиенты одинаково полезны
Концепция RFM
Есть 3 параметра, по которым будем классифицировать клиентов:
— R (Давность)
Давность последней покупки. Самый важный показатель. Покупатель, который неделю назад открыл счет в банке, с большей вероятностью откроет ваше письмо или вступит в диалог с менеджером, чем клиент, который уже 5 лет у вас обслуживается. Методология проста: разбиваем базу на 5 абсолютно равных частей (квинтилей), назначая каждой группе номера от 1 до 5. Самые "свежие" клиенты будут иметь номер 5, а те, кто давно не покупал, — номер 1. Разбивка может иметь разные методологии (через нахождение медианы или среднего, средневзвешенных значений), но обычно установка абсолютно правильных границ не стоит затрачиваемых усилий.
— F (Частота)
Частота покупок или сумма всех покупок клиента за период. Показатель, который говорит, как часто клиенты совершают покупки. Показатель частоты может также показать тенденцию покупательского поведения. Например, в среднем воду на дом заказывают 1 раз в 6 недель, а в офис — 1 раз в 4 недели. Соответственно, офисным клиентам нужно звонить с напоминанием или делать email и смс-рассылку чаще. В книге "Маркетинг на основе баз данных" , Артур М. Хьюз утверждает, что Частота в меньшей степени помогает спрогнозировать поведение клиентов, чем Давность. Метод разбивки точно такой же: от 1 до 5.
— M (Деньги)
Разбиваем клиентов на сегменты в зависимости от их денежной ценности. Критерий Деньги имеет нюансы: для небольших сумм (товары народного потребления) денежная ценность практически не имеет значений. По данным книги "Маркетинг на основе баз данных", отклик на ваше предложение у сегментов 1 и 5 фактически одинаковый. Это объясняется тем, что клиенты, которые покупают у вас много (сегмент 5) — люди занятые и меньше читают промо-рассылки и идут на контакт по телефону. Для крупных сумм показатель имеет уровень отклика, похожий на предыдущие, чем выше цифра, тем выше отклик.
— R (Давность)
Давность последней покупки. Самый важный показатель. Покупатель, который неделю назад открыл счет в банке, с большей вероятностью откроет ваше письмо или вступит в диалог с менеджером, чем клиент, который уже 5 лет у вас обслуживается. Методология проста: разбиваем базу на 5 абсолютно равных частей (квинтилей), назначая каждой группе номера от 1 до 5. Самые "свежие" клиенты будут иметь номер 5, а те, кто давно не покупал, — номер 1. Разбивка может иметь разные методологии (через нахождение медианы или среднего, средневзвешенных значений), но обычно установка абсолютно правильных границ не стоит затрачиваемых усилий.
— F (Частота)
Частота покупок или сумма всех покупок клиента за период. Показатель, который говорит, как часто клиенты совершают покупки. Показатель частоты может также показать тенденцию покупательского поведения. Например, в среднем воду на дом заказывают 1 раз в 6 недель, а в офис — 1 раз в 4 недели. Соответственно, офисным клиентам нужно звонить с напоминанием или делать email и смс-рассылку чаще. В книге "Маркетинг на основе баз данных" , Артур М. Хьюз утверждает, что Частота в меньшей степени помогает спрогнозировать поведение клиентов, чем Давность. Метод разбивки точно такой же: от 1 до 5.
— M (Деньги)
Разбиваем клиентов на сегменты в зависимости от их денежной ценности. Критерий Деньги имеет нюансы: для небольших сумм (товары народного потребления) денежная ценность практически не имеет значений. По данным книги "Маркетинг на основе баз данных", отклик на ваше предложение у сегментов 1 и 5 фактически одинаковый. Это объясняется тем, что клиенты, которые покупают у вас много (сегмент 5) — люди занятые и меньше читают промо-рассылки и идут на контакт по телефону. Для крупных сумм показатель имеет уровень отклика, похожий на предыдущие, чем выше цифра, тем выше отклик.
RFM-анализ, пример визуализации
Важные моменты
- Для небольших баз возможно деление каждого параметра от 1 до 3, итого 9 групп. Или деление: 5 групп (Давность) х 2 группы (Частота) х 2 группы (Деньги) = 20 RFM-ячеек
- RFM-анализ не превышает эффективности хорошего менеджера по продажам. Менеджер всегда может лучше понять клиента, так как общается лично и знает больше информации. Он может сделать полностью персонализированное предложение.
- Если база клиентов большая, то лучше 20% самых важных клиентов отдать менеджерам по продажам, а с остальными 80% вести коммуникацию с помощью email-маркетинга, смс, каталогов (директ-маркетинга) или обзванивать с помощью колл-центра, который состоит из менее квалифицированных (и менее оплачиваемых) сотрудников.
Делаем RFM-анализ
- Выгружаем из CRM сделки (транзакции). Нам понадобятся "Имя клиента", "Дата покупки", "Сумма покупки". Такую выгрузку может предоставить любая CRM-система:
Выгрузка из CRM
- Вставляем список клиентов на новый лист и с помощью функции "Удалить дубликаты" оставляем только уникальные значения:
Удаляем дубликаты
- Сортируем лист с выгрузкой от большей даты к меньшей и с помощью ВПР (VLOOKUP) подтягиваем на лист дату последней покупки:
=ВПР(A2;выгрузкаCrm;2;0))
Не забывайте именовать таблицы (выгрузкаCrm), чтобы не прописывать диапазоны
- Считаем дату последней покупки
Считаем дату последней покупки
- Считаем количество покупок, с помощью формулы СЧЕТЕСЛИМН (COUNTIFS)
=СЧЁТЕСЛИМН(выгрузкаCrm[Клиент];RFM_making!A2)
Считаем количество покупок
- Сумму всех поступлений от клиента считаем с помощью СУММЕСЛИМН (SUMIFS)
=СУММЕСЛИМН(выгрузкаCrm[Сумма];выгрузкаCrm[Клиент];RFM_making!A2)
Считаем сумму покупок
- Данные готовы. Нам нужно назначить каждому параметру значение. Для небольших баз можно назначать значения от 1 до 3, тогда у вас будет всего 333 = 27 сегментов. Для баз побольше можно назначать значения от 1 до 5, тогда будет 125 сегментов. Я выбираю от 1 до 5.
Также, я использую значение "RFM-балл", равное сумме всех трех значений. Оно необходимо, если для вас все параметры (R, F, M) одинаково важны.
Алгоритм везде одинаковый – определяем минимальное значение, максимальное, среднее и медиану. На основе этих данных делим сегменты на 5 частей. Либо делим просто арифметически на 5 равных сегментов.
Получаем табличку такого вида:
Лист с RFM-анализом в Excel
- RFM-анализ готов! Теперь давайте посмотрим, какие выводы мы можем сделать и как наглядно отобразить данные.
Визуализация отчета
Слайсеры
Самый простой и быстрый способ — вставить "Слайсеры".
Вставка — Срез
Использование интерактивных фильтров — Слайсеров
Я отфильтровал клиентов, которые получили баллы 4 и 5 по всем категориям — это самые важные для вас клиенты. Назначьте им отдельно менеджера, пишите им личные письма, звоните чаще других. Они самые лояльные и приносят большее количество денег. Из базы около 600 клиентов таких оказалось всего 7. Есть еще момент, именно у этих компаний можно просить отзывы, рекомендации, критику.
Следующий шаг — фильтруем сегмент: Деньги — 4,5, Частота — 4,5, Давность — 1-3
Получаем клиентов, которые ранее часто покупали на большие суммы, но перестали. Таких клиентов значительно больше, и их нужно реактивировать. Можно отправить рассылку с выгодным предложением, прозвонить и узнать, почему они перестали с вами сотрудничать. Или просто узнать, как у них дела.
Следующий шаг — фильтруем сегмент: Деньги — 4,5, Частота — 4,5, Давность — 1-3
Получаем клиентов, которые ранее часто покупали на большие суммы, но перестали. Таких клиентов значительно больше, и их нужно реактивировать. Можно отправить рассылку с выгодным предложением, прозвонить и узнать, почему они перестали с вами сотрудничать. Или просто узнать, как у них дела.
Использование интерактивных фильтров — Слайсеров, 2
С помощью слайсеров, вы самостоятельно можете выбрать необходимые фильтры и определить, как взаимодействовать с клиентами.
Power BI
Если у вас всего одна табличка, то вам достаточно Слайсеров для анализа. Однако, обычно в CRM есть еще данные по менеджерам и по источнику привлечения клиентов. Тогда можно связать таблички и построить еще несколько интересных отчетов. Давайте немного позадротствуем и посмотрим, что получится!
- Связываем таблицу RFM со справочниками — таблицей "Менеджеры" (Менеджер — Клиент) и "Источник" (Источник — Клиент). Тянем из справочников к таблице c RFM. Связь можно сделать в Power Pivot и открыть файл в Power BI или сразу соединить таблички в Power BI:
Делаем связь между таблицами
Выводы
RFM-анализ — мощный, быстрый и понятный инструмент повышения прибыли. В статье мы рассмотрели способ создания RFM-анализа в Excel. Для больших баз и автоматизации процесса лучше подойдет Power Pivot и Power Query, но для понимания сути достаточно и Excel. Пользуйтесь, внедряйте, улучшайте коммуникацию с вашими клиентами.
Примечания — Список клиентов взят из открытого справочника организаций, данные о продажах заполнены случайно. Данные не связаны с реальностью)
— Купить книгу "Маркетинг на основе баз данных", автор Артур М. Хьюз на ozon.ru.
Примечания — Список клиентов взят из открытого справочника организаций, данные о продажах заполнены случайно. Данные не связаны с реальностью)
— Купить книгу "Маркетинг на основе баз данных", автор Артур М. Хьюз на ozon.ru.