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.
RFM analysis evaluates and classifies customer data through the three key indicators to determine values of each segmented customers and develop corresponding marketing strategies for customers with different features.
After RFM analysis, customers are divided into different categories. The following figure shows the data analysis effect.
The following figure shows the dashboard effect.
For online preview, visit RFM Analysis.
Create an analysis subject to process raw data. The following figure shows the implementation principle.
(1) Create an analysis subject and select the required fields for RFM analysis.
(2) Process data to obtain the three key indicators and their average values.
(3) Vectorize the three indicators by comparing them with the average values.
(4) Classify customers based on feature vectors.
(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.
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.
(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.
Add a column to calculate the average consumption times of all customers, as shown in the following figure.
Add a column to calculate the average number of days since the last consumption of all customers, as shown in the following figure.
In this way, each customer's average consumption amount per time, average consumption amount of all customers per time, each customer's consumption times, average consumption times of all customers, number of days since the last consumption of each customer, and average number of days since the last consumption of all customers are calculated.
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).
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.
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.
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.
After obtaining the feature vector values of customers, you can classify customers according to the following table.
Customer Feature
Classification
Important valuable customer (111)
Customer that consumes recently with high consumption frequency and amount (VIP).
Important potential customer (101)
Customer that consumes recently with high consumption amount and relatively low consumption frequency and loyalty. These customers are potential ones that must be developed.
Important customer to maintain (011)
Customer that consumes long before with high consumption frequency and amount.
Important customer to retain (001)
Customer that consumes long before with high consumption amount and relatively low consumption frequency. These customers are to be or already lost, which should be retained.
General valuable customer (110)
Customer that consumes recently with high consumption frequency and relatively low consumption amount, for which unit price needs to be increased.
General potential customer (100)
Customer that consumes recently with relatively low consumption frequency and amount.
General customer to maintain (010)
Customer that consumes long before with high consumption frequency and relatively low consumption amount.
General customer to retain (000)
Customer whose all indicators are not high.
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.
Add an assignment column named Customer Type and assign values by grouping the records in the RFM column, as shown in the following figure.
Click the created RFM Analysis Table, and click Data Preview to preview the data, as shown in the following figure.
You can also use the subject to visualize data related to customer classification through the dashboard.
For details about the dashboard effect image, see section “Effect" in this document.
For details about the dashboard effect, refer to RFM Analysis.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy