RFM - часть #3. Анализ в Power BI

Юрий Мацегора
Follow me

Юрий Мацегора

Business analyst at Smart-Metrika
Бизнес - аналитик, а также автор и редактор на сайте, если у Вас есть вопросы о содержании и материалах статей пишите мне:)
Юрий Мацегора
Follow me

 

Сегодня речь пойдет о "вложенном методе сегментации" покупателей. Вложенный (иерархический) метод применяют не только в RFM анализе, во всяком случае в агентстве Smart-Metrika. По аналогии с прошлым постом Вы можете скачать уже готовые файлы с прописанными формулами.

Файлы с примерами в формате .xlsx для Excel и .pbi для Microsoft Power BI Desktop (см. в конце статьи).

Первая часть oб RFM анализе в Power BI 

 

Мастер кунфу RFM

 

Исходные данные для анализа взяты из первой статьи специально для того, чтобы сравнить результаты "независимого" и "вложенного" метода.

Суть вложенного метода - в создании иерархии, где вершиной будет оценка недавности, а оценка частоты будет ниже в структуре. Оценка денег будет ниже оценки частоты. Звучит непонятно, лучше разобраться на примере.

1-й уровень. На карте это выглядит так:

1,2,3,4,5 - оценка переменной. Соответственно 1 балл, 2 ... и т.д.

2-й уровень. При значении недавности в один балл мы делим значение частоты на 5 различных интервалов.

То же самое и при значении недавности в 2,3,4 и 5 баллов.

3-й уровень.  Вместо недавности - частота. Получаем нижний уровень иерархии - деньги.

Все еще достаточно сложно - повторим все шаги, но уже с помощью формул Excel, а потом и языка DAX в Power BI Desktop (см. ссылки на скачивание)

 

2-й Кю - коричневый пояс

 

Возьмем исходные данные с учебного примера и создадим 1-й уровень.

Формула достаточно простая и не отличается от примера с независимой сегментацией.

=ЕСЛИ(F:F<=145;5;ЕСЛИ(F:F<=290;4;ЕСЛИ(F:F<=435;3;ЕСЛИ(F:F<=580;2;ЕСЛИ(F:F>580;1)))))

2-й уровень. Для наглядности создано дополнительно пять столбцов: F5, F4,F3,F2,F1.

Столбец F5 - оценка частоты при значении недавности в 5 баллов

=ЕСЛИ(I2=5;ЕСЛИ(G:G<=2,2;1;ЕСЛИ(G:G<=4,4;2;ЕСЛИ(G:G<=6,6;3;ЕСЛИ(G:G<=8,8;4;ЕСЛИ(G:G>8,8;5)))));" ")

Столбец F4 - оценка частоты при значении недавности в 4 балла

=ЕСЛИ(I2=4;ЕСЛИ(G:G<=3,6;1;ЕСЛИ(G:G<=7,2;2;ЕСЛИ(G:G<=10,8;3;ЕСЛИ(G:G<=14,4;4;ЕСЛИ(G:G>14,4;5)))));" ")

Столбец F3 - оценка частоты при значении недавности в 3 балла

=ЕСЛИ(I2=3;ЕСЛИ(G:G<=7,4;1;ЕСЛИ(G:G<=14,8;2;ЕСЛИ(G:G<=22,2;3;ЕСЛИ(G:G<=29,6;4;ЕСЛИ(G:G>29,6;5)))));" ")

Столбец F2 - оценка частоты при значении недавности в 2 балла

=ЕСЛИ(I2=2;ЕСЛИ(G:G<=6,6;1;ЕСЛИ(G:G<=13,2;2;ЕСЛИ(G:G<=19,8;3;ЕСЛИ(G:G<=24,4;4;ЕСЛИ(G:G>24,4;5)))));" ")

Столбец F1 - оценка частоты при значении недавности в 1 балл.

=ЕСЛИ(I2=1;ЕСЛИ(G:G<=3,8;1;ЕСЛИ(G:G<=7,2;2;ЕСЛИ(G:G<=11;3;ЕСЛИ(G:G<=14,8;4;ЕСЛИ(G:G>14,8;5)))));" ")

Если всё это записать одной формулой, то получим оценку частоты (формулы дополнительных столбцов сведены в одну формулу):

