How to Perform RFM Analysis Step by Step

You can use rfm analysis to understand your customers better. RFM stands for Recency, Frequency, and Monetary value. These three factors help you see who buys often, who spends the most, and who has not bought in a while. RFM gives you a clear picture of customer behavior. With FanRuan and FineBI, you can quickly process large or complex data sets for your rfm projects. FineBI makes rfm easy to use for both new and experienced users.

RFM Analysis Basics

What Is RFM?

RFM analysis, a simple and practical customer analysis method proposed by American database marketing research institutes, evaluates customer data based on the following factors:

• Recency (R): interval between the current time and customers' last purchase time.
• Frequency (F): number of times customers purchase within a specified period of time.
• Monetary (M): customers' consumption capability, usually based on the average amount of consumption per transaction.

These three factors constitute the best indicators for data analysis.

You can use rfm analysis to understand your customers by looking at three important factors: Recency, Frequency, and Monetary value. Recency shows how recently a customer made a purchase. Frequency tells you how often a customer buys from you. Monetary value measures how much money a customer spends. These three parts work together to give you a clear picture of customer behavior.

When you use rfm, you can group your customers based on their buying habits. For example, some customers buy often and spend a lot, while others may not have purchased in a long time. By scoring each customer on these three factors, you can see who is most engaged and who might need more attention. Many businesses use rfm analysis with clustering methods like K-Means or Decision Trees. These tools help you create groups of customers who act in similar ways, making it easier to plan your marketing.

Tip: RFM analysis helps you spot your best customers and those who might leave soon. This lets you focus your efforts where they matter most.

Why Use RFM Analysis?

Rfm analysis gives you a powerful way to improve your marketing. You can target your messages to the right people and increase your return on investment. Here are some reasons why rfm is so effective:

• You can create personalized campaigns for high-value customers, which leads to better conversion rates and higher revenue.
• Rfm helps you find your most engaged customers and predict who will respond to offers.
• You can use rfm to run win-back campaigns for customers who have not bought in a while.
• Marketing teams can use rfm analysis to spend their budget more wisely, focusing on the groups that bring the most value.

FanRuan leads the way in data analytics, and FineBI makes rfm analysis easy for everyone. With FineBI, you can quickly connect your data, run rfm analysis, and see the results in clear dashboards. This helps you make smart decisions and grow your business.

Prepare Data with FineBI for RFM Analysis

Data Integration Steps

FineBI makes it easy for you to bring all your customer data together, no matter where it lives. You can connect to many types of data sources that are common in retail and e-commerce. Here are some examples:

1. Online retail datasets, such as UK online retail store transactions.
2. E-commerce platforms like Olist or Alibaba Cloud.
3. Grocery shopping data from Instacart.
4. Retail chain transactions and FMCG datasets.
5. Big data platforms, including Hadoop.
6. Cloud services like AWS, Oracle, and SAP.

You can also connect to Excel files, CSVs, and APIs. FineBI supports over 60 connectors, so you can link your databases, cloud warehouses, and even real-time data streams. This flexibility lets you unify all your customer and transaction records in one place for analysis.

Tip: FineBI’s integration with FanRuan’s FineDataLink platform helps you synchronize and transform data across multiple systems in real time. This means you always work with the latest and most accurate information.

Data Cleaning and Structuring

Once you have connected your data, you need to clean and organize it before starting RFM analysis. Clean data leads to better results and more reliable insights. Follow these best practices:

1. Remove duplicate records and fix any inconsistencies.
2. Check for missing values and fill or correct them as needed.
3. Standardize text fields, such as customer names and addresses.
4. Create new columns, like extracting the year from transaction dates or grouping customers by age range.
5. Select key fields for RFM: Customer ID, Transaction Date, and Transaction Amount.
6. Normalize your data by calculating percentiles for recency, frequency, and monetary value.
7. Group customers into buckets (such as quartiles or deciles) to prepare for scoring.

You should also make sure your data handling follows privacy laws like GDPR and CCPA. FineBI’s self-service tools help you preview, filter, and transform your data without needing to write code.

Note: Clean and well-structured data is the foundation of accurate RFM analysis. Take time to review your data before moving to the next step.

RFM Analysis Process

Creating an Analysis Subject

You start your rfm analysis by creating an analysis subject. This subject is the main group of data you want to study. In FineBI, you select your customer transaction records as the analysis subject. Make sure you include fields like Customer ID, Transaction Date, and Transaction Amount. These fields help you track each customer’s activity over time.

