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

  • 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
Была ли эта статья полезной?
Пользователи, считающие этот материал полезным: 1 из 1
Еще есть вопросы? Отправить запрос

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

Войдите в службу, чтобы оставить комментарий.