=ЕСЛИ(I2=5;ЕСЛИ(G:G<=2,2;1;ЕСЛИ(G:G<=4,4;2;ЕСЛИ(G:G<=6,6;3;ЕСЛИ(G:G<=8,8;4;ЕСЛИ(G:G>8,8;5;)))));ЕСЛИ(I2=4;ЕСЛИ(G:G<=3,6;1;ЕСЛИ(G:G<=7,2;2;ЕСЛИ(G:G<=10,8;3;ЕСЛИ(G:G<=14,4;4;ЕСЛИ(G:G>14,4;5)))));ЕСЛИ(I2=3;ЕСЛИ(G:G<=7,4;1;ЕСЛИ(G:G<=14,8;2;ЕСЛИ(G:G<=22,2;3;ЕСЛИ(G:G<=29,6;4;ЕСЛИ(G:G>29,6;5;)))));ЕСЛИ(I2=2;ЕСЛИ(G:G<=6,6;1;ЕСЛИ(G:G<=13,2;2;ЕСЛИ(G:G<=19,8;3;ЕСЛИ(G:G<=24,4;4;ЕСЛИ(G:G>24,4;5)))));ЕСЛИ(I2=1;ЕСЛИ(G:G<=3,8;1;ЕСЛИ(G:G<=7,2;2;ЕСЛИ(G:G<=11;3;ЕСЛИ(G:G<=14,8;4;ЕСЛИ(G:G>14,8;5))))))))))

3-й уровень.

Проделаем то же самое, но за основу уже возьмем оценку частоты.

Теже пять столбцов: М5,М4,М3,М2,М1

 

М5 - оценка частоты при значении недавности в 5 баллов

=ЕСЛИ(O2=5;ЕСЛИ(H:H<=12452;1;ЕСЛИ(H:H<=24904;2;ЕСЛИ(H:H<=37356;3;ЕСЛИ(H:H<=49808;4;ЕСЛИ(H:H>49808;5)))));" ")

М4 - оценка частоты при значении недавности в 4 балла

=ЕСЛИ(O2=4;ЕСЛИ(H:H<=8664;1;ЕСЛИ(H:H<=17328;2;ЕСЛИ(H:H<=25992;3;ЕСЛИ(H:H<=34656;4;ЕСЛИ(H:H>34656;5)))));" ")

М3 - оценка частоты при значении недавности в 3 балла

=ЕСЛИ(O2=3;ЕСЛИ(H:H<=17290;1;ЕСЛИ(H:H<=34580;2;ЕСЛИ(H:H<=51870;3;ЕСЛИ(H:H<=69160;4;ЕСЛИ(H:H>69160;5)))));" ")

М2 - оценка частоты при значении недавности в 2 балла

=ЕСЛИ(O2=2;ЕСЛИ(H:H<=8078;1;ЕСЛИ(H:H<=16156;2;ЕСЛИ(H:H<=24234;3;ЕСЛИ(H:H<=32312;4;ЕСЛИ(H:H>32312;5)))));" ")

М1 - оценка частоты при значении недавности в 1 балл

=ЕСЛИ(O2=1;ЕСЛИ(H:H<=11072;1;ЕСЛИ(H:H<=22144;2;ЕСЛИ(H:H<=33216;3;ЕСЛИ(H:H<=44288;4;ЕСЛИ(H:H>44288;5)))));" ")

Общая формула оценки "денег":

=ЕСЛИ(O2=5;ЕСЛИ(H:H<=12452;1;ЕСЛИ(H:H<=24904;2;ЕСЛИ(H:H<=37356;3;ЕСЛИ(H:H<=49808;4;ЕСЛИ(H:H>49808;5)))));ЕСЛИ(O2=4;ЕСЛИ(H:H<=8664;1;ЕСЛИ(H:H<=17328;2;ЕСЛИ(H:H<=25992;3;ЕСЛИ(H:H<=34656;4;ЕСЛИ(H:H>34656;5)))));ЕСЛИ(O2=3;ЕСЛИ(H:H<=17290;1;ЕСЛИ(H:H<=34580;2;ЕСЛИ(H:H<=51870;3;ЕСЛИ(H:H<=69160;4;ЕСЛИ(H:H>69160;5)))));ЕСЛИ(O2=3;ЕСЛИ(H:H<=17290;1;ЕСЛИ(H:H<=34580;2;ЕСЛИ(H:H<=51870;3;ЕСЛИ(H:H<=69160;4;ЕСЛИ(H:H>69160;5)))));ЕСЛИ(O2=2;ЕСЛИ(H:H<=8078;1;ЕСЛИ(H:H<=16156;2;ЕСЛИ(H:H<=24234;3;ЕСЛИ(H:H<=32312;4;ЕСЛИ(H:H>32312;5)))));ЕСЛИ(O2=1;ЕСЛИ(H:H<=11072;1;ЕСЛИ(H:H<=22144;2;ЕСЛИ(H:H<=33216;3;ЕСЛИ(H:H<=44288;4;ЕСЛИ(H:H>44288;5)))))))))))

Общая оценка RFM по-прежнему считается с помощью формулы "сцепить":

=СЦЕПИТЬ(I2;O2;U2)

 

