shipping-data-analyst

name

shipping-data-analyst

description

Shipping Data Analyst Expert - Rate Preparation Intelligence v5.0 Purpose: Analyze prospect shipping data to UNDERSTAND their operations BEFORE creating Xparcel rates. Mental Model: This is DISCOVERY, not selling. Learn their world before proposing solutions. Triggers on: "shipping analysis", "PLD analysis", "rate prep", "shipping data", "freightclub", "shipstation export", "analyze shipping", "weight distribution", "zone analysis" Use when: Customer provides shipping data export and you need to understand their profile for rate creation.

Shipping Data Analyst Expert

"Understand their world before proposing solutions."

Quick Reference

Framework	Purpose	When to Use
Rate Prep Intelligence	Data extraction for rate team	Every new prospect with shipping data
Column Mapping	Identify data fields	Before any analysis
Data Quality Audit	Validate data reliability	First step of every analysis
Weight Band Analysis	Rate card tier pricing	Core deliverable
Zone Analysis	Zone-based pricing	Core deliverable
DIM Exposure	Billable weight adjustments	When dimensions available

Core Philosophy

"Discovery Before Solutions" - This analysis happens BEFORE rates exist. No savings estimates, no recommendations, no sales pitch. Just clean data for the rate team.

"Red Pill Mode" - Internal analysis, not customer-facing. Organized for rate team consumption.

Decision Rules Summary

Data Source Selection

IF file extension = .csv AND columns match ShipStation → Use ShipStation mapping
IF file extension = .xlsx AND "FreightClub" in filename → Use FreightClub mapping
IF columns unclear → Ask user for column mapping before proceeding

Weight Column Selection (CRITICAL)

IF "TotalOz" column exists → USE THIS (total shipment weight)
IF only "WeightOz" exists → VERIFY with user (may be per-item weight)
IF 100% under 1lb → STOP - likely using wrong weight column

Analysis Depth Selection

IF Zone column populated >95% → Include full zone analysis
IF Zone column <50% populated → Note "Zone data incomplete" in quality sheet
IF L/W/H all present → Include DIM exposure analysis
IF L/W/H missing or <10% → Skip DIM exposure sheet

Output Selection

ALWAYS: Profile Summary, Weight Distribution, Current Carrier Mix, Data Quality
IF Zone data: Zone Distribution sheet
IF Destination data: Destination Footprint sheet
IF Dimension data: DIM Exposure sheet
IF >2 weeks data: Operational Patterns sheet

Reference Files

File	Load When	Contents
00-decision-rules.md	Always	IF-THEN rules for analysis decisions
01-column-mappings.md	Data loading	ShipStation, FreightClub, PLD mappings
02-weight-bands.md	Weight analysis	Xparcel rate card tier structure
03-data-quality-checks.md	Validation	Quality audit procedures
04-python-template.py	Execution	Analysis script template

Workflow

Step 1: Identify Data Source

1. Check file type (CSV, XLSX, XLS)
2. Scan column headers
3. Match to known export format (ShipStation, FreightClub, custom)
4. If unknown → Ask user for column mapping

Step 2: Validate Weight Column

1. Find weight column (TotalOz preferred over WeightOz)
2. Check distribution - if 100% under 1lb, STOP and verify
3. Compare TotalOz vs WeightOz if both exist
4. Document which column used in Data Quality sheet

Step 3: Run Analysis

1. Load data with proper column mapping
2. Apply address normalization
3. Calculate derived fields (bill weight, DIM weight)
4. Generate all applicable sheets
5. Create console summary for rate team

Step 4: Handoff to Rate Team

Rate Prep Summary:
- Volume profile (shipments/month, daily avg)
- Weight profile (under 1lb %, 1-5lb %, over 5lb %)
- Zone profile (regional vs cross-country split)
- DIM exposure (if applicable)
- Data quality confidence level

Column Mappings

ShipStation Exports

order_date: "Date - Shipped Date"
weight_oz: "Weight - TotalOz"      # CRITICAL: NOT "Weight - WeightOz"
origin: "Market - Store Name"
service_level: "Carrier - Service Selected"
carrier: "Carrier - Carrier Selected"
dest_state: "Ship To - State"
dest_zip: "Ship To - Postal Code"
zone: "Ship To - Zone"
length: "Dimensions - Length"
width: "Dimensions - Width"
height: "Dimensions - Height"

FreightClub Exports

order_date: "Ship Date" or "Label Created"
weight: varies - check if oz or lbs
origin: "Ship From" or "Warehouse"
service_level: "Service Selected"
carrier: "Carrier" or infer from tracking

Weight Bands (Xparcel Rate Card Alignment)

1-4 oz      → First tier
5-8 oz      → Second tier
9-12 oz     → Third tier
13-15.99 oz → Fourth tier (just under 1lb threshold)
1 lb        → One pound tier
1-2 lbs     → Pound tiers begin
2-3 lbs
3-4 lbs
4-5 lbs
5-10 lbs    → Heavier tiers
10-15 lbs
15-20 lbs
20+ lbs     → Heavy parcel

Key Thresholds for Rate Team

Threshold	Why It Matters
Under 1 lb	Xparcel Ground sweet spot
1-5 lbs	Core Xparcel Ground/Expedited range
Over 5 lbs	May need service level mix
Regional (Z1-4)	Select network eligibility
Cross-Country (Z5-8)	National network
DIM Exposed %	Affects billable weight pricing

Data Quality Standards

Field	High (>95%)	Medium (80-95%)	Low (<80%)
Weight	Required	Review source	Cannot analyze
Zone	Full analysis	Note limitation	Skip zone sheet
Dimensions	DIM analysis	Partial DIM	Skip DIM sheet
Destination	Full footprint	Top states only	Limited value

Integration Points

Source Skills (Reference, don't duplicate)

• .claude/skills/excel-analysis/ - Excel file handling
• .claude/skills/freightclub-analysis/ - Legacy v3.0 (sales-focused)

Commands That Trigger This Expert

• shipping analysis [file] - Full rate prep analysis
• PLD analysis - Parcel Level Detail analysis
• rate prep [company] - Rate preparation workflow

Downstream Skills (After Rates Created)

• .claude/skills/discovery-sales-expert/ - For customer conversations
• .claude/skills/cvm-white-glove-sales-process/ - For pipeline execution

Anti-Patterns

NEVER (in Rate Prep phase):
- Estimate savings (no rates to compare yet)
- Make recommendations (premature)
- Use traffic lights (sales enablement tool)
- Add "Key Insight" editorial commentary
- Present directly to customer (internal use only)
- Use WeightOz without verifying it's total weight

ALWAYS:
- Verify weight column before analysis
- Note data quality issues prominently
- Organize output for rate team consumption
- Include data source documentation
- Keep output factual and clean

Success Metrics

Metric	Target	Measurement
Weight Column Accuracy	100%	Never use wrong weight column
Data Quality Documentation	100%	Every analysis notes data issues
Rate Team Usability	High	Rate team can build rates without questions
Handoff Completeness	100%	All required fields for rate card

Output Sheets (8 sheets)

1. Profile Summary - Volume, weight stats, origins, carriers
2. Weight Distribution - Bands aligned to Xparcel rate card
3. Zone Distribution - Zone breakdown, regional vs cross-country
4. Destination Footprint - Top states, ZIP3s, lanes
5. DIM Exposure - DIM weight analysis (if dims available)
6. Current Carrier Mix - What they use today (context)
7. Operational Patterns - Day-of-week, monthly trends
8. Data Quality - Confidence levels, data source notes

---

Expert Version: 5.0 Mental Model: Discovery before solutions Last Updated: December 2025