ARCH-002: Cross-warehouse comparison #22
Description
ARCH-002: Cross-Warehouse Comparison
Overview
Implement cross-warehouse comparison analytics to benchmark performance across multiple warehouses.
Description
Cross-warehouse comparison enables:
- Side-by-side performance metrics
- Best practice identification
- Underperformer detection
- Standardization opportunities
- Resource allocation optimization
Technical Approach
Benchmark Queries
def compare_warehouse_performance(conn, lookback_days=30):
"""
Compare metrics across all warehouses.
Returns:
Dict with comparison analytics
"""
comparison = {}
# 1. Overall metrics by warehouse
comparison['overview'] = pd.read_sql_query(f"""
SELECT
w.id as warehouse_id,
w.name as warehouse_name,
COUNT(DISTINCT p.no_produit) as total_products,
COUNT(m.oid) as total_movements,
SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as outbound_count,
ROUND(AVG(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END), 2) as avg_outbound_per_day,
COUNT(DISTINCT m.operateur) as total_operators
FROM warehouses w
LEFT JOIN produits p ON w.id = p.warehouse_id
LEFT JOIN mouvements m ON w.id = m.warehouse_id
AND m.date_heure >= date('now', '-{lookback_days} days')
GROUP BY w.id, w.name
ORDER BY total_movements DESC
""", conn)
# 2. Product movement velocity comparison
comparison['product_velocity'] = pd.read_sql_query(f"""
SELECT
w.id as warehouse_id,
w.name as warehouse_name,
ROUND(AVG(pick_stats.picks_per_product), 1) as avg_picks_per_product,
MAX(pick_stats.picks_per_product) as max_picks_per_product,
COUNT(DISTINCT p.no_produit) as active_products
FROM warehouses w
LEFT JOIN (
SELECT
m.warehouse_id,
m.no_produit,
COUNT(*) as picks_per_product
FROM mouvements m
WHERE m.type = 'SORTIE'
AND m.date_heure >= date('now', '-{lookback_days} days')
GROUP BY m.warehouse_id, m.no_produit
) pick_stats ON w.id = pick_stats.warehouse_id
GROUP BY w.id, w.name
""", conn)
# 3. ABC classification comparison
comparison['abc_distribution'] = pd.read_sql_query(f"""
WITH warehouse_abc AS (
SELECT
m.warehouse_id,
p.no_produit,
SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) as picks,
NTILE(100) OVER (PARTITION BY m.warehouse_id ORDER BY SUM(CASE WHEN m.type = 'SORTIE' THEN 1 ELSE 0 END) DESC) as percentile_rank
FROM mouvements m
JOIN produits p ON m.no_produit = p.no_produit AND m.warehouse_id = p.warehouse_id
WHERE m.date_heure >= date('now', '-{lookback_days} days')
GROUP BY m.warehouse_id, p.no_produit
)
SELECT
w.id as warehouse_id,
w.name as warehouse_name,
SUM(CASE WHEN percentile_rank <= 20 THEN 1 ELSE 0 END) as class_a_count,
SUM(CASE WHEN percentile_rank BETWEEN 21 AND 50 THEN 1 ELSE 0 END) as class_b_count,
SUM(CASE WHEN percentile_rank > 50 THEN 1 ELSE 0 END) as class_c_count
FROM warehouse_abc abc
JOIN warehouses w ON abc.warehouse_id = w.id
GROUP BY w.id, w.name
""", conn)
return comparisonOutput Format
Terminal Output
$ wareflow analyze --compare-warehouses
🏢 Cross-Warehouse Comparison (Last 30 days)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Overview:
┌──────────┬─────────────────┬───────────┬────────────┬─────────────┬──────────┐
│ Warehouse│ Name │ Products │ Movements │ Outbound/Day│ Operators│
├──────────┼─────────────────┼───────────┼────────────┼─────────────┼──────────┤
│ paris │ Paris Warehouse │ 1,234 │ 28,901 │ 963 │ 12 │
│ lyon │ Lyon Warehouse │ 987 │ 15,432 │ 514 │ 8 │
│ marseille│ Marseille WH │ 1,456 │ 32,123 │ 1,071 │ 15 │
└──────────┴─────────────────┴───────────┴────────────┴─────────────┴──────────┘
Performance Metrics:
Highest Throughput: Marseille (1,071 picks/day) 🏆
Most Efficient: Paris (963 picks/operator/day) ⚡
Largest Catalog: Marseille (1,456 products) 📦
Most Staffed: Marseille (15 operators) 👥
ABC Classification Distribution:
┌──────────┬─────────┬─────────┬─────────┬─────────────┐
│ Warehouse│ Class A │ Class B │ Class C │ A % │
├──────────┼─────────┼─────────┼─────────┼─────────────┤
│ paris │ 247 │ 370 │ 617 │ 20% │
│ lyon │ 197 │ 296 │ 494 │ 20% │
│ marseille│ 291 │ 437 │ 728 │ 20% │
└──────────┴─────────┴─────────┴─────────┴─────────────┘
Benchmark vs Average:
Paris:
✅ +12% vs avg outbound efficiency
✅ +8% vs avg operator productivity
⚠️ -15% vs avg product count
Lyon:
⚠️ -18% vs avg throughput
✅ +5% vs avg product velocity
⚠️ -33% vs avg staffing
Marseille:
✅ +31% vs avg throughput
⚠️ -5% vs avg operator efficiency
✅ +18% vs avg product count
Best Practices:
🏆 Paris: Highest operator efficiency (963 picks/day/op)
→ Consider documenting SOPs for other warehouses
🏆 Marseille: Highest throughput (1,071 picks/day)
→ Analyze staffing model and shift patterns
Recommendations:
💡 Cross-train Lyon staff with Paris operators
💡 Investigate Paris's lower product count (assortment optimization?)
💡 Share Marseille's high-throughput practices
💡 Standardize ABC thresholds across warehousesImplementation Plan
Phase 1: Core Comparisons (1-2 days)
- Implement warehouse overview comparison
- Product velocity comparison
- ABC distribution comparison
- Calculate benchmark metrics
Phase 2: Enhanced Analytics (1 day)
- Trend comparison (improving/declining)
- Seasonal pattern comparison
- Operator efficiency comparison
- Export to Excel with conditional formatting
CLI Usage
# Compare all warehouses
wareflow analyze --compare-warehouses
# Compare specific warehouses
wareflow analyze --compare-warehouses --wh paris,lyon
# Compare specific metric
wareflow analyze --compare-warehouses --metric throughput
# Export comparison
wareflow analyze --compare-warehouses --export comparison.xlsx
# Show best practices
wareflow analyze --compare-warehouses --best-practicesComparison Dimensions
1. Throughput Metrics
- Total movements
- Outbound count
- Picks per day
- Picks per operator
2. Inventory Metrics
- Total products
- ABC distribution
- Active vs stale inventory
- Product velocity
3. Operational Metrics
- Operator count
- Average picks per operator
- Peak day throughput
- Utilization rate
4. Quality Metrics
- Data quality score
- Error rate (if available)
- fulfillment rate
- lead time
Success Criteria
- Compare all warehouses on key metrics
- Identify best performer per metric
- Calculate variance from average
- Highlight best practices
- Provide actionable recommendations
- Support warehouse filtering (subset comparison)
- Export to Excel with formatting
Future Enhancements
- Trend Comparison: Which warehouse is improving fastest?
- Seasonal Patterns: Compare seasonality across warehouses
- Operator Transfer: Identify high-performers for cross-training
- Standardization: Identify process differences
- Cost Comparison: Cost per pick, per order
Dependencies
Required
- ARCH-001 (multi-warehouse support)
- At least 2 warehouses with data
Related Issues
- Depends on: ARCH-001
- Related to: ARCH-003 (Consolidated Reporting)
- Enhances: All analysis commands
References
- Multi-warehouse architecture:
docs/FUTURE.md - Performance benchmarking best practices
Notes
This analysis is valuable for multi-site operations:
- Identify and spread best practices
- Detect underperforming locations
- Standardize processes across warehouses
- Optimize resource allocation
Key insights:
- "Who's most efficient?" → Study and replicate
- "Who's struggling?" → Provide support and training
- "What's different?" → Process standardization opportunities
The comparison should be fair (account for differences in):
- Warehouse size (product count)
- Order volume
- Staffing levels
- Seasonality