RFM анализ в Excel и Power BI

В этой статье мы разберем RFM-анализ с помощью Excel и построим простую визуализацию в Power BI. Вы узнаете, как анализировать ваших клиентов и почему клиенты, которые недавно совершили покупку, с большей вероятностью откроют вашу email-рассылку!

RFM-анализ в Excel и Power BI

Что такое RFM-анализ

Для начала давайте вспомним основные тезисы. RFM-анализ — анализ клиентов по давности, частоте и ценности покупок. Это один из классических и эффективных методов прогнозирования поведения клиентов. Преимущества RFM-анализа:
— не требует особых знаний. Не нужно привлекать аналитиков, математиков, анализ можно сделать на листе 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) — люди занятые и меньше читают промо-рассылки и идут на контакт по телефону. Для крупных сумм показатель имеет уровень отклика, похожий на предыдущие, чем выше цифра, тем выше отклик.

RFM-анализ, пример визуализации

RFM-анализ, пример визуализации

Важные моменты

  • Для небольших баз возможно деление каждого параметра от 1 до 3, итого 9 групп. Или деление: 5 групп (Давность) х 2 группы (Частота) х 2 группы (Деньги) = 20 RFM-ячеек
  • RFM-анализ не превышает эффективности хорошего менеджера по продажам. Менеджер всегда может лучше понять клиента, так как общается лично и знает больше информации. Он может сделать полностью персонализированное предложение.
  • Если база клиентов большая, то лучше 20% самых важных клиентов отдать менеджерам по продажам, а с остальными 80% вести коммуникацию с помощью email-маркетинга, смс, каталогов (директ-маркетинга) или обзванивать с помощью колл-центра, который состоит из менее квалифицированных (и менее оплачиваемых) сотрудников.

Делаем RFM-анализ

  • Выгружаем из CRM сделки (транзакции). Нам понадобятся “Имя клиента”, “Дата покупки”, “Сумма покупки”. Такую выгрузку может предоставить любая CRM-система:

Выгрузка из CRM

Выгрузка из CRM

  • Вставляем список клиентов на новый лист и с помощью функции “Удалить дубликаты” оставляем только уникальные значения:

Удаляем дубликаты

Удаляем дубликаты

  • Сортируем лист с выгрузкой от большей даты к меньшей и с помощью ВПР (VLOOKUP) подтягиваем на лист дату последней покупки:

=ВПР(A2;выгрузкаCrm;2;0))

Не забывайте именовать таблицы (выгрузкаCrm), чтобы не прописывать диапазоны

Не забывайте именовать таблицы (выгрузка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-анализом в Excel

  • RFM-анализ готов! Теперь давайте посмотрим, какие выводы мы можем сделать и как наглядно отобразить данные.

Визуализация отчета

Слайсеры
Самый простой и быстрый способ — вставить “Слайсеры”

Вставка — Срез

Использование интерактивных фильтров — Слайсеров

Использование интерактивных фильтров — Слайсеров

Я отфильтровал клиентов, которые получили баллы 4 и 5 по всем категориям — это самые важные для вас клиенты. Назначьте им отдельно менеджера, пишите им личные письма, звоните чаще других. Они самые лояльные и приносят большее количество денег. Из базы около 600 клиентов таких оказалось всего 7. Есть еще момент, именно у этих компаний можно просить отзывы, рекомендации, критику.

Следующий шаг — фильтруем сегмент: Деньги — 4,5, Частота — 4,5, Давность — 1-3
Получаем клиентов, которые ранее часто покупали на большие суммы, но перестали. Таких клиентов значительно больше, и их нужно реактивировать. Можно отправить рассылку с выгодным предложением, прозвонить и узнать, почему они перестали с вами сотрудничать. Или просто узнать, как у них дела.

Использование интерактивных фильтров — Слайсеров, 2

Использование интерактивных фильтров — Слайсеров, 2

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

Power BI
Если у вас всего одна табличка, то вам достаточно Слайсеров для анализа. Однако, обычно в CRM есть еще данные по менеджерам и по источнику привлечения клиентов. Тогда можно связать таблички и построить еще несколько интересных отчетов. Давайте немного позадротствуем и посмотрим, что получится!

  • Связываем таблицу RFM со справочниками — таблицей “Менеджеры” (Менеджер — Клиент) и “Источник” (Источник — Клиент). Тянем из справочников к таблице c RFM. Связь можно сделать в Power Pivot и открыть файл в Power BI или сразу соединить таблички в Power BI:

Делаем связь между таблицами

Делаем связь между таблицами

  • На основе этой информации мы можем построить несколько отчетов в Power BI. Данная статья не про возможности Power BI, поэтому я просто встрою в статью визуализацию, которую я сделал. Там 2 листа: основной и лист с анализом источников привлечения клиентов. Отчеты интерактивные — можете поиграться с ними

Выводы

RFM-анализ — мощный, быстрый и понятный инструмент повышения прибыли. В статье мы рассмотрели способ создания RFM-анализа в Excel. Для больших баз и автоматизации процесса лучше подойдет Power Pivot и Power Query, но для понимания сути достаточно и Excel. Пользуйтесь, внедряйте, улучшайте коммуникацию с вашими клиентами.

Примечания — Список клиентов взят из открытого справочника организаций, данные о продажах заполнены случайно. Данные не связаны с реальностью)
— Купить книгу “Маркетинг на основе баз данных”, автор Артур М. Хьюз на ozon.ru

Семантическое ядро и Uber. Что может быть общего?

Следующая статья
Предыдущая статья

Комментирование закрыто.

Подпишитесь на нашу рассылку, чтобы не пропустить новые статьи!

Подписка на рассылку NeedForData

Бесплатная шпаргалка по визуализации
Плакат Как что визуализировать

Плакат Как что визуализировать

© 2017    NeedForData