- Что такое RFM-анализ в Excel и зачем он нужен
- Какие данные нужны для RFM-анализа в Excel
- Как подготовить таблицу заказов перед расчётом
- Формулы Recency, Frequency и Monetary в Excel
- Как разбить клиентов на сегменты по RFM-шкале в Excel
- Что делать с каждым сегментом после RFM-анализа
- RFM-анализ в Excel или специализированные сервисы: что выбрать
- Сравнение подходов
- Когда RFM-анализ в Excel не подходит
- Типичные ошибки при RFM-анализе в Excel
- Что делать с сегментами после расчёта
- Частые вопросы про RFM-анализ в Excel
- Заключение
Когда клиентская база разрастается за пару сотен строк, «интуитивные» рассылки и одинаковые предложения для всех начинают работать хуже. Кто-то купил вчера на 12 400 ₽, а кто-то последний раз заходил девять месяцев назад — но в CRM оба выглядят как «клиент». RFM-анализ в Excel решает именно эту проблему: он делит базу на группы по трём показателям — давности, частоте и сумме покупок — без покупки дорогого BI-инструмента.
Я настраиваю контекстную рекламу и работаю с аналитикой для интернет-магазинов и сервисных компаний, и почти в каждом проекте рано или поздно встаёт вопрос: кому показывать ремаркетинг с скидкой, а кого не трогать, чтобы не размывать маржу. RFM-таблица в Excel — быстрый способ получить ответ за 1–2 часа, если под рукой есть выгрузка заказов.
В статье — пошаговая инструкция: какие данные нужны, какие формулы считают Recency, Frequency и Monetary, как разбить клиентов на сегменты и что делать с каждой группой в рекламе и рассылках.
Что такое RFM-анализ в Excel и зачем он нужен
RFM-анализ — метод сегментации клиентов по трём метрикам: Recency (давность последней покупки), Frequency (частота покупок за период) и Monetary (сумма, которую клиент потратил). Каждому клиенту присваивается балл по каждой из трёх осей, обычно от 1 до 5, и по сочетанию баллов клиент попадает в один из сегментов — от «чемпионов» до «спящих».
В Excel это делается без макросов: хватает функций MAXIFS, СЧЁТЕСЛИМН, СУММЕСЛИМН и квартилей через ПРОЦЕНТИЛЬ. Я подробно писал про базовый инструментарий такого анализа в статье про Excel для маркетингового анализа — RFM использует ровно этот же набор функций, просто в конкретной последовательности.
Смысл в том, что RFM — это не абстрактная теория, а конкретный список: кому звонить сегодня, кого включать в рекламную кампанию с акцентом на удержание, а кого временно исключить из рассылок, чтобы не тратить бюджет на тех, кто вряд ли вернётся.
🔍 Чем RFM отличается от общей сегментации клиентов
RFM — частный случай более широкой сегментации клиентов: он использует только транзакционные данные и не учитывает демографию, канал привлечения или поведение на сайте. Это плюс — не нужны сложные интеграции, и минус — RFM ничего не скажет о причинах поведения, только о факте.
Какие данные нужны для RFM-анализа в Excel
Минимальный набор — три столбца на уровне заказа: идентификатор клиента, дата заказа, сумма заказа. Всё остальное Excel досчитает сам. Источник данных зависит от того, где хранятся заказы:
- Выгрузка из CRM или личного кабинета CMS интернет-магазина (CSV или XLSX с заказами за 6–12 месяцев).
- Отчёт по электронной торговле из Яндекс Метрики, если оплата проходит на сайте — здесь пригодится связка с многоканальными последовательностями, чтобы не потерять повторные визиты одного клиента.
- Ручная выгрузка из 1С или Битрикс24 — часто самый чистый вариант, потому что там уже есть привязка заказа к клиенту.
Период выгрузки важен: слишком короткий (1–2 месяца) даст ложную картину — большинство клиентов окажутся «новыми» просто потому, что у них не было физической возможности купить второй раз. Я обычно беру 9–12 месяцев для товаров с частотой покупки раз в квартал и 3–4 месяца для быстрооборачиваемых категорий вроде расходников.
💡 Совет по чистоте данных
Перед расчётом уберите из выгрузки отменённые и возвращённые заказы — иначе клиент с суммой заказа 47 800 ₽, который вернул товар и получил деньги обратно, попадёт в сегмент «чемпионов», хотя реальная выручка по нему нулевая.
Как подготовить таблицу заказов перед расчётом
Разложите данные на два листа. Первый — «Заказы»: одна строка = один заказ, столбцы ID клиента, Дата, Сумма. Второй — «RFM», где на каждой строке один клиент и три расчётных столбца. Такая структура похожа на подготовку данных для ABC-XYZ анализа — там тоже сначала собирается сырая таблица транзакций, а потом строится сводная по объектам анализа.
Дальше — список уникальных клиентов на листе «RFM». Быстрее всего получить его через сводную таблицу: тянем ID клиента в область строк, Excel сам убирает дубли. У меня на базе из 2 340 клиентов и 6 900 заказов это заняло около 4 минут вместе с проверкой на опечатки в ID.
Формулы Recency, Frequency и Monetary в Excel
Дата отсчёта — сегодняшняя дата или дата выгрузки, фиксируем её в отдельной ячейке, например $B$1. Дальше для каждого клиента на листе «RFM» считаем три показателя.
Recency: =$B$1-МАКСЕСЛИ(Заказы!B:B;Заказы!A:A;A2)
Frequency: =СЧЁТЕСЛИ(Заказы!A:A;A2)
Monetary: =СУММЕСЛИ(Заказы!A:A;A2;Заказы!C:C)
Recency получается в днях: чем меньше число, тем свежее покупка. Frequency — количество заказов за период выгрузки. Monetary — суммарная выручка с клиента. На выборке из 2 340 клиентов у меня получился разброс Recency от 2 до 341 дня, Frequency — от 1 до 14 заказов, Monetary — от 890 ₽ до 218 600 ₽ у одного корпоративного клиента, которого я убрал из общей сегментации и обработал отдельно.
Как разбить клиентов на сегменты по RFM-шкале в Excel
Дальше каждому показателю нужно присвоить балл от 1 до 5. Самый честный способ — квартили или квинтили через ПРОЦЕНТИЛЬ.ВКЛ, а не деление «на глаз» по круглым порогам. Формула для балла Recency (где меньше дней — выше балл):
=ЕСЛИ(B2<=ПРОЦЕНТИЛЬ.ВКЛ($B$2:$B$2341;0,2);5;ЕСЛИ(B2<=ПРОЦЕНТИЛЬ.ВКЛ($B$2:$B$2341;0,4);4;ЕСЛИ(B2<=ПРОЦЕНТИЛЬ.ВКЛ($B$2:$B$2341;0,6);3;ЕСЛИ(B2<=ПРОЦЕНТИЛЬ.ВКЛ($B$2:$B$2341;0,8);2;1))))
Для Frequency и Monetary логика обратная — больше значение, выше балл. После этого склеиваем три балла в одну строку сегмента: =ТЕКСТ(F2;"0")&ТЕКСТ(G2;"0")&ТЕКСТ(H2;"0") — получится код вроде «534» или «115».
| Код RFM | Название сегмента | Что характерно |
|---|---|---|
| 5-5-5, 5-4-5 | Чемпионы | Покупали недавно, часто и на крупные суммы |
| 4-4-3, 3-4-4 | Лояльные клиенты | Стабильно покупают, средний или высокий чек |
| 5-1-1, 4-2-2 | Новые клиенты | Купили недавно, но пока одна-две покупки |
| 2-3-3, 2-2-4 | Клиенты под риском | Раньше покупали регулярно, но давно не возвращались |
| 1-1-1, 1-1-2 | Спящие / потерянные | Давно не покупали, низкая частота и сумма |
Присвоить итоговое название сегмента по коду удобно через ВПР или ИНДЕКС+ПОИСКПОЗ со справочной таблицей — так же, как я делал справочник сегментов в статье про ABC-XYZ анализ, только там оси другие.
Что делать с каждым сегментом после RFM-анализа
Ниже — обобщённый пример логики, а не описание одного конкретного проекта: у интернет-магазина косметики с базой около 2 300 клиентов после расчёта RFM в Excel сегмент «чемпионов» занял 9% базы, но давал 34% выручки за полугодие. Сегмент «под риском» — 17% базы и почти нулевая активность за последние 60 дней.
Чемпионы и лояльные — ретаргетинг с новинками, а не со скидками, маржа не пострадает
Новые клиенты — цепочка писем и объявлений на закрепление привычки, вторая покупка в течение 30 дней
Под риском — отдельная кампания в Директе с промокодом на возврат, но не для всех подряд
Спящие — исключить из большинства РК до отдельной реактивационной волны, чтобы не тратить бюджет вхолостую
Такая разбивка напрямую влияет на анализ рекламных расходов по товарным категориям — если реклама показывается на «спящих» с тем же бюджетом, что и на «чемпионов», ROMI кампании искажается, а причина остаётся незамеченной, потому что в отчёте по категориям товаров этого разреза просто нет.
RFM-анализ в Excel или специализированные сервисы: что выбрать
Сравнение подходов
Excel не единственный способ считать RFM — есть CRM со встроенной сегментацией и BI-инструменты вроде Looker Studio, о подключении которого к Яндекс Метрике я писал отдельно в статье про Looker Studio для Яндекс Метрики. Разница — в скорости запуска и в том, как часто нужно обновлять расчёт.
| Критерий | Excel | CRM / BI-сервис |
|---|---|---|
| Запуск | 1–2 часа при готовой выгрузке | Настройка интеграции от нескольких дней |
| Стоимость | Без доплат, если Excel уже есть | Подписка от 2 000 ₽/мес и выше |
| Обновление данных | Вручную, каждая выгрузка отдельно | Автоматически, в реальном времени |
| Подходит для | Базы до 10–15 тысяч клиентов, разовый или квартальный расчёт | Постоянный мониторинг, автоматические триггеры в рассылках |
На практике я советую начинать с Excel даже тем компаниям, где потом планируется CRM с автоматической сегментацией — так проще проверить логику сегментов на своих данных, прежде чем платить за автоматизацию того, что может не подойти по структуре базы.
Когда RFM-анализ в Excel не подходит
✅ Стоит считать в Excel
— База от 200 до 15 000 клиентов
— Есть выгрузка заказов с датой и суммой
— Расчёт нужен раз в квартал или реже
— Важна прозрачность формул для команды
❌ Лучше искать другой инструмент
— База свыше 50–100 тысяч клиентов
— Нужны ежедневные автотриггеры в рассылках
— Заказы приходят из 4–5 разных источников без единого ID
— Требуется автоматическая связка с рекламными кампаниями
Типичные ошибки при RFM-анализе в Excel
❌ Деление на баллы «на глаз»
Если пороги для баллов выбраны произвольно (например, «до 30 дней — 5, до 60 — 4»), сегменты получаются неравномерными по размеру. Квартили через ПРОЦЕНТИЛЬ.ВКЛ дают более честную картину, потому что подстраиваются под реальное распределение конкретной базы.
❌ Смешивание B2C и корпоративных заказов
Один клиент с заказом на 218 600 ₽ искажает квартили для остальных 2 339 клиентов. Крупных B2B-заказчиков лучше выносить в отдельную таблицу и анализировать без привязки к рознице.
❌ Игнорирование клиентов без покупок в анализируемый период
Если строить квартили только по «живым» покупателям, отвалившиеся клиенты вообще не попадают в таблицу — и компания теряет из виду самый дорогой для реактивации сегмент. Важно включать в исходную выгрузку всех, у кого была хотя бы одна покупка за последние 12–24 месяца, даже если сейчас они молчат.
❌ Расчёт R, F, M по разным периодам
Если Recency считается за последний месяц, а Frequency — за весь год, сегменты получаются несопоставимыми: клиент, который сделал 10 заказов два года назад и не покупал с тех пор, может попасть в один сегмент с тем, кто купил 10 раз за последние три месяца. Все три метрики должны считаться в одном временном окне — обычно 12 месяцев.
Что делать с сегментами после расчёта
Сам расчёт RFM-баллов — это только половина работы. Ценность появляется, когда под каждый сегмент подбирается конкретное маркетинговое действие. Ниже — примерная логика, которую можно адаптировать под свою нишу.
| Сегмент | Что делать |
|---|---|
| Champions | Ранний доступ к новинкам, программа лояльности, персональный менеджер, просить отзывы и рекомендации |
| Loyal Customers | Кросс-продажи смежных категорий, накопительные скидки, приглашение в закрытые распродажи |
| At Risk | Персональное письмо с напоминанием, ограниченная по времени скидка, опрос «что не так» |
| Lost | Разовая реактивационная рассылка с максимальной выгодой; при отсутствии реакции — исключить из регулярных рассылок, чтобы не портить репутацию отправителя |
| New Customers | Онбординг-цепочка писем, подсказки по использованию товара, лёгкий стимул для второй покупки |
Отдельно стоит сказать про рекламные кабинеты: сегменты Champions и Loyal Customers логично исключать из холодных кампаний на охват — это лишний расход бюджета на тех, кто и так купит. А вот сегмент At Risk имеет смысл догонять ретаргетингом с более выгодным предложением, чем в обычной рассылке.
Частые вопросы про RFM-анализ в Excel
Сколько минимум заказов нужно для корректного RFM-анализа?
Формально формулы сработают даже на 50 строках, но статистически надёжные квартили получаются от 200–300 уникальных клиентов с хотя бы одной покупкой за анализируемый период. На меньших выборках сегменты будут «дёргаться» при каждом новом заказе, и делать на них выводы рискованно.
Можно ли делать RFM-анализ без суммы заказа, только по датам и количеству?
Можно — тогда получится не RFM, а RF-анализ по двум метрикам. Он менее точен, потому что не учитывает ценность клиента в деньгах, но подходит, если сумма заказа скрыта или недоступна в выгрузке (например, для маркетплейсов, отдающих только даты обращений).
Как часто нужно пересчитывать RFM-сегменты?
Для большинства розничных и e-commerce проектов достаточно раз в квартал. Для бизнеса с частыми покупками (доставка еды, подписочные сервисы) имеет смысл пересчитывать раз в месяц, иначе клиенты «зависают» в старых сегментах и рассылки бьют не туда.
Почему квартили дают неровные границы сегментов?
Это нормально для реальных данных: распределение суммы и частоты покупок почти никогда не бывает равномерным. Если границы сегментов кажутся странными (например, «5 баллов» получают клиенты с очень разной суммой), можно перейти на ручные пороги, ориентируясь на бизнес-логику, а не строго на квартили.
Нужно ли отдельно считать RFM для новых клиентов текущего месяца?
Да, их стоит выделять в отдельный сегмент New Customers вместо того, чтобы прогонять через общие квартили — из-за короткой истории они всегда получат низкий балл по Frequency и Monetary, хотя по факту это свежий и потенциально ценный контакт.
Что делать, если в Excel не хватает функции ПРОЦЕНТИЛЬ.ВКЛ?
Скорее всего используется устаревшая версия Excel — можно заменить на функцию ПРОЦЕНТИЛЬ (без суффикса), она даёт идентичный результат в старых версиях 2007–2010. В Google Таблицах аналог называется PERCENTILE и работает так же.
Заключение
RFM-анализ в Excel — это не компромиссный, а вполне рабочий инструмент для баз до 10–15 тысяч клиентов: три формулы, сводная таблица и понятная логика квартилей позволяют за пару часов получить сегментацию, которую раньше делали на глаз или не делали вовсе. Ключевое — не гнаться за идеальными формулами, а начать с чистой выгрузки заказов и честного распределения баллов по реальным квартилям своей базы, а не по чужим шаблонным порогам.
Дальше сегменты нужно не просто увидеть, а превратить в действия: свою рассылку для Champions, реактивацию для At Risk, онбординг для New Customers. Даже если через полгода компания перейдёт на CRM с автоматической сегментацией, опыт ручного RFM-анализа в Excel останется полезным — он покажет, какие пороги и периоды действительно отражают поведение клиентов именно в этом бизнесе, а не в усреднённом кейсе из документации сервиса.
Хотите превратить RFM-сегменты в рабочие рекламные кампании и автоматические цепочки писем, а не просто в таблицу Excel? Оставьте заявку в TrafDealer — поможем настроить сегментацию и рекламу под каждый сегмент клиентов.
Читайте также
→ Сегментация клиентской базы: методы и примеры для интернет-магазина
→ Как считать LTV клиента: формулы и примеры расчёта
→ Почему клиенты уходят и как их вернуть: рабочие сценарии реактивации
Автор: Павел Комарков — специалист по контекстной рекламе с 2010 года, работал с бюджетами от 50 000 ₽ до 150 млн ₽ в месяц. Подробнее →
