Carrier

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Carrier: normalizes the raw carrier code on each order shipment to the governed carrier name
-- and reports shipment volume and delivery performance by carrier and ship date (shipments shipped in the last 90 days, bucketed in the store time zone).
WITH carrier_map AS (
  SELECT raw_carrier_code, carrier_name, carrier_parent_group, carrier_type
  FROM (
    SELECT
      UPPER(TRIM(m.raw_carrier_code)) AS raw_carrier_code,
      m.carrier_name,
      m.carrier_parent_group,
      m.carrier_type,
      ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(m.raw_carrier_code)) ORDER BY m.updated_at DESC) AS rn
    FROM `your-project.commerce.carrier_code_map` AS m
  )
  WHERE rn = 1
),
shipments AS (
  SELECT
    shipment_id, order_id, carrier_code, service_level, shipped_at, delivered_at,
    promised_delivery_date, shipment_status, shipping_cost, is_test_order, fulfillment_method
  FROM (
    SELECT
      s.*,
      ROW_NUMBER() OVER (PARTITION BY s.shipment_id ORDER BY s.updated_at DESC) AS rn
    FROM `your-project.commerce.order_shipments` AS s
    -- Replace 'America/New_York' with the store's own time zone.
    WHERE DATE(s.shipped_at, 'America/New_York') >= DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 90 DAY)
  )
  WHERE rn = 1
    AND COALESCE(is_test_order, FALSE) = FALSE
    AND fulfillment_method IN ('ship_to_address', 'ship_from_store')
),
labelled AS (
  SELECT
    s.*,
    DATE(s.shipped_at, 'America/New_York') AS ship_date,
    CASE
      WHEN s.carrier_code IS NULL OR TRIM(s.carrier_code) = '' THEN 'Unknown Carrier'
      WHEN m.carrier_name IS NULL THEN 'Unmapped Carrier'
      ELSE m.carrier_name
    END AS carrier,
    COALESCE(m.carrier_parent_group, 'Unassigned') AS carrier_parent_group,
    COALESCE(m.carrier_type, 'Unassigned') AS carrier_type
  FROM shipments AS s
  LEFT JOIN carrier_map AS m
    ON UPPER(TRIM(s.carrier_code)) = m.raw_carrier_code
)
SELECT
  ship_date,
  carrier,
  carrier_parent_group,
  carrier_type,
  COUNT(*) AS shipments,
  COUNT(DISTINCT order_id) AS orders,
  COUNTIF(delivered_at IS NOT NULL) AS delivered_shipments,
  -- Denominator: every shipment whose promised date has passed; undelivered ones count as late.
  -- Numerator is restricted to the same population so the rate never exceeds 100%.
  SAFE_DIVIDE(
    COUNTIF(promised_delivery_date < CURRENT_DATE('America/New_York') AND delivered_at IS NOT NULL AND DATE(delivered_at, 'America/New_York') <= promised_delivery_date),
    COUNTIF(promised_delivery_date < CURRENT_DATE('America/New_York'))
  ) AS on_time_delivery_rate,
  SAFE_DIVIDE(
    COUNTIF(shipment_status IN ('failed_attempt', 'returned_to_sender', 'lost')),
    COUNTIF(shipped_at IS NOT NULL)
  ) AS failed_delivery_rate,
  ROUND(AVG(IF(delivered_at IS NOT NULL AND shipped_at IS NOT NULL,
    TIMESTAMP_DIFF(delivered_at, shipped_at, HOUR) / 24.0, NULL)), 2) AS avg_transit_days,
  ROUND(SAFE_DIVIDE(SUM(shipping_cost), NULLIF(COUNTIF(shipping_cost IS NOT NULL), 0)), 2) AS avg_shipping_cost_per_shipment
