Как сделать rfm анализ в excel

Обновлено: 02.07.2024

Некоторые клиенты нуждаются в индивидуальном подходе. Только так они совершают целевые действия, повышают частоту покупок и начинают тратить больше денег. Но как понять, на каких потребителей распространять персональные предложения? В этом помогает RFM-анализ, о котором вы подробно узнаете из этой статьи.

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

RFM-анализ — метод сегментации клиентов по частоте и сумме покупок для выявления потребителей, приносящих больше денег.

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

Recency (Давность сделки). Сколько времени (часов, дней, недель, месяцев) прошло с момента последней покупки клиента.
Frequency (Частота сделки). Как часто потребитель совершает покупки или другие целевые действия.
Monetary (Вложения).Сколько средств/ресурсов потратил клиент за определенный промежуток времени. Этот показатель необязательно измерять в деньгах . Например, некоторые маркетологи оценивают время нахождения на сайте, количество переходов по внутренним страницам и т.п.

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

Область применения

RFM-анализ применяют компании из B2C-сегмента, клиентская база которых превышает 10 тысяч контактов. Но это не значит, что B2B-организациям или фирмам с меньшим количеством контактов стоит отказаться от метода. Просто в таком случае эффективность будет немного меньше. Этот негативный фактор обычно минимизируют уменьшением количества сегментов.

Метод применяют для организации эффективных email-рассылок, подготовки скриптов для телефонных звонков клиентам или любых узкотаргетированных рекламных кампаний на существующих клиентов.

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

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

Плюсы и минусы

Ничего идеального в этом мире нет, RFM-анализ — не исключение. У него есть как плюсы, так и минусы, о которых лучше узнать до начала применения.

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

эффективность RFM-анализа зависит от объема клиентской базы (если она состоит из 1000-2000 контактов, ощутимого результата добиться сложно);
не подходит для компаний, работа которых ориентирована на разовые продажи;
не прогнозирует будущее, а лишь систематизирует массивы данных из прошлого;
без специального программного обеспечения и скриптов провести RFM-анализ сложно, особенно если клиентская база состоит из сотен тысяч контактов;
база клиентов постоянно обновляется, поэтому, как минимум, 1 раз в год анализ проводят повторно.

Для осуществления RFM-анализа исходные данные приводят к определенному формату, о чем поговорим дальше.

Какие данные подойдут для проведения RFM-анализа

Для проведения RFM-анализа используют данные о покупках всех клиентов и суммах сделок. Как правило, их выгружают из CRM-систем или систем аналитик.

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

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

идентификатор клиента (email, номер телефона, ID и т.п.);
дата транзакции;
количество покупок;
сумма сделки.

Информацию выгружайте в формате Excel, так как дальнейший пример анализа проведем в этой программе. Оптимальный формат данных представлен на изображении выше. Информацию выгружайте в формате Excel, так как дальнейший пример анализа проведем в этой программе. Оптимальный формат данных представлен на изображении выше.

Как провести RFM-анализ

Приступаем к практике. Проведем анализ на примере небольшой организации. Сразу обозначим: навыков работы в специальном программном обеспечении не требуется. RFM-анализ легко провести в Excel или Google Таблицах.

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

Сбор данных

Начинайте со сбора данных. Определите период анализа. Здесь строгих рамок нет, по большому счету все зависит от особенностей организации (в том числе направление работы — B2B или B2C). Обычно берут сведения за последние 1-2 года.

Как уже говорили выше, есть несколько обязательных требований к исходным данным. Они должны содержать следующие сведения:

идентификатор клиента (ID, номер телефона или заказа, ФИО, email и т.п.);
дата транзакции;
количество сделанных покупок (других целевых действий);
сумма потраченных средств.

Не формируйте таблицу вручную, это займет много времени. Воспользуйтесь CRM или системой аналитики, которые автоматизируют экспорт нужной информации. Например, отлично подходит Битрикс24 или Мегаплан.

После выгрузки добавьте еще несколько столбцов:

текущая дата;
количество дней с последней покупки;
R, F и M (пока что их оставьте пустыми, но дальше они пригодятся).

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

Группировка и оценка потребителей

