Запрос возвращает для каждого пользователя номер сегмента по каждой из этих групп признаков:
- 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 Комментарии