Return Method

{
  "event": [
    {
      "eventInfo": {
        "eventName": "refund",
        "eventAction": "refund_approved",
        "type": "refund_approval"
      }
    }
  ],
  "transaction": {
    "transactionID": "T-100482",
    "attributes": {
      "rmaID": "RMA-55821",
      "returnMethod": "carrier_drop_off",
      "returnMethodSelected": "carrier_drop_off",
      "mixedMethod": true
    },
    "total": {
      "currency": "USD",
      "basePrice": 184.97
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-24018",
          "productName": "Trail Running Shoe"
        },
        "category": {
          "primaryCategory": "Footwear"
        },
        "quantity": 1,
        "price": {
          "basePrice": 129.99,
          "currency": "USD"
        },
        "attributes": {
          "rmaLineID": "RMA-55821-1",
          "returnMethod": "carrier_drop_off",
          "returnMethodSelected": "carrier_drop_off",
          "returnReason": "size_too_small"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-31177",
          "productName": "Merino Crew Sock 2-Pack"
        },
        "category": {
          "primaryCategory": "Accessories"
        },
        "quantity": 1,
        "price": {
          "basePrice": 54.98,
          "currency": "USD"
        },
        "attributes": {
          "rmaLineID": "RMA-55821-2",
          "returnMethod": "mail_in",
          "returnMethodSelected": "mail_in",
          "returnReason": "not_as_described"
        }
      }
    ]
  }
}

{
  "event": "refund",
  "rma_id": "RMA-55821",
  "return_method": "carrier_drop_off",
  "return_method_selected": "carrier_drop_off",
  "mixed_method": true,
  "ecommerce": {
    "transaction_id": "T-100482",
    "currency": "USD",
    "value": 184.97,
    "items": [
      {
        "item_id": "SKU-24018",
        "item_name": "Trail Running Shoe",
        "item_category": "Footwear",
        "price": 129.99,
        "quantity": 1,
        "rma_line_id": "RMA-55821-1",
        "return_method": "carrier_drop_off",
        "return_method_selected": "carrier_drop_off",
        "return_reason": "size_too_small"
      },
      {
        "item_id": "SKU-31177",
        "item_name": "Merino Crew Sock 2-Pack",
        "item_category": "Accessories",
        "price": 54.98,
        "quantity": 1,
        "rma_line_id": "RMA-55821-2",
        "return_method": "mail_in",
        "return_method_selected": "mail_in",
        "return_reason": "not_as_described"
      }
    ]
  }
}

{
  "event": "refund",
  "eventInfo": {
    "linkName": "refund",
    "linkType": "other",
    "source": "refund_approval"
  },
  "transaction": {
    "orderID": "T-100482",
    "rmaID": "RMA-55821",
    "currency": "USD",
    "total": 184.97,
    "returnMethod": "carrier_drop_off",
    "returnMethodSelected": "carrier_drop_off",
    "mixedMethod": true
  },
  "products": [
    {
      "productID": "SKU-24018",
      "productName": "Trail Running Shoe",
      "category": "Footwear",
      "quantity": 1,
      "price": 129.99,
      "rmaLineID": "RMA-55821-1",
      "returnMethod": "carrier_drop_off",
      "returnMethodSelected": "carrier_drop_off",
      "returnReason": "size_too_small"
    },
    {
      "productID": "SKU-31177",
      "productName": "Merino Crew Sock 2-Pack",
      "category": "Accessories",
      "quantity": 1,
      "price": 54.98,
      "rmaLineID": "RMA-55821-2",
      "returnMethod": "mail_in",
      "returnMethodSelected": "mail_in",
      "returnReason": "not_as_described"
    }
  ]
}

