Calculation of RFM segments

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