freightclub-analysis

name	freightclub-analysis
description	FreightClub/ShipStation Shipping Data Analysis v3.0 - Enhanced with Revenue Architect rubrics. Triggers on: "freightclub analysis", "shipping data analysis", "PLD analysis", "shipstation export" Use when: Customer provides shipping data export for rate analysis or FirstMile opportunity assessment.

FreightClub Shipping Data Analysis v3.0

Enhanced with Revenue Architect Practice Gym rubrics for Customer Shipping Analysis

What Changed from v2.0

v2.0	v3.0 Enhancement
Data tables only	+ "So what?" insights after each section
No savings estimate	+ FirstMile opportunity with $/annual projection
No confidence levels	+ Data quality flags (Confirmed/Inferred/Missing)
Generic distributions	+ Network fit analysis (Select vs National)
No next steps	+ Actionable recommendations with timeline
7 sheets	+ 2 sheets: FirstMile Opportunity, Recommendations

---

Rubric Alignment (Score Target: 4.5+/5)

Dimension	How v3.0 Addresses
Data Accuracy	Confidence levels on every metric; data quality audit section
Insight Clarity	"Key Insight" box after every distribution table
Savings Credibility	Conservative/moderate/aggressive scenarios with math shown
Visual Communication	Executive dashboard with traffic lights; highlighted findings
Geographic Intelligence	Hub mapping; zone-skip quantification; Select eligibility %
Actionable Next Steps	Recommendations sheet with pilot plan and timeline

---

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

Data Source	Best Tool	Notes
ShipStation export	Python + pandas	Handle large files
FreightClub export	Python + pandas	Similar structure
Small sample (<1000)	Excel formulas	Quick analysis
Customer PLD	Python	May need column mapping

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

Version	Date	Changes
v3.0	Dec 2025	Rubric integration, FirstMile opportunity sheet, recommendations
v2.0	Dec 2025	Carrier inference, enhanced service level sheet
v1.0	Dec 2025	Initial 7-sheet structure

---

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