Shipping Data Analysis

Shipping Data Analysis Skill

Brock Hansen's Reusable Prompt v4.0 - FirstMile branded shipping analytics Updated: January 7, 2026 - F.R.O.G. Fulfillment analysis learnings

Overview

This skill generates comprehensive shipping analysis workbooks using Brock's standardized v4.0 prompt structure. It produces up to 16 Excel sheets with FirstMile branding, intelligent address normalization, and conditional advanced analytics.

Quick Start

/shipping-analysis [file.xlsx] --company "Company Name"

Required Inputs

Column Mapping (Required)

Date Column Detection Priority

CRITICAL: PLD exports often have empty "Ship Date" columns. Check these in order:

1. Label Processed On - Usually has actual timestamps (e.g., "9/12/2025 2:47:54 PM")
2. Ship Date - Often empty in PLD exports
3. Order Date, Created Date - Fallback options

# Detection logic
if 'Label Processed On' in df.columns:
    date_col = 'Label Processed On'  # Priority - usually has data
else:
    # Fall back to keyword search
    for col in df.columns:
        if 'date' in col.lower() or 'processed' in col.lower():
            # Verify column has actual date values
            test = pd.to_datetime(df[col].dropna().head(10), errors='coerce')
            if test.notna().sum() > 5:
                date_col = col
                break

Optional Columns (Enable Advanced Modules)

Output Structure

Core Sheets (Always Generated)

Conditional Sheets (If Data Exists)

Executive Summary - 3-Column Layout

CRITICAL: Executive Summary must use the 3-column side-by-side layout:

| Metrics (A-B)          | Top Origins (D-F)      | Weight Bands (G-I)     |
|------------------------|------------------------|------------------------|
| Total Shipments: X     | Client: Count (%)      | 1 oz: Count (%)        |
| Date Range: X to Y     | Client: Count (%)      | 2 oz: Count (%)        |
| Days Covered: X        | Client: Count (%)      | ...                    |
| Avg Monthly Volume: X  | ...                    | 15.99 oz: Count (%)    |
| Unique Clients: X      |                        | 1 lb: Count (%)        |
| Avg Weight (lb): X     |                        | 2 lb: Count (%)        |
| Median Weight (lb): X  |                        | ...                    |
| Avg Weight (oz): X     |                        | 50 lb: Count (%)       |
| Median Weight (oz): X  |                        | 50+ lbs: Count (%)     |

Layout Structure

• Row 1: Navy title spanning all columns (A1:I1 merged)
• Row 2: Green section headers (Metric|Value, Top Origin IDs, Weight Band Breakdown)
• Row 3: Green sub-headers for middle/right sections
• Row 4+: Data rows

Weight Distribution Format

CRITICAL: Use individual weights, NOT bands like "0-5 lb" or "5-10 lb".

Correct Format (Individual Weights)

1 oz      181     0.35%
2 oz      194     0.37%
3 oz      937     1.79%
...
15 oz      70     0.13%
15.99 oz  288     0.55%
1 lb    1,768     3.39%
2 lb    1,570     3.01%
...
50 lb       6     0.01%
50+ lbs    15     0.03%

Implementation

weight_data = []
# Individual ounces 1-15
for oz in range(1, 16):
    count = len(df[(df['Weight_oz'] > oz-1) & (df['Weight_oz'] <= oz)])
    if count > 0:
        weight_data.append((f"{oz} oz", count, count/total))

# 15.99 oz (15-16 oz boundary)
count_1599 = len(df[(df['Weight_oz'] > 15) & (df['Weight_oz'] <= 16)])
if count_1599 > 0:
    weight_data.append(("15.99 oz", count_1599, count_1599/total))

# Individual pounds 1-50
for lb in range(1, 51):
    lb_oz_low = lb * 16
    lb_oz_high = (lb + 1) * 16
    count = len(df[(df['Weight_oz'] > lb_oz_low) & (df['Weight_oz'] <= lb_oz_high)])
    if count > 0:
        weight_data.append((f"{lb} lb", count, count/total))

