The query returns for each user segment number for each of the groups of signs:

  • Recency - recency of the last purchase. The less time has passed since the last purchase, the greater the likelihood that he will repeat the action;
  • Frequency - the number of purchases. The more the client purchases made, the greater the likelihood that he will make the purchase again;
  • Monetary - the amount of purchases in cash. The more money has been spent by the client, the greater the likelihood that he will make the order again.

The source of data for the calculations is the table or View in Google BigQuery data of each order with the following set of fields:

  • userId - User Id in the site / ERP database;
  • revenue - revenue per order;
  • created - the date of creation of the order.

To calculate RFM-segments, we recommend that you use an order confirmation from the ERP data. However, as the source for the View can be used data from OWOX BI Streaming or OWOX BI UserFeed, but in this case, the calculation results will be less accurate.


Dynamic parameters

  • numberOfRGroups - the number of segments, which will share users on the basis of R
  • numberOfFGroups - the number of segments, which will share users on the basis of F
  • numberOfMGroups - the number of segments, which will be divided on the basis of users M
  • userId - the name of the field in the table, in which the parameter userId stores
  • projectName - project name into Google BigQuery data about orders
  • dataSetName - name dataSet data about orders
  • tableName - the name of a table with data about orders
  • startDate - the date of the beginning of the analyzed period
  • endDate - the date of the end of the analyzed period

SQL Query

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

  (SELECT Rtab.userId, LastDate, R, TotalPurchases, F
    FROM
    --R segment
    (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 segment
  (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 segment
(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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.