Transaction ID

{
  "digitalData": {
    "transaction": {
      "transactionID": "US-1009358",
      "total": {
        "transactionTotal": 129.99,
        "currency": "USD"
      },
      "profile": [
        {
          "profileInfo": {
            "profileID": "cust-882917"
          }
        }
      ]
    }
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "US-1009358",
    "value": 129.99,
    "currency": "USD",
    "coupon": "SAVE10",
    "shipping": 6.99,
    "tax": 9.5,
    "items": [
      {
        "item_id": "SKU-4471",
        "item_name": "Wireless Earbuds",
        "price": 129.99,
        "quantity": 1
      }
    ]
  },
  "user_id": "cust-882917"
}

{
  "event": "commerce.purchases",
  "commerce": {
    "order": {
      "purchaseID": "US-1009358",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    },
    "purchases": {
      "value": 1
    }
  },
  "user": {
    "userId": "cust-882917"
  }
}

{
  "commerce": {
    "order": {
      "purchaseID": "US-1009358"
    }
  }
}

-- ==========================================
-- 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);

-- The OMS match (48-hour window, test-order exclusion) applies when reconciling against order records, not to the Transaction ID values returned here.
-- Purchase hits with the Transaction ID trimmed and case preserved
WITH purchase_hits AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_timestamp,
    TRIM(COALESCE(ecommerce.transaction_id,
      (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'transaction_id'))) AS transaction_id
  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 = 'purchase'
),
-- Classify empty, (not set), undefined, null, 0 and unrendered template values as invalid and rank repeat hits per ID by time
classified AS (
  SELECT
    event_date,
    event_timestamp,
    transaction_id,
    NOT (COALESCE(transaction_id, '') = ''
      OR LOWER(transaction_id) IN ('(not set)', 'undefined', 'null', '0')
      OR REGEXP_CONTAINS(transaction_id, r'\{\{.*\}\}')) AS is_valid_transaction_id,
    ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) AS hit_rank
  FROM purchase_hits
)
-- One row per order: the earliest hit of each valid ID; invalid hits stay in the order count, flagged for the tracking-quality exception
SELECT
  event_date,
  transaction_id,
  is_valid_transaction_id,
  TIMESTAMP_MICROS(event_timestamp) AS order_event_timestamp
FROM classified
WHERE NOT is_valid_transaction_id
   OR hit_rank = 1
ORDER BY event_date, order_event_timestamp;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- The OMS match (48-hour window, test-order exclusion) applies when reconciling against order records, not to the Transaction ID values returned here.
-- Relative reporting window: the last 90 complete days
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE, 90) AS start_date,
    DATE_SUB(CURRENT_DATE, 1) AS end_date
),
-- Purchase hits with the Transaction ID trimmed and case preserved
purchase_hits AS (
  SELECT
    CAST(e.timestamp AS DATE) AS event_date,
    e.timestamp AS event_timestamp,
    TRIM(e.commerce.`order`.purchaseID) AS transaction_id
  FROM your_experience_event_dataset e
  CROSS JOIN params p
  WHERE e.eventType = 'commerce.purchases'
    AND CAST(e.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
),
-- Classify empty, (not set), undefined, null, 0 and unrendered template values as invalid and rank repeat hits per ID by time
classified AS (
  SELECT
    event_date,
    event_timestamp,
    transaction_id,
    NOT (COALESCE(transaction_id, '') = ''
      OR LOWER(transaction_id) IN ('(not set)', 'undefined', 'null', '0')
      OR transaction_id RLIKE '\\{\\{.*\\}\\}') AS is_valid_transaction_id,
    ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) AS hit_rank
  FROM purchase_hits
)
-- One row per order: the earliest hit of each valid ID; invalid hits stay in the order count, flagged for the tracking-quality exception
SELECT
  event_date,
  transaction_id,
  is_valid_transaction_id,
  event_timestamp AS order_event_timestamp
FROM classified
WHERE NOT is_valid_transaction_id
   OR hit_rank = 1
ORDER BY event_date, order_event_timestamp;