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 =NOW()) і дата останньої покупки клієнта. Оскільки вивантаження являла собою невпорядкований масив «Дата-Клієнт-сумма_покупки», існувала складність виявлення останньої дати покупки по кожному з клієнтів. Проблема була вирішена сортуванням по всьому об'єму дат у вивантаженні (прошу не звинувачувати за «колгоспний стиль», але в той момент на красу забив, так як хотів максимально швидко реалізувати мав у голові рішення). Зеленим позначені колонки первісної інформації. У першій рядку залишив формули для розуміння, а сортував по колонці в порядку убування (колонка створена за допомогою зчепити)


4. Складові частини аркуша «Підсумок»

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

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

5. Recency (час з моменту останньої покупки)

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


6. Frequency (частота покупок за період) і Monetary (вартість покупок за період).

Формули ідентичні, тому розглянемо на прикладі Frequency. В даному випадку ми розділили всю сукупність на 3 рівних за кількістю членів сукупності проміжку і дивимося до якого з цих проміжків відноситься значення в колонці З виставленням оцінок 1(клієнт купує у нас рідше за інших), 3, 5 (клієнт купує у нас частіше інших).


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

Висновок: про додавання всіх показників разом і сортування в порядку убування самої правої колонки листа «Підсумок» писати не став — думаю, отже зрозуміло)) Моя мета — створити систему «на коліні», була повністю досягнута. Віддаю «як є». Дописуючи ці рядки розумію, що моє визначення медіани і приклад теж не самі легкі (для тих у кого не було в університеті мат.статистики). Якщо хтось запропонує більш простий і зрозумілий варіант — заміню.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.