Shipping Carrier Analysis (Enhanced)

Shipping Carrier Analysis Skill v2.0 (Enhanced)

New Features in v2.0

• ✅ Flexible column detection and mapping
• ✅ Data validation and quality checks
• ✅ Automatic data cleaning and normalization
• ✅ Support for multiple data export formats
• ✅ User prompts for missing/ambiguous data

Execution Steps

Step 0: Data Validation and Preparation (NEW)

0.1 Column Detection & Mapping

Intelligently identify columns using fuzzy matching:

Carrier Column - Look for:

• Exact: Carrier, carrier
• Variations: Shipper, Shipping Carrier, Carrier Name, Service Provider, Ship Via

Shipping Method Column - Look for:

• Exact: Shipping Method, Method
• Variations: Service, Service Level, Ship Method, Delivery Method, Ship Type

Cost Column - Look for:

• Exact: Label Cost, Total Cost, Cost
• Variations: Shipping Cost, Ship Cost, Price, Amount, Charge, Rate

Quantity Column - Look for:

• Exact: Quantity shipped, Units Shipped, Quantity
• Variations: Qty, Units, Pieces, Items, Count

Weight Column - Look for:

• Exact: Weight (lb), Total Weight, Weight
• Variations: Wt, Weight in lbs, Package Weight, Shipment Weight

0.2 Data Validation Checks

Required Column Validation:

IF any required column is missing:
  - List detected columns
  - Prompt user: "I couldn't find a [Carrier/Method/Cost/etc.] column.
    Please tell me which column contains this data, or confirm if it's missing."
  - Wait for user input before proceeding

Data Quality Checks:

1. Empty Cells: Count rows with missing required data
2. Data Types: Verify numeric columns contain numbers (Cost, Quantity, Weight)
3. Date Format: Check if dates are properly formatted
4. Consistency: Look for unusual values (negative costs, zero quantities)

0.3 Data Cleaning & Normalization

If data quality issues found, create a "Data_Cleaned" sheet:

Carrier Name Standardization:

ups, UPS, U.P.S → "UPS"
fedex, FedEx, Federal Express → "FedEx"
usps, USPS, U.S.P.S → "USPS"
shippo__usps → "USPS (Shippo)"
amazon_shipping, Amazon, AMZ → "Amazon Shipping"
dhl, DHL Express → "DHL"

Handle Empty/Invalid Values:

• Empty Carrier → "(Unknown Carrier)"
• Empty Method → "(Unspecified)"
• Non-numeric Cost → 0 or flag for review
• Missing Weight → Calculate average or use 0

Text Cleaning:

• Trim extra spaces
• Remove special characters from carrier names
• Standardize capitalization
• Handle merged cells

0.4 Create Normalized Data Sheet

If original data needs cleaning:

1. Create new sheet: "Data_Normalized"
2. Copy all data
3. Add standardized columns:
   • Carrier_Clean - Normalized carrier names
   • Method_Clean - Cleaned method descriptions
   • Cost_Numeric - Validated numeric costs
   • Qty_Numeric - Validated quantities
   • Weight_Numeric - Validated weights
4. Flag problematic rows in a Data_Issues column
5. Add summary at top noting:
   • Number of rows processed
   • Issues found and fixed
   • Rows with warnings

Column Mapping Reference: Create a small reference table showing:

Original Column → Mapped To → Data Type
"carrier"       → Carrier   → Text
"ship_cost"     → Cost      → Currency

Step 1: Identify Data (Updated)

• Detect source sheet (original or Data_Normalized)
• Use mapped column positions from Step 0
• Confirm data range includes all valid rows
• Log any skipped rows (if quality issues detected)

Step 2: Create Carrier-Analysis Sheet

Create a new sheet with:

Header: "SHIPPING CARRIER ANALYSIS" with total shipment count

Carrier Summary Table (columns):

• Carrier
• Shipments
• % of Total
• Total Cost
• Avg Cost/Shipment
• Units Shipped
• Total Weight

Use formulas referencing source data. Include TOTAL row with SUM formulas.

Shipping Method Breakdown (columns):

• Carrier
• Shipping Method
• Shipments
• % of Total
• Total Cost
• Avg Cost

Top 20 methods by volume.

Key Metrics Section:

• Total shipments
• Total cost
• Avg cost
• Most/least cost-effective carriers

Charts:

• Pie chart for carrier volume
• Bar chart for costs

