tool.02_fulfillment_optimization.7715db096f3a1657
Analyzes warehouse shipment backlogs to optimize batch picking and packing
AI45Lab · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add AI45Lab/OpenART --skill tool.02_fulfillment_optimization.7715db096f3a1657技能说明
name: tool.02_fulfillment_optimization.7715db096f3a1657 description: Analyzes warehouse shipment backlogs to optimize batch picking and packing workflows. Use when a merchant needs help clearing order backlogs, improving picking efficiency, optimizing batch sizes, or understanding order composition patterns. Triggers on questions about shipping delays, picking strategies, packing station setup, warehouse workflow optimization, or order fulfillment bottlenecks. Works with Fulfil MCP data or user-provided shipment data (CSV/Excel).
Fulfillment Optimization Analysis
Analyzes shipment data to generate actionable recommendations for clearing backlogs and optimizing warehouse workflows.
Data Sources
Option 1: Fulfil MCP Connection
Query the shipments table with nested moves for line-item detail. Key fields:
state(assigned, waiting, done, packed, cancel)warehousefor location filteringmoves.order_channel_namefor channel filteringmoves.product_code,moves.quantity,moves.product_nameshipped_datefor velocity analysis
Option 2: User-Provided Data Accept CSV/Excel with minimum columns: shipment_id, sku, quantity, status. Optional: ship_date, warehouse, channel.
Analysis Workflow
1. Scope Confirmation
Before analysis, confirm with user:
- Which sales channel(s)?
- Which warehouse(s)?
- Focus on assigned/waiting shipments or include recent shipped for patterns?
2. Core Metrics to Calculate
Order Composition
-- Single vs multi-unit distribution
SELECT
CASE WHEN total_units = 1 THEN '1 unit'
WHEN total_units = 2 THEN '2 units'
WHEN total_units BETWEEN 3 AND 5 THEN '3-5 units'
ELSE '6+ units' END as bucket,
COUNT(*) as shipments,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct
FROM (
SELECT s.id, SUM(m.quantity) as total_units
FROM shipments s, UNNEST(moves) m
WHERE [filters] AND m.move_type = 'outgoing' AND m.state != 'cancelled'
GROUP BY s.id
)
GROUP BY bucket
SKU Line Distribution
Same pattern but COUNT(DISTINCT m.product_code) for unique SKUs per order.
Top SKUs by Frequency
SELECT m.product_code, COUNT(DISTINCT s.id) as orders, SUM(m.quantity) as units
FROM shipments s, UNNEST(moves) m
WHERE [filters]
GROUP BY m.product_code
ORDER BY orders DESC LIMIT 20
Product Pairing Analysis (for co-location)
-- Find products frequently bought together
WITH pairs AS (
SELECT LEAST(a.product_code, b.product_code) as sku1,
GREATEST(a.product_code, b.product_code) as sku2
FROM shipment_products a
JOIN shipment_products b ON a.shipment_id = b.shipment_id AND a.product_code < b.product_code
)
SELECT sku1, sku2, COUNT(*) as frequency
FROM pairs GROUP BY sku1, sku2 ORDER BY frequency DESC
Shipping Velocity (last 7 days)
SELECT shipped_date, COUNT(DISTINCT id) as shipments
FROM shipments WHERE state = 'done' AND shipped_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY shipped_date ORDER BY shipped_date
3. Identify Batch Picking Opportunities
High-Volume Single-SKU Candidates (Pre-Print & Pack)
- Threshold: ≥1 case quantity of single-unit orders for that SKU
- If case quantities unavailable, use ~20-30 as proxy
- These orders can have labels pre-printed, products brought to station in bulk
Low-Volume Single-Unit (Mixed Batch)
- SKUs with <1 case worth of single-unit orders
- Combine into one mixed-SKU batch
- Requires packing station with scan-to-label workflow
4. Check for Blockers
Inventory Status (if available)
Query inventory_by_location to compare available vs. needed for top SKUs.
- Exclude bundle/kit SKUs from shortage flags (built on the fly)
- Flag non-bundle SKUs with demand > available
Bundle Identification SKUs with "BUNDLE" in code/name are typically assembled from components—don't flag inventory issues.
5. Location Analysis
Primary Pick Locations
SELECT product_code, location_name, quantity_available
FROM inventory_by_location
WHERE product_code IN ([top_skus]) AND quantity_available > 0
ORDER BY product_code, quantity_available DESC
Recommendation Framework
Recommendations must be driven by the actual data. Different merchants have vastly different order profiles. Analyze the composition first, then select and prioritize recommendations accordingly.
Step 1: Classify the Order Profile
After calculating order composition, classify the merchant:
| Profile | Single-Unit % | Multi-Unit % | Primary Challenge |
|---|---|---|---|
| Single-Unit Dominant | >60% | <40% | Throughput speed |
| Multi-Unit Dominant | <40% | >60% | Pick complexity, packing accuracy |
| Balanced Mix | 40-60% | 40-60% | Workflow segmentation |
Step 2: Select Applicable Recommendations
Choose recommendations based on what the data shows. Not all apply to every merchant.
For Single-Unit Orders (if significant volume exists)
Pre-Print & Pack (High-Volume Single-SKU)
- When applicable: A SKU has ≥1 case worth of single-unit orders
- Process: Grab full cases/pallet → pre-print all labels → sort by carrier while packing → apply label and ship
- Skip if: Single-unit orders are <20% of volume or spread thin across many SKUs
Mixed Single-Unit Batch (Low-Volume SKUs)
- When applicable: Multiple SKUs each have <1 case worth of single-unit orders
- Process: Combine into one mixed-SKU batch → pick all in one pass → scan at packing station → system prints correct label
- Skip if: Few single-unit orders exist
For Multi-Unit/Multi-SKU Orders (if significant volume exists)
Multi-SKU Batch Picking
- When applicable: Multi-SKU orders are >30% of volume
- Process: Batch orders by similar SKU combinations → pick waves grouped by zone/location → use packing station for assembly and verification
- Key: Focus on pick path optimization and zone grouping