Grab free access to all the OWOX BI features for 14 days

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.


Dynamic parameters

  • 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

SQL Query

#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
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

2 Comments

  • 1
    Avatar
    Emilan Kornacki

    Hi there are some of the last time am needs more information about the last time we have to make a decision about *

  • 0
    Avatar
    Alexander Baiak

    Hi,

    Could you please specify your question in more details?

Please sign in to leave a comment.