ANALYZE-002: Product performance KPIs

[AliiiBenn]

ANALYZE-002: Product Performance KPIs

Overview

Implement comprehensive product performance KPIs including movement frequency, pick velocity, peak days, seasonality, and trends.

Description

Product performance analysis provides detailed metrics for each product:

• Total movements and picks
• Movement velocity (picks per day)
• Peak movement days
• Last movement date (stale detection)
• Trends (increasing/decreasing)
• Comparison to average

Technical Approach

Comprehensive Product Query

def calculate_product_performance(conn, lookback_days=30, limit=50):
    """
    Calculate detailed product performance KPIs.

    Returns:
        DataFrame with product performance metrics
    """
    query = f"""
    WITH product_stats AS (
        SELECT
            m.no_produit,
            p.nom_produit,
            COUNT(*) as total_movements,
            SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as outbound_count,
            SUM(CASE WHEN m.type = 'SORTIE' THEN quantite ELSE 0 END) as total_picked,
            SUM(CASE WHEN m.type = 'ENTRÉE' THEN quantite ELSE 0 END) as total_received,
            MIN(m.date_heure) as first_movement,
            MAX(m.date_heure) as last_movement,
            COUNT(DISTINCT DATE(m.date_heure)) as active_days,
            JULIANDAY('now') - JULIANDAY(MAX(m.date_heure)) as days_since_last
        FROM mouvements m
        JOIN produits p ON m.no_produit = p.no_produit
        WHERE m.date_heure >= date('now', '-{lookback_days} days')
        GROUP BY m.no_produit
    ),
    with_velocity AS (
        SELECT
            *,
            CAST(total_picked AS REAL) / NULLIF(active_days, 0) as picks_per_day,
            CAST(total_picked AS REAL) / NULLIF({lookback_days}, 0) as avg_daily_velocity
        FROM product_stats
    )
    SELECT
        no_produit,
        nom_produit,
        total_movements,
        outbound_count,
        total_picked,
        total_received,
        ROUND(picks_per_day, 2) as picks_per_active_day,
        ROUND(avg_daily_velocity, 2) as avg_daily_picks,
        first_movement,
        last_movement,
        days_since_last,
        CASE
            WHEN days_since_last > 90 THEN 'STALE'
            WHEN days_since_last > 30 THEN 'SLOW'
            ELSE 'ACTIVE'
        END as activity_status
    FROM with_velocity
    ORDER BY total_picked DESC
    LIMIT {limit}
    """

    return pd.read_sql_query(query, conn)

Output Format

Terminal Output

$ wareflow analyze --products

📊 Product Performance Analysis (Top 50)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Top 10 Products by Picks:
┌──────────┬─────────────────────┬──────────┬───────────┬────────────┐
│ SKU      │ Name                │ Picks    │ Picks/Day │ Status     │
├──────────┼─────────────────────┼──────────┼───────────┼────────────┤
│ 1001     │ Product Alpha       │ 2,345    │ 78.2      │ ACTIVE 🔥  │
│ 1045     │ Product Beta        │ 1,987    │ 66.2      │ ACTIVE 🔥  │
│ 1123     │ Product Gamma       │ 1,654    │ 55.1      │ ACTIVE 🔥  │
│ 1089     │ Product Delta       │ 1,432    │ 47.7      │ ACTIVE 🔥  │
│ 1012     │ Product Epsilon     │ 1,234    │ 41.1      │ ACTIVE 🔥  │
│ 1067     │ Product Zeta        │ 987      │ 32.9      │ ACTIVE     │
│ 1034     │ Product Eta         │ 876      │ 29.2      │ ACTIVE     │
│ 1098     │ Product Theta       │ 765      │ 25.5      │ ACTIVE     │
│ 1023     │ Product Iota         │ 654      │ 21.8      │ ACTIVE     │
│ 1078     │ Product Kappa       │ 543      │ 18.1      │ ACTIVE     │
└──────────┴─────────────────────┴──────────┴───────────┴────────────┘

Inventory Status:
  🔥 Active (last 30 days):    1,156 products (93.6%)
  ⚠️  Slow (31-90 days):          67 products (5.4%)
  ❌ Stale (>90 days):            11 products (0.9%)

Movement Velocity:
  Average: 12.3 picks/day
  Median: 8.7 picks/day
  Peak: 78.2 picks/day (Product Alpha)

Recommendations:
  💡 Review 11 stale products for obsolescence
  💡 Consider safety stock optimization for top 10
  💡 Investigate velocity variance (std: 15.3)

Implementation Plan

Phase 1: Core KPIs (1-2 days)

• Implement product performance query
• Calculate movement statistics (total, avg, min, max)
• Determine activity status (ACTIVE, SLOW, STALE)
• Format top N table output

Phase 2: Enhanced Analytics (1 day)

• Velocity trends (increasing/decreasing)
• Comparison to warehouse average
• Peak day identification
• Export to Excel with conditional formatting

CLI Usage

# Top 50 products (default)
wareflow analyze --products

# Custom lookback period
wareflow analyze --products --days 60

# Top 20 only
wareflow analyze --products --limit 20

# Include stale products
wareflow analyze --products --include-stale

# Export to Excel
wareflow analyze --products --export products.xlsx

# Filter by status
wareflow analyze --products --status STALE

KPI Definitions

Movement Metrics

KPI	Formula	Description
Total Movements	COUNT(*)	All movement records
Outbound Count	COUNT(type='SORTIE')	Number of outbound transactions
Total Picked	SUM(quantite) WHERE type='SORTIE'	Total quantity picked
Picks/Active Day	total_picked / active_days	Velocity on active days
Avg Daily Velocity	total_picked / lookback_days	Average over entire period

Activity Status

Status	Definition	Action
ACTIVE	Last movement ≤ 30 days	Normal operations
SLOW	Last movement 31-90 days	Review for optimization
STALE	Last movement > 90 days	Consider obsolescence review

Success Criteria

• Calculate performance for all products
• Display top N products by picks
• Show activity status distribution
• Identify stale/slow products
• Calculate velocity metrics
• Provide actionable recommendations
• Support custom lookback periods
• Export to Excel with formatting

Future Enhancements

• Trend Analysis: Compare current period to previous (velocity increasing/decreasing)
• Seasonality: Identify seasonal patterns per product
• Correlation: Product affinity (products often picked together)
• Forecasting: Predict future demand based on trends
• Multi-warehouse: Compare product performance across warehouses

Dependencies

Required

• CORE-002 (analyze command)
• Products with movement history

Related Issues

• Depends on: CORE-002
• Related to: ANALYZE-001 (ABC Classification)
• Enables: Inventory optimization recommendations

References

• Product metrics specification: docs/features/analyze.md
• Schema: docs/SCHEMA.md

Notes

This analysis is critical for warehouse operations:

• Identifies high-velocity products (Class A candidates)
• Highlights stale inventory (obsolescence risk)
• Drives storage location decisions
• Informs safety stock calculations

The KPIs should be materialized in a table for quick access:

CREATE TABLE mv_product_performance AS
SELECT no_produit, total_picked, picks_per_day, activity_status, ...

This allows:

• Fast dashboard queries
• Trend analysis over time
• Comparison between periods