FROM labelled
GROUP BY ship_date, carrier, carrier_parent_group, carrier_type
ORDER BY ship_date DESC, shipments DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Carrier: same logic against the order shipment and carrier code map datasets ingested from the commerce backend.
WITH carrier_map AS (
  SELECT raw_carrier_code, carrier_name, carrier_parent_group, carrier_type
  FROM (
    SELECT
      UPPER(TRIM(m.raw_carrier_code)) AS raw_carrier_code,
      m.carrier_name,
      m.carrier_parent_group,
      m.carrier_type,
      ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(m.raw_carrier_code)) ORDER BY m.updated_at DESC) AS rn
    FROM commerce_carrier_code_map AS m
  ) AS x
  WHERE rn = 1
),
shipments AS (
  SELECT
    shipment_id, order_id, carrier_code, service_level, shipped_at, delivered_at,
    promised_delivery_date, shipment_status, shipping_cost, is_test_order, fulfillment_method
  FROM (
    SELECT
      s.*,
      ROW_NUMBER() OVER (PARTITION BY s.shipment_id ORDER BY s.updated_at DESC) AS rn
    FROM commerce_order_shipments AS s
    -- Replace 'America/New_York' with the store's own time zone.
    WHERE to_date(from_utc_timestamp(s.shipped_at, 'America/New_York')) >= date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 90)
  ) AS y
  WHERE rn = 1
    AND COALESCE(is_test_order, FALSE) = FALSE
    AND fulfillment_method IN ('ship_to_address', 'ship_from_store')
),
labelled AS (
  SELECT
    s.*,
    to_date(from_utc_timestamp(s.shipped_at, 'America/New_York')) AS ship_date,
    CASE
      WHEN s.carrier_code IS NULL OR TRIM(s.carrier_code) = '' THEN 'Unknown Carrier'
      WHEN m.carrier_name IS NULL THEN 'Unmapped Carrier'
      ELSE m.carrier_name
    END AS carrier,
    COALESCE(m.carrier_parent_group, 'Unassigned') AS carrier_parent_group,
    COALESCE(m.carrier_type, 'Unassigned') AS carrier_type
  FROM shipments AS s
  LEFT JOIN carrier_map AS m
    ON UPPER(TRIM(s.carrier_code)) = m.raw_carrier_code
)
SELECT
  ship_date,
  carrier,
  carrier_parent_group,
  carrier_type,
  COUNT(*) AS shipments,
  COUNT(DISTINCT order_id) AS orders,
  SUM(CASE WHEN delivered_at IS NOT NULL THEN 1 ELSE 0 END) AS delivered_shipments,
  -- Numerator and denominator share the promised-date-passed population so the rate never exceeds 100%.
  SUM(CASE WHEN promised_delivery_date < to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')) AND delivered_at IS NOT NULL AND to_date(from_utc_timestamp(delivered_at, 'America/New_York')) <= promised_delivery_date THEN 1 ELSE 0 END) * 1.0
    / NULLIF(SUM(CASE WHEN promised_delivery_date < to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')) THEN 1 ELSE 0 END), 0) AS on_time_delivery_rate,
  SUM(CASE WHEN shipment_status IN ('failed_attempt', 'returned_to_sender', 'lost') THEN 1 ELSE 0 END) * 1.0
    / NULLIF(SUM(CASE WHEN shipped_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS failed_delivery_rate,
  ROUND(AVG(CASE WHEN delivered_at IS NOT NULL AND shipped_at IS NOT NULL
    THEN (unix_timestamp(delivered_at) - unix_timestamp(shipped_at)) / 86400.0 END), 2) AS avg_transit_days,
  ROUND(SUM(shipping_cost) * 1.0 / NULLIF(SUM(CASE WHEN shipping_cost IS NOT NULL THEN 1 ELSE 0 END), 0), 2) AS avg_shipping_cost_per_shipment
FROM labelled
GROUP BY ship_date, carrier, carrier_parent_group, carrier_type
ORDER BY ship_date DESC, shipments DESC;