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)
• •warehouse for location filtering
• •moves.order_channel_name for channel filtering
• •moves.product_code, moves.quantity, moves.product_name
• •shipped_date for 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:

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

Product Co-location

• •When applicable: Product pairing analysis shows strong repeat combinations
• •Process: Move frequently-paired products adjacent in warehouse
• •Include: Top product pairs table with frequency counts

Multi-Unit Same-SKU Handling

• •When applicable: Many orders have multiple units of same SKU (e.g., 3x of SKU-A)
• •Process: May benefit from bulk picking with quantity verification at pack station

Operational Recommendations (apply based on bottleneck)

Packing Station Setup

• •When packing is bottleneck: High single-unit volume, simple picks but slow pack/label
• •Recommendation: More packing stations than pickers (e.g., 1:2 or 1:3 ratio)
• •Each station needs: Computer/tablet + thermal printer + barcode scanner

Picking Optimization

• •When picking is bottleneck: Complex multi-SKU orders, large warehouse, long pick paths
• •Recommendation: Zone picking, batch picking by location cluster, pick-to-cart workflows
• •May need: More pickers than packers

Cartonization

• •When applicable: Default box types are configured or could be
• •For single-unit: Enables pre-printing labels
• •For multi-unit: Helps but packing station workflow still recommended
• •Offer: Assistance with setup if not yet configured

Step 3: Prioritize by Impact

Order recommendations by shipment volume affected:

1. •First address the largest segment of orders
2. •Then address secondary segments
3. •Operational changes (stations, staffing) come after workflow changes

Step 4: Create Wave Plan

Based on selected recommendations, create execution waves:

• •Group by workflow type (Pre-Print vs. Packing Station)
• •Estimate shipment counts per wave
• •Sequence waves by priority/efficiency

Output Format

Generate PDF report with:

1. •Executive summary (backlog size, days of work at current velocity)
2. •Order composition analysis (tables + key stats highlighting the dominant profile)
3. •Top SKUs with relevant batching candidates
4. •Product pairing analysis (if multi-SKU orders are significant)
5. •Numbered recommendations tailored to this merchant's data
6. •Wave execution plan matching their order profile
7. •Shipping velocity (last 7 days)
8. •Bottom line summary

Use docx skill to create document, then convert to PDF:

soffice --headless --convert-to pdf document.docx --outdir /mnt/user-data/outputs/

Key Principles

1. •

   Data drives recommendations: Analyze composition first, then recommend. Never assume a particular order profile.

2. •

   Identify the bottleneck: Packing is often the constraint for single-unit operations; picking is often the constraint for complex multi-SKU operations. Staffing ratios should reflect the actual bottleneck.

3. •

   Bundle awareness: Built-on-fly bundles don't have inventory—components do. Never flag bundle inventory issues.

4. •

   Case quantity threshold: For single-unit batching, the dividing line between "Pre-Print & Pack" and "Mixed Batch" is typically 1 case worth of orders. If case quantities unavailable in system, ask merchant or estimate ~20-30 units.

5. •

   Optimize for the majority first: Whatever segment represents the largest share of orders should get the first and most detailed recommendation.