RFM Analysis Documentation

Overview
RFM Fundamentals
System Architecture
Implementation Details
Customer Segmentation
API Endpoints
Database Schema
Configuration
Usage Examples
Performance Considerations
Troubleshooting

Overview

RFM Analysis is a customer segmentation technique used in the Sales Automation system to analyze customer behavior and value. RFM stands for:

Recency: How recently a customer made a purchase
Frequency: How often a customer makes purchases
Monetary: How much money a customer spends

This system automatically categorizes customers into distinct segments based on their purchasing patterns, enabling targeted marketing strategies and improved customer relationship management.

RFM Fundamentals

What is RFM Analysis?

RFM Analysis is a marketing technique that uses past purchase behavior to divide customers into groups for future targeted marketing campaigns. It's based on the principle that customers who have:

Purchased recently (Recency)
Purchase frequently (Frequency)
Spend significant amounts (Monetary value)

Are more likely to respond to marketing campaigns and continue purchasing.

Key Metrics

1. Recency (R)

Definition: Days since the customer's last purchase
Calculation: Difference between maximum and minimum purchase dates within the analysis period
Scoring: 1-5 scale (5 = most recent, 1 = least recent)
Business Impact: Recent customers are more likely to purchase again

2. Frequency (F)

Definition: Number of distinct orders placed by the customer
Calculation: Count of unique orders within the analysis period
Scoring: 1-5 scale (5 = highest frequency, 1 = lowest frequency)
Business Impact: Frequent customers show strong engagement and loyalty

3. Monetary (M)

Definition: Total amount spent by the customer
Calculation: Sum of all order values within the analysis period
Scoring: 1-5 scale (5 = highest spender, 1 = lowest spender)
Business Impact: High-value customers contribute most to revenue

Scoring Methodology

The system uses NTILE(5) SQL function to divide customers into quintiles:

Score 5: Top 20% of customers (best performers)
Score 4: Next 20% (good performers)
Score 3: Middle 20% (average performers)
Score 2: Next 20% (below average)
Score 1: Bottom 20% (poor performers)

System Architecture

Core Components

graph TD
    A[CalculateRfmAnalysisService] --> B[Database Query Engine]
    B --> C[Customer Segmentation Logic]
    C --> D[RfmAnalysis Model]
    D --> E[MlClusterLabel Model]
    E --> F[Outlet Model Update]
    F --> G[MlClusteringReportingController]
    G --> H[Reporting APIs]
    H --> I[Dashboard Views]

File Structure

app/
├── Services/Eva/
│   └── CalculateRfmAnalysisService.php    # Core RFM calculation logic
├── Http/Controllers/ML/
│   └── MlClusteringReportingController.php # API endpoints and reporting
├── Models/
│   ├── RfmAnalysis.php                    # RFM data model
│   └── ForecastModels/MlClusterLabel.php  # Segment labels model
└── Services/Reports/Clustering/
    └── ForecastDataAgainstLabelReportService.php # Report generation
routes/modules/eva/ml/
└── ml_clustering.php                      # Route definitions

Implementation Details

1. CalculateRfmAnalysisService Class

Location: app/Services/Eva/CalculateRfmAnalysisService.php

This service is the core engine for RFM analysis calculation.

Key Methods:

`calculate()` - Main Execution Method

public static function calculate()

Orchestrates the entire RFM analysis process
Chooses between Orders V1 and V2 based on configuration
Updates RfmAnalysis and Outlet models
Records execution timestamp

`orders()` - Orders V1 Analysis

public static function orders()

Analyzes data from legacy orders and orderdetails tables
Uses configurable time interval (default: 2 months)
Implements complex SQL CTEs for performance

`ordersv2()` - Orders V2 Analysis

public static function ordersv2()

Analyzes data from new sales_order and sales_order_details tables
Same logic as V1 but adapted for new schema
Preferred method for new implementations

`chooseOrdersV2()` - Version Selection Logic

public static function chooseOrdersV2(): bool

Determines which order system to use
Based on configuration settings:
- settings.prefer_orders_v1: Force use of V1
- settings.use_sales_order: Enable V2

2. SQL Query Architecture

Common Table Expressions (CTEs)

The system uses a sophisticated 4-step CTE approach:

WITH FrequencyData AS (
    -- Calculate order frequency per customer
),
MonetaryData AS (
    -- Calculate total spending per customer  
),
RecencyData AS (
    -- Calculate days since last purchase
),
MergedData AS (
    -- Combine all metrics and apply NTILE scoring
)

Performance Optimizations

Uses NTILE(5) for efficient quintile calculation
Leverages database indexes on date and customer fields
Minimizes data transfer with selective column retrieval

3. Customer Segmentation Logic

The system implements a sophisticated 10-segment classification:

Segment Definitions

Segment	Recency Score	Frequency Score	Monetary Score	Description
CHAMPIONS	≥4	≥4	≥4	Best customers - high value, frequent, recent
POTENTIAL LOYALISTS	≥4	2-3	≥3	Recent customers with good value, can improve frequency
NEW CUSTOMERS	≥4	<2	Any	Recent but infrequent purchases - onboarding opportunity
LOYAL CUSTOMERS	2-3	≥4	≥3	Consistent, valuable customers with moderate recency
NEED ATTENTION	2-3	2-3	2-3	Middle-tier customers requiring engagement
ABOUT TO SLEEP	2-3	<2 or M<2	Variable	Declining engagement - intervention needed
CAN'T LOSE THEM	<2	≥3	≥4	High-value customers at risk - immediate action required
AT RISK	<2	2-3	2-3	Moderate customers showing decline
HIBERNATING	<2	<3	<3	Inactive customers - reactivation campaigns
AVERAGE	Any	Any	Any	Default category for edge cases

Segmentation Algorithm

CASE
    WHEN RECENCY_SCORE >= 4 AND FREQUENCY_SCORE >= 4 AND MONETARY_SCORE >= 4
        THEN 'CHAMPIONS'
    WHEN RECENCY_SCORE >= 4 AND FREQUENCY_SCORE >= 2 AND FREQUENCY_SCORE < 4 AND MONETARY_SCORE >= 3
        THEN 'POTENTIAL LOYALISTS'
    -- ... additional conditions
    ELSE 'AVERAGE'
END AS SEGMENT

API Endpoints

Base URL

/api/v1/eva/ml/

Authentication

All endpoints require API authentication:

Authorization: Bearer {token}
Middleware: auth:api

Available Endpoints

1. RFM Analysis Execution

GET /run-rfm-analysis

Purpose: Trigger RFM analysis calculation
Controller: MlClusteringReportingController@runRfmAnalysis
Response:

{
    "success": true,
    "message": "RFM analysis complete",
    "data": [],
    "status_code": "201"
}

2. Forecast Information Against Labels

GET /forecast-info-against-label

Purpose: Get segment performance metrics
Parameters:

search (optional): Filter by segment name
start_date (optional): Analysis start date
end_date (optional): Analysis end date

Response:

{
    "success": true,
    "data": [
        {
            "segment": "CHAMPIONS",
            "#_customers": "150",
            "%_customers": "15.5%",
            "revenue": "45,000.00",
            "%_revenue": "32.1%",
            "avg_recency": "5.2",
            "avg_frequency": "8.7"
        }
    ]
}

3. Customers Report

GET /rfm-customers-report

Purpose: Detailed customer-level RFM data
Parameters:

per_page (optional): Pagination size (default: 25)
segment (optional): Filter by segment
search (optional): Search customer name/phone

Response:

{
    "success": true,
    "data": {
        "current_page": 1,
        "data": [
            {
                "shop_id": 123,
                "customer_name": "John's Shop",
                "phone_number": "+254712345678",
                "segment": "CHAMPIONS",
                "currently_assigned": "Jane Doe",
                "no_visits": 12,
                "last_sale_days": 3,
                "lifetime_value": "25,000.00",
                "days_since_last_visit": 2
            }
        ]
    }
}

4. Segment Distribution

GET /segment-distribution

Purpose: Count of customers per segment
Response:

{
    "success": true,
    "data": [
        {
            "segment": "CHAMPIONS",
            "count": 150
        },
        {
            "segment": "LOYAL CUSTOMERS", 
            "count": 200
        }
    ]
}

5. Segments Management

GET /segments

Purpose: Manage segment labels and metadata
Response:

{
    "success": true,
    "data": [
        {
            "label": "CHAMPIONS",
            "slug": "champions",
            "suggested_actions": "Maintain relationship, upsell premium products",
            "color": "#28a745",
            "status": "Active",
            "no_shops": 150
        }
    ]
}

6. Download Customers Report

GET /download-customers-report

Purpose: Export customer RFM data to Excel
Response: File download with comprehensive customer data

7. Sales Reps in Segment Routes

GET /reps-in-routes-in-segment

Purpose: Get sales representatives assigned to customers in specific segments
Parameters:

segment (required): Segment name to filter

Database Schema

1. rfm_analyses Table

CREATE TABLE rfm_analyses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    cluster_label_id INT UNSIGNED,
    recency DECIMAL(8,2) DEFAULT 0,
    frequency INT DEFAULT 0,
    monetary DECIMAL(12,2) DEFAULT 0,
    recency_score TINYINT DEFAULT 0,
    frequency_score TINYINT DEFAULT 0,
    monetary_score TINYINT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    
    INDEX idx_customer_id (customer_id),
    INDEX idx_cluster_label_id (cluster_label_id),
    INDEX idx_scores (recency_score, frequency_score, monetary_score)
);

2. ml_cluster_labels Table

CREATE TABLE ml_cluster_labels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    suggested_actions TEXT,
    color VARCHAR(7) DEFAULT '#007bff',
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    added_by INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    
    UNIQUE KEY unique_label (label),
    INDEX idx_status (status)
);

3. Model Relationships

// RfmAnalysis Model
class RfmAnalysis extends Model
{
    protected $fillable = [
        'customer_id',
        'cluster_label_id', 
        'recency',
        'frequency',
        'monetary',
        'recency_score',
        'frequency_score',
        'monetary_score'
    ];
    
    public function customer()
    {
        return $this->belongsTo(Outlet::class, 'customer_id');
    }
    
    public function clusterLabel()
    {
        return $this->belongsTo(MlClusterLabel::class, 'cluster_label_id');
    }
}

Configuration

Config File Settings

File: config/settings.php

return [
    // RFM analysis time window in months
    'rfm_interval_in_months' => env('RFM_INTERVAL_IN_MONTHS', 2),
    
    // Custom table name for RFM analysis
    'rfm_analysis_table' => env('RFM_ANALYSIS_TABLE', 'rfm_analyses'),
    
    // Force use of legacy orders system
    'prefer_orders_v1' => env('PREFER_ORDERS_V1', 'no'),
    
    // Enable new sales order system
    'use_sales_order' => env('USE_SALES_ORDER', 'true'),
    
    // RFM reporting period
    'rfm_period_in_months' => env('RFM_PERIOD_IN_MONTHS', 2),
];

Usage Examples

1. Running RFM Analysis Programmatically

use App\Services\Eva\CalculateRfmAnalysisService;

// Execute RFM analysis
CalculateRfmAnalysisService::calculate();

// Check if using Orders V2
if (CalculateRfmAnalysisService::chooseOrdersV2()) {
    echo "Using Sales Order V2 system";
} else {
    echo "Using legacy Orders V1 system";
}

2. Retrieving Customer Segments

use App\Models\ForecastModels\MlClusterLabel;
use App\RfmAnalysis;

// Get all segments with customer counts
$segments = MlClusterLabel::withCount('rfmAnalyses')->get();

// Get customers in a specific segment
$champions = RfmAnalysis::whereHas('clusterLabel', function($query) {
    $query->where('label', 'CHAMPIONS');
})->with('customer')->get();

// Get segment distribution
$distribution = RfmAnalysis::join('ml_cluster_labels', 'ml_cluster_labels.id', 'rfm_analyses.cluster_label_id')
    ->select('ml_cluster_labels.label as segment', DB::raw('COUNT(*) as count'))
    ->groupBy('ml_cluster_labels.label')
    ->get();

3. Custom Reporting

use App\Services\Reports\Clustering\ForecastDataAgainstLabelReportService;

// Generate comprehensive segment report
$request = new Request(['start_date' => '2023-01-01', 'end_date' => '2023-12-31']);
$report = ForecastDataAgainstLabelReportService::report($request);

