Reprinted: rfm analysis of member customer transaction data using Excel

Case: rfm analysis of member customer transaction data using Excel

Background:

A Member Service Enterprise has about 1200 member customers in the past year. As the company wants to activate promotions for different categories of inactive customers, it also plans to launch a series of promotions for key customers to retain these customers and maintain their activity. Therefore, we hope to use this data for customer classification research.

 

Analysis:

The RFM model is relatively simple and direct based on the customer's needs. The RFM model is based on R (recency-closeness), F (Frequency-frequency), and M (monetary-quota) customer groups are subdivided in three dimensions. Because the number of customers is small (about 1200), it is more appropriate to use 3x3x3 = 27 cubes (about 1200/27 = 44, although the average number of customers in each category is small, considering the concentration distribution, a large number of categories can also be around-, which is suitable for short-term telemarketing, text message marketing, or letter marketing for member customers.

 

RFM model principles:

The RFM model is a simple classification based on the customer's activity level and transaction amount contribution. Because the operation is simple, it is usually used.

 

Closeness R: indicates the time distance between the latest active time of the customer and the data collection point. The larger the R value, the longer the customer has not made a transaction. The smaller the R value, the closer the customer has a transaction. The larger the R, the more likely the customer will be "sleeping", and the more likely the loss is. Among these customers, there may be some high-quality customers, which deserve to be activated by the company through certain marketing means.

Frequency F: f Represents the customer's active frequency in the past period. The larger the value of F, the more frequent transactions between customers and the company, not only bring popularity to the company, but also stable cash flow. The smaller the value of F, it indicates that the customer is not active enough and may be a frequent customer of competitors. For customers with a small value of F and a large consumption amount, a certain competitive strategy should be launched to win these customers from their competitors.

Line M: indicates the amount of the customer's consumption each time. You can use the last consumption amount or the previous average consumption amount. Different identification methods can be used based on the analysis purpose. Generally, a customer with a large transaction amount has a strong payment ability and low price sensitivity. This is a relatively high-quality customer, and a customer with a small transaction amount, the payment capability and willingness may be low. Of course, it is not absolute.

 

There are many RFM analysis tools. You can use SPSS or SAS for modeling and analysis, and then perform in-depth mining. Ibm spss also has a modeler with specialized RFM mining algorithms. This article introduces how to use Excel (version 2007) for a preliminary RFM analysis.

Procedure:

Step 1: clean data

Original dataset: See the appendix excel(simulation data .xlsx) for data ). You can download the exercise. The dataset contains more than 26600 data records, including the record ID (primarykey of the database), customer number, cashier time, sales amount, and sales type.

Through simple filtering, we can see that there are 0 consumption amounts in the transaction amount and negative consumption amounts. Continue to check the transaction type and find it is caused by "gift" and "return. These data cannot be used in this analysis. Therefore, you must filter and remove the data during data processing.

Excel operation:

Click "1" on the first line to select the first line.

Click "data" in the menu bar, and click "filter" in the shortcut bar"

Click the drop-down button of "sales type" to view all sales types in the dataset.

Click the small-angle icon in the "sales amount" field to see a negative number.

 

Step 2: Data Processing

According to the analysis requirements, r uses the time difference (days) between the customer's final transaction time and the data collection point time as the Metering Standard; f The number of transactions of each member customer in the data set is used as the Metering Standard (the number of transactions per year). m is based on the average customer transaction volume. You can use a pivot table in Excel to calculate the above RFM data.

 

Excel operation:

Click "insert" in the menu bar"

Click "pivot table" in the shortcut bar"

Select the data area to confirm that all data is selected

Select Insert data in "New worksheet" and click "OK"

Drag "Customer ID" into the "line label" column

Drag "cashier time", "Record ID", and "transaction amount" into the numerical calculation column.

Click the "Cash Register time" numeric calculation column and select "Value Field Settings"

Select "maximum" in "calculation type"

In the lower-left corner of the dialog box, click "Number Format", set the time format to yyyy-mm-dd, and then "OK"

Click the "sales amount" value calculation column and select "Value Field Settings"

Select "average" in "calculation type", and then "OK"

In the "Record ID" numeric calculation button bar, select "Value Field Settings"

Select "count" in "calculation type", and then "OK"

In the filter item "sales type" at the top of the pivot table, click the small-angle icon in the drop-down list and check the box before "select multiple items, click the check box before "return" and "complimentary", and then click "OK" to get the following result.

We have obtained the following information:

1) F value: How many times did the customer spend in the past year?

2) m value: the average consumption amount of each customer transaction

 

However, the R value still needs to be processed. Currently, the R value only obtains the customer's last consumption date, and the number of days from the data collection date needs to be calculated.

Excel operation:

Drag the column label ABCD and select the four columns in the pivot table.

