ANALYZE-003: Movement analytics

[AliiiBenn]

ANALYZE-003: Movement Analytics

Overview

Implement detailed movement analytics including type distribution, temporal patterns, peak days, source/destination analysis, and movement trends.

Description

Movement analytics provides insights into warehouse operational patterns:

• Movement type distribution (inbound/outbound/transfer/adjustment)
• Temporal patterns (daily, weekly, monthly trends)
• Peak days and hours
• Source/destination analysis (zones, locations)
• Movement velocity and trends

Technical Approach

Multi-Dimensional Movement Analysis

def analyze_movements(conn, lookback_days=30):
    """
    Analyze movement patterns across multiple dimensions.

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

    # 1. Type distribution
    analytics['by_type'] = pd.read_sql_query("""
        SELECT
            type,
            COUNT(*) as movement_count,
            SUM(quantite) as total_quantity,
            ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage,
            MIN(date_heure) as first_movement,
            MAX(date_heure) as last_movement
        FROM mouvements
        WHERE date_heure >= date('now', '-{lookback_days} days')
        GROUP BY type
        ORDER BY movement_count DESC
    """, conn)

    # 2. Daily trends
    analytics['daily_trends'] = pd.read_sql_query("""
        SELECT
            DATE(date_heure) as movement_date,
            COUNT(*) as movements,
            SUM(CASE WHEN type = 'SORTIE' THEN 1 ELSE 0 END) as outbound,
            SUM(CASE WHEN type = 'ENTRÉE' THEN 1 ELSE 0 END) as inbound
        FROM mouvements
        WHERE date_heure >= date('now', '-{lookback_days} days')
        GROUP BY DATE(date_heure)
        ORDER BY movement_date DESC
        LIMIT 30
    """, conn)

    # 3. Hourly patterns
    analytics['hourly_patterns'] = pd.read_sql_query("""
        SELECT
            CAST(strftime('%H', date_heure) AS INTEGER) as hour,
            COUNT(*) as movements,
            ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage
        FROM mouvements
        WHERE date_heure >= date('now', '-{lookback_days} days')
        GROUP BY hour
        ORDER BY hour
    """, conn)

    # 4. Day of week patterns
    analytics['dow_patterns'] = pd.read_sql_query("""
        SELECT
            CAST(strftime('%w', date_heure) AS INTEGER) as day_of_week,
            CASE CAST(strftime('%w', date_heure) AS INTEGER)
                WHEN 0 THEN 'Sunday'
                WHEN 1 THEN 'Monday'
                WHEN 2 THEN 'Tuesday'
                WHEN 3 THEN 'Wednesday'
                WHEN 4 THEN 'Thursday'
                WHEN 5 THEN 'Friday'
                WHEN 6 THEN 'Saturday'
            END as day_name,
            COUNT(*) as movements,
            AVG(COUNT(*)) OVER() as avg_movements
        FROM mouvements
        WHERE date_heure >= date('now', '-{lookback_days} days')
        GROUP BY day_of_week, day_name
        ORDER BY day_of_week
    """, conn)

    return analytics

Output Format

Terminal Output

$ wareflow analyze --movements

📈 Movement Analytics (Last 30 days)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Movement Type Distribution:
┌─────────────┬──────────┬───────────────┬────────────┐
│ Type        │ Count    │ Quantity      │ %          │
├─────────────┼──────────┼───────────────┼────────────┤
│ SORTIE      │ 28,901   │ 156,789       │ 63.3% ↘    │
│ ENTRÉE      │ 12,345   │ 98,765        │ 27.0% ↗    │
│ TRANSFERT   │ 4,432    │ 12,345        │ 9.7% ↔     │
│ AJUSTEMENT  │ 234      │ 1,234         │ 0.5% ⚙️     │
└─────────────┴──────────┴───────────────┴────────────┘

Temporel Patterns:
  Peak Day: Monday (3,456 movements, +23% vs avg)
  Slowest Day: Sunday (456 movements, -84% vs avg)

  Peak Hour: 14:00-15:00 (2,345 movements, 8.2% of daily)
  Slowest Hour: 01:00-02:00 (12 movements, 0.04% of daily)

Daily Trend (Last 7 days):
  2025-01-21: 1,234 movements ↗
  2025-01-20: 1,189 movements ↔
  2025-01-19: 1,456 moves ↑ (PEAK)
  2025-01-18: 1,123 moves ↓
  2025-01-17: 1,234 moves ↔
  2025-01-16: 1,089 moves ↓ (LOW)
  2025-01-15: 1,234 moves ↔

Velocity Metrics:
  Average: 1,523 movements/day
  Median: 1,445 movements/day
  Peak: 1,456 movements (Jan 19)
  Trend: Stable (0.3% increase vs last period)

Recommendations:
  💡 Schedule resource allocation for Monday peaks
  💡 Consider shift adjustments for 14:00-15:00 peak
  💡 Investigate Sunday dip (opportunity for maintenance?)

Implementation Plan

Phase 1: Core Analytics (1-2 days)

• Movement type distribution query
• Daily trend analysis
• Hourly pattern detection
• Day of week analysis
• Format terminal output

Phase 2: Enhanced Insights (1 day)

• Source/destination zone analysis
• Movement distance calculations
• Trend detection (increasing/decreasing)
• Export to Excel with charts
• Anomaly detection (unusual patterns)

CLI Usage

# Movement overview
wareflow analyze --movements

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

# Daily trends only
wareflow analyze --movements --trends

# Hourly patterns
wareflow analyze --movements --hourly

# Zone analysis (source/destination)
wareflow analyze --movements --zones

# Export with charts
wareflow analyze --movements --export movements.xlsx

Analytic Dimensions

1. Movement Type Analysis

Breakdown by:

• Inbound (ENTRÉE)
• Outbound (SORTIE)
• Transfer (TRANSFERT)
• Adjustment (AJUSTEMENT)

2. Temporal Patterns

Daily:

• Movements per day
• Trend (increasing/decreasing)
• Comparison to average

Hourly:

• Movements per hour
• Peak hours
• Shift patterns

Weekly:

• Day of week patterns
• Weekend vs weekday
• Seasonal trends

3. Spatial Analysis (Future)

• Source zone analysis
• Destination zone analysis
• Movement distance
• Zone-to-zone heat map

Success Criteria

• Show movement type distribution
• Calculate daily/hourly patterns
• Identify peak days and hours
• Detect trends (increasing/decreasing)
• Provide actionable insights
• Support custom lookback periods
• Export to Excel with charts
• Complete analysis in < 3 seconds

Future Enhancements

• Source/Destination Analysis: Zone-to-zone movement matrix
• Distance Calculation: Average travel distance per movement type
• Operator Analysis: Movements per operator
• Predictive Analytics: Forecast future movement volume
• Anomaly Detection: Unusual patterns (spikes, drops)
• Seasonality: Monthly/quarterly patterns

Dependencies

Required

• CORE-002 (analyze command)
• Movement history with timestamps

Related Issues

• Depends on: CORE-002
• Related to: ANALYZE-005 (Picking Efficiency)
• Enables: Resource optimization recommendations

References

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

Notes

This analysis is essential for operational planning:

• Staff scheduling (peak days/hours)
• Resource allocation (equipment, space)
• Shift planning (busy periods)
• Capacity planning (forecasting)

Key insights for warehouse managers:

• "When are we busiest?" → Schedule staff accordingly
• "What's our outbound/inbound ratio?" → Balance dock assignments
• "Are we trending up or down?" → Capacity planning