(1) To create an RFM model analysis subject, choose My Analysis > New Subject, select RFM analysis table detailed data from the built-in public data, and click OK to add data, as shown in the following figures.

(2) Click Field Settings, select required fields, and click Save and Update, as shown in the following figure.

Calculating Customers' Consumption Indicators

Next, you calculate the three main indicators for each customer: Recency, Frequency, and Monetary value. Recency measures how long it has been since a customer’s last purchase. Frequency counts how many times a customer has bought from you during a set period. Monetary value shows how much money each customer has spent. FineBI lets you do these calculations quickly, even with large datasets.

Calculate the average consumption amount per time, consumption times, and number of days since the last consumption of each customer.

(1) Group and summarize data, as shown in the following figure.

• Latest consumption time (R): Select Latest Time from the drop-down list of DATE to obtain the latest purchase time of each customer.
• Consumption times (F): Since one record represents one consumption time of a customer, drag CUSTOMERNAME and select Count from its drop-down list.
• Average consumption amount per time (M): Select Average from the drop-down list of MONEY1 to calculate each customer's average consumption amount per time.
• Select Sum from the drop-down list of MONEY to calculate each customer's consumption amount.

(2) Rename the fields for easier understanding, as shown in the following figure.

(3) Calculate the number of days since the last consumption of each customer and add a column to calculate the time interval, as shown in the following figure.

Calculating the Average Values of All Customers' Consumption Indicators

You need to know how your customers compare to the average. To do this, use percentile-based methods like quartiles or deciles. These methods group customers into buckets based on their rfm values. For example:

• Quartiles split customers into four groups (25% each).
• Deciles split them into ten groups (10% each).
• You can use equal width or equal frequency bucketing.

This approach helps you see which customers are above or below average in recency, frequency, or spending.

Average Consumption Amount of Customers

(1) Add a column to calculate the total consumption amount of all customers, as shown in the following figure.

(2) Add a column to calculate the total consumption times of all customers, as shown in the following figure.

3) Add a column to calculate the average consumption amount of all customers (Average consumption amount of all customers = Total consumption amount of all customers/Total consumption times), as shown in the following figure.

Customer Consumption Indicator Evaluation

Perform evaluation based on whether the key indicators are higher than average levels of customers. In the IF(xxx>Average xxx of customers,1,0) formula, mark records lower than or equal to the average using 0 (indicating positive features) and records higher than the average using 1 (indicating negative features).

Evaluation of Average Consumption Amount Per Time

Add a formula column named Evaluation of Average Consumption Amount Per Time, and add the IF (Average Consumption Amount Per Time>Average Consumption Amount of All Customers,1,0) formula to mark records whose average consumption amount of a customer per time is greater than the average one of all customers using 1 and records whose average consumption amount of a customer per time is less than or equal to the average one of all customers using 0, as shown in the following figure.

Consumption Times Evaluation

Add a formula column named Consumption Times Evaluation, and add the IF (Consumption Times>Average Consumption Times of Customers,1,0) formula to mark records whose average consumption times of a customer is greater than the average one of all customers using 1 and records whose average consumption times of a customer is less than or equal to the average one of all customers using 0, as shown in the following figure.

Evaluation of Number of Days Since the Last Consumption

Add a formula column named Evaluation of Number of Days Since the Last Consumption, and add the IF (Number of Days Since the Last Consumption>Average Number of Days Since the Last Consumption,1,0) formula to mark records whose average number of days since the last consumption is greater than the average one of all customers using 1 and records whose average number of days since the last consumption is less than or equal to the average one of all customers using 0, as shown in the following figure.

Customer Classification

After scoring, you classify your customers. High-spending customers with recent and frequent purchases become your top group. Others may fall into groups like “potential,” “to maintain,” or “to recover.” This classification helps you target your marketing. You can focus on loyal customers or try to win back those who have not purchased in a while. Rfm analysis gives you a clear way to segment your audience and improve your business strategy.

Adding RFM Indicators

Add a formula column named RFM, and use the CONCATENATE() function to concatenate the vectorized values of RFM in the order of evaluation of number of days since the last consumption, consumption times evaluation, and evaluation of average amount of consumption per time. The formula is CONCATENATE(Evaluation of Number of Days Since the Last Consumption,Consumption Times Evaluation,Evaluation of Average Amount of Consumption Per Time), as shown in the following figure.

