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

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

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

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

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

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

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

numberOfRGroups - количество сегментов, на которые будут делиться пользователи по признаку R
numberOfFGroups - количество сегментов, на которые будут делиться пользователи по признаку F
numberOfMGroups - количество сегментов, на которые будут делиться пользователи по признаку M
userId - название поля таблицы, в котором хранится параметр userId
projectName - название проекта в Google BigQuery с данными о заказах
dataSetName - название dataSet с данными о заказах
tableName - название таблицы с данными о заказах
startDate - дата начала анализируемого периода
endDate - дата окончания анализируемого периода

SQL-запрос

SELECT Rtab.userId AS userId, R, F, M
FROM

  (SELECT Rtab.userId, LastDate, R, TotalPurchases, F
    FROM
    --расчёт R сегмента - по давности последней покупки
    (SELECT userId, LastDate, NTILE({numberOfRGroups default="5" type="select" values="1,3,5,7,10,12,15,18,20"}) OVER (ORDER BY LastDate DESC) R
    FROM (
      SELECT
      {userId default="user.id" type="select" values="user.id,userId"} AS userId, MAX(created) AS LastDate
      FROM [{projectName default="owox-demo"}:{dataSetName default="Collections"}.{tableName default="transactions"}]
      WHERE status=1 AND created>=TIMESTAMP('{startDate default="2015-01-01" type="datetime"}')
      AND created<DATE_ADD(TIMESTAMP('{endDate default="2016-03-31" type="datetime"}'),1,"DAY")
      GROUP BY userId
      ORDER BY LastDate DESC
    )) AS Rtab

JOIN EACH
   --расчёт F сегмента - по частоте покупок
  (SELECT userId, TotalPurchases, NTILE({numberOfFGroups default="5" type="select" values="1,3,5,7,10,12,15,18,20"}) OVER (ORDER BY TotalPurchases DESC) F
  FROM (
    SELECT {userId default="user.id" type="select" values="user.id,userId"} AS userId, COUNT(1) AS TotalPurchases
    FROM [{projectName default="owox-demo"}:{dataSetName default="Collections"}.{tableName default="transactions"}]
    WHERE status=1 AND created>=TIMESTAMP('{startDate default="2015-01-01" type="datetime"}')
    AND created<DATE_ADD(TIMESTAMP('{endDate default="2016-03-31" type="datetime"}'),1,"DAY")
    GROUP BY userId
    ORDER BY TotalPurchases DESC
  )) AS Ftab

ON (Rtab.userId = Ftab.userId)) RFtab

JOIN EACH
--расчёт M сегмента - по сумме заказов в денежном эквиваленте
(SELECT userId, TotalRevenue, NTILE({numberOfMGroups default="5" type="select" values="1,3,5,7,10,12,15,18,20"}) OVER (ORDER BY TotalRevenue DESC) M
FROM (
  SELECT {userId default="user.id" type="select" values="user.id,userId"} AS userId, AVG(revenue) AS TotalRevenue
  FROM [{projectName default="owox-demo"}:{dataSetName default="Collections"}.{tableName default="transactions"}]
  WHERE status=1 AND created>=TIMESTAMP('{startDate default="2015-01-01" type="datetime"}')
    AND created<DATE_ADD(TIMESTAMP('{endDate default="2016-03-31" type="datetime"}'),1,"DAY")
  GROUP EACH BY userId
  ORDER BY TotalRevenue DESC
)) AS Mtab

ON (Rtab.userId = Mtab.userId)
ORDER BY UserID

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