freightclub-analysis
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/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. 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。
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