1-й Кю

 

Адаптируем данную модель для Power BI Desktop, все те же 3 уровня:

1-й уровень. Берем исходные данные из отчета в прошлой статье и создаем уровень недавности:

R = IF('Лист1'[недавность]<=145;5;
IF('Лист1'[недавность]<=290;4;
IF('Лист1'[недавность]<=435;3;
IF('Лист1'[недавность]<=580;2; IF('Лист1'[недавность]>580;1)))))

 

2-й уровень. Создадим оценки частоты без вспомогательных столбцов, одной формулой:

F = IF('Лист1'[R]=5;IF('Лист1'[частота]<=2,2;1;IF('Лист1'[частота]<=4,4;2;IF('Лист1'[частота]<=6,6;3;IF('Лист1'[частота]<=8,8;4;IF('Лист1'[частота]>8,8;5)))));IF('Лист1'[R]=4;IF('Лист1'[частота]<=3,6;1;IF('Лист1'[частота]<=7,2;2;IF('Лист1'[частота]<=10,8;3;IF('Лист1'[частота]<=14,4;4;IF('Лист1'[частота]>14,4;5)))));IF('Лист1'[R]=3;IF('Лист1'[частота]<=7,4;1;IF('Лист1'[частота]<=14,8;2;IF('Лист1'[частота]<=22,2;3;IF('Лист1'[частота]<=29,6;4;IF('Лист1'[частота]>29,6;5)))));IF('Лист1'[R]=2;IF('Лист1'[частота]<=6,6;1;IF('Лист1'[частота]<=13,2;2;IF('Лист1'[частота]<=19,8;3;IF('Лист1'[частота]<=24,4;4;IF('Лист1'[частота]>24,4;5)))));IF('Лист1'[R]=1;IF('Лист1'[частота]<=3,8;1;IF('Лист1'[частота]<=7,2;2;IF('Лист1'[частота]<=11;3;IF('Лист1'[частота]<=14,8;4;IF('Лист1'[частота]>14,8;5))))))))))

3-й уровень. Оценка денег одной формулой:

M = IF('Лист1'[F]=5;IF('Лист1'[сумма]<=12452;1;IF('Лист1'[сумма]<=24904;2;IF('Лист1'[сумма]<=37356;3;IF('Лист1'[сумма]<=49808;4;IF('Лист1'[сумма]>49808;5)))));IF('Лист1'[F]=4;IF('Лист1'[сумма]<=8664;1;IF('Лист1'[сумма]<=17328;2;IF('Лист1'[сумма]<=25992;3;IF('Лист1'[сумма]<=34656;4;IF('Лист1'[сумма]>34656;5)))));IF('Лист1'[F]=3;IF('Лист1'[сумма]<=17290;1;IF('Лист1'[сумма]<=34580;2;IF('Лист1'[сумма]<=51870;3;IF('Лист1'[сумма]<=69160;4;IF('Лист1'[сумма]>69160;5)))));IF('Лист1'[F]=3;IF('Лист1'[сумма]<=17290;1;IF('Лист1'[сумма]<=34580;2;IF('Лист1'[сумма]<=51870;3;IF('Лист1'[сумма]<=69160;4;IF('Лист1'[сумма]>69160;5)))));IF('Лист1'[F]=2;IF('Лист1'[сумма]<=8078;1;IF('Лист1'[сумма]<=16156;2;IF('Лист1'[сумма]<=24234;3;IF('Лист1'[сумма]<=32312;4;IF('Лист1'[сумма]>32312;5)))));IF('Лист1'[F]=1;IF('Лист1'[сумма]<=11072;1;IF('Лист1'[сумма]<=22144;2;IF('Лист1'[сумма]<=33216;3;IF('Лист1'[сумма]<=44288;4;IF('Лист1'[сумма]>44288;5)))))))))))

и общая оценка с помощью амперсандов:

RFM = ('Лист1'[R] & 'Лист1'[F] & 'Лист1'[M])

 

Scorpion vs Sub-Zero

 

 

Если визуализировать результаты получим отчет:

Изменены только значения F, M и общая RFM-оценка.

Сравним с отчетом из прошлой статьи.

Кардинальных изменений не произошло, но все же ситуация стала более равномерной (сглаженной), чего мы и добивались. Появились дополнительные оценки, а некоторые изменились. Наше агентство рекомендует использовать вложенный метод сегментации клиентской базы в Power BI.

Еще большей равномерности оценок можно добиться, если использовать более сложные методы интервальной сегментации, но об этом - в другой раз.


**При подготовке статьи использовалась официальная документация и сопутствующие материалы по Power BI Desktop, а также наработки компании Smart-Metrika.

Cсылка на скачивание Excel файла

Ссылка на скачивание файла Power BI