Кейс: RFM-анализ в Excel | Кто на самом деле приносит деньги? 💰
В предыдущей статье мы разбирали удержание клиентов. А теперь пришло время узнать, кто из них приносит реальную прибыль. Поэтому сегодня я покажу, как разделить базу онлайн-школы на сегменты по методу RFM, и выясню, на кого маркетингу стоит тратить бюджет, а кого нужно разбудить.
Для RFM-анализа я взяла выгрузку из 300 заказов СмартВека. Чтобы сегментация была точной, нужно отсортировать клиентов по трём параметрам:
- R - Recency (дата последней покупки)
- F - Frequency (количество заказов)
- M - Monetary (общая сумма трат)
R (Recency) 📆
Для того чтобы выгрузить даты последних покупок, мне понадобились данные из таблицы заказов.
На этот раз я не стала использовать формулы ВПР или ИНДЕКС. Решила, что лучше пойти путём функции АГРЕГАТ, чтобы избежать громоздких сводных таблиц. И здесь я разделила список всех-всех дат на id клиентов (это убрало деление на 0):
Даты всех покупок / Логическое условие (совпадение с id)
Как видите, в формуле есть команды 14 и 6.
- 14 - ищет наибольшую дату (то есть, самую свежую)
- 6 - игнорирует #ДЕЛ/0!
Также мне понадобилось узнать количество дней, которые прошли с момента последней покупки. Для этого из сегодняшней даты я вычла дату покупки. На момент проведения анализа было 16 апреля 2026 года:
F (Frequency) 🛒
Для расчёта количества заказов я использовала формулу СЧЁТЕСЛИ:
M (Monetary) 💸
Чтобы найти сумму всех заказов, воспользовалась функцией СУММЕСЛИ:
В таблице появились новые столбцы:
Сегменты 📊
Теперь осталось распределить баллы для сегментов. Для Recency я использовала экспертные интервалы.
- 5 баллов — клиенты, совершившие покупку в последние 30 дней
- 4 — в последние 90 дней
- 3 — в последние 180 дней
- 2 — в последние 365 дней
- 1 — без покупки
Для Frequency: если клиент купил один раз — получает 1 балл, если совершил много покупок — получает 5.
Для Monetary:
Склеила эти 3 столбца:
В конце присвоила названия сегментам при помощи функций ЕСЛИ, ЛЕВСИМВ и ПСТР:
В итоге, получилась такая таблица:
RFM-анализ 💹
Далее я создала сводную таблицу на основе этих данных. В строки вошел RFM-код, а в значения — количество user_id.
И, наконец, мы можем увидеть результат на графике:
Сразу хочу обратить ваше внимание на левую часть диаграммы. На графике отчетливо видны лидеры — сегменты 412, 413, 423. О чём это говорит?
Маркетинг работает отлично, приток новых клиентов хороший. Но есть риск. Для СмартВека это неустойчивая прибыль. Если завтра реклама подорожает, то школа может уйти в минус. Потому что люди покупают 1-2 раза и не становятся лояльными. Скорее всего, школа живет за счет новичков.
Но чтобы убедиться в этом, я решила копнуть глубже и создала ещё три сводные таблицы под каждый сегмент.
Здесь мы уже видим, что всего лишь 5 человек из всей базы являются по-настоящему лояльными (445, 455, 544) — а это 2,5%. То есть, только малая часть клиентов приносит основную прибыль компании. Однако именно она может оказаться самой важной группой для тестирования новых продуктов.
Я бы пока не стала предлагать им скидку (они и так покупают). Было бы куда лучше дать им статус VIP-студентов, доступ в закрытое сообщество или раннее бронирование новых курсов. Удержание одного такого клиента стоит в разы дешевле, чем привлечение десяти новых.
А вот новичков больше всего (свыше 55 человек суммарно). Это именно те люди, которые пришли недавно после запуска новой рекламной кампании в ВК, но купили всего один раз. Привлечение сработало отлично, но удержание под вопросом. Клиенты пришли, попробовали продукт и не решились на второй онлайн-курс. Значит нужны допродажи.
Для данного сегмента я бы порекомендовала внедрить автоматическую воронку допродаж. Для 512 и 513 было бы неплохо отправить оффер на следующий уровень обучения в течение 48 часов после покупки первого курса. А для 412, 413 и 423 — использовать контент-маркетинг (полезные статьи, презентации и вебинары, чтобы прогреть их ко второй покупке и не дать им превратиться в спящих).
P.S. О том, как СмартВек привел этих новичков через ВК и как я анализировала их удержание, можно почитать в моем кейсе по когортному анализу.
Целых 16 человек находятся в самом спящем состоянии — сегмент 111. Они зарегистрировались и вообще ничего не купили, либо же купили один раз, очень давно и на минимальную сумму. В отличие от остальных, это самый холодный сегмент. На их возврат точно не стоит тратить деньги через платные каналы (таргет, звонки, рассылки). Так как это будет бессмысленно и не окупится.
Для спящих я предлагаю два варианта.
1. Сегмент 111 просто оставить в покое. Максимум — отправить опрос «Почему вы ушли?». Можно собрать только обратную связь для улучшения продукта.
2. А вот сегментов 212 и 222 обязательно нужно реанимировать. Можно предложить им бесплатный интенсив, пригласить на вебинар по теме, которой они интересовались, чтобы восстановить контакт.
Остальных спящих (около 15-20%) пора собрать в отдельную группу на удаление из рассылок. Иначе письма начнут улетать в спам. Им можно провести одну финальную email-рассылку с прощальным оффером, и тех, кто не откроет, просто отписать. Это сэкономит бюджет и очистит статистику.
Итоги 📰
RFM-анализ показал, что текущая модель СмартВека активно привлекает новых студентов, но слабо их удерживает. Основная масса клиентов совершает лишь однократные покупки. Для масштабирования бизнеса онлайн-школе нужно полностью сфокусироваться на работе с базой: превратить новичков 412, 413, 423 в лояльных клиентов 455 через цепочки допродаж и программу лояльности.
Этот кейс был создан мной в качестве учебного проекта для тренировки навыков Excel и работы с маркетинговым анализом :)
Для визуального восприятия я собрала все данные Excel в дашборд и загрузила в One Drive. Вы можете лично увидеть по ссылке ниже: