This document provides a comprehensive analysis of all Key Performance Indicators (KPIs) calculated in the RFM (Recency, Frequency, Monetary) customer segmentation SQL query. The query uses a multi-CTE approach to segment customers into actionable marketing groups with specific recommendations.
FirstPurchaseDate represents the date of a customer’s first purchase transaction. This marks the beginning of the customer relationship and is used for cohort analysis and customer age calculations.
SQL Expression:
MIN(fis.OrderDate) AS FirstPurchaseDate
Mathematical Formula: \(\text{FirstPurchaseDate} = \min_{i}(\text{OrderDate}_i)\)
where $i$ ranges over all orders for the customer.
Business Purpose: Establishes the customer’s entry point into the business ecosystem, enabling age-based analysis and cohort tracking.
LastPurchaseDate represents the date of a customer’s most recent purchase. This is the foundation for recency analysis in RFM segmentation.
SQL Expression:
MAX(fis.OrderDate) AS LastPurchaseDate
Mathematical Formula: \(\text{LastPurchaseDate} = \max_{i}(\text{OrderDate}_i)\)
Business Purpose: Critical for determining customer activity status and identifying at-risk or churned customers.
TotalOrders counts the number of distinct purchase orders a customer has placed. This is the foundation for the Frequency dimension in RFM analysis.
SQL Expression:
COUNT(DISTINCT fis.SalesOrderNumber) AS TotalOrders
Mathematical Formula: \(\text{TotalOrders} = |\{\text{SalesOrderNumber}_i : i \in \text{all transactions}\}|\)
| where $ | \cdot | $ denotes cardinality (count of unique values). |
Business Purpose: Measures purchase frequency and customer engagement level. Higher values indicate more frequent purchasers.
DaysSinceLastPurchase calculates the number of days from the customer’s most recent purchase to the current date. This is the raw recency metric.
SQL Expression:
(CURRENT_DATE - MAX(fis.OrderDate)::DATE) AS DaysSinceLastPurchase
Mathematical Formula: \(\text{DaysSinceLastPurchase} = \text{CURRENT\_DATE} - \text{LastPurchaseDate}\)
Business Purpose: Direct measure of customer recency, used to calculate RecencyScore and identify churn risk.
Critical Thresholds (used in RecencyScore):
480 days: Churned
CustomerTenureDays measures the span of days between a customer’s first and last purchase, representing the duration of their active relationship.
SQL Expression:
(MAX(fis.OrderDate)::DATE - MIN(fis.OrderDate)::DATE) AS CustomerTenureDays
Mathematical Formula: \(\text{CustomerTenureDays} = \text{LastPurchaseDate} - \text{FirstPurchaseDate}\)
Business Purpose: Indicates the length of the customer relationship. Used to differentiate new customers from longstanding ones.
Note: Single-purchase customers have 0 tenure days.
TotalRevenue sums all sales amounts across all customer purchases, representing their lifetime monetary contribution. This forms the Monetary dimension in RFM analysis.
SQL Expression:
ROUND(SUM(fis.SalesAmount), 2) AS TotalRevenue
Mathematical Formula: \(\text{TotalRevenue} = \sum_{i=1}^{n} \text{SalesAmount}_i\)
where $n$ is the total number of line items purchased.
Business Purpose: Primary monetary value metric for customer ranking. Used in MonetaryScore calculation.
TotalGrossProfit calculates the cumulative gross profit (revenue minus product costs) generated by the customer, representing true margin contribution.
SQL Expression:
ROUND(SUM(fis.SalesAmount - fis.TotalProductCost), 2) AS TotalGrossProfit
Mathematical Formula: \(\text{TotalGrossProfit} = \sum_{i=1}^{n} (\text{SalesAmount}_i - \text{TotalProductCost}_i)\)
Business Purpose: More accurate value assessment than revenue alone. Identifies customers who buy high-margin vs. low-margin products.
AvgTransactionValue calculates the average sales amount per line item across all customer purchases, indicating typical transaction size.
SQL Expression:
ROUND(AVG(fis.SalesAmount), 2) AS AvgTransactionValue
Mathematical Formula: \(\text{AvgTransactionValue} = \frac{1}{n}\sum_{i=1}^{n} \text{SalesAmount}_i\)
where $n$ is the total number of line items.
Business Purpose: Indicates price point preference and basket size characteristics.
RecencyScore assigns a 1-5 score based on how recently the customer made their last purchase, with 5 being most recent. This quantifies the “R” in RFM analysis.
SQL Expression:
CASE
WHEN cph.DaysSinceLastPurchase <= 60 THEN 5
WHEN cph.DaysSinceLastPurchase <= 120 THEN 4
WHEN cph.DaysSinceLastPurchase <= 240 THEN 3
WHEN cph.DaysSinceLastPurchase <= 480 THEN 2
ELSE 1
END AS RecencyScore
Mathematical Formula (Piecewise Function): \(\text{RecencyScore} = \begin{cases} 5 & \text{if } d \leq 60 \\ 4 & \text{if } 60 < d \leq 120 \\ 3 & \text{if } 120 < d \leq 240 \\ 2 & \text{if } 240 < d \leq 480 \\ 1 & \text{if } d > 480 \end{cases}\)
where $d = \text{DaysSinceLastPurchase}$.
Range: 1 to 5 (higher is better)
Business Purpose: Standardizes recency into a comparable score. Recency is the strongest predictor of future purchase behavior in RFM analysis.
Interpretation:
FrequencyScore assigns customers to quintiles (1-5) based on total order count, with 5 representing the most frequent purchasers. This quantifies the “F” in RFM analysis.
SQL Expression:
NTILE(5) OVER (ORDER BY cph.TotalOrders ASC) AS FrequencyScore
Mathematical Formula: \(\text{FrequencyScore} = \text{NTILE}_5(\text{TotalOrders}_{\text{asc}})\)
This divides customers into 5 equal-sized groups based on their TotalOrders rank (ascending order).
Range: 1 to 5 (higher is better)
Business Purpose: Standardizes purchase frequency into comparable quintiles, enabling cross-customer comparison regardless of absolute order counts.
With 1,000 customers:
Note: Using ASC ordering means higher order counts get higher scores.
MonetaryScore assigns customers to quintiles (1-5) based on total revenue, with 5 representing the highest-value customers. This quantifies the “M” in RFM analysis.
SQL Expression:
NTILE(5) OVER (ORDER BY cph.TotalRevenue ASC) AS MonetaryScore
Mathematical Formula: \(\text{MonetaryScore} = \text{NTILE}_5(\text{TotalRevenue}_{\text{asc}})\)
Range: 1 to 5 (higher is better)
Business Purpose: Normalizes monetary value into comparable quintiles. Often reveals Pareto distribution (top 20% generate 60-80% of revenue).
With $10M total revenue across 1,000 customers:
RFMString concatenates the three RFM scores into a single 3-digit identifier (e.g., “555”, “321”) that uniquely identifies the customer’s RFM profile.
SQL Expression:
CAST(rfm.RecencyScore AS TEXT) || CAST(rfm.FrequencyScore AS TEXT) || CAST(rfm.MonetaryScore AS TEXT) AS RFMString
Mathematical Formula: \(\text{RFMString} = \text{concat}(R, F, M)\)
where $R, F, M \in {1,2,3,4,5}$.
Range: 111 to 555 (125 possible combinations)
Business Purpose: Provides a compact representation of customer profile for quick identification and segment grouping.
Pattern Analysis:
AvgRFMScore calculates the arithmetic mean of the three RFM scores, providing a single composite metric of overall customer quality.
SQL Expression:
ROUND((rfm.RecencyScore + rfm.FrequencyScore + rfm.MonetaryScore) / 3.0, 2) AS AvgRFMScore
Mathematical Formula: \(\text{AvgRFMScore} = \frac{R + F + M}{3}\)
where $R, F, M \in {1,2,3,4,5}$.
Range: 1.00 to 5.00
Business Purpose: Simplifies customer comparison with a single metric. Useful for quick ranking when detailed segment analysis isn’t needed.
Limitation: Simple average treats all dimensions equally, but recency is typically most predictive. Segment-based classification (RFMSegment) is more nuanced.
RFMSegment classifies customers into 11 strategic marketing segments based on their RFM score patterns. This is the core output of RFM analysis, translating scores into actionable business categories.
SQL Expression:
CASE
WHEN rfm.RecencyScore >= 4 AND rfm.FrequencyScore >= 4 AND rfm.MonetaryScore >= 4 THEN 'Champions'
WHEN rfm.FrequencyScore >= 4 AND rfm.MonetaryScore >= 4 AND rfm.RecencyScore >= 3 THEN 'Loyal Customers'
WHEN rfm.RecencyScore >= 4 AND rfm.FrequencyScore >= 3 AND rfm.MonetaryScore >= 3 THEN 'Potential Loyalists'
WHEN rfm.RecencyScore >= 4 AND rfm.FrequencyScore <= 2 THEN 'New Customers'
WHEN rfm.RecencyScore >= 4 AND rfm.FrequencyScore <= 3 AND rfm.MonetaryScore <= 3 THEN 'Promising'
WHEN rfm.RecencyScore = 3 AND rfm.FrequencyScore >= 3 AND rfm.MonetaryScore >= 3 THEN 'Need Attention'
WHEN rfm.RecencyScore = 2 AND rfm.FrequencyScore >= 3 AND rfm.MonetaryScore >= 3 THEN 'About To Sleep'
WHEN rfm.RecencyScore <= 2 AND rfm.FrequencyScore >= 4 AND rfm.MonetaryScore >= 4 THEN 'At Risk'
WHEN rfm.RecencyScore = 1 AND rfm.FrequencyScore >= 4 AND rfm.MonetaryScore >= 4 THEN 'Cannot Lose Them'
WHEN rfm.RecencyScore <= 2 AND rfm.FrequencyScore <= 3 AND rfm.MonetaryScore <= 3 THEN 'Hibernating'
WHEN rfm.RecencyScore = 1 AND rfm.FrequencyScore <= 2 AND rfm.MonetaryScore <= 2 THEN 'Lost'
ELSE 'Others'
END AS RFMSegment
Segments Defined:
Criteria: $R \geq 4$ AND $F \geq 4$ AND $M \geq 4$
Profile: Recent, frequent, high-value customers. Best customers.
Example: RFM “555”, “544”, “454”
Criteria: $F \geq 4$ AND $M \geq 4$ AND $R \geq 3$
Profile: Frequent and valuable, but slightly less recent than Champions.
Example: RFM “345”, “354”
Criteria: $R \geq 4$ AND $F \geq 3$ AND $M \geq 3$
Profile: Recent with moderate frequency and value, showing loyalty potential.
Example: RFM “433”, “443”, “434”
Criteria: $R \geq 4$ AND $F \leq 2$
Profile: Recent purchasers but low frequency (new to the business).
Example: RFM “411”, “512”, “521”
Criteria: $R \geq 4$ AND $F \leq 3$ AND $M \leq 3$
Profile: Recent but with moderate-low frequency and value. Show promise.
Example: RFM “423”, “431”, “422”
Criteria: $R = 3$ AND $F \geq 3$ AND $M \geq 3$
Profile: Moderate recency with good frequency and value. Starting to decline.
Example: RFM “334”, “343”, “344”
Criteria: $R = 2$ AND $F \geq 3$ AND $M \geq 3$
Profile: Declining recency but were previously active and valuable.
Example: RFM “234”, “243”, “244”
Criteria: $R \leq 2$ AND $F \geq 4$ AND $M \geq 4$
Profile: Low recency but historically very frequent and valuable. High churn risk.
Example: RFM “145”, “245”, “154”
Criteria: $R = 1$ AND $F \geq 4$ AND $M \geq 4$
Profile: Worst recency but were champions. Critical recovery priority.
Example: RFM “145”, “154”, “155”
Note: Overlaps with “At Risk” but specifically the lowest recency (R=1).
Criteria: $R \leq 2$ AND $F \leq 3$ AND $M \leq 3$
Profile: Low recency, moderate-low frequency and value. Dormant.
Example: RFM “122”, “223”, “233”
Criteria: $R = 1$ AND $F \leq 2$ AND $M \leq 2$
Profile: Lowest scores across all dimensions. Effectively churned.
Example: RFM “111”, “121”, “112”
Fallback: Any patterns not matching above criteria.
Business Purpose: Enables targeted marketing strategies by grouping customers with similar behavioral patterns and needs.
Customer A: R=5, F=5, M=5 → “Champions”
Customer B: R=4, F=2, M=3 → “New Customers”
Customer C: R=2, F=4, M=5 → “About To Sleep”
Customer D: R=1, F=1, M=1 → “Lost”
CustomerCount aggregates the number of customers within each RFM segment, showing segment size distribution.
SQL Expression:
COUNT(*) AS CustomerCount
Mathematical Formula: \(\text{CustomerCount}_s = |\{c : \text{RFMSegment}(c) = s\}|\)
for each segment $s$.
Business Purpose: Reveals customer base composition. Identifies which segments are largest and may need specific attention.
Example distribution across 10,000 customers:
SegmentPct calculates what percentage of the total customer base each segment represents, enabling proportional understanding of segment sizes.
SQL Expression:
ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM RFMSegmentation), 2) AS SegmentPct
Mathematical Formula: \(\text{SegmentPct}_s = \frac{\text{CustomerCount}_s}{\sum_{s'} \text{CustomerCount}_{s'}} \times 100\)
Range: 0% to 100%, sum across all segments = 100%
Business Purpose: Provides relative segment sizing for resource allocation and benchmark comparison.
Industry Benchmarks:
AvgRecencyScore calculates the mean RecencyScore within each segment, validating that segment definitions align with recency patterns.
SQL Expression:
ROUND(AVG(rfms.RecencyScore), 2) AS AvgRecencyScore
Mathematical Formula: \(\text{AvgRecencyScore}_s = \frac{1}{n_s}\sum_{c \in s} \text{RecencyScore}(c)\)
where $n_s$ is the number of customers in segment $s$.
Business Purpose: Quality check for segmentation logic. Verifies expected recency patterns within segments.
Expected average recency by segment:
AvgFrequencyScore calculates the mean FrequencyScore within each segment, validating frequency patterns align with segment definitions.
SQL Expression:
ROUND(AVG(rfms.FrequencyScore), 2) AS AvgFrequencyScore
Mathematical Formula: \(\text{AvgFrequencyScore}_s = \frac{1}{n_s}\sum_{c \in s} \text{FrequencyScore}(c)\)
Business Purpose: Validates that segment classification correctly identifies frequency patterns.
Expected average frequency by segment:
AvgMonetaryScore calculates the mean MonetaryScore within each segment, confirming monetary value patterns.
SQL Expression:
ROUND(AVG(rfms.MonetaryScore), 2) AS AvgMonetaryScore
Mathematical Formula: \(\text{AvgMonetaryScore}_s = \frac{1}{n_s}\sum_{c \in s} \text{MonetaryScore}(c)\)
Business Purpose: Validates segment value characteristics and helps prioritize high-value segments.
Expected average monetary score by segment:
AvgDaysSinceLastPurchase calculates the mean days since last purchase for customers in each segment, providing concrete recency metrics in days.
SQL Expression:
ROUND(AVG(rfms.DaysSinceLastPurchase), 0) AS AvgDaysSinceLastPurchase
Mathematical Formula: \(\text{AvgDaysSinceLastPurchase}_s = \frac{1}{n_s}\sum_{c \in s} \text{DaysSinceLastPurchase}(c)\)
Business Purpose: Translates RecencyScore back into actionable time metrics. Helps set campaign timing.
Expected averages by segment:
Application: If “At Risk” averages 400 days, campaigns should trigger at 300-350 days to prevent reaching this stage.
AvgTotalOrders calculates the mean number of lifetime orders for customers in each segment.
SQL Expression:
ROUND(AVG(rfms.TotalOrders), 2) AS AvgTotalOrders
Mathematical Formula: \(\text{AvgTotalOrders}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalOrders}(c)\)
Business Purpose: Reveals typical engagement levels within segments. Validates frequency-based segment definitions.
Expected averages by segment:
AvgLifetimeRevenue calculates the mean total revenue per customer within each segment.
SQL Expression:
ROUND(AVG(rfms.TotalRevenue), 2) AS AvgLifetimeRevenue
Mathematical Formula: \(\text{AvgLifetimeRevenue}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalRevenue}(c)\)
Business Purpose: Quantifies average customer value by segment, informing marketing investment levels and prioritization.
Expected averages by segment (hypothetical retail business):
ROI Calculation: If recovering one “Cannot Lose Them” customer costs $500 in marketing but they average $28K lifetime, ROI is 56x.
AvgLifetimeProfit calculates the mean gross profit per customer within each segment, providing a margin-adjusted value metric.
SQL Expression:
ROUND(AVG(rfms.TotalGrossProfit), 2) AS AvgLifetimeProfit
Mathematical Formula: \(\text{AvgLifetimeProfit}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalGrossProfit}(c)\)
Business Purpose: More accurate than revenue for investment decisions, as it accounts for cost structure and product mix differences.
Expected averages by segment:
Application: If “At Risk” customers average $6K profit, spending up to $1K per customer on retention has strong ROI.
TotalSegmentRevenue sums the total revenue generated by all customers within a segment, showing absolute segment contribution.
SQL Expression:
ROUND(SUM(rfms.TotalRevenue), 2) AS TotalSegmentRevenue
Mathematical Formula: \(\text{TotalSegmentRevenue}_s = \sum_{c \in s} \text{TotalRevenue}(c)\)
Business Purpose: Identifies which segments drive total business revenue, often revealing Pareto distributions.
With $50M total revenue across segments:
Insight: Top 13% of customers (Champions + Loyal) drive 64% of revenue - classic Pareto.
RevenueSharePct calculates what percentage of total company revenue each segment generates, enabling proportional value assessment.
SQL Expression:
ROUND(100.0 * SUM(rfms.TotalRevenue) / (SELECT SUM(TotalRevenue) FROM RFMSegmentation), 2) AS RevenueSharePct
Mathematical Formula: \(\text{RevenueSharePct}_s = \frac{\text{TotalSegmentRevenue}_s}{\sum_{s'} \text{TotalSegmentRevenue}_{s'}} \times 100\)
Range: 0% to 100%, sum across all segments = 100%
Business Purpose: Reveals disproportionate value concentration. Guides resource allocation and risk assessment.
Typical distribution:
Strategic Insight: “At Risk” segment with 15% revenue share from 4% of customers represents major risk if they churn.
AvgYearlyIncome calculates the mean annual income of customers within each segment, revealing demographic-economic patterns.
SQL Expression:
ROUND(AVG(rfms.YearlyIncome), 2) AS AvgYearlyIncome
Mathematical Formula: \(\text{AvgYearlyIncome}_s = \frac{1}{n_s}\sum_{c \in s} \text{YearlyIncome}(c)\)
Business Purpose: Links RFM behavior to economic capacity. Helps tailor messaging and product recommendations by segment income levels.
Expected patterns:
Insight: If “Champions” have significantly higher income, consider whether lower-income segments are being adequately served with appropriate product lines.
MinLifetimeRevenue and MaxLifetimeRevenue show the range of customer values within each segment, revealing segment heterogeneity.
SQL Expression:
MIN(rfms.TotalRevenue) AS MinLifetimeRevenue,
MAX(rfms.TotalRevenue) AS MaxLifetimeRevenue
Mathematical Formula: \(\text{MinLifetimeRevenue}_s = \min_{c \in s}(\text{TotalRevenue}(c))\) \(\text{MaxLifetimeRevenue}_s = \max_{c \in s}(\text{TotalRevenue}(c))\)
Business Purpose: Identifies segments with high internal variance, suggesting potential for further sub-segmentation.
Champions segment range:
New Customers segment range:
Action: High-variance segments may benefit from additional stratification (e.g., “Champions - Tier 1” for >$100K customers).
MarketingAction provides specific, actionable marketing recommendations for each customer based on their RFM segment, translating analytical segments into operational tactics.
SQL Expression:
CASE
WHEN rfms.RFMSegment = 'Champions' THEN 'Reward with VIP benefits, early access, exclusive offers'
WHEN rfms.RFMSegment = 'Loyal Customers' THEN 'Upsell premium products, loyalty program, referral incentives'
WHEN rfms.RFMSegment = 'Potential Loyalists' THEN 'Nurture with membership offers, personalized recommendations'
WHEN rfms.RFMSegment = 'New Customers' THEN 'Onboarding campaigns, product education, welcome discounts'
WHEN rfms.RFMSegment = 'Promising' THEN 'Cross-sell campaigns, bundle offers, engagement emails'
WHEN rfms.RFMSegment = 'Need Attention' THEN 'Re-engagement campaigns, limited-time offers, feedback surveys'
WHEN rfms.RFMSegment = 'About To Sleep' THEN 'Win-back campaigns, personalized discounts, reminder emails'
WHEN rfms.RFMSegment = 'At Risk' THEN 'Urgent win-back offers, satisfaction surveys, retention discounts'
WHEN rfms.RFMSegment = 'Cannot Lose Them' THEN 'HIGH PRIORITY: Executive outreach, special recovery offers'
WHEN rfms.RFMSegment = 'Hibernating' THEN 'Low-cost reactivation, seasonal promotions, product updates'
WHEN rfms.RFMSegment = 'Lost' THEN 'Minimal investment, brand awareness only, or exclude from campaigns'
ELSE 'Standard marketing communications'
END AS MarketingAction
Recommended Actions by Segment:
Action: Reward with VIP benefits, early access, exclusive offers
Rationale: Already best customers, focus on retention and deepening relationship
Tactics: VIP events, exclusive product previews, concierge service, brand ambassador programs
Action: Upsell premium products, loyalty program, referral incentives
Rationale: Strong relationship established, ready for premium offerings and advocacy
Tactics: Premium tier upgrades, refer-a-friend bonuses, co-creation opportunities
Action: Nurture with membership offers, personalized recommendations
Rationale: Showing loyalty signals, need encouragement to become fully loyal
Tactics: Personalized product recommendations, membership benefits preview, engagement content
Action: Onboarding campaigns, product education, welcome discounts
Rationale: Early in journey, need education and positive reinforcement
Tactics: Welcome series, how-to content, first-purchase discount, satisfaction checks
Action: Cross-sell campaigns, bundle offers, engagement emails
Rationale: Recent but light engagement, expand basket and frequency
Tactics: Bundle promotions, category exploration emails, use case content
Action: Re-engagement campaigns, limited-time offers, feedback surveys
Rationale: Declining activity, need intervention before they slip further
Tactics: “We miss you” emails, special reactivation discounts, feedback requests
Action: Win-back campaigns, personalized discounts, reminder emails
Rationale: At critical inflection point, aggressive intervention needed
Tactics: Personalized win-back offers, cart abandonment reminders, lifecycle emails
Action: Urgent win-back offers, satisfaction surveys, retention discounts
Rationale: High-value customers at serious churn risk, justify high investment
Tactics: Deep discounts, executive outreach, satisfaction investigations, service recovery
Action: HIGH PRIORITY: Executive outreach, special recovery offers
Rationale: Were champions, now lost - highest priority recovery given historical value
Tactics: Executive calls, special recovery packages, account review meetings, loyalty restoration plans
Action: Low-cost reactivation, seasonal promotions, product updates
Rationale: Low historical value, limit investment but maintain presence
Tactics: Seasonal campaigns, new product announcements, low-cost email touches
Action: Minimal investment, brand awareness only, or exclude from campaigns
Rationale: Low probability of recovery, avoid wasting resources
Tactics: Passive brand awareness, exclude from paid campaigns, suppression lists
MarketingPriority assigns a priority level (High, Medium, Moderate, Low) to each customer based on their segment, guiding resource allocation and attention levels.
SQL Expression:
CASE
WHEN rfms.RFMSegment IN ('Champions', 'Loyal Customers', 'Cannot Lose Them') THEN 'High Priority'
WHEN rfms.RFMSegment IN ('Potential Loyalists', 'At Risk', 'Need Attention') THEN 'Medium Priority'
WHEN rfms.RFMSegment IN ('New Customers', 'Promising', 'About To Sleep') THEN 'Moderate Priority'
ELSE 'Low Priority'
END AS MarketingPriority
Priority Definitions:
Segments: Champions, Loyal Customers, Cannot Lose Them
Rationale: Highest current or historical value, deserve maximum attention
Resource Allocation: Premium service, personal touches, high marketing spend justified
Metrics: Typically 10-20% of customers, 50-70% of revenue
Segments: Potential Loyalists, At Risk, Need Attention
Rationale: Growth potential or retention risk, strategic importance
Resource Allocation: Targeted campaigns, moderate investment, systematic monitoring
Metrics: Typically 20-30% of customers, 20-30% of revenue
Segments: New Customers, Promising, About To Sleep
Rationale: Unknown potential or moderate value, deserve standard attention
Resource Allocation: Automated campaigns, standard service, selective investment
Metrics: Typically 25-35% of customers, 10-20% of revenue
Segments: Hibernating, Lost, Others
Rationale: Low value or recovery probability, minimize investment
Resource Allocation: Passive communications, low-cost touches, suppression consideration
Metrics: Typically 30-40% of customers, 5-10% of revenue
ExpectedROI categorizes the expected return on marketing investment for each customer based on their segment characteristics, guiding budget allocation decisions.
SQL Expression:
CASE
WHEN rfms.RFMSegment IN ('Champions', 'Loyal Customers', 'Potential Loyalists') THEN 'High ROI Expected'
WHEN rfms.RFMSegment IN ('New Customers', 'Promising', 'At Risk', 'Cannot Lose Them') THEN 'Medium ROI Expected'
ELSE 'Low ROI Expected'
END AS ExpectedROI
ROI Categories:
Segments: Champions, Loyal Customers, Potential Loyalists
Rationale:
Investment Strategy: Justify premium spend, maximize share-of-wallet
Expected Campaign ROI: 5:1 to 20:1
Segments: New Customers, Promising, At Risk, Cannot Lose Them
Rationale:
Investment Strategy: Targeted investment with clear metrics
Expected Campaign ROI: 2:1 to 5:1
Segments: Hibernating, Lost, About To Sleep, Need Attention, Others
Rationale:
Investment Strategy: Minimize spend, focus on high-efficiency channels
Expected Campaign ROI: 0.5:1 to 2:1 (often negative)
High ROI Scenario:
Medium ROI Scenario:
Low ROI Scenario:
The query builds customer segmentation through five progressive CTEs:
Combines RFM scores into 11 strategic segments:
This RFM framework provides a complete, actionable customer segmentation system that translates analytical scores into strategic business decisions and tactical marketing actions.