Currency

{
  "transaction": {
    "transactionID": "T-100294",
    "total": {
      "transactionTotal": 129.99,
      "currency": "USD"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-4821"
        },
        "price": {
          "basePrice": 129.99,
          "currency": "USD"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "T-100294",
    "currency": "USD",
    "value": 129.99,
    "tax": 8.99,
    "shipping": 5,
    "items": [
      {
        "item_id": "SKU-4821",
        "item_name": "Wireless Headphones",
        "price": 129.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100294",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4821",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    }
  ]
}

{
  "commerce": {
    "order": {
      "purchaseID": "T-100294",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    },
    "purchases": {
      "value": 1
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4821",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    }
  ]
}

-- ==========================================
-- 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 iso_4217 AS (
  -- Active ISO 4217 alphabetic currency codes; any other value reports as (not set)
  SELECT code FROM UNNEST(SPLIT('AED AFN ALL AMD AOA ARS AUD AWG AZN BAM BBD BDT BHD BIF BMD BND BOB BRL BSD BTN BWP BYN BZD CAD CDF CHF CLP CNY COP CRC CUP CVE CZK DJF DKK DOP DZD EGP ERN ETB EUR FJD FKP GBP GEL GHS GIP GMD GNF GTQ GYD HKD HNL HTG HUF IDR ILS INR IQD IRR ISK JMD JOD JPY KES KGS KHR KMF KPW KRW KWD KYD KZT LAK LBP LKR LRD LSL LYD MAD MDL MGA MKD MMK MNT MOP MRU MUR MVR MWK MXN MYR MZN NAD NGN NIO NOK NPR NZD OMR PAB PEN PGK PHP PKR PLN PYG QAR RON RSD RUB RWF SAR SBD SCR SDG SEK SGD SHP SLE SOS SRD SSP STN SVC SYP SZL THB TJS TMT TND TOP TRY TTD TWD TZS UAH UGX USD UYU UZS VED VES VND VUV WST XAF XCD XCG XOF XPF YER ZAR ZMW ZWG', ' ')) AS code
),
monetary_events AS (
  -- Monetary events in the window with currency (trimmed, uppercased) and transaction_id pivoted from event_params
  SELECT
    event_name,
    UPPER(TRIM((SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'currency'))) AS raw_currency,
    (SELECT ep.value.string_value FROM UNNEST(event_params) 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 IN ('view_item', 'add_to_cart', 'remove_from_cart', 'begin_checkout', 'add_shipping_info', 'add_payment_info', 'purchase', 'refund')
),
purchase_currency AS (
  -- Valid ISO 4217 currency of each original purchase up to end_date, so refunds inherit it via transaction_id
  SELECT
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'transaction_id') AS transaction_id,
    MAX(UPPER(TRIM((SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'currency')))) AS currency
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX <= FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'purchase'
    AND UPPER(TRIM((SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'currency'))) IN (SELECT code FROM iso_4217)
  GROUP BY transaction_id
),
classified AS (
  -- Currency per event: refunds take the original purchase currency, otherwise the event code if it is on the ISO 4217 list, else NULL
  SELECT
    e.event_name,
    COALESCE(IF(e.event_name = 'refund', p.currency, NULL), c.code) AS currency
  FROM monetary_events e
  LEFT JOIN purchase_currency p
    ON e.event_name = 'refund' AND e.transaction_id = p.transaction_id
  LEFT JOIN iso_4217 c
    ON e.raw_currency = c.code
)
-- Events by currency and event; missing or invalid codes stay in the counts as (not set)
SELECT
  COALESCE(currency, '(not set)') AS currency,
  event_name,
  COUNT(*) AS event_count
FROM classified
GROUP BY currency, event_name
ORDER BY currency, event_name;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window: the last 90 complete days
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
iso_4217 AS (
  -- Active ISO 4217 alphabetic currency codes; any other value reports as (not set)
  SELECT explode(split('AED AFN ALL AMD AOA ARS AUD AWG AZN BAM BBD BDT BHD BIF BMD BND BOB BRL BSD BTN BWP BYN BZD CAD CDF CHF CLP CNY COP CRC CUP CVE CZK DJF DKK DOP DZD EGP ERN ETB EUR FJD FKP GBP GEL GHS GIP GMD GNF GTQ GYD HKD HNL HTG HUF IDR ILS INR IQD IRR ISK JMD JOD JPY KES KGS KHR KMF KPW KRW KWD KYD KZT LAK LBP LKR LRD LSL LYD MAD MDL MGA MKD MMK MNT MOP MRU MUR MVR MWK MXN MYR MZN NAD NGN NIO NOK NPR NZD OMR PAB PEN PGK PHP PKR PLN PYG QAR RON RSD RUB RWF SAR SBD SCR SDG SEK SGD SHP SLE SOS SRD SSP STN SVC SYP SZL THB TJS TMT TND TOP TRY TTD TWD TZS UAH UGX USD UYU UZS VED VES VND VUV WST XAF XCD XCG XOF XPF YER ZAR ZMW ZWG', ' ')) AS code
),
monetary_events AS (
  -- Monetary XDM commerce events in the window, labelled with the unified event names; order currency on checkout steps, purchases and refunds, product-line currency on product views and cart events
  SELECT
    CASE e.eventType
      WHEN 'commerce.productViews' THEN 'view_item'
      WHEN 'commerce.productListAdds' THEN 'add_to_cart'
      WHEN 'commerce.productListRemovals' THEN 'remove_from_cart'
      WHEN 'commerce.checkouts' THEN 'begin_checkout'
      WHEN 'commerce.purchases' THEN 'purchase'
      ELSE e.eventType
    END AS event_name,
    upper(trim(COALESCE(e.commerce.order.currencyCode, e.productListItems[0].currencyCode))) AS raw_currency,
    e.commerce.order.purchaseID AS transaction_id
  FROM aep_web_events_dataset e
  CROSS JOIN params
  WHERE to_date(e.timestamp) BETWEEN params.start_date AND params.end_date
    AND e.eventType IN ('commerce.productViews', 'commerce.productListAdds', 'commerce.productListRemovals', 'commerce.checkouts', 'add_shipping_info', 'add_payment_info', 'commerce.purchases', 'refund')
),
purchase_currency AS (
  -- Valid ISO 4217 currency of each original purchase up to end_date, so refunds inherit it via purchase ID
  SELECT
    e.commerce.order.purchaseID AS transaction_id,
    MAX(upper(trim(e.commerce.order.currencyCode))) AS currency
  FROM aep_web_events_dataset e
  CROSS JOIN params
  WHERE to_date(e.timestamp) <= params.end_date
    AND e.eventType = 'commerce.purchases'
    AND upper(trim(e.commerce.order.currencyCode)) IN (SELECT code FROM iso_4217)
  GROUP BY e.commerce.order.purchaseID
),
classified AS (
  -- Currency per event: refunds take the original purchase currency, otherwise the event code if it is on the ISO 4217 list, else NULL
  SELECT
    m.event_name,
    COALESCE(CASE WHEN m.event_name = 'refund' THEN p.currency END, c.code) AS currency
  FROM monetary_events m
  LEFT JOIN purchase_currency p
    ON m.event_name = 'refund' AND m.transaction_id = p.transaction_id
  LEFT JOIN iso_4217 c
    ON m.raw_currency = c.code
)
-- Events by currency and event; missing or invalid codes stay in the counts as (not set)
SELECT
  COALESCE(currency, '(not set)') AS currency,
  event_name,
  COUNT(*) AS event_count
FROM classified
GROUP BY currency, event_name
ORDER BY currency, event_name;