# 50+ lbs
count_50plus = len(df[df['Weight_oz'] > 800])
if count_50plus > 0:
    weight_data.append(("50+ lbs", count_50plus, count_50plus/total))

FirstMile Branding

Formatting Standards

Formatting Functions

def format_pct(val):
    """Format decimal as percentage: 0.792 -> '79.22%'"""
    if isinstance(val, (int, float)):
        return f"{val * 100:.2f}%"
    return val

def format_num(val):
    """Format number with commas: 41368 -> '41,368'"""
    if isinstance(val, (int, float)):
        return f"{int(val):,}"
    return val

Key Features

Address Normalization

Combines address variations like a pivot table:

• "300 Seabrook Parkway" + "300 SEABROOK PKWY" → Same warehouse
• Uppercase, standardize abbreviations (ST, AVE, BLVD)
• Reduces unique addresses by ~10-15%

If no Ship Address column: Use Client Name as origin proxy (common in 3PL data)

DIM Exposure Analysis

• Standard DIM divisor: 139 (166 for USPS)
• Calculates: dim_weight = (L x W x H) / 139 * 16
• Identifies right-size opportunities

Carrier Inference

From tracking number patterns:

• 9400, 9205, 9208, 9261 → USPS
• 12-15 digit numeric → FedEx
• Starts with "1Z" → UPS

openpyxl Technical Notes

MergedCell Handling

CRITICAL: Always check for MergedCell before accessing cell properties:

from openpyxl.cell.cell import MergedCell

for row in ws.iter_rows():
    for cell in row:
        if isinstance(cell, MergedCell):
            continue  # Skip - MergedCells don't have normal properties
        cell.alignment = center_align
        cell.border = thin_border

Period Objects Cannot Be Written to Excel

When using pandas to_period(), the resulting Period objects will cause errors when writing to Excel. Drop temporary columns before writing raw data:

# Drop temp columns before Raw Data Sample sheet
temp_cols = ['_Month', 'Weight_oz', 'Weight_lb', 'DIM_Weight']
sample = df.drop(columns=[c for c in temp_cols if c in df.columns], errors='ignore')

File Locking

If output file is open in Excel, you'll get PermissionError. Either:

1. Close the file in Excel
2. Change output filename (e.g., v6 → v7)

Usage Examples

Example 1: Full Analysis (F.R.O.G. Pattern)

File: PLD 9.12 - 10.12.xlsx (+ 2 more PLD files)
Company: F.R.O.G. Fulfillment

Column Mapping:
- Date: "Label Processed On" (NOT "Ship Date" - it's empty)
- Origin Proxy: "Client Name" (they're a 3PL)
- Service Level: "Service Code Description"
- Carrier: "Carrier"
- Weight (lbs): "Weight"
- Destination State: "Ship To State"
- Dimensions: "Box Length", "Box Width", "Box Height"

Output: 11-sheet workbook with 3-column Executive Summary

Example 2: Basic Analysis (Limited Data)

File: Client_Export.xlsx
Company: Beta Brands

Column Mapping:
- Order Date: Column B
- Ship Address: Column G
- Service Level: Column K
- Weight (oz): Column R
- DIMS: L(S), W(T), H(U)

No cost or delivery data. Run core + DIM exposure.

Workflow

1. Upload file and provide company name
2. List all columns - check for "Label Processed On" date column
3. Map columns to standard fields
4. Identify optional columns for advanced modules
5. Generate analysis - produces branded Excel workbook
6. Verify formatting - percentages, commas, alignment
7. Review insights - text summary of findings

Reference Implementation

See scripts/frog_combined_analysis.py for complete working example with:

• 3-column Executive Summary layout
• Individual weight distribution (1 oz → 50+ lbs)
• FirstMile color scheme
• All formatting standards
• MergedCell handling
• Date column detection

Reference Files

• references/full-prompt.md - Complete v4.0 prompt text
• references/column-mapping.md - Detailed field mapping guide
• references/output-tables.md - All table structures

Source: Brock Hansen, VP of Sales - "Prompt for Quick Data Analysis" (Dec 17, 2025) Updated: January 7, 2026 - F.R.O.G. Fulfillment analysis learnings