Freightclub Analysis

FreightClub Shipping Data Analysis v3.0

Enhanced with Revenue Architect Practice Gym rubrics for Customer Shipping Analysis

What Changed from v2.0

Rubric Alignment (Score Target: 4.5+/5)

Enhanced Column Mapping

Required Fields

order_date: "Date column (Ship Date, Order Date, Label Created)"
weight_total: "TOTAL shipment weight (not per-item)"
  - CRITICAL: Verify this is total, not item weight
  - Check by comparing to item count
carrier: "Carrier column (or infer from tracking)"
service_level: "Service Selected column"
destination_state: "Ship To State"
destination_zip: "Ship To Postal Code (for zone calculation)"
zone: "Zone column (if available)"
shipping_cost: "Amount paid for shipping"

Data Quality Checks (NEW)

# Run these BEFORE analysis
quality_checks = {
    "weight_validation": "Compare Weight vs TotalOz columns",
    "date_range": "Verify date range matches expected period",
    "null_audit": "Count nulls per column, flag if >5%",
    "outlier_detection": "Flag weights >150 lbs, costs >$100",
    "zone_validation": "Verify zones 1-8 only, no blanks"
}

Enhanced Output Structure (9 Sheets)

Sheet 1: Executive Dashboard (NEW FORMAT)

## SHIPPING PROFILE: [Company Name]

**Analysis Date**: [Date]
**Data Period**: [Start] to [End] ([X] days)
**Data Quality Score**: [A/B/C] - [Notes on any issues]

---

### Traffic Light Summary

| Metric | Value | Status | Insight |
|--------|-------|--------|---------|
| Daily Volume | 197 | GREEN | Meets FirstMile 100+/day threshold |
| Avg Weight | 1.28 lbs | GREEN | Sweet spot for Xparcel Ground |
| Cross-Country % | 56.4% | YELLOW | Zone-skip opportunity via Select |
| Carrier Diversity | 3 carriers | GREEN | Not locked into single carrier |

---

### Key Findings (Top 3)

1. **Volume Fit**: 6,000/month exceeds FirstMile minimum; qualifies for volume pricing
2. **Weight Profile**: 99% under 5 lbs = Xparcel Ground/Expedited candidate
3. **Zone Opportunity**: 56% zones 5-8 = significant zone-skip savings potential

---

### Data Confidence Levels

| Field | Status | Notes |
|-------|--------|-------|
| Volume | CONFIRMED | Full 31-day sample |
| Weight | CONFIRMED | Used TotalOz (verified vs WeightOz) |
| Carrier | CONFIRMED | Explicit in data |
| Zone | CONFIRMED | Populated for 100% of records |
| Cost | CONFIRMED | Shipping cost column present |
| Origin | INFERRED | Store name used as proxy |

Sheet 2-7: (Same as v2.0 but with Key Insight boxes)

Each distribution sheet adds:

### KEY INSIGHT
[One sentence explaining what this data means for FirstMile fit]
[One sentence on action implication]

Sheet 8: FirstMile Opportunity Assessment (NEW)

## FIRSTMILE OPPORTUNITY: [Company Name]

### Network Allocation

| Network | Eligibility | Volume | % | Notes |
|---------|-------------|--------|---|-------|
| Select (Metro Hubs) | Zones 1-4 + major metros | X,XXX | XX% | Zone-skip advantage |
| National (All ZIPs) | Remaining volume | X,XXX | XX% | Full coverage |

### Xparcel Service Mapping

| Current Service | Volume | Recommended Xparcel | Rationale |
|-----------------|--------|---------------------|-----------|
| USPS First Class | 2,636 | Xparcel Ground | Sub-1lb, 3-8 day acceptable |
| UPS Ground Saver | 1,638 | Xparcel Expedited | 1-5 lb, faster transit needed |
| USPS Priority | 991 | Xparcel Ground | Cost optimization |

### Savings Estimate

| Scenario | Assumption | Annual Savings | % Reduction |
|----------|------------|----------------|-------------|
| Conservative | 10% rate improvement | $XX,XXX | 10% |
| Moderate | 15% rate improvement | $XX,XXX | 15% |
| Aggressive | 20% + zone-skip | $XX,XXX | 20% |

**Methodology**: Current avg cost ($6.86) × volume × rate improvement %

### Optimization Flags

| Flag | Count | % | Opportunity |
|------|-------|---|-------------|
| 15.99oz threshold | XXX | XX% | Move to 1-lb rate |
| Zone 8 premium | XXX | XX% | Consider regional fulfillment |
| Residential surcharge | XXX | XX% | Already priced; no change |

Sheet 9: Recommendations & Next Steps (NEW)

## RECOMMENDATIONS: [Company Name]

### Recommended Actions

| Priority | Action | Owner | Timeline | Expected Impact |
|----------|--------|-------|----------|-----------------|
| 1 | Run 500-shipment pilot on Xparcel Ground | FirstMile + Customer | Week 1-2 | Validate transit times |
| 2 | Compare pilot costs to current USPS | Customer | Week 3 | Confirm savings |
| 3 | Phase 1 rollout: Sub-1lb volume | Both | Week 4+ | Capture quick wins |
| 4 | Phase 2: Evaluate Xparcel Expedited for UPS volume | Both | Month 2 | Additional savings |

### Pilot Plan

**Scope**: 500 shipments over 2 weeks
**Service**: Xparcel Ground
**Target Volume**: Sub-1lb, Zones 5-8 (highest savings potential)
**Success Criteria**:
- Transit ≤8 days for 90%+
- Cost ≤$5.50 avg (vs current $6.86)
- Claims rate <1%

### Questions to Resolve

- [ ] Contract status with current carriers (penalties?)
- [ ] Peak season volume expectations
- [ ] Integration requirements (API vs manual)
- [ ] Decision-maker for carrier changes

Process Improvements

Pre-Analysis Checklist

• Identify correct weight column (Total vs Item weight)
• Verify date column and format
• Check for tracking numbers (carrier inference)
• Confirm origin vs destination fields
• Note any data quality issues

Post-Analysis Checklist

• All confidence levels documented
• Key Insight added to each section
• Savings calculated with methodology shown
• Recommendations include timeline
• Pilot plan is specific and actionable

Tool Selection Guide

MCP Tools Used

• BrightData: Web scraping for carrier rate benchmarks
• Perplexity: Current carrier pricing research
• Context7: pandas/openpyxl documentation

Anti-Patterns

NEVER:
- Present weight without verifying Total vs Item
- Skip data quality audit
- Show distributions without "so what?" insight
- Omit savings estimate (even rough)
- End without recommendations
- Use generic FirstMile pitch (customize to data)

ALWAYS:
- Verify weight column is total shipment weight
- Flag data quality issues upfront
- Connect every metric to FirstMile opportunity
- Show savings methodology (not just number)
- Include specific pilot plan
- Document confidence levels

Version History

Skill Version: 3.0 Framework: Revenue Architect Practice Gym Rubric 1 Last Updated: December 2025