ANALYZE-004: Order analytics

[AliiiBenn]

ANALYZE-004: Order Analytics

Overview

Implement comprehensive order analytics including status distribution, fulfillment rates, lead times, priority analysis, and order patterns.

Description

Order analytics provides critical insights into warehouse order fulfillment:

• Order status distribution (pending, in-progress, completed, cancelled)
• Fulfillment rate and completion trends
• Lead time analysis (request → ship)
• Priority distribution and fulfillment by priority
• Order lines statistics
• Order composition (products per order)

Technical Approach

Comprehensive Order Queries

def analyze_orders(conn, lookback_days=30):
    """
    Analyze order fulfillment metrics.

    Returns:
        Dict with order analytics
    """
    analytics = {}

    # 1. Status distribution
    analytics['by_status'] = pd.read_sql_query(f"""
        SELECT
            etat as status,
            COUNT(*) as order_count,
            ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage,
            ROUND(AVG(lignes), 1) as avg_lines,
            ROUND(AVG(priorite), 1) as avg_priority,
            MIN(date_creation) as oldest_order,
            MAX(date_creation) as newest_order
        FROM commandes
        WHERE date_creation >= date('now', '-{lookback_days} days')
        GROUP BY etat
        ORDER BY order_count DESC
    """, conn)

    # 2. Fulfillment rate
    analytics['fulfillment'] = pd.read_sql_query(f"""
        WITH period_stats AS (
            SELECT
                COUNT(*) as total_orders,
                SUM(CASE WHEN etat = 'TERMINÉ' THEN 1 ELSE 0 END) as completed_orders,
                SUM(CASE WHEN etat = 'ANNULÉ' THEN 1 ELSE 0 END) as cancelled_orders
            FROM commandes
            WHERE date_creation >= date('now', '-{lookback_days} days')
        )
        SELECT
            total_orders,
            completed_orders,
            cancelled_orders,
            ROUND(100.0 * completed_orders / NULLIF(total_orders, 0), 1) as completion_rate,
            ROUND(100.0 * cancelled_orders / NULLIF(total_orders, 0), 1) as cancellation_rate
        FROM period_stats
    """, conn)

    # 3. Priority analysis
    analytics['by_priority'] = pd.read_sql_query(f"""
        SELECT
            priorite as priority,
            COUNT(*) as order_count,
            SUM(CASE WHEN etat = 'TERMINÉ' THEN 1 ELSE 0 END) as completed,
            ROUND(AVG(lignes), 1) as avg_lines,
            MIN(date_creation) as oldest_pending
        FROM commandes
        WHERE date_creation >= date('now', '-{lookback_days} days')
        GROUP BY priorite
        ORDER BY priorite DESC
    """, conn)

    # 4. Lead time (if available)
    if has_lead_time_data(conn):
        analytics['lead_time'] = pd.read_sql_query(f"""
            SELECT
                ROUND(AVG(JULIANDAY(date_expedition) - JULIANDAY(date_creation)), 1) as avg_lead_time_days,
                MIN(JULIANDAY(date_expedition) - JULIANDAY(date_creation)) as min_lead_time,
                MAX(JULIANDAY(date_expedition) - JULIANDAY(date_creation)) as max_lead_time,
                ROUND(
                    JULIANDAY(date_expedition) - JULIANDAY(date_creation),
                    1
                ) as median_lead_time
            FROM commandes
            WHERE date_creation >= date('now', '-{lookback_days} days')
              AND etat = 'TERMINÉ'
              AND date_expedition IS NOT NULL
        """, conn)

    return analytics

Output Format

Terminal Output

$ wareflow analyze --orders

