Shipping Cost Analysis

OPERATIONS DATA CONSOLIDATION REPORT
Report Type: Shipping Cost Analysis
Period: December 1-8, 2024
Generated: December 4, 2024

═══════════════════════════════════════════════════════════════

DATA SOURCES LOADED:

✓ fedex.csv: 15 shipments, $188.20 total
✓ shopify.csv: 35 orders, $161.75 shipping charged (15 matched)

DATA QUALITY:
✓ All date formats standardized (YYYY-MM-DD)
✓ All currency values parsed correctly
✓ 15 of 15 FedEx shipments matched to Shopify orders (100% match rate)
⚠ 20 Shopify orders without shipping data (likely different carrier or pickup)

═══════════════════════════════════════════════════════════════

SHIPPING COST SUMMARY:

Total Actual Shipping Costs: $188.20

• FedEx: $188.20 (100%)

Total Shipping Revenue: $161.75
Shipping Margin: -$26.45 (-14.05% margin) ⚠ UNPROFITABLE

Average Cost per Shipment: $12.55
Average Revenue per Shipment: $10.78
Average Loss per Shipment: -$1.76

═══════════════════════════════════════════════════════════════

PROFITABILITY BY ZONE:

Zone 2 (Short Distance):

• 2 shipments (13.3%), Avg Cost: $7.02, Avg Charged: $9.50
• Margin: $2.47/shipment (35.2%) ✓ PROFITABLE

Zone 3 (Short-Medium Distance):

• 2 shipments (13.3%), Avg Cost: $8.12, Avg Charged: $9.50
• Margin: $1.38/shipment (17.0%) ✓ PROFITABLE

Zone 4 (Medium Distance):

• 2 shipments (13.3%), Avg Cost: $10.02, Avg Charged: $11.25
• Margin: $1.22/shipment (12.2%) ✓ PROFITABLE

Zone 5 (Medium-Long Distance):

• 3 shipments (20.0%), Avg Cost: $12.20, Avg Charged: $11.25
• Margin: -$0.95/shipment (-7.8%) ⚠ UNPROFITABLE
• Total Loss: -$2.85

Zone 6 (Long Distance):

• 2 shipments (13.3%), Avg Cost: $14.40, Avg Charged: $11.25
• Margin: -$3.15/shipment (-21.9%) ⚠ UNPROFITABLE
• Total Loss: -$6.30

Zone 7 (Very Long Distance):

• 3 shipments (20.0%), Avg Cost: $17.67, Avg Charged: $11.25
• Margin: -$6.42/shipment (-36.3%) 🚨 CRITICAL LOSS
• Total Loss: -$19.26

Zone 8 (Maximum Distance):

• 1 shipment (6.7%), Avg Cost: $19.45, Avg Charged: $11.25
• Margin: -$8.20/shipment (-42.2%) 🚨 CRITICAL LOSS
• Total Loss: -$8.20

═══════════════════════════════════════════════════════════════

KEY INSIGHTS:

🚨 CRITICAL: Zones 5-8 are hemorrhaging money

• 9 shipments at average -$3.76 each = -$33.81/week
• These zones (33.3% of volume) account for 127.8% of total losses
  RECOMMENDATION: Implement zone-based pricing immediately
• Zone 5: Increase to $13.00 (+$1.75)
• Zone 6: Increase to $15.50 (+$4.25)
• Zone 7: Increase to $18.50 (+$7.25)
• Zone 8: Increase to $20.00 (+$8.75)
  Projected impact: +$38.31/week ($1,992/year)

🚨 Flat $9.50/$11.25 pricing model is broken

• You’re losing $1.76 per shipment on average
• Only Zones 2-4 (40% of volume) are profitable
  RECOMMENDATION: Switch to zone-based or weight-tiered pricing
  Projected impact: Eliminate all shipping losses

═══════════════════════════════════════════════════════════════

DOWNLOADS:

✓ /tmp/consolidated_output.csv (15 rows, all shipments with costs/revenue/margins)
✓ /tmp/shipping_daily_breakdown.csv (8 rows, daily metrics from Dec 1-8)