The query returns the segment number for each user by each of these signs:
- Recency: the recency of the last purchase. The less time has passed since the last purchase, the greater the likelihood that the customer will repeat the action.
- Frequency: the number of purchases. The more the client purchases made, the greater the likelihood that they will make the purchase again.
- Monetary: the number of purchases in cash. The more money the customer has spent, the greater the likelihood that they will make the order again.
As the data source for the calculations you can use either a the table or a View in Google BigQuery containing the following fields:
- userId: a user ID in the site / ERP database
- revenue: revenue per order
- created: the order creation date
To calculate the RFM segments, we recommend using order confirmation data from an ERP. You can use the data uploaded via OWOX BI Pipeline as well.
- NTILE( ): the number of segments to divide users by each sign (R,F, and M)
- userId: the name of the field in the table, in which the parameter userId stores
- projectName: the BigQuery project containing data about orders
- dataSetName: the name of a dataset containing data about orders
- tableName: the name of the table with the data about orders
#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
--Specify the data source `projectName.dataSetName.tableName_*` WHERE
--Select the time period _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 --The R segment calculation: by the recency of the latest purchase, where NTILE(5) is a number of segments ( 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 --The F segment calculation: by the frequency of purchases, where NTILE(5) is a number of segments ( 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 --The M segment calculation: by total orders in money equivalent, where NTILE(5) is a number of segments ( 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