Utilising RFM model in eCommerce to pick the best customers

There are many companies in the market who work wholly and solely on optimizing eCommerce platforms. The services range from understanding the traffic of users visiting your website to recommending and acting upon the changes like improving the landing page designs to facilitating onsite engagement activities. Having done all these activities, you will look forward to a detailed campaign report highlighting the performance of the campaigns. But what should be your target audience??

“The big question arise on how to pick the right audience”

The answer is a model which boosts the selection process of the valuable users from the whole bunch of users utilizing the RFM Model. In my previous blog, I explained what is RFM model and its use to find out the customers likely to stay more active in the coming future. As you know, the RFM model (Recency, Frequency and Monitory gain) is used to calculate the total of all three parameters represented by R, F and M respectively to pick the customers with highest value and affinity towards the brand.

To explain this model in detail, I am making use of an excel sheet with 100 records of customers with details like Customer ID, Date of last purchase, Number of purchases and Total spend over time frame. You can create this data set by making use of simple formulas which I will mention here.

STEP 1: Open an Excel Sheet and add Row 1 with above parameters in respective cells denoting the column heads.

STEP 2: Under Column 1 named Customer ID, use formula “=A2+1” in cell A3 and drag the cell till Row 101.

STEP 3: In cell B2, we need purchase dates happening on random dates ranging from Jan 1, 2019 to Feb 28, 2019. Using the formula:

=RANDBETWEEN(DATE(Year, Month and Start_Date), DATE(Year, Month and Last_Date))

STEP 4: Column C and D will follow the same formula as we have to randomly allocate values to both columns with distinct ranges. For column C, we shall keep the number of purchases in a range of 1-20. Drag and drop till C 101 cell. Use the formula:

=RAND()*(a-b)+b

STEP 5: Column D will follow the use of the above formula. The values will range from 1-2000 in this column as the maximum amount spent by the customer is 2000. Drag and drop the cell D2 till D101.

STEP 6: We shall copy our sheet 1 and paste the data on sheet 2 utilizing Paste Special option> Values only. You must have heard about a concept named percentile where you can group values in sets. For example, I have made a set of 11 days for the complete date range from January 1 till 28 February.

The R,F,M values range from 1-5 where 1 represents lowest value and 5 represents highest value fulfilling the parameter scale. Hence, the purchases made between 19-28 February will have R factor value as 5 and consequently the rest of the sets will have values ranging from 1-4.

We will then add columns named R,F,M as shown in below image.

STEP 7: Once you have all the values filled in R,F,M column. Copy Column A,C,F and M in a new sheet. You can sort the Columns R,F,M values based on largest to smallest. Add one new column E to add values from R,F,M columns (R+F+M) and apply the sort values from largest to smallest now. You will have customers will highest RFM score on top followed by others. You can plan the campaigns accordingly. I am sharing the final sheet image below for a better understanding.

The customers with highest RFM values is the most active bunch of users and can be considered for campaign looking forward to customer lifetime value. You can approach this set of customers by offering them customized services and ensure repeated business.

If you have liked this blog, please leave a comment below.