📋 Order Analytics (Last 30 days)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Status Distribution:
┌─────────────┬──────────┬─────────────┬────────────┬─────────────┐
│ Status      │ Count    │ %           │ Avg Lines  │ Avg Priority│
├─────────────┼──────────┼─────────────┼────────────┼─────────────┤
│ TERMINÉ     │ 456      │ 57.8% ✅    │ 15.2       │ 2.1         │
│ EN_COURS    │ 234      │ 29.7% 🔄    │ 8.3        │ 3.2         │
│ EN_ATTENTE  │ 89       │ 11.3% ⏳    │ 5.1        │ 2.8         │
│ ANNULÉ      │ 10       │ 1.3% ❌     │ 2.0        │ 1.5         │
└─────────────┴──────────┴─────────────┴────────────┴─────────────┘

Fulfillment Metrics:
  Total Orders: 789
  Completion Rate: 57.8%
  Cancellation Rate: 1.3%
  Avg Lines/Order: 12.3

Priority Analysis:
  Priority 1 (Urgent): 23 orders, 89% completed ⚡
  Priority 2 (High):   156 orders, 67% completed
  Priority 3 (Normal): 456 orders, 54% completed
  Priority 4 (Low):    154 orders, 52% completed

Lead Time:
  Average: 2.3 days
  Median: 1.8 days
  Range: 0.5 - 7.2 days

Oldest Pending:
  Order #12345: 12 days in EN_COURS (Priority 1)
  Order #12346: 8 days in EN_ATTENTE (Priority 2)

Recommendations:
  ⚠️  Investigate 2 urgent orders pending > 10 days
  💡 Completion rate decreasing (vs 65% last period)
  💡 Consider SLA by priority tier

Implementation Plan

Phase 1: Core Analytics (1 day)

• Status distribution query
• Fulfillment rate calculation
• Priority breakdown
• Order lines statistics
• Format terminal output

Phase 2: Enhanced Metrics (1 day)

• Lead time analysis (if data available)
• Trend analysis (completion rate over time)
• Oldest pending identification
• SLA compliance tracking
• Export to Excel

CLI Usage

# Order overview
wareflow analyze --orders

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

# Status breakdown only
wareflow analyze --orders --status

# Priority analysis
wareflow analyze --orders --priority

# Lead time analysis
wareflow analyze --orders --lead-time

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

Key Metrics

Fulfillment Metrics

Metric	Formula	Target
Completion Rate	completed / total	> 90%
Cancellation Rate	cancelled / total	< 5%
Avg Lines/Order	SUM(lignes) / COUNT	N/A
Avg Lead Time	AVG(ship - request)	< 3 days

Priority Levels

Priority	Definition	SLA Target
1 - Urgent	Emergency orders	< 4 hours
2 - High	Expedited	< 1 day
3 - Normal	Standard	< 3 days
4 - Low	Bulk	< 7 days

Success Criteria

• Calculate order status distribution
• Determine fulfillment rate
• Analyze priority fulfillment
• Calculate average lines per order
• Identify oldest pending orders
• Calculate lead time metrics (if data available)
• Provide actionable recommendations
• Support custom lookback periods
• Export to Excel

Future Enhancements

• SLA Tracking: Compliance rate by priority tier
• Trend Analysis: Completion rate over time
• Order Composition: Products per order analysis
• Backlog Analysis: Aged pending orders
• Customer Analysis: Order patterns by customer
• Seasonality: Monthly/quarterly order patterns

Dependencies

Required

• CORE-002 (analyze command)
• Orders table with status and dates

Related Issues

• Depends on: CORE-002
• Related to: ANALYZE-005 (Picking Efficiency)
• Enables: Customer service metrics

References

• Orders schema: docs/SCHEMA.md
• Analyze command: docs/features/analyze.md

Notes

This analysis is critical for customer satisfaction:

• Track fulfillment performance
• Identify bottlenecks (pending orders)
• Monitor SLA compliance
• Optimize staffing based on order volume

Key questions answered:

• "What's our completion rate?" → Quality metric
• "Do we fulfill urgent orders faster?" → Priority effectiveness
• "What's our average lead time?" → Customer expectation setting
• "Which orders are stuck?" → Bottleneck identification