Добрый день уважаемые читатели! О данном методе сегментации клиентов по давности покупок, частоте и сумме сделок написано довольно много материалов. На просторах Интернета вы без труда найдете публикации с описанием теории и практики rfm-анализа. Он может выполняться как на платформе табличного редактора (при небольшом количестве данных), так и с помощью sql-запросов или силами тематических библиотек Python/R. Методология всех примеров одна и та же, расхождение будет только в деталях. Например, порядок присвоения номеров сегментам или принцип деления на группы. Ввиду всего вышеизложенного мне будет трудно привнести новизну в эту тему. В статье я лишь постараюсь заострить ваше внимание на некоторых моментах, которые могут помочь начинающим аналитикам данных.
Для демонстрации работы скриптов я выбрал базу данных PostgreSQL и JupyterLab из комплекта Anaconda. Все примеры кода, которые вы увидите в публикации, можно найти на GitHub (ссылка). Данные для анализа взяты с портала Kaggle (ссылка).
Перед загрузкой датасета в БД изучите данные, если вы заранее не уверены в их качестве. Особенное внимание следует уделять колонкам с датами, пропускам в записях, неверному определению типа полей. Для упрощения демо-примера я также отклонил записи с возвратами товаров.
import pandas as pd
import numpy as np
import datetime as dt
pd.set_option('display.max_columns', 10)
pd.set_option('display.expand_frame_repr', False)
df = pd.read_csv('dataset.csv', sep=',', index_col=[0])
#Приводим названия столбцов датасета к нижнему регистру
df.columns = [_.lower() for _ in df.columns.values]
#Трансформируем строку-дату в правильный формат и избавляемся от времени
df['invoicedate'] = pd.to_datetime(df['invoicedate'], format='%m/%d/%Y %H:%M')
df['invoicedate'] = df['invoicedate'].dt.normalize()
#Удаляем строки с пропусками и возвратами
df_for_report = df.loc[(~df['description'].isnull()) &
(~df['customerid'].isnull()) &
(~df['invoiceno'].str.contains('C', case=False))]
#Назначаем всем числовым столбцам правильные форматы
convert_dict = {'invoiceno': int, 'customerid': int, 'quantity': int, 'unitprice': float}
df_for_report = df_for_report.astype(convert_dict)
#Контроль проведенных преобразований
# print(df_for_report.head(3))
# print(df_for_report.dtypes)
# print(df_for_report.isnull().sum())
# print(df_for_report.info())
#Выгружаем датасет в новый файл формата csv
df_for_report.to_csv('dataset_for_report.csv', sep=";", index=False)На следующем этапе необходимо создать в БД новую таблицу. Сделать это можно как в режиме графического редактора с помощью утилиты pgAdmin, так и с помощью кода Python.
import psycopg2
# Подключение к БД
conn = psycopg2.connect("dbname='db' user='postgres' password='gfhjkm' host='localhost' port='5432'")
print("Database opened successfully")
# Создаем курсор
cursor = conn.cursor()
with conn:
cursor.execute("""
DROP TABLE IF EXISTS dataset;
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS dataset (
invoiceno INTEGER NOT NULL,
stockcode TEXT NOT NULL,
description TEXT NOT NULL,
quantity INTEGER NOT NULL,
invoicedate DATE NOT NULL,
unitprice REAL NOT NULL,
customerid INTEGER NOT NULL,
country TEXT NOT NULL);
""")
print("Operation done successfully")
# Закрываем соединение и курсор
cursor.close()
conn.close()
Скрипт прост, поэтому останавливаться подробно на нем я не буду. Еще шаг и мы загружаем данные в PostgreSQL. Я уже обсуждал этот момент в своих предыдущих публикациях, здесь скажу лишь суть. Для ускорения добавления большого количества данных в БД лучше отказаться от услуг библиотеки Pandas.
import psycopg2
from datetime import datetime
start_time = datetime.now()
# Подключение к БД
conn = psycopg2.connect("dbname='db' user='postgres' password='gfhjkm' host='localhost' port='5432'")
print("Database opened successfully")
# Создаем курсор
cursor = conn.cursor()
# Открываем файл. Считываем его построчно с записью в БД
with open('dataset_for_report.csv', 'r') as f:
next(f)
cursor.copy_from(f, 'dataset',sep=';', columns=('invoiceno', 'stockcode', 'description', 'quantity',
'invoicedate','unitprice', 'customerid', 'country'))
conn.commit()
f.close()
print("Operation done successfully")
# Закрываем соединение и курсор
cursor.close()
conn.close()
end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))На этом вся предварительная работа подошла к концу и можно непосредственно приступить к построению rfm-отчета. Как вы уже, наверное, успели догадаться главенствующую роль здесь будут играть запросы sql. Для первичной аналитики я решил выбрать именно этот инструмент, так как по большому счету при обработке больших массивов неагрегированных данных у специалистов нет особенного выбора (обсуждение экосистемы Hadoop я оставляю за скобками разговора). Прежде чем писать основной запрос для rfm-анализа необходимо подготовить три вспомогательные функции: сегментирование давности, частоты и суммы покупок.
Деление диапазона данных на сегменты это процесс простой и сложный одновременно. Его простота заключается в том, что вы можете использовать встроенные возможности (пример функции Pandas – cut и qcut) или слепо перенять чужой опыт. Но сложность заключается в том, что получить оптимальные результаты таким путем не получится. Максимальный эффект может быть достигнут при учете сферы бизнеса, сложившейся маркетинговой политики, целей конкретного рекламно-маркетингового мероприятия. Если акция относительно затратная и отдел маркетинга хочет точно идентифицировать бизнес-партнеров, то следует использовать максимально допустимое число сегментов. Если нужно провести массовый контакт с покупателями, то можно размыть границы сократив это число. Какой бы вы не выбрали путь, всегда задавайте сами себе несколько вопросов: удалось ли захватить всех потенциально интересных клиентов в нужные группы, какова будет себестоимость взаимодействия для компании и возможно ли будет при таком разделении максимизировать прибыль. В публикации я привожу лишь шаблонную заготовку кода функций, чтобы при работе вы уже концентрировались лишь на разработке бизнес-логики.
--Функции для rfm-анализа
create function func_recency(days integer) returns integer as $$
select case when days<90 then 1
when (days>=90) and (days<=180) then 2
else 3
end;
$$ language sql;
create function func_frequency(transactions integer) returns integer as $$
select case when transactions>50 then 1
when (transactions>=10) and (transactions<=50) then 2
else 3
end;
$$ language sql;
create function func_monetary(amount integer) returns integer as $$
select case when amount>10000 then 1
when (amount>=1000) and (amount<=10000) then 2
else 3
end;
$$ language sql;Теперь можно перейти к разбору скрипта, который строит rfm-отчет. Сначала мы накладываем ограничение на столбец с датами. Точка отсчета выбрана произвольно и задана в виде константы. Вы же в своем решении можете обойти этот недостаток и, отловив актуальную дату составления отчета, сдвинуться относительно нее на любой временной диапазон назад. Далее следует составление, по сути, сводной таблицы, где в первом поле мы считаем разницу между выбранной временной константой и последней датой покупки для каждого клиента, второе поле отвечает за частоту покупок, последнее – за общую сумму покупок за период. Если применить к указанным полям наши функции, в итоге мы получим искомый rfm-отчет. Сохраняем sql-скрипт в отдельное представление и получаем возможность подключаться к нему из BI системы компании или использовать его в расчетах в ноутбуках JupyterLab.
-- rfm-анализ
select d3.*, concat(d3.rfm_recency,d3.rfm_frequency,d3.rfm_monetary) as rfm
from
(select d2.customerid,
date('2011-11-01')- max(d2.invoicedate) as recency,
cast(count(distinct(d2.invoiceno)) as integer) as frequency,
cast(sum(d2.amount) as integer) as monetary,
func_recency(date('2011-11-01')- max(d2.invoicedate)) as rfm_recency,
func_frequency(cast(count(distinct(d2.invoiceno))as integer)) as rfm_frequency,
func_monetary(cast(sum(d2.amount)as integer)) as rfm_monetary
from
(select d.*, d.quantity * d.unitprice as amount
from public.dataset as d
where d.invoicedate < date('2011-11-01')) as d2
group by d2.customerid
order by d2.customerid) as d3;На первый взгляд мы выполнили поставленную задачу, но это лишь иллюзия. Во-первых, сам по себе rfm-анализ не дает достаточно информации для начала маркетинговых действий, а, во-вторых, массив данных, который был предоставлен для проведения расчетов еще таит массу полезных метрик, которые помогут вашим коллегам принять верное решение.
Чем еще можно дополнить отчет? Для начала хотелось бы получить средний чек для каждого сегмента. Почему данный показатель может быть важен. Дело в том, что любое рекламно-маркетинговое взаимодействие с клиентом небесплатно. Допустим, я вам скажу, что мы хотим потрать 50 рублей, чтобы мотивировать покупателя сделать очередную покупку в нашем магазине. Это много или мало? Ответить на этот вопрос очень сложно, если вы не знаете к какому сегменту относиться этот клиент и с какой вероятностью результативного действия обычно работают такие маркетинговые мероприятия. Но также важно иметь под рукой средний чек, как некую цель, за которую будет бороться рекламный отдел. Если компания ждет, что покупатель, пришедший в магазин, совершит операций на 5000 рублей, то это одно дело. Если в среднем чек не выше 500 рублей, то это совершенно другой расклад. Sql-запрос для данной метрики прикладываю ниже. Данные расчеты уже носят вспомогательный характер, поэтому удобнее всего их проводить в JupyterLab с подключением к БД.
-- Суммарные продажи в сегменте, количество клиентов, средний чек
select r.rfm,
sum(r.monetary) as total_amount,
count(r.rfm) as count_customer,
cast(avg(r.monetary/r.frequency) as integer) as avg_check
from public.report_rfm_analysis as r
group by r.rfm;Что еще можно узнать из предложенного датасета. Конечно, можно рассчитать структуру продаж по странам в абсолютном и относительном выражении. Особенно это важно для интернет-торговли, когда в один сегмент могут попасть люди со всех концов земного шара. Если проанализировать демо-данные по мы сразу получим, что в ряде групп 70% покупок в денежном выражении совершают жители одной страны. Неплохая подсказка сотрудникам отдела маркетинга.
-- Структура продаж по странам в абсолютном и относительном выражении
select d2.rfm,
d2.country,
cast(sum(d2.amount) as integer) as amount_country,
round(cast(sum(d2.amount)/sum(sum(d2.amount))over(partition by d2.rfm)*100 as numeric),2) as percent_total_amount
from
(select d.*, d.quantity * d.unitprice as amount, r.rfm
from public.dataset as d left join
public.report_rfm_analysis as r on d.customerid = r.customerid
where d.invoicedate < date('2011-11-01')) as d2
group by d2.rfm, d2.country
order by d2.rfm, sum(d2.amount)desc;Будущее всей торговли это строгая персонализация предложений и рекламы. У идеальной торговой площадки есть данные по каждому клиенту с момента его первой покупки: структура потребительской корзины за прошедший год, топ-7 товаров, топ-3 дня для совершения закупок, средний чек и т. д. Но для хранения всей этой информации требуется сначала много места в хранилище данных, а затем вычислительные мощности, чтобы состыковать людей и проводимые акции в режиме реального времени. В этом случае не будет ситуаций, что за всю историю бизнес-коммуникаций клиент покупал в вашем магазине только сыр, а вы постоянно шлете ему смс-сообщения о скидках на шоколад, бананы, охлажденную курицу. В наше время клиенты уже вряд ли оценят такую заботу о своем кошелке и скорее отнесут сообщения к категории спама. Если коммуникация с клиентом – то обязательно максимально целевая. Для демонстрации данного подхода я реализовал расчет топ-3 дня по объему продаж в разрезе сегмент-страна.
-- Наименование дня месяца
create function func_day_of_week(number_day integer) returns text as $$
select (string_to_array('sunday,monday,tuesday,wednesday,thursday,friday,saturday',','))[number_day];
$$ language sql;
-- Топ-3 дня по объему продаж в разрезе сегмент-страна
select d4.rfm, d4.country, max(d4.top) as top_3_days
from
(select d3.rfm, d3.country, string_agg(d3.day_of_week,', ')over(partition by d3.rfm, d3.country) as top
from
(select d2.rfm, d2.country, d2.day_of_week,sum(d2.amount) as total_amount,
row_number ()over(partition by d2.rfm, d2.country order by d2.rfm, d2.country, sum(d2.amount)desc)
from
(select r.rfm,
d.country,
func_day_of_week(cast(to_char(d.invoicedate, 'D') as integer)) as day_of_week,
d.quantity * d.unitprice as amount
from public.dataset as d left join public.report_rfm_analysis as r on d.customerid = r.customerid
where d.invoicedate < date('2011-11-01')) as d2
group by d2.rfm, d2.country, d2.day_of_week
order by d2.rfm, d2.country, sum(d2.amount) desc) as d3
where d3.row_number <= 3) as d4
group by d4.rfm, d4.countryКраткие выводы. RFM-анализ и вспомогательные расчеты для него удобнее всего выполнять путем комбинирования sql и ноутбуков Python. При сегментации клиентов важно учитывать сферу бизнеса, маркетинговую политику и цели рекламной акции. RFM-отчет не дает всей полноты картины, поэтому его лучше всего сопровождать вспомогательными расчетами.
На этом все. Всем здоровья, удачи и профессиональных успехов!