{
  "web": {
    "webInteraction": {
      "name": "refund",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "commerce": {
    "order": {
      "purchaseID": "T-100482",
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-24018",
      "name": "Trail Running Shoe",
      "quantity": 1,
      "priceTotal": 129.99,
      "_tenant": {
        "returns": {
          "rmaLineID": "RMA-55821-1"
        }
      },
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar61": "carrier_drop_off",
              "eVar62": "carrier_drop_off"
            }
          }
        }
      }
    },
    {
      "SKU": "SKU-31177",
      "name": "Merino Crew Sock 2-Pack",
      "quantity": 1,
      "priceTotal": 54.98,
      "_tenant": {
        "returns": {
          "rmaLineID": "RMA-55821-2"
        }
      },
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar61": "mail_in",
              "eVar62": "mail_in"
            }
          }
        }
      }
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar60": "RMA-55821",
          "eVar61": "carrier_drop_off",
          "eVar62": "carrier_drop_off"
        }
      }
    }
  },
  "_tenant": {
    "returns": {
      "rmaID": "RMA-55821",
      "returnMethod": "carrier_drop_off",
      "returnMethodSelected": "carrier_drop_off",
      "mixedMethod": true,
      "returnSource": "refund_approval"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Return Method at return-line (RMA line) grain. The base is every completed customer return line in the returns platform / OMS,
-- so in-store (BORIS) returns at POS, agent-created RMAs and returnless refunds are counted even when there is no returns-portal hit.
-- `your-project.returns_platform.rma_lines` is a stand-in name for the returns platform / OMS RMA-line export
-- (rma_line_id, rma_id, order_id, sku, item_category, quantity, refund_amount, return_origin, line_status, completed_at, updated_at,
--  selected_return_method, effective_return_method). effective_return_method is the method observed at first custody
-- (POS return transaction, carrier acceptance scan, drop-off receipt), locked once the refund is issued.
-- The GA4 refund event (sent once the RMA is confirmed and the refund approved) carries return_method and return_method_selected;
-- it is LEFT JOINed on rma_line_id only to fill the selected method when the platform did not store one.
-- Unknown or free-text values are normalized to '(not set)'.
WITH rma_base AS (
  SELECT
    b.rma_line_id,
    b.rma_id,
    b.order_id AS transaction_id,
    b.sku AS item_id,
    b.item_category,
    COALESCE(b.quantity, 1) AS quantity,
    COALESCE(b.refund_amount, 0) AS line_value,
    b.effective_return_method AS platform_effective_method,
    b.selected_return_method AS platform_selected_method
  FROM `your-project.returns_platform.rma_lines` AS b
  WHERE b.line_status = 'completed'
    -- Exclusions: pre-shipment cancellations, carrier return-to-origin parcels and warehouse recalls are not customer returns;
    -- exchanges and store-credit returns stay in
    AND COALESCE(b.return_origin, 'customer') NOT IN ('pre_shipment_cancellation', 'carrier_return_to_origin', 'warehouse_recall')
    AND DATE(b.completed_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY b.rma_line_id ORDER BY b.updated_at DESC) = 1
),
web_lines AS (
  -- Web refund lines, one row per RMA line (latest event wins); used only for the selected method
  SELECT
    (SELECT MAX(IF(ip.key = 'rma_line_id', ip.value.string_value, NULL)) FROM UNNEST(it.item_params) AS ip) AS rma_line_id,
    COALESCE(
      (SELECT MAX(IF(ip.key = 'return_method_selected', ip.value.string_value, NULL)) FROM UNNEST(it.item_params) AS ip),
      (SELECT MAX(IF(ep.key = 'return_method_selected', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    ) AS web_return_method_selected
  FROM `your-project.analytics_123456789.events_*` AS e,
    UNNEST(e.items) AS it
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                            AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'refund'
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY (SELECT MAX(IF(ip.key = 'rma_line_id', ip.value.string_value, NULL)) FROM UNNEST(it.item_params) AS ip)
    ORDER BY e.event_timestamp DESC
  ) = 1
),
effective AS (
  SELECT
    r.*,
    -- Observed method replaces the provisional selection; lines with no custody event yet keep the selection
    CASE
      WHEN LOWER(TRIM(COALESCE(r.platform_effective_method, r.platform_selected_method, w.web_return_method_selected))) IN ('mail_in', 'carrier_drop_off', 'in_store', 'scheduled_pickup', 'self_ship', 'returnless')
        THEN LOWER(TRIM(COALESCE(r.platform_effective_method, r.platform_selected_method, w.web_return_method_selected)))
      ELSE '(not set)'
    END AS return_method,
    CASE
      WHEN LOWER(TRIM(COALESCE(r.platform_selected_method, w.web_return_method_selected))) IN ('mail_in', 'carrier_drop_off', 'in_store', 'scheduled_pickup', 'self_ship', 'returnless')
        THEN LOWER(TRIM(COALESCE(r.platform_selected_method, w.web_return_method_selected)))
      ELSE '(not set)'
    END AS return_method_selected
  FROM rma_base AS r
  LEFT JOIN web_lines AS w
    ON w.rma_line_id = r.rma_line_id
),
rma_mix AS (
  -- mixed_method attribute: lines of the same RMA use different known methods ('(not set)' lines are not compared)
  SELECT
    rma_id,
    COUNT(DISTINCT IF(return_method != '(not set)', return_method, NULL)) > 1 AS mixed_method
  FROM effective
  GROUP BY rma_id
),
flagged AS (
  SELECT
    f.*,
    COALESCE(m.mixed_method, FALSE) AS mixed_method
  FROM effective AS f
  LEFT JOIN rma_mix AS m
    ON m.rma_id = f.rma_id
)
SELECT
  return_method,
  COUNT(*) AS return_lines,
  COUNT(DISTINCT rma_id) AS rmas,
  COUNT(DISTINCT IF(mixed_method, rma_id, NULL)) AS mixed_method_rmas,
  SUM(quantity) AS return_units,
  ROUND(SUM(line_value), 2) AS returned_value,
  -- Share denominator = all completed customer return lines in the period
  SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER ()) AS share_of_return_lines,
  COUNTIF(return_method_selected != '(not set)' AND return_method != return_method_selected) AS lines_switched_from_selection,
  -- Switch rate denominator = all return lines with this effective method
  SAFE_DIVIDE(COUNTIF(return_method_selected != '(not set)' AND return_method != return_method_selected), COUNT(*)) AS selection_switch_rate
FROM flagged
GROUP BY return_method
ORDER BY return_lines DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Return Method at return-line (RMA line) grain. The base is every completed customer return line in aep_returns_platform_dataset,
-- a stand-in name for the returns platform / OMS RMA-line dataset (rma_line_id, rma_id, order_id, sku, quantity, refund_amount,
-- return_origin, line_status, completed_at, updated_at, selected_return_method, effective_return_method), so in-store (BORIS) returns,
-- agent-created RMAs and returnless refunds are counted even without a returns-portal hit.
-- The web refund interaction (sent once the RMA is confirmed and the refund approved) is LEFT JOINed on the RMA line ID
-- (productListItems[]._tenant.returns.rmaLineID) only to fill the selected method (line eVar62, then _tenant.returns.returnMethodSelected).
-- Unknown or free-text values are normalized to '(not set)'.
WITH rma_base AS (
  SELECT *
  FROM (
    SELECT
      b.rma_line_id,
      b.rma_id,
      b.order_id AS transaction_id,
      b.sku,
      COALESCE(b.quantity, 1) AS quantity,
      COALESCE(b.refund_amount, 0) AS line_value,
      b.effective_return_method AS platform_effective_method,
      b.selected_return_method AS platform_selected_method,
      ROW_NUMBER() OVER (PARTITION BY b.rma_line_id ORDER BY b.updated_at DESC) AS rn
    FROM aep_returns_platform_dataset AS b
    WHERE b.line_status = 'completed'
      -- Exclusions: pre-shipment cancellations, carrier return-to-origin parcels and warehouse recalls are not customer returns
      AND COALESCE(b.return_origin, 'customer') NOT IN ('pre_shipment_cancellation', 'carrier_return_to_origin', 'warehouse_recall')
      AND b.completed_at >= date_sub(current_date(), 28)
      AND b.completed_at < current_date()
  ) AS x
  WHERE rn = 1
),
web_lines AS (
  -- Web refund lines, one row per RMA line ID (latest event wins); used only for the selected method
  SELECT rma_line_id, web_return_method_selected
  FROM (
    SELECT
      pli._tenant.returns.rmaLineID AS rma_line_id,
      COALESCE(pli._experience.analytics.customDimensions.eVars.eVar62, e._tenant.returns.returnMethodSelected) AS web_return_method_selected,
      ROW_NUMBER() OVER (PARTITION BY pli._tenant.returns.rmaLineID ORDER BY e.timestamp DESC) AS rn
    FROM aep_web_events_dataset AS e
    LATERAL VIEW explode(e.productListItems) plv AS pli
    WHERE e.web.webInteraction.name = 'refund'
      AND pli._tenant.returns.rmaLineID IS NOT NULL
      AND e.timestamp >= date_sub(current_date(), 90)
      AND e.timestamp < current_date()
  ) AS y
  WHERE rn = 1
),
effective AS (
  SELECT
    r.*,
    -- Observed method replaces the provisional selection; lines with no custody event yet keep the selection
    CASE WHEN LOWER(TRIM(COALESCE(r.platform_effective_method, r.platform_selected_method, w.web_return_method_selected))) IN ('mail_in', 'carrier_drop_off', 'in_store', 'scheduled_pickup', 'self_ship', 'returnless')
         THEN LOWER(TRIM(COALESCE(r.platform_effective_method, r.platform_selected_method, w.web_return_method_selected)))
         ELSE '(not set)' END AS return_method,
    CASE WHEN LOWER(TRIM(COALESCE(r.platform_selected_method, w.web_return_method_selected))) IN ('mail_in', 'carrier_drop_off', 'in_store', 'scheduled_pickup', 'self_ship', 'returnless')
         THEN LOWER(TRIM(COALESCE(r.platform_selected_method, w.web_return_method_selected)))
         ELSE '(not set)' END AS return_method_selected
  FROM rma_base AS r
  LEFT JOIN web_lines AS w
    ON w.rma_line_id = r.rma_line_id
),
rma_mix AS (
  -- mixed_method attribute: lines of the same RMA use different known methods ('(not set)' lines are not compared)
  SELECT
    rma_id,
    COUNT(DISTINCT CASE WHEN return_method <> '(not set)' THEN return_method END) > 1 AS mixed_method
  FROM effective
  GROUP BY rma_id
),
flagged AS (
  SELECT
    f.*,
    COALESCE(m.mixed_method, FALSE) AS mixed_method
  FROM effective AS f
  LEFT JOIN rma_mix AS m
    ON m.rma_id = f.rma_id
)
SELECT
  return_method,
  COUNT(*) AS return_lines,
  COUNT(DISTINCT rma_id) AS rmas,
  COUNT(DISTINCT CASE WHEN mixed_method THEN rma_id END) AS mixed_method_rmas,
  SUM(quantity) AS return_units,
  ROUND(SUM(line_value), 2) AS returned_value,
  -- Share denominator = all completed customer return lines in the period
  COUNT(*) / NULLIF(SUM(COUNT(*)) OVER (), 0) AS share_of_return_lines,
  SUM(CASE WHEN return_method_selected <> '(not set)' AND return_method <> return_method_selected THEN 1 ELSE 0 END) AS lines_switched_from_selection,
  -- Switch rate denominator = all return lines with this effective method
  SUM(CASE WHEN return_method_selected <> '(not set)' AND return_method <> return_method_selected THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS selection_switch_rate
FROM flagged
GROUP BY return_method
ORDER BY return_lines DESC;