Расчет RFM сегментов – Справочный центр OWOX

Запрос возвращает для каждого пользователя номер сегмента по каждой из этих групп признаков:

Recency (давность) — давность последней покупки. Чем меньше времени прошло с момента последней покупки, тем выше вероятность, что покупатель повторит действие;
Frequency (частота) — количество покупок. Чем больше покупок совершил клиент, тем выше вероятность, что он совершит покупку снова;
Monetary (деньги) — сумма покупок в денежном эквиваленте. Чем больше денег было потрачено клиентом, тем выше вероятность, что он снова совершит заказ.

В качестве источника данных для расчетов служит таблица или View в Google BigQuery с данными о каждом заказе со следующим набором полей:

userId — ID пользователя в БД сайта / ERP;
revenue — доход с заказа
created — дата создания заказа

Для расчета RFM-сегментов мы рекомендуем использовать данные о подтвержденных заказах из ERP. В качестве источника для View можно использовать и данные, собранные OWOX BI Pipeline.

Динамические параметры

NTILE( ) — количество сегментов, на которые будут делиться пользователи по каждому признаку (R, F и M), например NTILE(5)
userId — название поля таблицы, в котором хранится параметр userId
projectName — название проекта в Google BigQuery с данными о заказах
dataSetName — название dataSet с данными о заказах
tableName — название таблицы с данными о заказах, например streaming_

SQL-запрос

#standardsql
WITH
  transactions AS (
  SELECT
    userId,
    transaction.transactionId AS transactionId,
    ARRAY_AGG(transaction.transactionRevenue
    ORDER BY
      timestamp DESC) [
  OFFSET
    (0)] AS revenue,
    ARRAY_AGG(DATE(TIMESTAMP_MICROS(timestamp))
    ORDER BY
      timestamp DESC)[
  OFFSET
    (0)] AS created
  FROM
  --укажите источник данных
    `projectName.dataSetName.tableName_*`
  WHERE
  --Выберите период времени
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%E4Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -12 MONTH))
    AND FORMAT_DATE("%E4Y%m%d", CURRENT_DATE())
    AND transaction.transactionId IS NOT NULL
    AND transaction.transactionRevenue>0
  GROUP BY
    userId,
    transaction.transactionId)
SELECT
  userId AS userId,
  R AS dimension7,
  F AS dimension8,
  M AS dimension9
FROM (
  SELECT
    Rtab.userId AS userId,
    LastDate,
    R,
    TotalPurchases,
    F,
    TotalRevenue,
    M
  FROM
    --расчёт R сегмента - по давности последней покупки (NTILE(5) - количество сегментов)
    (
    SELECT
      userId,
      LastDate,
      NTILE(5) OVER (ORDER BY LastDate DESC) R
    FROM (
      SELECT
        userId,
        MAX(created) AS LastDate
      FROM
        `transactions`
      GROUP BY
        userId )) AS Rtab
  JOIN
    --расчёт F сегмента - по частоте покупок (NTILE(5) - количество сегментов)
    (
    SELECT
      userId,
      TotalPurchases,
      NTILE(5) OVER (ORDER BY TotalPurchases DESC) F
    FROM (
      SELECT
        userId AS userId,
        COUNT(DISTINCT transactionId) AS TotalPurchases
      FROM
        `transactions`
      GROUP BY
        userId )) AS Ftab
  ON
    Rtab.userId = Ftab.userId
  JOIN
    --расчёт M сегмента - по сумме заказов в денежном эквиваленте (NTILE(5) - количество сегментов)
    (
    SELECT
      userId,
      TotalRevenue,
      NTILE(5) OVER (ORDER BY TotalRevenue DESC) M
    FROM (
      SELECT
        userId,
        AVG(revenue) AS TotalRevenue
      FROM
        `transactions`
      GROUP BY
        userId )) AS Mtab
  ON
    Rtab.userId = Mtab.userId)
ORDER BY
  userId

0 Комментарии