Affiliation

{
  "transaction": {
    "transactionID": "txn-1",
    "attributes": {
      "affiliation": "store-1"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-1"
        },
        "attributes": {
          "affiliation": "store-1"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "affiliation": "store-1",
  "ecommerce": {
    "transaction_id": "txn-1",
    "currency": "USD",
    "value": 100,
    "items": [
      {
        "item_id": "SKU-1",
        "item_name": "item",
        "affiliation": "store-1",
        "price": 100,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "eventInfo": {
    "affiliation": "store-1"
  },
  "xdm": {
    "commerce": {
      "purchases": {
        "value": 1
      },
      "order": {
        "purchaseID": "txn-1",
        "currencyCode": "USD"
      }
    },
    "productListItems": [
      {
        "SKU": "SKU-1",
        "quantity": 1,
        "priceTotal": 100,
        "_tenantId": {
          "affiliation": "store-1"
        }
      }
    ],
    "_tenantId": {
      "affiliation": "store-1"
    }
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "txn-1",
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1",
      "quantity": 1,
      "priceTotal": 100,
      "_tenantId": {
        "affiliation": "store-1"
      }
    }
  ],
  "_tenantId": {
    "affiliation": "store-1"
  }
}

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

WITH txn_items AS (
  -- Purchase and refund rows per item with item-level and order-level affiliation (blank treated as missing)
  SELECT
    e.event_name,
    e.ecommerce.transaction_id AS transaction_id,
    i.item_id,
    NULLIF(TRIM(i.affiliation), '') AS item_affiliation,
    NULLIF(TRIM((SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'affiliation')), '') AS order_affiliation
  FROM `your-project.analytics_123456789.events_*` AS e
  LEFT JOIN UNNEST(e.items) AS i
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND e.event_name IN ('purchase', 'refund')
),
original_items AS (
  -- Original purchase affiliation per transaction and item, used to credit refunds
  SELECT transaction_id, item_id, MAX(item_affiliation) AS purchase_item_affiliation
  FROM txn_items
  WHERE event_name = 'purchase' AND transaction_id IS NOT NULL
  GROUP BY transaction_id, item_id
),
original_orders AS (
  -- Original purchase order-level affiliation per transaction, used to credit refunds
  SELECT transaction_id, MAX(order_affiliation) AS purchase_order_affiliation
  FROM txn_items
  WHERE event_name = 'purchase' AND transaction_id IS NOT NULL
  GROUP BY transaction_id
)
-- Credited affiliation per transaction and item: refunds take the original purchase value (item, then order); then item, then order, else (not set)
SELECT DISTINCT
  t.transaction_id,
  t.item_id,
  COALESCE(
    IF(t.event_name = 'refund', oi.purchase_item_affiliation, NULL),
    IF(t.event_name = 'refund', oo.purchase_order_affiliation, NULL),
    t.item_affiliation,
    t.order_affiliation,
    '(not set)'
  ) AS affiliation
FROM txn_items AS t
LEFT JOIN original_items AS oi
  ON oi.transaction_id = t.transaction_id AND oi.item_id = t.item_id
LEFT JOIN original_orders AS oo
  ON oo.transaction_id = t.transaction_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window: last 90 complete days
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
txn_items AS (
  -- Purchase and refund rows per item with item-level and order-level affiliation (blank treated as missing)
  SELECT
    e.eventType AS event_type,
    e.commerce.order.purchaseID AS transaction_id,
    pli.SKU AS item_id,
    NULLIF(TRIM(pli._tenantId.affiliation), '') AS item_affiliation,
    NULLIF(TRIM(e._tenantId.affiliation), '') AS order_affiliation
  FROM aep_web_events_dataset AS e
  LATERAL VIEW OUTER explode(e.productListItems) AS pli
  CROSS JOIN params AS p
  WHERE to_date(e.timestamp) BETWEEN p.start_date AND p.end_date
    AND e.eventType IN ('commerce.purchases', 'refund')
),
original_items AS (
  -- Original purchase affiliation per transaction and item, used to credit refunds
  SELECT transaction_id, item_id, MAX(item_affiliation) AS purchase_item_affiliation
  FROM txn_items
  WHERE event_type = 'commerce.purchases' AND transaction_id IS NOT NULL
  GROUP BY transaction_id, item_id
),
original_orders AS (
  -- Original purchase order-level affiliation per transaction, used to credit refunds
  SELECT transaction_id, MAX(order_affiliation) AS purchase_order_affiliation
  FROM txn_items
  WHERE event_type = 'commerce.purchases' AND transaction_id IS NOT NULL
  GROUP BY transaction_id
)
-- Credited affiliation per transaction and item: refunds take the original purchase value (item, then order); then item, then order, else (not set)
SELECT DISTINCT
  t.transaction_id,
  t.item_id,
  COALESCE(
    CASE WHEN t.event_type = 'refund' THEN oi.purchase_item_affiliation END,
    CASE WHEN t.event_type = 'refund' THEN oo.purchase_order_affiliation END,
    t.item_affiliation,
    t.order_affiliation,
    '(not set)'
  ) AS affiliation
FROM txn_items AS t
LEFT JOIN original_items AS oi
  ON oi.transaction_id = t.transaction_id AND oi.item_id = t.item_id
LEFT JOIN original_orders AS oo
  ON oo.transaction_id = t.transaction_id;