Returned Items

{
  "transaction": {
    "transactionID": "txn-1",
    "total": {
      "currency": "USD",
      "transactionTotal": 100
    },
    "item": [
      {
        "productInfo": {
          "sku": "SKU-1",
          "productName": "item"
        },
        "category": {
          "primaryCategory": "category"
        },
        "quantity": 1,
        "price": {
          "basePrice": 100
        }
      }
    ],
    "attributes": {
      "refund_id": "rf-1",
      "rma_id": "rma-1",
      "refund_type": "partial",
      "return_method": "mail",
      "return_reason": "size",
      "traffic_type": "external"
    }
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "refund"
      }
    }
  ]
}

{
  "event": "refund",
  "ecommerce": {
    "transaction_id": "txn-1",
    "currency": "USD",
    "value": 100,
    "items": [
      {
        "item_id": "SKU-1",
        "item_name": "item",
        "item_category": "category",
        "price": 100,
        "quantity": 1
      }
    ]
  },
  "refund_id": "rf-1",
  "rma_id": "rma-1",
  "refund_type": "partial",
  "return_method": "mail",
  "return_reason": "size",
  "traffic_type": "external"
}

{
  "event": "refund",
  "eventInfo": {
    "refund_id": "rf-1",
    "rma_id": "rma-1",
    "refund_type": "partial",
    "return_method": "mail",
    "return_reason": "size",
    "traffic_type": "external"
  },
  "commerce": {
    "order": {
      "purchaseID": "txn-1",
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1",
      "name": "item",
      "quantity": 1,
      "priceTotal": 100
    }
  ]
}

{
  "commerce": {
    "order": {
      "purchaseID": "txn-1",
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1",
      "name": "item",
      "quantity": 1,
      "priceTotal": 100
    }
  ],
  "_tenantId": {
    "refund": {
      "refundID": "rf-1",
      "rmaID": "rma-1",
      "refundType": "partial",
      "returnMethod": "mail",
      "returnReason": "size",
      "trafficType": "external"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE start_date DATE DEFAULT DATE_SUB(end_date, INTERVAL 90 DAY);

-- Refund events tied to a return (rma_id present), excluding test orders (traffic_type 'internal' or 'test'), one row per refund_id, dated by the refund posting date; cancellations have no rma_id and drop out
WITH refunds AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'refund_id') AS refund_id,
    ecommerce.transaction_id AS transaction_id,
    items
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'refund'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'rma_id') IS NOT NULL
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'refund_id') IS NOT NULL
    AND COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type'), '') NOT IN ('internal', 'test')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY refund_id ORDER BY event_timestamp) = 1
),
-- Original purchase lines for refunds sent without an items array, one purchase per transaction_id
purchase_lines AS (
  SELECT
    ecommerce.transaction_id AS transaction_id,
    items
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX <= FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'purchase'
    AND ecommerce.transaction_id IN (SELECT transaction_id FROM refunds WHERE ARRAY_LENGTH(items) = 0)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ecommerce.transaction_id ORDER BY event_timestamp) = 1
)
-- Sum returned units: refund item lines, or the purchase lines when the refund has none
SELECT SUM(item.quantity) AS returned_items
FROM refunds r
LEFT JOIN purchase_lines p USING (transaction_id)
CROSS JOIN UNNEST(IF(ARRAY_LENGTH(r.items) > 0, r.items, p.items)) AS item;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window and the property time zone used to date events
WITH params AS (
  SELECT
    date_sub(current_date(), 90) AS start_date,
    current_date() AS end_date,
    'America/New_York' AS property_tz
),
-- Refund events tied to a return (rmaID present), excluding test orders (trafficType 'internal' or 'test'), dated by the refund posting date; cancellations have no rmaID and drop out
refund_hits AS (
  SELECT
    e._tenantId.refund.refundID AS refund_id,
    e.commerce.order.purchaseID AS transaction_id,
    e.productListItems AS items,
    e.timestamp AS event_ts
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE e._tenantId.refund.refundID IS NOT NULL
    AND e._tenantId.refund.rmaID IS NOT NULL
    AND coalesce(e._tenantId.refund.trafficType, '') NOT IN ('internal', 'test')
    AND to_date(from_utc_timestamp(e.timestamp, p.property_tz)) BETWEEN p.start_date AND p.end_date
),
-- One row per refund_id
refunds AS (
  SELECT refund_id, transaction_id, items
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY refund_id ORDER BY event_ts) AS rn
    FROM refund_hits
  ) d
  WHERE rn = 1
),
-- Original purchase lines for refunds sent without an items array, one purchase per transaction_id
purchase_lines AS (
  SELECT transaction_id, items
  FROM (
    SELECT
      e.commerce.order.purchaseID AS transaction_id,
      e.productListItems AS items,
      ROW_NUMBER() OVER (PARTITION BY e.commerce.order.purchaseID ORDER BY e.timestamp) AS rn
    FROM aep_web_events_dataset e
    CROSS JOIN params p
    WHERE e.eventType = 'commerce.purchases'
      AND to_date(from_utc_timestamp(e.timestamp, p.property_tz)) <= p.end_date
      AND e.commerce.order.purchaseID IN (SELECT transaction_id FROM refunds WHERE coalesce(size(items), 0) <= 0)
  ) d
  WHERE rn = 1
),
-- Refund item lines, or the purchase lines when the refund has none
returned_lines AS (
  SELECT explode(CASE WHEN coalesce(size(r.items), 0) > 0 THEN r.items ELSE p.items END) AS item
  FROM refunds r
  LEFT JOIN purchase_lines p ON r.transaction_id = p.transaction_id
)
-- Sum returned units
SELECT SUM(item.quantity) AS returned_items
FROM returned_lines;