Understanding RFM Analysis and Building an RFM Model in Marketing Cloud

RFM analysis is a popular technique used by marketers to segment customers based on their purchase behavior. RFM stands for Recency, Frequency, and Monetary Value, and it is a data-driven approach that helps marketers to identify groups of customers who are likely to be most responsive to marketing campaigns. In this article, we will explain the principles of RFM analysis and show you how to build an RFM model in Marketing Cloud.

What is RFM Analysis?

Marketers use RFM analysis to divide customers into groups based on how they buy things. It uses three key metrics to measure how recently a customer has made a purchase (recency), how frequently they make purchases (frequency), and how much money they have spent (monetary value). By looking at these metrics, marketers can figure out which groups of customers are most likely to respond to their campaigns and which ones are most likely to leave.

Here is a brief overview of each of the RFM metrics:

Recency: This metric measures how recently a customer has made a purchase. Customers who have made a purchase more recently are more likely to be responsive to marketing campaigns.
Frequency: This metric measures how frequently a customer makes purchases. Customers who make purchases more frequently are also more likely to be responsive to marketing campaigns.
Monetary Value: This metric measures how much money a customer has spent. Customers who have spent more money are more valuable to a business and may warrant a different marketing approach.

By segmenting customers based on these three metrics, marketers can create targeted marketing campaigns that are tailored to the specific needs and behaviors of each group.

Building an RFM Model in Marketing Cloud

Now that you understand what RFM analysis is, let’s walk through the process of building an RFM model in Marketing Cloud. In this example, we will assume that we have a database of customer orders with the following columns:

ContactId: A unique identifier for each customer
OrderId: A unique identifier for each order
Total: The total amount spent on each order
Date: The date that the order was placed

Our goal is to use this data to calculate the Recency, Frequency, and Monetary Value metrics for each customer, and then use these metrics to segment customers into groups. We will then use the resulting segments to create targeted marketing campaigns. In our example the data extension is called Transactions_data.

Create a Data Extension for RFM Scores

First, we’ll create a new data extension to store the RFM scores. This data extension should include columns for the ContactId, recency_score, frequency_score and monetary_score.

Create a SQL Query Activity

Next, we’ll create a SQL Query activity to perform the calculations for the RFM scores. The SQL Query activity should include the following SQL code:

SELECT
  ContactId,
  CASE
    WHEN recency >= 365 THEN 1
    WHEN recency >= 180 THEN 2
    WHEN recency >= 90 THEN 3
    WHEN recency >= 30 THEN 4
    ELSE 5
  END AS recency_score,
  CASE
    WHEN frequency = 1 THEN 1
    WHEN frequency <= 3 THEN 2
    WHEN frequency <= 5 THEN 3
    WHEN frequency <= 7 THEN 4
    ELSE 5
  END AS frequency_score,
  CASE
    WHEN monetary <= 100 THEN 1
    WHEN monetary <= 200 THEN 2
    WHEN monetary <= 400 THEN 3
    WHEN monetary <= 600 THEN 4
    ELSE 5
  END AS monetary_score
FROM (
  SELECT
    ContactId,
    OrderId,
    DATEDIFF(day, Date, GETDATE()) AS recency,
    COUNT(DISTINCT OrderId) AS frequency,
    SUM(Total) AS monetary
  FROM
    Transactions_data
  GROUP BY
    ContactId,
    OrderId,
    DATEDIFF(day, Date, GETDATE())
) AS subquery
GROUP BY
  ContactId,
  CASE
    WHEN recency >= 365 THEN 1
    WHEN recency >= 180 THEN 2
    WHEN recency >= 90 THEN 3
    WHEN recency >= 30 THEN 4
    ELSE 5
  END,
  CASE
    WHEN frequency = 1 THEN 1
    WHEN frequency <= 3 THEN 2
    WHEN frequency <= 5 THEN 3
    WHEN frequency <= 7 THEN 4
    ELSE 5
  END,
  CASE
    WHEN monetary <= 100 THEN 1
    WHEN monetary <= 200 THEN 2
    WHEN monetary <= 400 THEN 3
    WHEN monetary <= 600 THEN 4
    ELSE 5
  END

This SQL code calculates the RFM scores for each subscriber based on their order history. The recency_score is calculated based on the number of days since the customer’s most recent purchase, while the frequency_score is based on the number of unique orders the customer has placed. The monetary_score is based on the total value of the customer’s orders.

Link RFM data extension to the Data Designer in Contact Builder

Navigate to Data Designer in Marketing Cloud and create a new Attribute Group and call it RFM. When in the newly created Attribute Group select the data extension you created above and map ContactKey with ContactId.

Using RFM in Activation Studio

Once the RFM data extension is linked to the Data Designer, you can use it in segmentation, personalization, and automation activities, just like any other data source. For example, you could create a segment that targets customers with a high MonetaryScore, indicating that they are high-value customers that should be targeted with special offers or promotions.

Example of a Champion segment with recency, frequency and monetary score higher or equal to 4

By linking the RFM data extension to the Data Designer, you create a unified view of customer data that can be used to drive more effective marketing campaigns and personalized experiences via the Activation Studio.