RFM Analysis Report

Date: 9/8/2024

This report provides a comprehensive analysis of customer segments based on Recency, Frequency, and Monetary (RFM) metrics. My analysis, conducted using Power BI and SQL in BigQuery, reveals key insights into customer behavior, spending patterns, and the distribution of revenue across different segments. A total of 4,331 customers were analyzed, with €7.95 million in total sales recorded. Key findings highlight that 67.21% of total revenue is generated by the top customer segment, demonstrating significant concentration among high-value customers. The analysis also reveals areas for improvement, particularly in re-engaging at-risk and lost customers.

Key Performance Indicators (KPIs)

• Total Customers: 4,331
• Total Sales: €7.95 million
• Average Recency: 91.36 days
• Average Frequency: 4.91 purchases
• Top Segment Revenue: €5.35 million
• Percentage of Revenue from Top Segment: 67.21%
• Average Purchase Value: €1.84K

RFM Segment Overview

1. Champions (21% of Customers)

• Recency (Avg): 10.63 days
• Frequency (Avg): 13.13
• Monetary (Avg): €5,741.27
• Action Plan: Champions are the most valuable customers, contributing significantly to overall revenue. To maintain this segment’s engagement, it is essential to implement loyalty programs and personalized marketing campaigns. These customers should be continuously nurtured through exclusive offers and early access to new products.

2. Customers Needing Attention (16%)

• Recency (Avg): 96.42 days
• Frequency (Avg): 1.77
• Monetary (Avg): €491.31
• Action Plan: This segment represents customers whose engagement is declining. A focused reactivation strategy should be applied, using targeted email marketing campaigns and personalized discounts to rekindle interest.

3. Loyal Customers (16%)

• Recency (Avg): 32.90 days
• Frequency (Avg): 5.10
• Monetary (Avg): €1,604.86
• Action Plan: Loyal customers display consistent purchasing patterns, though their average spend is lower than the champions. Consider offering upselling and cross-selling opportunities, product recommendations, and personalized rewards to increase average transaction size.

4. At Risk (9%)

• Recency (Avg): 155.17 days
• Frequency (Avg): 4.36
• Monetary (Avg): €1,418.72
• Action Plan: These customers were once highly engaged but have not made a purchase in a considerable time. Immediate win-back campaigns should be prioritized, with personalized communication that emphasizes the value they previously received.

5. Lost Customers (7%)

• Recency (Avg): 281.69 days
• Frequency (Avg): 1.00
• Monetary (Avg): €117.25
• Action Plan: Given their extended inactivity and low monetary contribution, minimal resources should be allocated to trying to re-engage lost customers. Efforts can be focused on lower-cost reactivation strategies or simply acknowledging that they are unlikely to return.

6. Other Segments

• Promising (4%): These customers show potential for growth but require nurturing.
• Potential Loyalists (13%): Engaged customers who are on the path to becoming loyal if retention efforts are sustained.

SQL Code for RFM Extraction

To perform this analysis, I utilized the following SQL code in BigQuery to extract and compute Recency, Frequency, and Monetary values, along with customer segmentation based on percentile scoring. Below is the SQL query used:

WITH 
-- Compute Recency, Frequency, and Monetary Values
t1 AS (
    SELECT  
        CustomerID,
        Country,
        MAX(InvoiceDate) AS last_purchase_date,
        COUNT(DISTINCT InvoiceNo) AS frequency,
        SUM(UnitPrice * Quantity) AS monetary 
    FROM turing_data_analytics.rfm
    WHERE InvoiceDate BETWEEN '2010-12-01' AND '2011-12-01'
    GROUP BY CustomerID, Country 
),
-- Compute Recency
t2 AS (
    SELECT *,
        DATE_DIFF(DATE('2011-12-01'), DATE(last_purchase_date), DAY) AS recency
    FROM (
        SELECT *,
            DATE_ADD(DATE(MAX(last_purchase_date) OVER ()), INTERVAL 1 DAY) AS reference_date
        FROM t1
    )  
),
t3 AS (
    SELECT 
        a.*,
        -- All percentiles for MONETARY
        b.percentiles[OFFSET(20)] AS m20, 
        b.percentiles[OFFSET(40)] AS m40,
        b.percentiles[OFFSET(60)] AS m60, 
        b.percentiles[OFFSET(80)] AS m80,
        b.percentiles[OFFSET(100)] AS m100,    
        -- All percentiles for FREQUENCY
        c.percentiles[OFFSET(20)] AS f20, 
        c.percentiles[OFFSET(40)] AS f40,
        c.percentiles[OFFSET(60)] AS f60, 
        c.percentiles[OFFSET(80)] AS f80,
        c.percentiles[OFFSET(100)] AS f100,    
        -- All percentiles for RECENCY
        d.percentiles[OFFSET(20)] AS r20, 
        d.percentiles[OFFSET(40)] AS r40,
        d.percentiles[OFFSET(60)] AS r60, 
        d.percentiles[OFFSET(80)] AS r80,
        d.percentiles[OFFSET(100)] AS r100
    FROM 
        t2 a,
        (SELECT APPROX_QUANTILES(monetary, 100) AS percentiles FROM t2) b,
        (SELECT APPROX_QUANTILES(frequency, 100) AS percentiles FROM t2) c,
        (SELECT APPROX_QUANTILES(recency, 100) AS percentiles FROM t2) d
),
t4 AS (
    SELECT *, 
        CAST(ROUND((f_score + m_score) / 2, 0) AS INT64) AS fm_score
    FROM (
        SELECT *, 
            CASE 
                WHEN monetary <= m20 THEN 1
                WHEN monetary <= m40 AND monetary > m20 THEN 2 
                WHEN monetary <= m60 AND monetary > m40 THEN 3 
                WHEN monetary <= m80 AND monetary > m60 THEN 4 
                WHEN monetary <= m100 AND monetary > m80 THEN 5
            END AS m_score,
            CASE 
                WHEN frequency <= f20 THEN 1
                WHEN frequency <= f40 AND frequency > f20 THEN 2 
                WHEN frequency <= f60 AND frequency > f40 THEN 3 
                WHEN frequency <= f80 AND frequency > f60 THEN 4 
                WHEN frequency <= f100 AND frequency > f80 THEN 5
            END AS f_score,
            -- Recency scoring is reversed
            CASE 
                WHEN recency <= r20 THEN 5
                WHEN recency <= r40 AND recency > r20 THEN 4 
                WHEN recency <= r60 AND recency > r40 THEN 3 
                WHEN recency <= r80 AND recency > r60 THEN 2 
                WHEN recency <= r100 AND recency > r80 THEN 1
            END AS r_score
        FROM t3
    )
),
t5 AS (
    SELECT 
        CustomerID, 
        Country,
        recency,
        frequency, 
        monetary,
        r_score,
        f_score,
        m_score,
        fm_score,
        CASE 
            WHEN (r_score = 5 AND fm_score = 5) 
                OR (r_score = 5 AND fm_score = 4) 
                OR (r_score = 4 AND fm_score = 5) 
            THEN 'Champions'
            WHEN (r_score = 5 AND fm_score = 3) 
                OR (r_score = 4 AND fm_score = 4)
                OR (r_score = 3 AND fm_score = 5)
                OR (r_score = 3 AND fm_score = 4)
            THEN 'Loyal Customers'
            WHEN (r_score = 5 AND fm_score = 2) 
                OR (r_score = 4 AND fm_score = 2)
                OR (r_score = 3 AND fm_score = 3)
                OR (r_score = 4 AND fm_score = 3)
            THEN 'Potential Loyalists'
            WHEN r_score = 5 AND fm_score = 1 THEN 'Recent Customers'
            WHEN (r_score = 4 AND fm_score = 1) 
                OR (r_score = 3 AND fm_score = 1)
            THEN 'Promising'
            WHEN (r_score = 3 AND fm_score = 2) 
                OR (r_score = 2 AND fm_score = 3)
                OR (r_score = 2 AND fm_score = 2)
            THEN 'Customers Needing Attention'
            WHEN r_score = 2 AND fm_score = 1 THEN 'About to Sleep'
            WHEN (r_score = 2 AND fm_score = 5) 
                OR (r_score = 2 AND fm_score = 4)
                OR (r_score = 1 AND fm_score = 3)
            THEN 'At Risk'
            WHEN (r_score = 1 AND fm_score = 5)
                OR (r_score = 1 AND fm_score = 4)        
            THEN 'Cant Lose Them'
            WHEN r_score = 1 AND fm_score = 2 THEN 'Hibernating'
            WHEN r_score = 1 AND fm_score = 1 THEN 'Lost'
        END AS rfm_segment 
    FROM t4
)
SELECT * FROM t5;