Classifying RFM Indicators

Add an assignment column named Customer Type and assign values by grouping the records in the RFM column, as shown in the following figure.

Details of the creation process can be found in the RFM Analysis help file.

Calculate RFM Metrics for RFM Analysis

To get the most out of your rfm analysis, you need to calculate rfm metrics for each customer. FineBI makes this process simple and fast. You can use its self-service analytics tools to automate the calculations and handle large datasets without writing code. Let’s break down each metric step by step.

Calculate Recency 

Recency tells you how long it has been since a customer’s last purchase. This metric helps you find out who your most active customers are. In FineBI, you can calculate recency by following these steps:

1. Import your transaction data into FineBI. Make sure you have fields like Customer ID and Transaction Date.
2. For each customer, find the most recent transaction date.
3. Subtract this date from the current date to get the number of days since their last purchase.

For example, if today is June 30 and a customer last bought something on June 20, their recency is 10 days.

You can use FineBI’s drag-and-drop interface to set up this calculation. FineBI lets you group customers by recency, such as “last 7 days,” “last month,” or “last 3 months.” This grouping helps you score customers and see who is most engaged.

Here is a table that shows how recency fits into the rfm model:

FineBI can automate this process, so you always have up-to-date recency scores for your customers.

Calculate Frequency

Frequency measures how often a customer buys from you within a set period. This metric helps you spot loyal customers who return again and again. To calculate frequency in FineBI, follow these steps:

1. Use your transaction data with Customer ID and Transaction Date.
2. Set a time frame, such as the past year or past six months.
3. Count the number of purchases each customer made during this period.

Tip: FineBI lets you filter and group data easily, so you can adjust the time frame to fit your business needs.

You can score frequency by creating buckets, such as “bought once a month,” “bought three times a year,” or other custom ranges. FineBI’s self-service analytics make it easy to visualize and compare frequency across your customer base.

Here is how frequency appears in the rfm scoring table:

With FineBI, you can automate frequency calculations and update them as new data comes in.

Calculate Monetary Value

Monetary value shows how much money each customer spends with your business. This metric helps you identify your highest-value customers. FineBI makes it easy to calculate monetary value with these steps:

1. Collect customer transaction data, including Transaction Date, Customer ID, and Order Amount.
2. Import this data from your CRM, ERP, or database system into FineBI.
3. Aggregate the total amount spent by each customer. You can do this by summarizing transactions per customer using pivot tables or built-in formulas.
4. Use the aggregated totals as the monetary metric for your rfm analysis.

Note: FineBI supports data from many sources, so you can bring in all your sales records for a complete view.

You can group customers by how much they spend, such as “spent more than $1,000,” “spent between $500 and $1,000,” and so on. FineBI’s automation means you can refresh these values whenever you update your data.

Here is the monetary value row from the rfm scoring table:

FineBI’s self-service analytics let you calculate rfm metrics quickly and accurately. You can focus on analyzing your results and planning your marketing strategies, rather than spending time on manual calculations.

With FineBI, you can automate the entire rfm process, from data import to scoring and segmentation. This saves you time and ensures your analysis is always up to date.

RFM Scoring and Segmentation for RFM Analysis

Assign RFM Scores

You can start by assigning RFM scores to each customer. This step turns raw data into clear numbers that show how valuable each customer is to your business. FineBI helps you do this quickly and accurately.

First, you need to score each part of RFM—Recency, Frequency, and Monetary value. FineBI uses percentile ranks, often called quintiles, to do this. You divide your customers into five groups for each metric. Customers in the top 20% for spending get a score of 5 for Monetary value. Those in the bottom 20% get a score of 1. You repeat this for Recency and Frequency.

You combine these three scores to create an RFM code for each customer. For example, a customer with scores of 5 for Recency, 4 for Frequency, and 5 for Monetary value would have an RFM code of 545. This code helps you see which customers are most engaged and valuable.

FineBI makes this process easy. You can use its drag-and-drop tools to set up scoring rules. The platform calculates scores for you, even if you have thousands of customers. You do not need to write any code.

Tip: Assigning RFM scores helps you narrow down the segments that matter most for your business. You can focus your marketing on the right groups.

Segment Customers

After you assign RFM scores, you can segment your customers into groups. This is the heart of customer behavior segmentation. FineBI gives you powerful ways to see and understand these segments.

