Customer Segmentation Using RFM Model

Chaya Chaipitakporn

Data Analyst

Data Scientist

Google BigQuery

SQL

E-Commerce

👥 Customer Segmentation (RFM Model) – Showcase Project

Segmenting users based on behavior to enable targeted retention, reactivation, and loyalty marketing strategies.

🧩 Project Summary

This project demonstrates how to segment users using the RFM (Recency, Frequency, Monetary) framework, a classic method for identifying different customer lifecycle stages. The dataset is based on a public e-commerce sample from Google Analytics 4 and was used purely for showcase purposes.

⚠️ Disclaimer:

This segmentation is performed on public sample data and does not reflect actual business value. In a real-world scenario:

The time range, thresholds, and segmentation strategy would align with specific business goals

The output would support strategic planning, marketing automation, and ROI analysis

The scoring system would be tuned to the company’s specific customer journey and CLV model

📌 Business Problem

The client wanted to better understand their user base and improve retention, but had no clear visibility into who their high-value customers were, who was churning, or which users showed potential. A behavioral segmentation model was needed to unlock campaign opportunities and guide resource allocation.

⚙️ My Approach

Queried GA4 raw ecommerce data in BigQuery (purchase events only)

Built RFM scores for each user:

Recency: Days since last purchase

Frequency: Total number of purchases

Monetary: Total spend

Ranked each user from 1 to 5 for R, F, and M, then classified them into behavioral segments

Aggregated users by group to visualize lifecycle distribution

Provided a framework for strategic CLV planning and ROI modeling based on segment movement

📊 Segment Distribution (Based on GA4 Sample)

🧠 Segment Strategy Map

Each group reflects a specific customer state based on recency and engagement value (frequency + spend):

🔵 Champions – Best customers, high RFM. Maintain with rewards & early access.

🟢 Loyal / Potential Loyalists – Core revenue drivers. Reward and nurture.

🟠 Needs Attention / Hibernating – Were once valuable, now dormant. Reactivation is critical.

🟣 Recent / Promising / Price Sensitive – New or growing users. Nurture toward higher value.

⚫ Lost – Very low engagement. Minimal return expected.

🔍 Strategic Planning Use Case

In real consulting settings, this RFM model is used not just for insight, but for business impact modeling:

✅ CLV by Segment:

Each segment’s average Customer Lifetime Value (CLV) can be estimated. For example, Champions may generate 10x the revenue of Promising users.

✅ ROI Modeling by Movement:

We simulate: "What if we can move 20% of Potential Loyalists into Loyal Customers?"

→ The ROI from that shift might outweigh moving Lost users up one level.

✅ Action Prioritization:

We found that improving Frequency scores from 3 to 4 (mid → high) has more return than moving users from 1 to 2 (low → mid).

→ This allows targeted spending on lifecycle campaigns where they matter most.

✅ Effort vs Value Map:

Segments are ranked by ease of movement vs value gained. For example:

Promising → Potential Loyalist = High success rate

At Risk → Loyal = Medium effort, high ROI

Hibernating → Active = High cost, low return

This forms the basis for campaign sequencing, incentive planning, and budgeting.

✅ Strategic Recommendations

Create segment-specific automations:

Champions → surprise rewards, referral programs

Loyal / Potential Loyalist → upsell, cross-sell, personalized perks

Hibernating / Can’t Lose Them → reactivation with urgency-based incentives

Monitor recency & frequency shifts monthly and re-score to track segment migration

Use CLV estimates per segment to guide marketing investment

Test behavior-based thresholds (not just time-based) for lifecycle targeting

🛠️ Tools & Techniques

BigQuery • GA4 • SQL (Window Functions) • RFM Scoring • Customer Lifecycle Strategy • CLV Modeling • Segment ROI Simulation

Like this project

Posted Apr 18, 2025

Segmented users using RFM model for targeted marketing strategies.

Likes

Views