Повторно используемый SQL для анализа RFM и CLV

Введение

За последние 10 лет своей карьеры я работал над моделями потребительской ценности (CLV) с различными клиентами, отраслями и наборами данных. Существует много различных способов решения проблемы, но эта статья не об этом. Вместо этого я хочу объяснить настройку данных, которой в блогосфере часто пренебрегают.

В моем последнем сообщении в блоге Пойдем дальше с SQL я говорил о том, как я взволнован потенциалом объединения jinja и SQL, чтобы использовать семантические абстракции, лежащие над самим кодом SQL. Этот пост — первая из многих идей, которые крутились в моей голове в последнее время. Моя цель состоит в том, чтобы (1) показать вам, что подготовка данных для модели CLV может быть выполнена в SQL, но, что более важно, (2) дать вам шаблон, который вы можете применить в другом месте, чтобы вам не нужно было на самом деле писать SQL с нуля.

Начиная

Наиболее часто используемой вероятностной моделью для прогнозирования CLV является модель бета-геометрического/отрицательного биномиального распределения (часто сокращенно BG/NBD). У него есть популярный родственник — модель Парето/NBD. Эти модели пытаются предсказать будущие транзакции данного клиента. Модель расширения Гамма-Гамма фокусируется на денежном аспекте суммы этих транзакций.

В этом году сотни, если не тысячи специалистов по обработке и анализу данных по всему миру собираются построить такую ​​модель.

Как они начнут? С чего бы вы начали? Если быть честным, первый шаг, вероятно, состоит в том, чтобы погуглить, прочитать несколько блогов, а затем следовать своим собственным данным. Если вы можете найти достаточно хороший пример в Интернете, вы можете методично манипулировать своими данными способом, похожим на сообщение в блоге, до тех пор, пока вы не подберете его достаточно близко, чтобы начать работу.

Появляется знакомая картина

Если вы прочитаете достаточное количество этих блогов, вы начнете понимать, что существует знакомая закономерность. Все они начинаются с преобразования данных в определенную форму, которую часто называют таблицей RFM.

Существуют и другие методы расчета CLV, и я не буду их описывать. Я в основном занимаюсь таблицей RFM, потому что это не только очень популярный подход, но и очень воспроизводимый. Если вы не знакомы с таблицей RFM, ознакомьтесь с публикацией в блоге моего друга Olubukunola Akinsola.

Основы:

R – дата самой последней покупки в компании.
F – как часто клиент совершает покупку в компании.
M — денежная стоимость покупки клиента.

Выбор SQL

Если вы читали какие-либо из моих прошлых сообщений в блоге, то вы уже знаете, что я увлечен использованием SQL, чтобы воспользоваться преимуществами вычислительной мощности хранилища данных. Чтобы создать таблицу RFM, нам нужно агрегировать данные с уровня необработанных транзакций, который, вероятно, является одной из самых больших таблиц в нашей базе данных. Хотя это правда, что существует множество доступных решений на Python, зачем тратить усилия на разгрузку миллионов или миллиардов транзакций, которые могут даже не поместиться в моей памяти Python, когда у меня есть прекрасное хранилище данных?

Сделать это повторяемым

В следующих разделах я собираюсь рассмотреть пример создания таблицы RFM из набора данных о продажах, который я нашел в Интернете. Однако это означает, что любой, кто наткнется на этот пост в будущем, все равно будет тщательно переписывать SQL, чтобы он соответствовал своей собственной базе данных.

Здесь в игру вступает джиндзя. Jinja — это 14-летний движок шаблонов, который в основном используется для динамической генерации HTML, XML и других форматов разметки. Недавно было показано, что он является мощным партнером SQL, и его используют как dbt, так и Rasgo. Если вы хотите ознакомиться с основами, Жюльен Кервизик написал пророческий пост еще в 2019 году под названием Джиндзя — SQL-путь ниндзя.

Итак, вместо того, чтобы просто поделиться кодом и логикой SQL, я собираюсь перейти на следующий уровень — я собираюсь поделиться шаблоном, который вы можете использовать для динамического создания SQL для вашего собственного данные.

Прогулка по примеру

Давайте возьмем несколько примеров транзакций продаж из Репозитория машинного обучения UCI.

Я подошел к этой проблеме, сначала создав SQL. Эта часть, очевидно, сложная, но я сделал это, чтобы вам не пришлось. Вот рабочий SQL.

Результатом этого запроса является правильно отформатированная таблица RFM, точно так же, как нам нужно было бы начать наше моделирование CLV.

Быть джиндзя-ниндзя

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

  • CustomerID — столбец, однозначно идентифицирующий клиента.
  • TransactionID — столбец, однозначно идентифицирующий транзакцию или покупку.
  • TransactionDate — столбец, определяющий дату/время транзакции.
  • TransactionAmount — столбец, определяющий сумму денежного выражения для транзакции.

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

  • ObservationEnd — дата, в которую мы считаем конец наших данных (конец удержания)
  • TrainingPeriodEnd — дата, на которую мы отделяем конец обучения от начала задержки

Из нашего примера данных мы знаем следующее:

Следующим шагом является создание шаблона SQL, совместимого с jinja, чтобы я мог повторно использовать его для разных проектов и клиентов.

Поскольку код длинный, я объясню, как это делается, на коротком примере. Если наш исходный запрос:

SELECT CustomerID
FROM tblCustomers

И мы хотим создать такие параметры, как

  • имя_столбца
  • имя_таблицы

Тогда наш шаблон jinja будет таким:

SELECT {{ column_name }}
FROM {{ table_name }}

Таким образом, я параметризовал весь запрос CLV-RFM, чтобы в будущем мы могли запускать его для любой таблицы транзакций.

Для рендеринга jinja существует множество онлайн-рендереров, или вы можете использовать пакет python jinja2. Лично я использую Rasgo для рендеринга шаблонов.

Вот полный код, готовый к работе! Просто введите собственное имя таблицы, имена столбцов, и это будет динамически отображать SQL, настроенный для ваших данных.

Заключение

Я действительно в восторге от возможностей таких шаблонов SQL. На самом деле у меня уже есть несколько других идей. Например, проблемы временных рядов часто требуют аналогичных шагов подготовки данных, таких как:

  • удалить отрицательные суммы продаж
  • добавить строки для дат, в которые не было продаж
  • вперед заполнить последние известные значения для этих отсутствующих строк
  • добавить лаги и оконные агрегаты

Я думаю, что это звучит как хороший кандидат для другого шаблона.

Вы можете думать о любом? Хотите внести свой вклад? Пожалуйста, дайте мне знать!