RFM analysis

1. Overview

1.1 Definition

RFM analysis is a simple and practical customer analysis method proposed by the "Database Marketing Research Institute" in the United States. It finds that there are three important elements in customer data:

• Last consumption time (R) : the interval between the customer and the last purchase time.

• Consumption frequency in the latest period (F) : refers to the number of purchases made by the customer within a limited period.

• Consumption amount in the latest period (M) : The customer's consumption power, usually measured by the average consumption amount of a customer at a time.

These three factors constitute the best indicators for data analysis.

1.2 Problem solved

RFM analysis is to observe and classify customers through three key indicators and judge the value of each user segment. Carry out corresponding marketing strategies for customers with different characteristics.

1.3 Expected effect

After RFM analysis, customers are divided into different categories. The effect of self-service dataset is shown in the figure below:

The dashboard effect is as follows:

1.4 Solutions

The raw data is processed using self-service dataset. The realization idea is shown in the figure below:

1) Create a self-service dataset and select the fields required for RFM analysis.

2) The data are processed to obtain the three key indicators and their average values.

3) Vectorize the three indicators by comparing them with the average value.

4) Customer classification according to feature vector.

2. Example

Sample data: 

RFM Data.xlsx

2.1 Create a self-service dataset

This section uses "RFM Data. XLSX" as an example to analyze customer consumption details and classify customers.

1) Go to the "Data Preparation" interface, select user's own service package under "My Self-service Dataset", click "Add Table", and select add "Excel Dataset", as shown in the following figure:

Select the sample Data "RFM Data. xlsx", then add the "Excel Dataset" to the "Self-service Dataset" and name it "RFM Data Analysis", as shown in the following figure:

Then calculate the consumption indicator for each customer.

There are three key indicators for each customer:

• Recency per customer (R)

• Frequency of consumption per customer (F)

• Montary(M): Calculate the average of the total consumption amount of the customer before calculating the average consumption amount of each user.

2.2 Consumption indicator for each customer

2.2.1 Calculate the average consumption

1) Click "+" and select "New column", as shown below:

2) Name the newly added column "Average Consumption", select "all values/within group" to set it, and click "OK" to obtain the overall average indicator "Average Consumption". As shown below:

Detailed Settings of "all Values/within group" :

• Value rule: all values

• Value from: "MONEY"

• Statistical method: "Average"

2.2.2 Calculate the average consumption (M) and consumption frequency (F)

1) Click "+" and select "Group Summary", as shown below:

2) Drag the field on the left to the corresponding group summary box on the right, and set the summary indicator to display the results to complete the sorting of the original data. As shown below:

Summary of indicator settings in the box:

• DATE: Latest time

• "MONEY" : "Average"

• "AMOUNT" : "Records Count"

The groupings obtained in the "Data Preview" section are summarized to obtain key indicators of customer characteristics. The "MONEY" and "AMOUNT" fields respectively represent: Average Consumption (M) and Consumption Frequency (F).

2.2.3 Calculate the last consumption distance time (R)

Click "+" and select "New column", as shown below:

Set the new column name as "Last consumption distance Time", select "time difference" and click "OK" to obtain "Last consumption distance time (R)", as shown below:

Setting details of "time difference" :

• Time difference = "Current Date" - "DATE"

• Measurement method: "day"

So far, three customer key indicators and an overall average value are obtained in this section: "Last consumption distance time" (R), "AMOUNT" (F), "MONEY" (M) and "Average Consumption".

2.3 Calculate the total customer consumption indicator

2.3.1 Calculate the average consumption frequency

1) Click "+" and select "New column", as shown below:

2) Set the name of the new column to "Average consumption frequency", select "all values/within group", and click "OK" to obtain the overall average value indicator "Average consumption frequency". As shown below:

Setting details of "all values/within group" :

• Value rule: all values

• Value from: "AMOUNT" (F)

• Statistical method: "Average"

2.3.2 Calculate the average time of last consumption distance

1) Click "+" and select "New column", as shown below:

2) Set the name of the new column as "Average time of last consumption distance", select "all values/within group", and click "OK". The overall mean indicator "Average time of last consumption distance" was obtained. As shown below:

Setting details of "all values/within group" :

• Value rule: all values

• Value from: "Last consumption distance time" (R)

• Statistical method: "Average"

2.3.3 Preview effect

The three key indicators obtained and their corresponding overall average values are shown in the figure below:

2.4 Vectorization of customer characteristics

Vectorization of customer characteristics based on whether key indicators are greater than the overall average level of customers.

Where, in IF(XXX > XXX average value,1,0), the value less than the population average is set to "0", and the value greater than the population average is set to "1", so that "1" and "0" maintain positive characteristics and negative characteristics.

2.4.1 Amount vectorization

Click "+" and select "New column", as shown below:

Name the new column "Amount vectorization", enter the formula IF(MONEY>Average Consumption, 1,0) and click "OK", as shown below:

2.4.2 Frequency vectorization

Similarly, add the "Frequency vectorization" field, as shown below:

2.4.3 Last consumption vectorization

Similarly, the field of "Last consumption vectorization" is calculated. When entering the formula, it should be noted that "Last consumption distance time" < "Average time of last consumption distance" belongs to the positive vector, representing "1". As shown below:

2.5 Customer characteristics analysis

2.5.1 Divide customer types

The characteristic vector values of customers have been obtained, and customers can be classified according to the following table:

2.5.2 Add RFM indicator

Click "New column" and use CONCATENATE() to CONCATENATE RFM vectorization values, as shown below:

2.5.3 RFM custom grouping

1) Add "Group Summary", as shown below:

2) Drag the following fields into the group box and set the display value of "DATE" to "Latest time". As shown below:

3) Set the RFM field to "Custom Grouping", as shown below:

4) Grouping is based on the customer type analysis table in section 2.5.1. Click "011" "Add a group" and name the group, and then click the next ungrouped indicator to add a group, as shown below:

5) Click "OK", then click "Save" to save the self-service dataset. As shown below:

3. Preview effect

Click the created "RFM Data Analysis" for "Data Preview" to view the Data. As shown below:

At this point, the self-service dataset can also be visualized through the dashboard of customer categorization related data.

Refer to section 1.3 for dashboard effect.