// Get aggregate metrics
$aggregates = ForecastDataAgainstLabelReportService::aggregateReport($request);

// Export customer data
foreach(ForecastDataAgainstLabelReportService::customersReport($request)->cursor() as $customer) {
    // Process each customer record
    echo $customer->customer_name . " - " . $customer->segment . "\n";
}

4. Scheduled Execution

// In Laravel Scheduler (Console/Kernel.php)
protected function schedule(Schedule $schedule)
{
    $schedule->call(function () {
        CalculateRfmAnalysisService::calculate();
    })->weekly()->mondays()->at('02:00');
}

Troubleshooting

Common Issues

1. No Data Returned

Symptoms: Empty results from RFM analysis Causes:

No orders within the configured time window
Database connection issues
Incorrect date format in queries

Solutions:

// Check configuration
$interval = config('settings.rfm_interval_in_months', 2);
echo "Using interval: {$interval} months";

// Verify data exists
$orderCount = DB::table('orders')
    ->where('order_time', '>=', now()->subMonths($interval))
    ->count();
echo "Orders found: {$orderCount}";

2. Segment Classification Issues

Symptoms: All customers assigned to "AVERAGE" segment Causes:

Scoring thresholds too restrictive
Data quality issues
Logic errors in segmentation rules

Solutions:

// Debug scoring distribution
$scoreDistribution = RfmAnalysis::select(
    'recency_score',
    'frequency_score', 
    'monetary_score',
    DB::raw('COUNT(*) as count')
)->groupBy('recency_score', 'frequency_score', 'monetary_score')
->get();

dd($scoreDistribution);

3. Performance Issues

Symptoms: Slow query execution, timeouts Causes:

Missing indexes
Large dataset without optimization
Inefficient SQL queries

Solutions:

// Enable query logging
DB::enableQueryLog();
CalculateRfmAnalysisService::calculate();
$queries = DB::getQueryLog();
foreach($queries as $query) {
    echo $query['query'] . " ({$query['time']}ms)\n";
}

4. Memory Exhaustion

Symptoms: PHP fatal error: allowed memory size exhausted Causes:

Processing too many records at once
Inefficient data handling

Solutions:

// Increase memory limit temporarily
ini_set('memory_limit', '512M');

// Use chunked processing
DB::table('some_large_table')->chunk(100, function($records) {
    // Process records in smaller batches
});

Logging and Monitoring

Enable Debug Logging

// In CalculateRfmAnalysisService
Log::info('Starting RFM analysis', [
    'interval_months' => $this->rfm_interval_in_months,
    'using_orders_v2' => static::chooseOrdersV2()
]);

Log::info('RFM analysis completed', [
    'records_processed' => $data ? count($data) : 0,
    'execution_time' => microtime(true) - $start_time
]);

Monitor Execution

// Check last execution time
$lastRun = Schedules::where('command', 'calculate:rfm-analysis')
    ->value('last_runtime');

if (!$lastRun || Carbon::parse($lastRun)->diffInHours() > 24) {
    Log::warning('RFM analysis may be overdue', [
        'last_run' => $lastRun,
        'hours_since' => $lastRun ? Carbon::parse($lastRun)->diffInHours() : 'never'
    ]);
}

Testing

Unit Tests

class RfmAnalysisTest extends TestCase
{
    public function test_calculate_rfm_analysis()
    {
        // Create test data
        $customer = factory(Outlet::class)->create();
        $orders = factory(Order::class, 5)->create([
            'shop_id' => $customer->id,
            'order_time' => now()->subDays(rand(1, 60))
        ]);
        
        // Run analysis
        CalculateRfmAnalysisService::calculate();
        
        // Assert results
        $rfm = RfmAnalysis::where('customer_id', $customer->id)->first();
        $this->assertNotNull($rfm);
        $this->assertGreaterThan(0, $rfm->frequency);
    }
}

This comprehensive documentation covers all aspects of the RFM Analysis system implementation, from basic concepts to advanced troubleshooting. The system provides powerful customer segmentation capabilities that enable data-driven marketing strategies and improved customer relationship management.

RFM Analysis

On this page