| 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)
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
Post-Analysis Checklist
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
