RFM Analysis
This topic demonstrates how to set up a dashboard that allows you to segment your customers by their recency, frequency, and monetary rankings. RFM analysis is a marketing technique that takes customer behaviors into account to help you determine segmentation for outreach. It accounts for three aspects:
 Recency in how recently a customer purchased from your store
 Frequency in how often they purchase from you
 Monetary in how much the customer spends
The RFM analysis can only be configured if you have the Adobe Commerce Intelligence Pro plan on the new architecture (for example, if you have the Data Warehouse Views
option under the Manage Data
menu). These columns can be created from the Manage Data > Data Warehouse page. Detailed instructions are provided below.
Getting Started
You need to first upload a file containing just a primary key with the value of one. This allows the creation of some necessary calculated columns for the analysis.
You can use this article and the image below to format your file.
Calculated Columns
A further distinction is made if your business allows guest orders. If so, you can ignore all steps for the customer_entity
table. If guest orders are not allowed, ignore all steps for the sales_flat_order
table.
Columns to create

Sales_flat_order/customer_entity
table 
Customer's last order date

Column type:
Many to one > Max

Pat:
sales_flat_order.customer_id > customer_entity.entity_id

Selected column:
created_at

Filter:
Orders we count

code languagenone Seconds since customer's last order date

Column type: – "Same table > Age

Selected column:
Customer's last order date

(input) Count reference

Column type:
Same table > Calculation

Inputs:
entity_id

Calculation:
**case when A is null then null else 1 end**

Datatype:
Integer

Count reference table (this is the file you uploaded with the number “1”)

Number of customers

Column type:
Many to One > Count Distinct

Path:
ales_flat_order.(input) reference > Count reference.Primary Key
ORcustomer_entity.(input)reference > Count Reference
.Primary Key

Selected column:
sales_flat_order.customer_email
ORcustomer_entity.entity_id

Customer_entity table

Number of customers

Column type:
One to Many > JOINED_COLUMN

Path:
customer_entity
.(input) reference > Customer Concentration.Primary Key

Selected column:
Number of customers

(input)
Ranking by customer lifetime revenue

Column type:
Same table > Event Number

Event owner:
(input) reference for count

Event rank:
Customer's lifetime revenue

Ranking by customer lifetime revenue

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer lifetime revenue
,Number of customers

Calculation:
case when A is null then null else (B(A1)) end

Datatype:
Integer

Customer’s monetary score (by percentiles)

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer lifetime revenue
,Number of customers

Calculation:
Case when round((BA+1)*100/B,0) <= 20 then 5 when round((BA+1)*100/B,0) <= 40 then 4 when round((BA+1)*100/B,0) <= 60 then 3 when round((BA+1)*100/B,0) <= 80 then 2 when round((BA+1)*100/B,0) <= 100 then 1 else 0 end

Datatype:
Integer

(input) Ranking by customer lifetime number of orders

Column type:
Same table > Event Number

Event owner:
(input) reference for count

Event rank:
Customer's lifetime number of orders

Ranking by customer lifetime number of orders

Column type: – “Same table > Calculation”

Inputs: – (input) Ranking by customer lifetime number of orders, Number of customers

Calculation: – case when A is null then null else (B(A1)) end

Datatype: – Integer

Customer’s frequency score (by percentiles)

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer lifetime number of orders
,Number of customers

Calculation:
Case when round((BA+1)*100/B,0) <= 20 then 5 when round((BA+1)*100/B,0) <= 40 then 4 when round((BA+1)*100/B,0) <= 60 then 3 when round((BA+1)*100/B,0) <= 80 then 2 when round((BA+1)*100/B,0) <= 100 then 1 else 0 end

Datatype:
Integer

Ranking by seconds since customer’s last order date

Column type:
Same table > Event Number

Event owner:
(input) reference for count

Event rank:
Seconds since customer's last order date

Customer’s recency score (by percentiles)

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer lifetime number of orders
,Number of customers

Calculation:
Case when (A * 100/B,0) <= 20 then 5 when (A * 100/B,0) <= 40 then 4 when (A * 100/B,0) <= 60 then 3 when (A * 100/B,0) <= 80 then 2 when (A * 100/B,0) <= 100 then 1 else 0 end

Datatype:
Integer

Customer’s recency score (by percentiles)

Column type:
Same table > Calculation

Inputs:
Customer's recency score (by percentiles)
,Customer's frequency score (by percentiles)
,Customer's monetary score (by percentiles)

Calculation:
case when (A IS NULL or B IS NULL or C IS NULL) then null else concat(A,B,C) end

Datatype: String

Count reference table

Number of customers:
(RFM > 0)

Column type:
Many to One > Count Distinct

Path:
sales_flat_order.(input) reference > Customer Concentration. Primary Key
ORcustomer_entity.(input)reference > Customer Concentration.Primary Key

Selected column:
sales_flat_order.customer_email
ORcustomer_entity.entity_id

Filter:
Customer's RFM score (by percentile)
Not Equal To 000 
Customer_entity table

Number of customers:
(RFM > 0)

Column type:
One to Many > JOINED_COLUMN

Path:
customer_entity.(input) reference > Customer Concentration.Primary Key

Selected column: –
Number of customers

Customer’s recency score
(R+F+M)

Column type:
Same table > Calculation

Inputs: –
Customer's recency score (by percentiles)
,Customer's frequency score (by percentiles)
,Customer's monetary score (by percentiles)

Calculation:
case when (A IS NULL or B IS NULL or C IS NULL) then null else A+B+C end

Datatype:
Integer

(input) Ranking by customer’s overall RFM score

Column type:
Same table > Event Number

Event owner:
(input) reference for count

Event rank:
Customer's recency score (R+F+M)

Filter:
Customer's RFM score (by percentile)
Not Equal To 000 
Ranking by customer’s overall RFM score

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer's overall RFM score
,Number of customers (RFM > 0)

Calculation:
case when A is null then null else (B(A1)) end

Datatype:
Integer

Customer’s RFM group

Column type:
Same table > Calculation

Inputs:
(input) Ranking by customer lifetime revenue
,Number of customers

Calculation:
Case when round(A * 100/B,0) <= 20 then '5. copper' when round(A * 100/B,0) <= 40 then '4. bronze' when round(A * 100/B,0) <= 60 then '3. silver' when round(A * 100/B,0)<= 80 then '2. gold' else '1. Platinum' end

Datatype:
Integer
Metrics
No new metrics!
Reports

Customers by RFM grouping

Metric
A
:New customers

Metric:
New customers

Filter:
Customer's RFM score (by percentiles) Not Equal to 000

Time period:
All time

Interval:
None

Hide chart

Group by:
Customer's RFM group

Group by:
Email

Chart type:
Table

Customers with five recency score

Metric
A
:New customers

Metric:
New customers

Filter:
Customer's recency score (by percentiles) Equal to 5

Time period:
All time

Interval:
None

Chart Type:
Scalar

Hide chart

Group by:
Email

Group by:
Customer's RFM score (R+F+M)

Chart type:
Table

Customers with one recency score

Metric
A
:New customers

Metric:
New customers

Filter:
Customer's recency score (by percentiles) Equal to 1

Time period:
All time

Interval:
None

Chart Type:
Scalar

Hide chart

Group by:
Email

Group by:
Customer's RFM score (R+F+M)

Chart type:
Table
After compiling all the reports, you can organize them on the dashboard as you desire. The result may look like the above sample dashboard, but the three generated tables are just examples of the types of customer segmentation you can perform.