You can use different methods to create segments based on RFM codes. Some businesses use simple rules, while others use clustering algorithms like K-Means or Decision Trees. These methods help you find patterns in your data and group customers who act in similar ways.

FineBI lets you visualize these segments with many chart types:

• Ring charts show the proportion of customers in each segment.
• Labels display the number of customers in every group.
• Waterfall charts illustrate how much each segment contributes to your sales.
• Detailed lists let you see customer-level data, such as how often they buy and how much they spend.

You can classify segments with clear names. For example, an RFM code of 111 might mean "Lost member." A code of 112 could mean "Important retention of members." These labels make it easy to spot which groups need special attention.

Here are some rfm segmentation examples you might see in FineBI:

• "Champions": Customers with high scores in all three areas. They buy often, spend a lot, and purchased recently.
• "Loyal Customers": High Frequency and Monetary scores, but Recency may be lower.
• "At Risk": Customers who used to buy often and spend a lot but have not purchased recently.
• "Lost": Low scores in all three areas.

You can use these segments to plan your next campaign. For example, you might send a loyalty reward to "Champions" or a win-back offer to "At Risk" customers. FineBI dashboards let you track how each campaign performs for every segment.

Note: RFM analysis helps you understand your customers better. You can use it to increase revenue, improve retention, and design campaigns that fit each segment.

When you use rfm analysis, you see real business results. You can increase your company’s revenue by focusing on the right customers. You can also improve customer retention, which is often more valuable than finding new customers. Targeted campaigns based on RFM segments help you get the most out of your marketing budget.

FineBI’s dashboards make it easy to monitor your segments. You can see which groups respond best to each campaign. You can also adjust your strategy as customer behavior changes over time.

With FineBI, you can turn complex data into clear insights. You can make smarter decisions and grow your business with confidence.

Apply RFM Analysis Insights

Create a RFM Model in Excel

You can create a rfm model in excel to analyze your customer data without advanced software. Follow these steps to build your own model:

1. Clean your data and check that each column has the correct type. For example, Customer ID should be text, Date as date, and Price as a decimal.
2. Select important columns such as Customer ID, Age, Gender, Invoice No, Invoice Date, and Price.
3. Transform your data by adding new columns. You might use IF and AND functions to group ages or other categories.
4. Calculate rfm metrics using Pivot Tables. Summarize by Customer ID. Rename fields so Price becomes Monetary, Invoice No becomes Frequency, and Invoice Date becomes Recency.
5. Find Recency by using the formula =DATEDIF(purchase_date, current_date, "D") to get days since the last purchase.
6. Score each metric. Use Excel’s PERCENTRANK.INC function to assign scores for Recency, Frequency, and Monetary.
7. Combine the scores into a single RFM score column.
8. Segment your customers. Use VLOOKUP with a lookup table to assign each customer to a segment based on their RFM score.
9. Analyze your results. Build pivot tables and charts to see customer segments, age groups, and revenue.

You can repeat this process to create a rfm model in excel for different datasets. This method helps you spot high-spending customers and plan your next campaign.

Marketing Strategies with RFM

When you use rfm, you can target your marketing more effectively. Many retail businesses group customers into segments like Core, Loyal, Whales, Promising, Rookies, and Slipping. You can design a campaign for each group. For example, you might send loyalty rewards to Core customers or special offers to Rookies.

Membership management systems work even better when you add rfm insights. You can spot Champions, At-Risk, Loyal, and Lost Customers. This lets you send the right message to each group. You can reduce churn and increase customer lifetime value by using targeted rewards and re-engagement offers.

FanRuan’s solutions help you combine rfm data from your CRM, e-commerce, or loyalty programs. You get a full view of each customer. You can automate campaigns and update rfm scores often. This keeps your retention strategies fresh and effective. With dashboards and visual tools, you can track how each campaign performs and adjust your approach for better results.

Tip: Use rfm segments to personalize every campaign. You will see higher engagement and better returns.

You now know how to use rfm to group your customers and improve your marketing. FineBI gives you the tools to automate each step, from data integration to customer segmentation. As more companies use AI for customer analysis, you can expect even smarter segmentation and real-time insights. New trends include predictive segmentation, privacy-focused AI, and real-time campaign activation. Try exploring advanced analytics or connect your rfm insights with other FanRuan business intelligence solutions to stay ahead.

Click the banner below to try FineBI for free and empower your enterprise to transform data into productivity!

Continue Reading About RFM Analysis

What is RFM Model & RFM Analysis？