X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Customer RFM Analysis

Guest Author

Customer RFM Analysis

Author: Grace Wang

Customer RFM

RFM (Recency, Frequency, Monetary) is a marketing technique used to determine quantitatively which customers are the best ones by examining:

  • Recency – How recently did the customer purchase?
  • Frequency – How often do they purchase?
  • Monetary Value – How much do they spend?

The method is based on the marketing axiom that “80% of your business comes from 20% of your customers.” RFM methodology provides an easy and economic way to help business identify those valuable customers and is widely used in various industries. Case study performed at University of North Carolina Kenan-Flagler Business School has proven:

  • Customers who purchased recently were more likely to buy again versus customers who had not purchased in a while
  • Customers who purchased frequently were more likely to buy again versus customers who had made just one or two purchases
  • Customers who had spent the most money in total were more likely to buy again.  The most valuable customers tended to continue to become even more valuable

RFM Methodology

There are variations of doing RFM. The most common method is finding a table with Customer Name, Date of Purchase and Purchase Value.

  • Using RFM analysis, customers are ranked and assigned a ranking number of 1,2,3,4, or 5 (with 5 being highest) based on quintile distribution for each RFM parameter.
  • The three ranking number makes up the score in each category (Recency Score / Frequency Score / Monetary Score), three scores together are referred to as an RFM "cell". e.g. 555,231..
  • The Customer data is sorted to determine which customers were "the best customers" in the past, with a cell ranking of "555" being ideal.

Customer RFM in OBIA

In OBIA, Customer Recency / Frequency / Monetary category assignment and score calculation are done at PLP (Post Load Process) stage during ETL. When CRM Revenue Fact table (W_REVN_F) completes data loading, a PLP task will access the “won” opportunities in CRM Revenue Fact table and evaluate RFM based on the Opportunity Closed Date for Recency, Number of Opportunities for Frequency and Opportunity Closed Revenue for Monetary.

Each Customer then is ranked among three categories and assigned a Recency Category Code and Recency Score, a Frequency Category Code and Frequency Score, a Monetary Category Code and Monetary Score. RFM cell score is also calculated for each customer.

The outcome of the RFM category codes and scores are captured in one task (PLP_PartyDimension_RFMInfo) and stored in Customer Dimension table  (W_PARTY_D) through one update task (PLP_PartyDimension_UpdateRFMInfo).

Below is a quick glance of RFM ETL process:

 

RECENCY

FREQUENCY

MONETARY

 

How long ago the customer last made a purchase? How many Purchases the customer has made? How much Revenue the customer has generated?

Source

MAX

(W_REVN_F.CLOSE_DT_WID)

COUNT

(DISTINCT W_REVN_F.OPTY_WID)

SUM

(W_REVN_F.DOC_CLOSED_REVN)

Target

W_PARTY_D.

W_RECENCY_CAT_CODE

RECENCY_SCORE

W_PARTY_D.

W_FREQUENCY_CAT_CODE

FREQUENCY_SCORE

W_PARTY_D.

W_MONETARY_CAT_CODE

MONETARY _SCORE

 

Rank Customer with the WON opportunity closed date. The later the Closed Date the higher rank. Rank Customer with the number of WON opportunities. The more Opportunities the higher rank.

Rank Customer with the highest WON Revenue. The higher the Revenue the higher rank.

 

Domain

Code

 

W_DBM_QUINTILE_TYPE

W_RECENCY_CAT_CODE

W_DBM_QUINTILE_TYPE
W_FREQUENCY_CAT_CODE

W_DBM_QUINTILE_TYPE
W_MONETARY_CAT_CODE

Customer RFM in RPD

Total of ten logical columns related to RFM are defined in Customer Dimension in OBIA RPD.

Logical

Column

Description

Recency

Category Code

Contains

the value of : 1st Quintile, 2nd Quintile,3rd Quintile, 4th Quintile, 5th Quintile

 

Recency

Category

 

The

descriptive value of Recency Category Code: Most Recent, Very Recent, Recent, Somewhat Recent, Less Recent

Recency

Score

Contains

the score from 1 to 5 for Recency

Frequency

Category Code

Contains

the value of : 1st Quintile, 2nd  Quintile,3rd Quintile, 4th Quintile, 5th Quintile

Frequency

Category

 

The

descriptive value of Frequency Category Code: Most Frequent, Very Frequent, Frequent, Somewhat Frequent, Less Frequent

Frequency

Score

Contains

the score from 1 to 5 for Frequency

Monetary

Category Code

Contains

the value of : 1st Quintile, 2ndQuintile,3rd Quintile, 4th Quintile, 5th Quintile

Monetary

Category

The

descriptive value of Recency Category Code: Most Valued, Very Valued, Valued, Somewhat Valued, Less Valued

Monetary

Score

Contains

the score from 1 to 5 for Monetary

RFM

Score

Contains

the value of RFM cell ranging from 111 to 555.

 

Sample Customer RFM Webcat

 

 

More Applications

RFM is an easy and economic way to mine current data to provide quick snapshot of customer. As a Customer dimension attribute, RFM columns can be used
as a filter in various Customer related reports to produce more insightful analysis. In the mean time, the RFM methodology could be expanded into different subject areas. For example,

  • Instead of Revenue Fact, the RFM can be performed on Web Visit and Web Submit fact from online marketing. We could identify what cluster of Customers visited the website most recently and frequently with high order amount.
  • Using Purchase Order Fact from Spending, RFM can be applied to Supplier. We could identify who are the Suppliers most recently and frequently used by
    the company and with most spending expenditure.
  • With RFM scores captured on the periodical basis, it can also help detect the change of Customer Purchasing Behavior. For example, if we keep the monthly RFM scores for each customer on a separate table. By doing time series analysis, a declining RFM score trend may indicate the customer is losing interest while an ascending RFM score showing the customer is picking up momentum.

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.