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.

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

    UPD от 30.04.2020: ввиду текущей экономической ситуации решил вспомнить Excel и бесплатно помочь малому бизнесу созданием подобных инструментов до конца 2020 года. Все интеллектуальные права останутся за мной и будут опубликованы в виде статьи (без данных, содержащих коммерческую тайну).

    Критерии попадания в выборку кому хочу помочь:

    1. Ваше дело имеет очевидную ценность для общества (благотворительные фонды, волонтерские движения и т.д.)
    2. Вы платите белые зарплаты и приемлемый для оборота объем налогов (придется доказать выписками перечислений в фонды и на счета ФНС)
    3. Вы не МММ, продавцы алкоголя возле школ, микрофинансовые организации и прочие лохотронщики

    3 пункт — обязательный, также необходимо попадание в 1 или 2 пункты. Если попадаете в выборку, то пишите в личку.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

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

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

        Извините за нытье.
        0
        Интересно за что минус) Отпишитесь хоть — прокомментирую, подискутируем.
          0
          Пришел сюда по ссылке из статьи про импортозамещение. Интересная задачка — я бы её решал одним запросом в SQLite. Но это потому что я с ним имел больше дела, чем с Excel'ем :-)

          Посмею себе только не согласиться с утверждением про формулу в колонке Frequency:
          мы разделили всю совокупность на 3 равных по количеству членов совокупности промежутка и смотрим к какому из этих промежутков относится значение в колонке С


          Вы делите всю совокупность на 3 промежутка: где значение Frequency меньше, равно, и больше медианы — в этом мы с Вами согласны. Однако неверно утверждать, что эти три промежутка равны. Если 6 клиентов совершили 1, 2, 3, 4, 5, 6 покупок, то медиана равна 3.5, и во втором промежутке нет клиентов. Но, конечно, в реальности распределение будет ровнее, так что несколько клиентов в медиану все равно попадут.
            0
            Вы, наверное, 10-й, кто мне сообщает, что это можно сделать не в Excel))) Сделайте (ссылка на условия для Вашего предшественника), напишите статью об этом и обещаю дать ссылку на нее в своей.

            Только не забудьте главное — нужно не просто повторить вычисления (тут ума много не надо), но необходимо создать готовый под ключ инструмент для тех, кто не имеет большого IT-бэкграунда. Т.е. бэкенд+фронтэнд+инструкция по разворачиванию всего этого для пользователя. Вангую, что времени вы потратите и сил сильно больше, чем я на создание всего этого. Каждой задаче — свой инструмент. И для задач ad-hoc аналитики на массивах до сотен тысяч строк лучше и распространеннее Excel еще не придумали. А спорят с этим выводом только те, кто шурупы микроскопом забивает никогда не работал аналитиком данных.
              0
              Не-не-не, я ни в коем случае не соревнуюсь — сам непрограммист, у меня бы в той задаче час ушел бы только на то, чтобы input'ы по форме разбросать да подписать :-) Про SQLite — это мысли вслух о том, что те, кто ничего кроме микроскопа в руках не держал — и шурупы будет им забивать ;-)

              Корректности ради следовало бы спросить читателей, сколько времени им потребовалось бы, чтобы реализовать то же самое в том же самом Excel'е. Как бы Вы отреагировали на возражение вроде: «я попробовал повторить то же самое, но за час не успел. Следовательно, Вы всё врёте!»? :-D

              А теперь вопрос по сути — от человека, не пользовавшегося Excel со школы: вот в одном месяце Вам дали xls-файл с исходными данными, Вы (видимо, в нём же) ввели все эти формулы и получили красивый результат. Хорошо. Но что Вы будете делать, если через месяц Вам принесут новый файл с исходными данными? Заново будете вводить все формулы или скопируете исходные данные в файл с формулами? Наверное, второе. Но что делать с количеством строк в исходных данных (на скриншотах в формулах много где присутствует волшебное число 592), и списка клиентов ("копируем с первого листа список клиентов оставляем только уникальные записи при помощи стандартного функционала (Данные — Удалить дубликаты)" — это вряд ли «само пересчитается»)? Или это делается "при помощи вычисляемых объектов сводной таблицы, чтобы было красиво"?
                0
                На мой комментарий об ad-hoc задачах в Excel вы отвечаете пассажем о целесообразности автоматизации. Ок, уйдем в сторону — любая автоматизация имеет смысл, когда эффект от нее превосходит трудозатраты на нее саму. Базовые экономические понятия: эффект/эффективность. В свете полученных знаний, попробуйте сами ответить на Ваши вопросы)

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

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

                Засим предлагаю закончить дискуссию не по теме статьи. Пишите в личку, если вопросы остались — без проблем отвечу.

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

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