This query enabled the segmentation of customers into various RFM groups, allowing me to derive insights on their purchasing behavior and tailor marketing strategies accordingly.

Country-Specific Insights

The analysis reveals significant variation in customer behavior and spending across different regions. Here are the key takeaways:

Netherlands

• Average Price per Purchase: €2,785.04
• Recency: 94.89 days
• Frequency: 10.89
• Monetary (Avg): €30,325.95
• Insight: Netherlands represents one of the highest-value markets. Customers here make large purchases relatively frequently. To capitalize on this, a strategy focusing on product recommendations and exclusive access could encourage even more frequent purchasing.

Singapore

• Average Price per Purchase: €912.04
• Recency: 43 days
• Frequency: 10.00
• Monetary (Avg): €9,120.39
• Insight: Singapore also has high-value customers, but the market size is small. Consider expanding customer acquisition efforts here to grow this already valuable segment.

United Kingdom

• Customers: 3,916
• Monetary (Avg): €1,652.18
• Insight: The UK represents the largest customer base, though with relatively lower monetary value compared to other countries. There’s an opportunity to implement up-selling tactics to increase the average spend per customer in this market.

Countries with High Recency (e.g., Lebanon, Lithuania)

• Customers in Lebanon and Lithuania have not made purchases in over 300 days.
• Action Plan: Immediate attention should be given to re-engagement campaigns in these markets to understand customer needs and reignite interest.

Revenue Concentration and Risk Mitigation

A critical finding from the RFM analysis is that 67.21% of total revenue is generated by the top customer segment. While this highlights the importance of high-value customers, it also poses a significant risk. A downturn in engagement from this segment could lead to a sharp revenue decline. As such, diversifying the customer base is essential. By nurturing segments such as Potential Loyalists and Promising, the business can reduce its reliance on top customers and build a more stable revenue stream.

Recommendations and Actionable Steps

1. Focus on Retention for High-Value Customers: Maintain strong relationships with Champions and Loyal Customers by continuing personalized, high-touch engagement strategies.
2. Reactivation Campaigns: Implement targeted reactivation efforts for segments like Customers Needing Attention and At Risk. Tailor communication to remind them of their positive experiences with the brand, using personalized offers or exclusive promotions.
3. Country-Specific Strategies: Focus on expanding customer bases in high-value regions such as the Netherlands and Singapore, while also working to re-engage customers in markets like Lebanon and Lithuania.
4. Revenue Diversification: Broaden the focus beyond top-performing segments. Invest in strategies to nurture Promising and Potential Loyalists, ensuring that the business is not overly reliant on a small group of high-value customers.

Conclusion

RFM

The RFM analysis, combined with the SQL extraction, provides critical insights into customer behavior, highlighting both opportunities and risks. By focusing on retention, re-engagement, and market expansion strategies, the business can continue to grow its revenue base while minimizing potential risks related to revenue concentration. Future efforts should prioritize building stronger relationships across all customer segments, with tailored approaches for each group.