Вариант реализации RFM-анализа. Интересно будет владельцам небольшого B2C бизнеса, не имеющим значительного бюджета на исследования, а также всем интересующимся практическим применением Excel в бизнесе.
Предлагаю, для начала, договориться о терминах. Далее под RFM-анализом подразумевается анализ ценности клиента для компании. По сути, слегка продвинутый вариант ABC-анализа, только с фокусом не на товарах, а на клиентах. Во главу угла ставится формализация размера пользы каждого клиента для бизнеса. С целью выявления это пользы каждый клиент рассматривается по следующим параметрам:
Recency — новизна (время с момента последней покупки)
Frequency — частота (частота покупок за период)
Monetary — монетизация (стоимость покупок за период)
Дано:
1. История продаж интернет-магазина в виде .xlsx выгрузки, наподобие
Sic! Не ищите смысла в цифрах, все полу-рандомно изменено на 1-2 порядка
2. ТЗ от собственника, полная версия которого звучит не сложнее фразы «RFM-анализ сделать можешь?»
Есть ли альтернатива Excel в сфере бюджетирования и бизнес-аналитики
Результат:
Поначалу, полдня потратил на раздумья «Как все это сделать при помощи вычисляемых объектов сводной таблицы, чтобы было красиво». В итоге, забил на красоту и за час сделал с помощью промежуточного листа и обычных формул типа «=ЕСЛИ» и т.д.
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.
Воронка продаж: делаем автоматически обновляемый отчет из базы данных с помощью Excel
