RFM-анализ на коленке (Excel)

    Добрый день! Летом 2014 года, работая обычным аналитиком и сильно страдая от прокрастинации, поучаствовал в создании онлайн магазина одежды. Успешно «запилив» для этого проекта систему управленческого учета, обрел в глазах собственника ореол бога аналитики в целом, и Excel'я в частности)) С тех пор собственник, будучи человеком неглупым, хотя и жутко ленивым, привлекал меня для решения всех мало-мальски близких к аналитике задач. Результатом одной из этих задач и хочу поделиться. Под катом мой вариант реализации RFM-анализа. Интересно будет владельцам небольшого B2C бизнеса, не имеющим значительного бюджета на исследования, а также всем интересующимся практическим применением Excel в бизнесе.


    Офтоп: с тегом RFM на Хабре лишь 2 статьи, и обе из корпоративных блогов. Странно, почему так мало контента по тематике, ведь на Хабре много людей из e-commerce related area?

    Однако, бросаю лить воду и предлагаю, для начала, договориться о терминах. Далее под RFM-анализом подразумевается анализ ценности клиента для компании. По сути, слегка продвинутый вариант ABC-анализа, только с фокусом не на товарах, а на клиентах. Во главу угла ставится формализация размера пользы каждого клиента для бизнеса. С целью выявления это пользы каждый клиент рассматривается по следующим параметрам:

    Recency — новизна (время с момента последней покупки)
    Frequency — частота (частота покупок за период)
    Monetary — монетизация (стоимость покупок за период)

    Дано:

    1. История продаж интернет-магазина в виде .xlsx выгрузки, наподобие
    image

    Sic! Не ищите смысла в цифрах, все полу-рандомно изменено на 1-2 порядка

    2. ТЗ от собственника, полная версия которого звучит не сложнее фразы «RFM-анализ сделать можешь?»

    Результат:

    Поначалу, полдня потратил на раздумья «Как все это сделать при помощи вычисляемых объектов сводной таблицы, чтобы было красиво». В итоге, забил на красоту и за час сделал с помощью промежуточного листа и обычных формул типа "=ЕСЛИ" и т.д.

    3. Промежуточные вычисления

    Для вычисления времени с момента последней покупки необходима текущая дата (стандартная функция в Excel =ТДАТА()) и дата последней покупки клиента. Поскольку выгрузка представляла собой неупорядоченный массив «Дата-Клиент-сумма_покупки», существовала сложность выявления последней даты покупки по каждому из клиентов. Проблема была решена сортировкой по всему объему дат в выгрузке (прошу не винить за «колхозный стиль», но в тот момент на красоту забил, так как хотел максимально быстро реализовать имевшееся в голове решение). Зеленым отмечены колонки первоначальной информации. В первой строке оставил формулы для понимания, а сортировал по колонке в порядке убывания (колонка создана при помощи сцепить)


    4. Составные части листа «Итог»

    Теперь собираем результат RFM-анализа на одном листе. Начинаем со списка клиентов (сортировка не имеет значения) — копируем с первого листа список клиентов оставляем только уникальные записи при помощи стандартного функционала (Данные — Удалить дубликаты). В колонку B при помощи ВПР тянем дату последнего заказа клиента. Формула в колонке С считает количество заказов клиента по всей выгрузке. В колонке D похожим образом считается сумма заказов по клиенту. А столбец E вычисляет для нас количество дней с момента последней покупки клиентом.

    Sic! пример формулы для колонки E указан в ячейке K1, а в самом столбце E сохранены лишь значения для демонстрации результата

    5. Recency (время с момента последней покупки)

    Суть выделенной формулы в следующем: смотрим в каком из пяти равных промежутков от 0 до максимума (подсвечено в формуле красным) находится значение каждой ячейки колонки Е и проставляем оценку от 1 (клиент, купивший у нас нечто год назад) до 5 (клиент купивший что-либо в последнее время).


    6. Frequency (частота покупок за период) и Monetary (cтоимость покупок за период).

    Формулы идентичны, поэтому рассмотрим на примере Frequency. В данном случае мы разделили всю совокупность на 3 равных по количеству членов совокупности промежутка и смотрим к какому из этих промежутков относится значение в колонке С с выставлением оценок 1(клиент покупающий у нас реже остальных), 3, 5 (клиент покупающий у нас чаще остальных).


    Для тех кому сложно или лениво понять определение медианы в википедии : медиана — это значение, делящее совокупность данных на 2 равные по количеству части. Пример: cреднее арифметическое значение 5 клиентов совершивших 1, 2, 2, 2, 100 покупок = 21,4 (ничего не говорящая нам средняя температура по больнице); медиана для этого же ряда = 2.

    Заключение: про сложение всех показателей вместе и сортировку в порядке убывания самой правой колонки листа «Итог» писать не стал — думаю, итак понятно)) Моя цель — создать систему «на коленке», была полностью достигнута. Отдаю «как есть». Дописывая эти строчки понимаю, что мое определение медианы и пример тоже не самые легкие (для тех у кого не было в университете мат.статистики). Если кто предложит более простой и понятный вариант — заменю.
    • +12
    • 21,1k
    • 3
    Поддержать автора
    Поделиться публикацией

    Комментарии 3

      0
      Спасибо. Может напишете о своем опыте аналитики в e-commerce? Интересны применяемые методики и достигнутые результаты.
        0
        Пожалуйста. Как только будут настоящие результаты — обязательно напишу. А пока я лишь «прокрастинирую» на работе и занимаюсь халтурой (типа описанной выше) — мне уже полгода не могу SQL'ный сервак для BI сделать, т.к. это «not 1 priority» :-)

        Извините за нытье.
        0
        Интересно за что минус) Отпишитесь хоть — прокомментирую, подискутируем.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое