Customer Segmentation (RFM Analysis)

Project Overview:

An RFM Analysis (Recency, Frequency, Monetary) is a method used by companies to identify the most valuable customers. Analyzing customer behavior helps you create personalized marketing strategies that build loyalty and drive long-term value. RFM analysis identifies which customers to prioritize, which to nurture, and which have less impact on your business. Each RFM component highlights a distinct aspect of customer behavior.

Recency –The most recent purchase made by a customer. Shows that customers are engaged are likely to make another purchase in the future.

Frequency – How often a customer makes a purchase. This variable measures the number of purchases a customer has made over a certain period of time, usually between 6 to 12 months. This segments customers who make less purchases in this timeframe compared to those that purchase more. Customers who make more purchases demonstrate loyalty.

Monetary – How much a customer spends. This variable measures the total revenue a customer has generated over a defined period. The higher the revenue, the more incentives offered to maintain high retention.

Tools Used:

  • Excel
  • MySQL

Business Request:

Management would like to rank and group Loyal, Premium, Returning and New Customers. The objective is to segment customers based on monetary value, most valuable customers and least valuable. Recommendations are suggested to target inactive customers with marketing tactics to win back their business.

Dataset Used:

Click on Image to access Dataset:

  • Dataset shows customer purchases from 2023 – 2026.

Process:

Data Filtering – The original dataset contains customer purchase records made in a three-year period. Irrelevant data was filtered resulting in information needed to find trends and derive insights.

Data Transformation – MySQL was used to find trends and calculate the RFM score. More information such as total overall orders, amount of money spent, monthly percentage share was explored.

RFM calculation – The RFM calculation resulted in grouping customers in segments. These segments each suggest different customer spending habits; “Loyal” being frequent spenders and “Hibernating” customers being the lowest and inactive spenders.

Data Transformation – SQL

The following code was used to calculate the RFM score:

SQL
WITH rfm_base AS (
    SELECT
        Customer_ID,
        MAX(Order_Date) AS last_order_date,
        COUNT(DISTINCT Order_ID) AS frequency,
        SUM(Order_Amount) AS monetary
    FROM ecommerce_orders_dataset
    GROUP BY Customer_ID
),
rfm_with_recency AS (
    SELECT
        b.Customer_ID,
        DATEDIFF(
            (SELECT MAX(Order_Date) FROM ecommerce_orders_dataset),
            b.last_order_date
        ) AS recency,
        b.frequency,
        b.monetary
    FROM rfm_base b
),
rfm_scored AS (
    SELECT
        r.Customer_ID,
        r.recency,
        r.frequency,
        r.monetary,
        NTILE(5) OVER (ORDER BY r.recency ASC) AS r_score,
        NTILE(5) OVER (ORDER BY r.frequency DESC) AS f_score,
        NTILE(5) OVER (ORDER BY r.monetary DESC) AS m_score
    FROM rfm_with_recency r
),
rfm_segmented AS (
    SELECT
        Customer_ID,
        recency,
        frequency,
        monetary,
        r_score,
        f_score,
        m_score,
        CONCAT(r_score, f_score, m_score) AS rfm_score,
        CASE
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
            WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
            WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Potential Loyalist'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'Recent Customers'
            WHEN r_score = 3 AND f_score <= 2 THEN 'Promising'
            WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
            WHEN r_score = 1 AND f_score >= 3 THEN 'Can’t Lose Them'
            WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Hibernating'
            ELSE 'Needs Attention'
        END AS segment
    FROM rfm_scored
)
SELECT *
FROM rfm_segmented
ORDER BY rfm_score DESC;

Result:

Chart Visualization:

  • Loyal Customers – Make purchases regularly scoring high in the frequency category.
  • Champions – Top value customers, score high in all three categories.
  • Promising – Customers that have made a recent purchase, show moderate frequency and monetary value. Potential to be loyal customers with the right marketing strategy.
  • Potential Loyalist – A level above Promising Customers. High recency, frequency and moderate monetary score. Marketing strategy would be introducing loyalty programs and tailored promotions.
  • Recent – Customers that have recently made a first-time purchase.
  • Needs Attention – Customers that have been active but slowly drifting away, frequency starting to decline.
  • At risk – High frequency and Monetary, low recency score. These are high-value customers that haven’t purchased in a long time.
  • Can’t lose them – High Frequency and Monetary, low recency. High-value customers that are profitable, requiring re-engagement tactics to trigger another purchase.
  • Hibernating – Customers with lowest scores on all three categories. Low engagement and low spend customers at risk of being lost.

RFM Profile:

Additional Data Exploration:

Charts based on Findings:

  • Loyal and Premium customers make up about 17% of the customer base, with Returning customers at 39% and New Customers at 44% as of the current date. Not much fluctuation has happened in a three-year period.
  • Returning customers have the most purchases overall, showing total amount spent at roughly $4.5 Million. Premium Customers show least amount spent at roughly $1.1 Million.
  • Percentage share chart shows which segment generates the most revenue per year. New customers generated the most revenue in 2023, Premium customers in 2024, Premium Customers in 2025, and Loyal customers in 2026.
  • Electronic products have had the most sales every year.

Recommendations:

Hibernating Customers Strategy – Attempt a free automated email. If there is no response within 90 days, remove from advertisement targeting to minimize budget. Also, offer discounts and limited time offers to create urgency.

Can’t Lose Them/At Risk Customers Strategy – Offer discounts on feedback surveys. Win back emails with strong incentives such as 30% off.

Needs Attention Customers Strategy – Targeted Ads and social media campaigns that offer time-sensitive promotions and discounts. Focus on previous purchases and re-engaging with similar products.

Recent Customers Strategy – Personalized email as a welcome and a limited time offer to trigger a second purchase.

Potential Loyalist/Promising Customers Strategy – Upselling, introducing loyalty programs and re-engaging with targeted offers.

Champion and Loyal customers demonstrate high RFM scores, which signals that they are the most valuable repeat customers. They have access to exclusive offers and experiences, and they can also be brand advocates. Strategies to maintain high retention would be:

  • Rewarding champions with VIP discounts or early access to new products. Increasing points per dollar spent, introduce tier rewards that scale with spending. Turn them into brand advocates by encouraging them to share photos, reviews or videos on social media.
  • Offering loyal customers with event invitations, product launches and behind the scenes content. Public loyalty acknowledgement to boost morale. Sending small gifts on birthdays, anniversaries as a thank-you.