Начинается самое интересное и важное — группировка и оценка потребителей. Чем больше исходная база данных, тем больше групп и шире шкала оценок. Обычно ориентируются на три группы (оценки от 1 до 3). Но некоторые маркетологи, работая с большими массивами информации (от нескольких сотен тысяч контактов), берут 4 или 5 групп.

В нашем случае такой необходимости нет, поэтому остановимся на трех группах:

Группа 1. Выгодные потребители — часто покупают на крупные суммы.
Группа 2. Нормальные потребители — иногда покупают на средние суммы.
Группа 3. Невыгодные потребители — редко покупают на маленькие суммы.

Крупные суммы и частые покупки — относительные понятия. Каждый определяет это индивидуально для своего бизнеса. Например, для маленького цветочного магазина в остановочном комплексе выгодным потребителем будет человек, делающий две покупки в год на 3-4 тысяч рублей. А для крупного гипермаркета — делающий покупки несколько раз в неделю на 5-7 тысяч рублей.

Для каждой буквы (R, F и M) база разбирается отдельно. Поэтому после окончания анализа получается 27 сегментов потребителей.

Давность покупки (Recency)

Отсортируем потребителей по количеству дней с последний покупки (или иного целевого действия). Определите, что для вашего бизнеса считается хорошим, средним и плохим показателем в соответствии со средней длительностью сделок.

В нашем примере градация следующая:

«Хороший» показатель (Группа 1) — до 180 дней;
«Средний» показатель (Группа 2) — от 181 до 360 дней;
«Плохой» показатель (Группа 3) — от 361 дня.

Для упрощения и ускорения сортировки сделайте несколько дополнительных манипуляций. Во-первых, заполните столбец «Количество дней с последней покупки».

Для этого посчитайте разность между текущей датой и днем последней покупки. Автоматизируйте процесс специальной формулой:

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

Готовая сортировка

что это такое

Невольно хочется ответить, что те, кто больше всех заплатил. Но если бы всё решалось так просто, тогда предприниматели не ломали бы головы над стратегиями маркетинга.

На самом деле имеет значение не только сумма, которую потратил клиент, но и то, как часто он совершает покупки. Потому и была разработана классификация потребителей по методу 3-х значений RFM:

Этот анализ помогает бизнесу расставить акценты и решить четыре основных вопроса:

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

Важно. Выжимайте из бизнеса максимум с помощью нашей методички формата “фишечная стратегия”. В ней самый сок из сотен тренингов и книг по маркетингу и продажам. А также концентрат успешных действий. По ссылке скидка 50% в течение 4 часов, кликайте -> “Реальный маркетинг: 165 фишек + 33 основы“.

Но как бы ни был хорош RFM анализ, важно понимать, что в реальном мире не всё так идеально, и этот инструмент имеет свои плюсы и минусы:

Пошаговое руководство

Если Вы компания крупная, то используйте дополнительное ПО ну или формулы.

Шаг 1. Собираем данные

За какой период брать данные, зависит от специфики бизнеса (в том числе B2B или B2C). Как правило, собирают информацию за последний год. В качестве данных клиентов выступают следующие позиции:

ID клиента (это может быть номер телефона, ФИО или e-mail);
Даты покупок, сделок или иных действий;
Количество покупок;
Сумма этих покупок.

Чтобы упростить данный шаг, лучше автоматизировать процесс и применить специальное ПО, например, CRM-систему Битрикс24 или Мегаплан. Там выгрузить эти данные довольно легко.

Создание таблицы

Шаг 2. Группируем потребителей и оцениваем

Кстати, если Вы знакомы с ABC анализом, то метод RFM будет для Вас достаточно простым, так как принцип у него похожий, мы также делим на группы.

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

В качестве примера:

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

Формула для количества дней

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

Сортировка по дням

Теперь сортируем клиентов по тому, как часто они совершали свою активность.

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

Сортировка по покупкам

Здесь сортируем клиентов согласно их тратам. Все то же самое, как и в предыдущих вариантах. Или пользуйтесь формулой, или же фильтруйте от большего к меньшему.

Сортировка по сумме

Шаг 3. Даём оценку RFM

Кратко все сегменты

Анализ полученных данных

Клиенты распределены по сегментам и теперь понятно, кто есть кто. Осталось создать свою рекламную стратегию для каждого сегмента, и для наглядности я свела информацию в таблицу.

коротко о главном

Читайте также: