Fulfillment Optimization Analysis

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.