Press ctrl ^ C (copy) and click the small drop-down triangle icon under "start" and "Paste" in the shortcut bar, select "paste value" or click "select Paste value" and then select Paste value to overwrite the original Pivot table in the form of data.

Enter the data collection date in cell C1, in the format of yyyy-mm-dd.

Select cell C1 and copy the content.

Select B5: b1204 [shortcut operation: B5, hold down SHIFT ^ Ctrl, click the down arrow, release Ctrl, continue holding down shift, and press the up arrow once, cancel the summary of the last row of data]

Click the drop-down arrow below the "Paste" button in the shortcut bar on the "Start" menu bar, select "select Paste", select "Subtract" in the dialog box, and then "OK"

If you do not cancel the current selection, select the format drop-down menu in the shortcut bar in the "Start" menu bar and select "number"

Because the data obtained is the last transaction date minus the number of days of the data collection date, it is a negative value, so you still need to process it.

Enter-1 in cell d1

Then press Ctrl ^ C to copy the value (-1) in the D1 cell)

Then select B5: b1204. [the shortcut operation is the same as above]

"Start"-"Paste" drop-down button-"select Paste"-select "Multiply" in the Calculation Section and click "OK"

Finally, we get:

At this point, we get the r, f, and m values for each customer number.

 

Step 3: Data Analysis

R-score, F-score, and m-score. In order to score customers by r, f, and m, we need to calculate the data range (the difference between the maximum and minimum values), and determine the R (or F, m) by comparing the R (or F, m) value and the third division of the range) r-score, F-score, and m-score.

 

Therefore, calculate the maximum, minimum, and range of r, f, and M.

Excel operation:

F1 to H1 represents the maximum value of R \ f \ m, calculated using the formula "= max (B5: b1204)". (when F is calculated, B is changed to C, and m is changed to D)

F2 to H2 represents the minimum value of R \ f \ m. The formula "= min (B5: b1204)" is used for calculation (B is changed to C when F is calculated, and B is changed to D when m is calculated)

F3 to H3 represents the range of R \ f \ m, and the formula "= (F1-F2)/3" is used to calculate F to G, change M hour f to H)

[The preceding quick operation is available. First, enter the formula in cells F1, F2, and F3, select F1: F3, and then pull the black cross in the lower right corner, copy the formula of column F to column G and column H to the right]

 

The formula for calculating the R-score is:

In the E5 cell, enter "= If (Roundup (B5-$ F $2)/$ F $) =, Roundup (B5-$ F $2) /$ F $3, 0 ))"

When the R value is the minimum value, the value of Roundup (B5, 0) is 0, and if the value is 0, the value is 1.

$ F $2 is used to lock the referenced cell for subsequent formula copying, the minimum and maximum values are not subject to relative references and the position changes. [locking the referenced cell, except for manually adding the $ symbol, is used to select the referenced cell and press the F4 shortcut key. This is troublesome, enter the $ symbol manually]

[Another simple processing method is to directly use the formula "= Roundup (B5-$ F $2)/$ F $3, 0)", and then use ctrl ^ h for quick operations, replace the value 0 with 1. Replace the value with 1 and copy the formula]

F-score and M-score are processed as examples.

The formula for the F5 cell is: = If (Roundup (C5-$ G $2)/$ G $) =, Roundup (C5-$ G $2) /$ G $3, 0 ))

G5 cell formula: = If (Roundup (D5-$ h $2)/$ h $) =, Roundup (D5-$ h $2) /$ h $3, 0 ))

For RFM-score calculation, multiply by 100-10-1 and then add the values respectively, so that r, f, and m are expressed as three percentile, percentile, and single digit of a three-digit number respectively, the three digits of this three-digit number represent the coordinates of the three dimensions of 3x3x3 = 27 cube.

H5 cell formula: = E5 * 100 + F5 * 10 + G5

Select E4 to H4, double-click the small black cross in the lower right corner, and copy the E4 to H4 formula to all customer data.

The result is as follows:

The next step is to count the number of customers on each cube.

Use the pivot table to generate statistical results again

Excel operation:

Click "pivot" in the shortcut bar on the "insert" menu bar and select A4: h1204 in the data table area. [confirm this selection and change the automatically jumped area ], click "OK"

Drag RFM-score into "Row tag", drag "customer number" into "value calculation", and click "customer number" in "value calculation, select "field value settings" and set the calculation method to "count". The processing result is as follows:

Step 4: data analysis result interpretation and Visualization

This analysis result is obtained, and the conditional format function of Excel can be used to visualize the data analysis result.

Excel operation:

Extend Column B in a pivot table (for example)

Column B5: B22 selected

Click the shortcut bar in the "Start" menu bar and select "Data bar" from the "condition format" drop-down menu. Then, select a color.

Through bar chart visualization, You can intuitively compare which customers have a large number.

 

A video tutorial for this operation will be provided later;