Return to Origin Rate

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Dispatch cohort ends 21 days ago so every shipment in it has matured
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY);
DECLARE start_date DATE DEFAULT DATE_SUB(end_date, INTERVAL 90 DAY);

-- One row per shipment (tracking number): first pickup scan, first RTO scan, lost/damaged flag
WITH shipments AS (
  SELECT
    tracking_number,
    ANY_VALUE(payment_method) AS payment_method,
    MIN(IF(scan_status = 'picked_up', scan_timestamp, NULL)) AS dispatch_ts,
    MIN(IF(scan_status = 'rto_initiated', scan_timestamp, NULL)) AS first_rto_ts,
    LOGICAL_OR(scan_status IN ('lost', 'damaged')) AS is_lost_or_damaged
  FROM `your-project.erp.shipment_scans`
  WHERE scan_timestamp >= TIMESTAMP(start_date)
  GROUP BY tracking_number
)

-- RTO shipments over all dispatched shipments, COD and prepaid reported separately
-- (a pickup scan is required, so orders cancelled before dispatch never enter; customer returns after delivery carry no RTO scan)
SELECT
  IF(payment_method = 'cod', 'COD', 'Prepaid') AS payment_group,
  COUNT(*) AS shipments_dispatched,
  COUNTIF(first_rto_ts IS NOT NULL) AS shipments_returned_to_origin,
  ROUND(SAFE_DIVIDE(COUNTIF(first_rto_ts IS NOT NULL), COUNT(*)) * 100, 2) AS rto_rate_pct
FROM shipments
WHERE DATE(dispatch_ts) BETWEEN start_date AND end_date
  AND NOT is_lost_or_damaged
GROUP BY payment_group
ORDER BY payment_group;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Dispatch cohort ends 21 days ago so every shipment in it has matured
WITH params AS (
  SELECT
    date_sub(current_date(), 21) AS end_date,
    date_sub(current_date(), 111) AS start_date
),

-- One row per shipment (tracking number): first pickup scan, first RTO scan, lost/damaged flag
shipments AS (
  SELECT
    s.tracking_number,
    MAX(s.payment_method) AS payment_method,
    MIN(CASE WHEN s.scan_status = 'picked_up' THEN s.scan_timestamp END) AS dispatch_ts,
    MIN(CASE WHEN s.scan_status = 'rto_initiated' THEN s.scan_timestamp END) AS first_rto_ts,
    MAX(CASE WHEN s.scan_status IN ('lost', 'damaged') THEN 1 ELSE 0 END) AS is_lost_or_damaged
  FROM aep_backend_dataset.shipment_scans s
  CROSS JOIN params p
  WHERE s.scan_timestamp >= CAST(p.start_date AS TIMESTAMP)
  GROUP BY s.tracking_number
)

-- RTO shipments over all dispatched shipments, COD and prepaid reported separately
-- (a pickup scan is required, so orders cancelled before dispatch never enter; customer returns after delivery carry no RTO scan)
SELECT
  CASE WHEN sh.payment_method = 'cod' THEN 'COD' ELSE 'Prepaid' END AS payment_group,
  COUNT(*) AS shipments_dispatched,
  SUM(CASE WHEN sh.first_rto_ts IS NOT NULL THEN 1 ELSE 0 END) AS shipments_returned_to_origin,
  ROUND(100.0 * SUM(CASE WHEN sh.first_rto_ts IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS rto_rate_pct
FROM shipments sh
CROSS JOIN params p
WHERE CAST(sh.dispatch_ts AS DATE) BETWEEN p.start_date AND p.end_date
  AND sh.is_lost_or_damaged = 0
GROUP BY CASE WHEN sh.payment_method = 'cod' THEN 'COD' ELSE 'Prepaid' END
ORDER BY payment_group;