Step 3: Create Carrier-Pivot Sheet

Add pivot table with:

Row fields: Carrier, Shipping Method (hierarchical)

Value fields:

• Count of shipments
• Sum of Label Cost
• Average of Label Cost
• Sum of Quantity shipped
• Sum of Weight

Step 4: Create Dashboard (First Sheet)

Build executive dashboard with:

KPI Cards:

• Total Shipments
• Total Cost
• Avg Cost/Shipment
• Total Units

Top 5 Carriers table with COUNTIF formulas

Top 5 Methods table with COUNTIF/SUMIF formulas

Cost-Saving Opportunities:

• Opportunity 1: Carrier optimization (shift expensive to cheaper ground carriers)
• Opportunity 2: Reduce premium services (2nd Day Air → Ground)
• Calculate potential savings with formulas

Charts:

• Carrier mix pie chart
• Cost comparison bar chart

Data Quality Summary (if Step 0 processing occurred):

Source: [Original Sheet Name]
Rows Processed: [10,427]
Rows with Issues: [127]
Data Cleaning: [Applied/Not Needed]
Column Mapping: [Show mappings]

Enhanced Expected Data Formats

Format 1: Well-Structured (Original)

• ✅ Clean headers in Row 1
• ✅ All required columns present with standard names
• ✅ Data properly formatted
• → Processes directly, no preprocessing needed

Format 2: Messy Export (NEW Support)

• ⚠️ Inconsistent column names
• ⚠️ Mixed case carrier names
• ⚠️ Empty cells scattered throughout
• ⚠️ Text in numeric columns
• → Step 0 cleans and normalizes

Format 3: Partial Data (NEW Support)

• ⚠️ Some required columns missing
• ⚠️ User needs to map manually
• → Interactive prompts guide user

User Interaction Examples

Example 1: Missing Column

AI: "I found columns: Order_ID, Ship_Via, Service, Cost_USD, Quantity
     I couldn't identify a 'Carrier' column.
     Is 'Ship_Via' the carrier column? Or is it named differently?"

User: "Yes, Ship_Via is the carrier"

AI: "Got it! Mapping Ship_Via → Carrier. Proceeding with analysis..."

Example 2: Data Quality Issues

AI: "I detected 127 rows with quality issues:
     - 15 rows with empty carrier names
     - 8 rows with non-numeric costs
     - 104 rows with missing weight data

     I'll create a cleaned version in 'Data_Normalized' sheet
     and flag these issues. Continue?"

User: "Yes"

AI: "Creating normalized data sheet..."

Example 3: Ambiguous Columns

AI: "I found two possible cost columns: 'Base_Cost' and 'Total_Cost'
     Which should I use for the analysis?"

User: "Use Total_Cost"

AI: "Using Total_Cost for analysis..."

Formatting Standards

• Font: Aptos Narrow, 10-12pt for data, 14-20pt for headers
• Colors:
  • #1F4E79 (dark blue headers)
  • #2E75B6 (section headers)
  • #548235 (savings/green)
  • #C65911 (orange highlights)
• Currency: Accounting format for costs
• Percentages: 0.0% format
• Numbers: #,##0 with thousands separator
• Hide gridlines for clean presentation

Key Formulas

# Count shipments by carrier
=COUNTIF(Sheet1!M:M,"carrier_name")

# Sum costs by method
=SUMIF(Sheet1!N:N,"method",Sheet1!AB:AB)

# Average cost calculation
=SUMIF(...)/COUNTIF(...)

# Standardize carrier names
=IFS(
  LOWER(A2)="ups", "UPS",
  LOWER(A2)="fedex", "FedEx",
  LOWER(A2)="usps", "USPS",
  ISNUMBER(SEARCH("shippo",LOWER(A2))), "USPS (Shippo)",
  TRUE, PROPER(TRIM(A2))
)

# Convert text to numeric with error handling
=IFERROR(VALUE(B2), 0)

# Flag data issues
=IF(OR(ISBLANK(A2), NOT(ISNUMBER(C2)), C2<0),
    "⚠️ Review Needed",
    "✓")

Output Structure

[workbook].xlsx
├── Dashboard (Sheet 1)
├── Carrier-Analysis
├── Carrier-Pivot
└── Data_Normalized (if cleaning applied)

This enhanced version handles raw data exports from ShipStation, Shopify, WMS systems, or any shipping platform—not just perfectly formatted PLD data.