Customer Segmentation (RFM)

Project Overview

Working from a stakeholder-driven business scenario, I analyzed transactional retail data to better understand customer purchasing behavior and identify opportunities to improve retention, loyalty, and revenue growth.

Using MySQL, AWS RDS, and Tableau, I transformed raw transaction records into business insights and developed an RFM (Recency, Frequency, Monetary) segmentation framework to support targeted marketing strategies.

The analysis highlighted opportunities to re-engage dormant customers, increase spending among active customers, and nurture emerging high-value customers.

Business Requirements

The analysis needed to provide:

A business performance overview
Product performance insights
Customer purchasing patterns
Customer segmentation based on value and engagement
Recommendations to support retention and marketing initiatives

The final deliverable aimed to identify opportunities to retain high-value customers, re-engage dormant customers, and nurture potential loyalists through targeted actions.

Methodology

Cleaned and validated transactional retail data using MySQL.
Removed duplicate, missing, and erroneous records.
Analyzed revenue, orders, customer activity, and product performance.
Assessed purchasing behaviour across time periods and customer groups.
Developed an RFM segmentation framework using business-defined scoring thresholds.
Used Tableau box plots to validate segmentation thresholds and customer value distributions.

Executive Summary

Generated £254,172 in revenue from 716 orders placed by 110 customers.
Approximately 60% of annual revenue was generated between August and November, highlighting strong seasonality.
Around 25% of products were purchased only once, indicating potential opportunities for product portfolio optimization.
Most purchases occurred during the afternoon (66%), suggesting an optimal timeframe for marketing campaigns.
Customer segmentation revealed that 30% of customers were loyal or high-value customers, while 25% showed signs of disengagement and may require retention efforts.

Customer Insights

While customer #12748 placed the highest number of orders (209), customer #12931 generated the highest revenue (£41,519) through only 15 purchases. This illustrates how purchase frequency alone may not accurately reflect customer value.

This observation reinforces the value of an RFM (Recency, Frequency, Monetary) framework, which provides a more holistic view of customer engagement and value.

Most Active Customer
Customer #12748
209 orders
£33,053 revenue

Highest-Value Customer
Customer #12931
15 orders
£41,519 revenue

Customer Segmentation

Using an RFM framework, customers were grouped according to their purchasing recency, frequency, and monetary value.

The analysis found that:

29% required attention and presented potential churn risk.
26% of customers belonged to loyal or top-performing segments.
21% were identified as potential loyalists.
17% were classified as inactive or lost customers.
6% belonged to the best customers segment.

These findings highlighted opportunities to re-engage dormant customers, strengthen retention efforts for high-value segments, and nurture potential loyalists into long-term customers.

Segmentation Methodology

To classify customers according to their value and engagement, I developed an RFM (Recency, Frequency, Monetary) scoring model. Customer scores were first calculated from transaction history and then grouped into business-oriented segments ranging from Best Customer to Lost Customer.

Image Description

The box plots were used to identify appropriate scoring thresholds for purchase frequency and customer spending, helping align segment definitions with observed customer behavior.

Framework:

Recency: How recently a customer made a purchase.
Frequency: How often a customer purchased.
Monetary: How much revenue a customer generated.

Example of the scoring logic:

CASE
    WHEN recency <= 30 THEN 5
    WHEN recency <= 60 THEN 4
    WHEN recency <= 120 THEN 3
    ELSE 1
END AS recency_score

The individual scores were then combined to assign customers to segments:

CASE
	WHEN (recency + frequency + monetary) = 15
		THEN 'Best Customer'
	WHEN (recency + frequency + monetary) >= 12
		THEN 'Loyal Customers'
	WHEN (recency + frequency + monetary) BETWEEN 9 AND 11
		THEN 'Potential Loyalists'
	WHEN (recency + frequency + monetary) BETWEEN 6 AND 8
		THEN 'Customers Needing Attention'
	WHEN (recency + frequency + monetary) BETWEEN 4 AND 5
		THEN 'Inactive'
	ELSE 'Lost'
END AS customer_segment

Image Description

Business Questions

Reactivating Valuable Customers

Identify previously high-value customers who have become inactive and may benefit from retention campaigns.

SELECT *
FROM rfm_segm
WHERE recency <= 2
AND frequency >= 4
AND monetary >= 4;

Upselling Active Customers

Identify engaged customers with potential to increase spending.

SELECT *

FROM rfm_segm
WHERE recency >= 4
AND frequency >= 4
AND monetary <= 3;

Nurturing Emerging High-Value Customers

Identify newer customers showing strong revenue potential.

SELECT *

FROM rfm_segm
WHERE recency >= 4
AND frequency = 2
AND monetary >= 4;

Assumption and caveats

More than 365 days of data

The data frame spans from December 1st, 2010 to December 9th, 2011. While this additional week slightly extends the one-year window, its impact on the overall accuracy of the extracted insights is minimal.

Sources

Data and overall project structure: RFM-Based-Customer-Segmentation.
RFM segmentation by Optimove

Skills applied

SQL · Data Cleaning · Exploratory Data Analysis (EDA) · Tableau · Business Analysis · Customer Segmentation · RFM Analysis