What goes in a shipping cost dashboard?

A shipping dashboard puts label spend next to delivery outcomes, because either one alone leads to a bad decision. Cheap carriers that miss delivery promises cost more in support tickets than they save in postage; premium service on a zone-2 one-pound package is money set on fire. Model labels and tracking events separately and join them.

Which cards belong on this dashboard?

• Total label spend this month and vs. last month
• Shipping cost per order and per shipment
• Shipping cost as a percentage of order value
• Carrier and service-level mix by volume and by spend
• Cost by zone and by billed weight band
• On-time delivery rate against the carrier's promised date
• Transit time median and p90 by carrier and zone
• Voided and refunded labels, with the unused-label rate

What data does this dashboard need?

• A label table: label ID, order ID, carrier, service level, zone, billed weight, cost, purchase timestamp
• Void and refund timestamps on labels, since unused postage is often refundable
• A tracking table with delivery timestamp and the carrier's promised delivery date
• Order value on or joinable to each label for the cost-share view
• Dimensional weight and surcharge lines where the carrier bills them separately

How do you build it?

1. Land the source data in a database — via a managed connector, scheduled API pulls, or a CSV loaded with mb upload csv — keeping raw IDs, timestamps, currencies, and fee lines intact.
2. Build models at the grain this dashboard needs, with gross, fee, tax, and net amounts as explicit columns rather than per-card arithmetic.
3. Create one saved question per card and certify the shared models so every dashboard downstream inherits the same definitions.
4. Add dashboard filters for Date range, carrier, service level, zone, origin warehouse, destination country.
5. Show data freshness on the dashboard — these sources lag, and every viewer should see by how much.

Example card SQL

WITH labels AS (
  SELECT
    l.label_id,
    l.carrier,
    l.service_level,
    l.purchased_at,
    l.label_cost_usd,
    l.order_value_usd,
    CASE
      WHEN l.billed_weight_oz < 16 THEN '0-1 lb'
      WHEN l.billed_weight_oz < 80 THEN '1-5 lb'
      WHEN l.billed_weight_oz < 320 THEN '5-20 lb'
      ELSE '20 lb+'
    END AS weight_band
  FROM shipping_labels l
  WHERE l.purchased_at >= CURRENT_DATE - INTERVAL '90 days'
    -- Voided labels were never shipped; leaving them in inflates
    -- both spend and the on-time denominator.
    AND l.voided_at IS NULL
), joined AS (
  SELECT
    lb.*,
    t.delivered_at,
    t.promised_delivery_date,
    EXTRACT(EPOCH FROM (t.delivered_at - lb.purchased_at)) / 86400.0
      AS transit_days
  FROM labels lb
  LEFT JOIN shipment_tracking t ON t.label_id = lb.label_id
)
SELECT
  carrier,
  service_level,
  weight_band,
  COUNT(*) AS labels,
  ROUND(AVG(label_cost_usd), 2) AS avg_label_cost,
  ROUND(
    100.0 * SUM(label_cost_usd) / NULLIF(SUM(order_value_usd), 0), 1
  ) AS cost_pct_of_order_value,
  -- On-time rate has its own denominator: only shipments that
  -- actually reported a delivery scan can be judged.
  COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS tracked,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE delivered_at::date <= promised_delivery_date
    ) / NULLIF(COUNT(*) FILTER (WHERE delivered_at IS NOT NULL), 0), 1
  ) AS on_time_pct,
  ROUND(
    percentile_cont(0.9) WITHIN GROUP (ORDER BY transit_days)::numeric, 1
  ) AS p90_transit_days
FROM joined
GROUP BY 1, 2, 3
ORDER BY labels DESC;

Related

Dashboards

Integrations

Metrics

Analytics

FAQ