INSIGHTS FOR AN E-COMMERCE COMPANY: A USE CASE ANALYSIS FOR CUSTOMER SEGMENTATION
By: Paula Obeng-Bioh — Business Intelligence Associate
1. INTRODUCTION
This data analysis project embarks on the exploration of customer segmentation using the RFM analysis which stands for Recency, Frequency, and Monetary Value; three key dimensions used to evaluate customer behavior. This technique helps businesses to understand and categorize their customers based on their transactional behavior for informed decision-making.
Recency (R): This measure how recently a customer made a purchase or interacted with your business. Recent purchases earn higher scores.
Frequency(F): Frequency checks how often customers interact with your business or make purchases, higher scores are given to frequent buyers.
Monetary Value (M): This represents the total of money a customer spends on purchases, giving higher scores to those who spend more.
After scoring customers on these metrics, businesses can group them into different segments. These segments help tailor marketing and customer engagement strategies to meet each category’s specific needs***.In essence, RFM analysis helps businesses make informed decisions to improve customer relationships and revenue across various customer groups.
Microsoft Excel was the main analytical tool used for this project.
1.1 BUSINESS PROBLEM AND OBJECTIVES
An E-commerce mall needed to identify and categorize its customers into four distinct segments based on their buying habits and engagement levels. This segmentation would enable them to create effective marketing strategies and campaigns specific to each group.
Given this problem, the objectives of the project were;
- Delve into customer data for comprehensive analysis.
- Identify and segment customers into four different groups based on the RFM metrics.
- Provide actionable insights to inform marketing campaigns and enhance customer satisfaction.
1.2 SUMMARY OF FINDINGS
The total number of customers considered for this analysis were 2,213, and their distribution across different segments is as follows:
19% were categorized as top customers.
26% were listed as loyal customers.
34% were classified as at-risk/need attention customers.
21% fell into the immediate attention customer category.
Despite being a minority, individuals in the Top Customer segment, characterized by prolific purchasing habits, are the highest spenders among the company’s customers, generating an average income of $72,290.
While constituting 26% of the customer base and demonstrating frequent and substantial spending, the Loyal Customer segment has not engaged with the company recently.
Comprising the largest portion at 34% of the customer base, the At-Risk/Need Attention segment exhibits low monetary value and frequency but demonstrates appreciable recency in their engagements with the company.
Comprising 21% of the company’s clientele, Immediate Attention segment exhibit the lowest scores in all three RFM analysis metrics, contributing an average income of $32,364 to the company.
The progression from the Top Customer segment to the Immediate Action segment reveals an ascending trend in average income levels, mirroring the observed monetary scores and emphasizing a positive correlation between income and purchasing behavior among customers.
2. DATA PROCESSING
Prior to RFM analysis, meticulous data cleaning and preprocessing were executed in Microsoft Excel. This included handling missing values, duplicates, and standardizing data formats. While some columns were eliminated through feature selection, others were aggregated to create new columns. This was to ensure the accuracy and reliability of the dataset for meaningful analysis.
3. CUSTOMER SEGMENTATION USING RFM
3.1 Calculate RFM scores using Percent Rank
To calculate the RFM scores, the Recency, Frequency and Monetary Value were computed using the PercentRank.inc function in Excel. This function assigns a rank to a value within a dataset as a percentage, generating values ranging from 0 to 1. To correspond with our analysis, we scaled these values by multiplying them by 10, making them whole numbers within the range of 1 to 10.
For frequency and monetary value scores, the formula below was used. PERCENTRANK.INC($F:$F,F2,1) * 10
For Recency score, the formula was adjusted to figure in the concept of time within a year (365 days), ensuring that recency is appropriately determined in the scoring process. The formula below was implemented to allot Recency scores. =(1-PERCENTRANK.INC($R:$R,R2,1))*10
3.2 Create RFM Score
After generating the scores for the individual metrics, a new column named “RFM” was created by summing the individual scores for Recency, Frequency, and Monetary value. Furthermore, the Percentrank.inc function was used to determine the RFM score, ranging from 0 to 10, for each customer.
3.3 Customer Segmentation
Using VLOOKUP, customers are segmented into various categories based on their RFM scores. The outlined segmentation is displayed in the lookup table below:
4. PIVOT TABLE ANALYSIS
Pivot tables were created to analyze the clean data and understand the different characteristics of the customer segments. This is shown below.
● At Risk/Need Attention customers count to a total of 741, which is 34% of the total number of customers.
● Immediate Attention customers were 470 in total.
● Loyal Customers also summed up to 584.
● Top Customers were the least with a total of 418. That is 19% of the total customers.
5. SUMMARY AND RECOMMENDATIONS
In conclusion, this RFM-driven data analysis using Microsoft Excel yields a comprehensive understanding of customer segments and transactional behaviors. The customized recommendations below aim to improve strategic decisions for enhanced customer-centricity.
RECOMMENDATIONS:
IMMEDIATE ATTENTION CUSTOMERS
● Improved Customer Experience: Prompt and effective responses to their queries and concerns can enable their overall experience and increase their loyalty.
AT RISK/NEED ATTENTION CUSTOMERS
● Feedback Survey: Ask about their experiences and ways you can make them better. This can rebuild their interest and make them more active as customers.
LOYAL CUSTOMERS
● Loyalty Programs: Host special events like sales exclusively for loyal customers. Creating a sense of appreciation can increase their loyalty and motivate them to make repeated purchases.
TOP CUSTOMERS
● Personalized Recommendations: Based on their interest and purchase habits, product recommendations from past orders and complementary items can be made to them. Additionally, custom packaging or handwritten thank-you note are excellent customer service strategies to sustain their loyalty.
