Coupon Redemption Rate

{
  "pageInstanceID": "order-confirmation-prod",
  "page": {
    "pageInfo": {
      "pageID": "order-confirmation",
      "pageName": "Order Confirmation",
      "destinationURL": "https://www.example.com/checkout/confirmation"
    },
    "category": {
      "primaryCategory": "Checkout"
    }
  },
  "transaction": {
    "transactionID": "ORD-10045821",
    "profile": {
      "profileInfo": {
        "profileID": "c-8891023"
      }
    },
    "total": {
      "basePrice": 214,
      "voucherCode": "SAVE20",
      "voucherDiscount": 42.8,
      "currency": "USD",
      "taxRate": 0.0825,
      "shipping": 9.95,
      "shippingMethod": "Standard",
      "priceWithTax": 185.32,
      "transactionTotal": 195.27
    },
    "attributes": {
      "couponApplied": true,
      "couponScope": "order",
      "couponCount": 1,
      "orderStatus": "completed",
      "checkoutType": "registered",
      "paymentType": "credit_card"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-4421",
          "productName": "Merino Crew Sweater",
          "sku": "SKU-4421",
          "manufacturer": "Northbank"
        },
        "category": {
          "primaryCategory": "Apparel/Knitwear"
        },
        "quantity": 1,
        "price": {
          "basePrice": 129,
          "voucherCode": "SAVE20",
          "voucherDiscount": 25.8,
          "currency": "USD"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-7788",
          "productName": "Cotton Rib Socks 3-Pack",
          "sku": "SKU-7788",
          "manufacturer": "Northbank"
        },
        "category": {
          "primaryCategory": "Apparel/Accessories"
        },
        "quantity": 2,
        "price": {
          "basePrice": 42.5,
          "voucherCode": "SAVE20",
          "voucherDiscount": 17,
          "currency": "USD"
        }
      }
    ]
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "Purchase",
        "eventAction": "purchase",
        "timeStamp": "2026-09-17T14:32:05Z"
      },
      "attributes": {
        "transactionID": "ORD-10045821",
        "couponCode": "SAVE20",
        "couponApplied": true,
        "couponScope": "order",
        "discountAmount": 42.8
      }
    }
  ]
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "ORD-10045821",
    "value": 171.2,
    "currency": "USD",
    "tax": 14.12,
    "shipping": 9.95,
    "coupon": "SAVE20",
    "discount_amount": 42.8,
    "items": [
      {
        "item_id": "SKU-4421",
        "item_name": "Merino Crew Sweater",
        "item_brand": "Northbank",
        "item_category": "Apparel",
        "item_category2": "Knitwear",
        "price": 129,
        "quantity": 1,
        "discount": 25.8,
        "coupon": "SAVE20",
        "index": 1
      },
      {
        "item_id": "SKU-7788",
        "item_name": "Cotton Rib Socks 3-Pack",
        "item_brand": "Northbank",
        "item_category": "Apparel",
        "item_category2": "Accessories",
        "price": 42.5,
        "quantity": 2,
        "discount": 8.5,
        "coupon": "SAVE20",
        "index": 2
      }
    ]
  },
  "coupon_applied": true,
  "coupon_scope": "order",
  "coupon_count": 1,
  "promotion_name": "Autumn Sitewide 20",
  "order_status": "completed",
  "test_order": false,
  "internal_traffic": false,
  "checkout_type": "registered",
  "payment_type": "credit_card",
  "customer_type": "returning"
}

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "purchase",
    "path": "commerce.order",
    "timestamp": "2026-09-17T14:32:05.000Z"
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "promotionID": "SAVE20",
    "order": {
      "purchaseID": "ORD-10045821",
      "purchaseOrderNumber": "ORD-10045821",
      "currencyCode": "USD",
      "priceTotal": 195.27,
      "discountAmount": 42.8,
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentAmount": 195.27,
          "currencyCode": "USD"
        }
      ]
    }
  },
  "coupon": {
    "code": "SAVE20",
    "applied": true,
    "scope": "order",
    "count": 1,
    "discountAmount": 42.8,
    "promotionName": "Autumn Sitewide 20"
  },
  "orderAttributes": {
    "orderStatus": "completed",
    "testOrder": false,
    "internalTraffic": false
  },
  "productListItems": [
    {
      "SKU": "SKU-4421",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 129,
      "currencyCode": "USD",
      "discountAmount": 25.8
    },
    {
      "SKU": "SKU-7788",
      "name": "Cotton Rib Socks 3-Pack",
      "quantity": 2,
      "priceTotal": 85,
      "currencyCode": "USD",
      "discountAmount": 17
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "Order Confirmation",
      "siteSection": "Checkout"
    }
  }
}

{
  "eventType": "commerce.purchases",
  "timestamp": "2026-09-17T14:32:05.000Z",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "promotionID": "SAVE20",
    "order": {
      "purchaseID": "ORD-10045821",
      "purchaseOrderNumber": "ORD-10045821",
      "currencyCode": "USD",
      "priceTotal": 195.27,
      "discountAmount": 42.8,
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentAmount": 195.27,
          "currencyCode": "USD"
        }
      ]
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4421",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 129,
      "currencyCode": "USD",
      "discountAmount": 25.8
    },
    {
      "SKU": "SKU-7788",
      "name": "Cotton Rib Socks 3-Pack",
      "quantity": 2,
      "priceTotal": 85,
      "currencyCode": "USD",
      "discountAmount": 17
    }
  ],
  "channel": {
    "_type": "https://ns.adobe.com/xdm/channels/channel",
    "_id": "https://ns.adobe.com/xdm/channels/web"
  },
  "web": {
    "webPageDetails": {
      "name": "Order Confirmation",
      "siteSection": "Checkout"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar12": "SAVE20",
          "eVar13": "true",
          "eVar14": "order",
          "eVar15": "20-25%"
        },
        "props": {
          "prop12": "Autumn Sitewide 20"
        }
      }
    }
  },
  "_yourtenant": {
    "coupon": {
      "code": "SAVE20",
      "applied": true,
      "scope": "order",
      "count": 1,
      "discountAmount": 42.8,
      "promotionName": "Autumn Sitewide 20"
    },
    "orderAttributes": {
      "orderStatus": "completed",
      "testOrder": false,
      "internalTraffic": false
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- KPI: Coupon Redemption Rate
-- Formula: Orders with >= 1 coupon code applied / Total completed orders * 100
-- Grain: ONE row per order (transaction_id). An order carrying the same code on
-- three line items counts once; a re-fired purchase beacon counts once.
-- Denominator: EVERY completed order in the window, including full-price,
-- zero-discount orders. It is never narrowed to coupon-exposed sessions.
-- Breakdown rows (Coupon Code, Discount Amount band) keep that same full base
-- population as their denominator, so the rate stays comparable across cells.

WITH purchase_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS order_date,
    e.event_timestamp,
    e.user_pseudo_id,
    e.device.category AS device_category,
    e.traffic_source.medium AS traffic_medium,
    e.traffic_source.name AS campaign_name,
    -- Transaction-level parameters are pivoted out of the event_params array.
    -- One conditional pass per key with MAX(IF(...)), so two different keys are
    -- never demanded of the same array element.
    (SELECT MAX(IF(p.key = 'transaction_id', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)), NULL))
       FROM UNNEST(e.event_params) AS p) AS transaction_id,
    (SELECT MAX(IF(p.key = 'coupon', TRIM(p.value.string_value), NULL))
       FROM UNNEST(e.event_params) AS p) AS order_coupon_code,
    (SELECT MAX(IF(p.key = 'discount_amount', COALESCE(p.value.double_value, p.value.float_value, CAST(p.value.int_value AS FLOAT64)), NULL))
       FROM UNNEST(e.event_params) AS p) AS order_discount_amount,
    (SELECT MAX(IF(p.key = 'value', COALESCE(p.value.double_value, p.value.float_value, CAST(p.value.int_value AS FLOAT64)), NULL))
       FROM UNNEST(e.event_params) AS p) AS order_value,
    (SELECT MAX(IF(p.key = 'order_status', LOWER(TRIM(p.value.string_value)), NULL))
       FROM UNNEST(e.event_params) AS p) AS order_status,
    (SELECT MAX(IF(p.key = 'coupon_applied', LOWER(COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING))), NULL))
       FROM UNNEST(e.event_params) AS p) AS coupon_applied_flag,
    (SELECT MAX(IF(p.key = 'coupon_scope', p.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS p) AS coupon_scope,
    (SELECT MAX(IF(p.key = 'promotion_name', p.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS p) AS promotion_name,
    (SELECT MAX(IF(p.key = 'checkout_type', p.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS p) AS checkout_type,
    (SELECT MAX(IF(p.key = 'payment_type', p.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS p) AS payment_type,
    (SELECT MAX(IF(p.key = 'customer_type', p.value.string_value, NULL))
       FROM UNNEST(e.event_params) AS p) AS customer_type,
    (SELECT MAX(IF(p.key IN ('test_order', 'internal_traffic'), LOWER(COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING))), NULL))
       FROM UNNEST(e.event_params) AS p) AS internal_or_test_flag,
    -- Item-level coupon lives in the items array, not in event_params.
    -- MAX() collapses the repeated code to one value per order line-set.
    (SELECT MAX(TRIM(i.coupon))
       FROM UNNEST(e.items) AS i
      WHERE i.coupon IS NOT NULL AND TRIM(i.coupon) <> '') AS item_coupon_code,
    (SELECT COUNTIF(i.coupon IS NOT NULL AND TRIM(i.coupon) <> '')
       FROM UNNEST(e.items) AS i) AS couponed_line_items,
    (SELECT COUNT(1) FROM UNNEST(e.items) AS i) AS line_items,
    (SELECT SUM(COALESCE(ip.value.double_value, ip.value.float_value, CAST(ip.value.int_value AS FLOAT64), 0) * IFNULL(i.quantity, 1))
       FROM UNNEST(e.items) AS i, UNNEST(i.item_params) AS ip
      WHERE ip.key = 'discount') AS item_discount_total
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _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 = 'purchase'
),

-- Order grain: one surviving row per transaction_id (earliest beacon wins), so a
-- confirmation-page reload cannot double count either side of the ratio.
orders AS (
  SELECT
    order_date,
    transaction_id,
    COALESCE(NULLIF(order_coupon_code, ''), NULLIF(item_coupon_code, '')) AS coupon_code,
    COALESCE(order_discount_amount, item_discount_total, 0) AS discount_amount,
    order_value,
    coupon_applied_flag,
    coupon_scope,
    promotion_name,
    checkout_type,
    payment_type,
    customer_type,
    device_category,
    traffic_medium,
    campaign_name,
    couponed_line_items,
    line_items
  FROM purchase_events
  WHERE transaction_id IS NOT NULL
    AND TRIM(transaction_id) <> ''
    -- order_status is a string status, never a numeric metric
    AND COALESCE(order_status, 'completed') IN ('completed', 'complete', 'paid', 'success')
    AND COALESCE(internal_or_test_flag, 'false') NOT IN ('true', '1')
    AND NOT STARTS_WITH(UPPER(transaction_id), 'TEST')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),

order_grain AS (
  SELECT
    order_date,
    transaction_id,
    coupon_code,
    -- Only a customer-facing code qualifies an order. An automatic cart price
    -- rule that moves discount_amount without a code does NOT count.
    -- The coupon_applied flag alone never qualifies an order: a non-empty code is required.
    (coupon_code IS NOT NULL) AS has_coupon,
    discount_amount,
    CASE
      WHEN discount_amount IS NULL OR discount_amount <= 0 THEN '0 | no discount'
      WHEN discount_amount < 10 THEN '1 | 0.01-9.99'
      WHEN discount_amount < 25 THEN '2 | 10-24.99'
      WHEN discount_amount < 50 THEN '3 | 25-49.99'
      WHEN discount_amount < 100 THEN '4 | 50-99.99'
      ELSE '5 | 100+'
    END AS discount_band,
    order_value,
    coupon_scope,
    promotion_name,
    checkout_type,
    payment_type,
    customer_type,
    device_category,
    traffic_medium,
    campaign_name,
    couponed_line_items,
    line_items
  FROM orders
),

-- Full base population per day: ALL completed orders, couponed or not.
base_population AS (
  SELECT order_date, COUNT(DISTINCT transaction_id) AS total_orders
  FROM order_grain
  GROUP BY order_date
)

SELECT
  o.order_date,
  IF(GROUPING(o.coupon_code) = 1, '(all orders)', COALESCE(o.coupon_code, '(no code applied)')) AS coupon_code,
  IF(GROUPING(o.discount_band) = 1, '(all bands)', o.discount_band) AS discount_amount_band,
  ANY_VALUE(b.total_orders) AS total_orders,
  COUNTIF(o.has_coupon) AS orders_with_coupon,
  ROUND(SAFE_DIVIDE(COUNTIF(o.has_coupon), ANY_VALUE(b.total_orders)) * 100, 2) AS coupon_redemption_rate_pct,
  ROUND(SUM(IF(o.has_coupon, o.discount_amount, 0)), 2) AS discount_amount_total,
  ROUND(SAFE_DIVIDE(SUM(IF(o.has_coupon, o.discount_amount, 0)), COUNTIF(o.has_coupon)), 2) AS avg_discount_per_redeemed_order,
  ROUND(SAFE_DIVIDE(SUM(IF(o.has_coupon, o.order_value, 0)), COUNTIF(o.has_coupon)), 2) AS avg_order_value_redeemed,
  SUM(o.couponed_line_items) AS couponed_line_items,
  SUM(o.line_items) AS line_items
FROM order_grain AS o
JOIN base_population AS b
  USING (order_date)
GROUP BY GROUPING SETS (
  (o.order_date),
  (o.order_date, o.coupon_code),
  (o.order_date, o.discount_band)
)
ORDER BY order_date DESC, coupon_redemption_rate_pct DESC, coupon_code, discount_amount_band;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- KPI: Coupon Redemption Rate
-- Formula: Orders with >= 1 coupon code applied / Total completed orders * 100
-- Source: ExperienceEvent dataset, eventType = 'commerce.purchases'.
-- Order identity: commerce.order.purchaseID. Order-level code: commerce.promotionID
-- (mirrored in _yourtenant.coupon.code and eVar12); productListItems carry the per-line
-- discountAmount only, so line-level context can never multiply an order in the numerator.
-- Denominator: every completed order in the window, zero-discount orders included.
-- Completed status and test/internal exclusions come from _yourtenant.orderAttributes,
-- matching the order_status / test_order / internal_traffic filters in the GA4 query.

WITH purchase_events AS (
  SELECT
    _id AS event_id,
    timestamp AS event_timestamp,
    to_date(timestamp) AS order_date,
    commerce.order.purchaseID AS transaction_id,
    commerce.order.purchaseOrderNumber AS purchase_order_number,
    commerce.order.currencyCode AS currency_code,
    commerce.order.priceTotal AS order_value,
    commerce.order.discountAmount AS order_discount_amount,
    commerce.purchases.value AS purchase_units,
    trim(commerce.promotionID) AS order_promotion_id,
    trim(_yourtenant.coupon.code) AS coupon_code_tenant,
    lower(trim(_yourtenant.orderAttributes.orderStatus)) AS order_status,
    trim(_experience.analytics.customDimensions.eVars.eVar12) AS coupon_code_evar,
    lower(trim(_experience.analytics.customDimensions.eVars.eVar13)) AS coupon_applied_evar,
    _experience.analytics.customDimensions.eVars.eVar14 AS coupon_scope,
    _experience.analytics.customDimensions.eVars.eVar15 AS discount_rate_band,
    _experience.analytics.customDimensions.props.prop12 AS promotion_name,
    web.webPageDetails.name AS page_name,
    web.webPageDetails.siteSection AS site_section
  FROM your_experience_event_dataset
  WHERE eventType = 'commerce.purchases'
    AND timestamp >= date_trunc('day', now() - interval '90 days')
    AND timestamp < date_trunc('day', now())
    AND commerce.order.purchaseID IS NOT NULL
    AND trim(commerce.order.purchaseID) <> ''
    AND lower(commerce.order.purchaseID) NOT LIKE 'test%'
    -- order_status is a string status, never a numeric metric
    AND coalesce(lower(trim(_yourtenant.orderAttributes.orderStatus)), 'completed') IN ('completed', 'complete', 'paid', 'success')
    AND coalesce(_yourtenant.orderAttributes.testOrder, false) = false
    AND coalesce(_yourtenant.orderAttributes.internalTraffic, false) = false
),

-- productListItems is exploded in its own branch and folded straight back to the
-- event, so a five-line order with one sitewide code stays one order.
item_context AS (
  SELECT
    event_id,
    sum(coalesce(item.discountAmount, 0)) AS item_discount_total,
    sum(coalesce(item.quantity, 0)) AS units,
    count(1) AS line_items,
    sum(CASE WHEN coalesce(item.discountAmount, 0) > 0 THEN 1 ELSE 0 END) AS discounted_line_items
  FROM (
    SELECT _id AS event_id, explode(productListItems) AS item
    FROM your_experience_event_dataset
    WHERE eventType = 'commerce.purchases'
      AND timestamp >= date_trunc('day', now() - interval '90 days')
      AND timestamp < date_trunc('day', now())
  ) AS exploded_items
  GROUP BY event_id
),

-- One surviving row per purchaseID (earliest beacon), so a confirmation reload
-- cannot inflate the numerator or the denominator.
deduplicated_orders AS (
  SELECT
    p.*,
    coalesce(i.item_discount_total, 0) AS item_discount_total,
    coalesce(i.line_items, 0) AS line_items,
    coalesce(i.discounted_line_items, 0) AS discounted_line_items,
    row_number() OVER (PARTITION BY p.transaction_id ORDER BY p.event_timestamp ASC) AS beacon_rank
  FROM purchase_events AS p
  LEFT JOIN item_context AS i
    ON i.event_id = p.event_id
),

order_grain AS (
  SELECT
    order_date,
    transaction_id,
    coalesce(nullif(order_promotion_id, ''), nullif(coupon_code_tenant, ''), nullif(coupon_code_evar, '')) AS coupon_code,
    -- Only a non-empty customer-facing code qualifies; the applied flag (eVar13) alone never does.
    CASE
      WHEN coalesce(nullif(order_promotion_id, ''), nullif(coupon_code_tenant, ''), nullif(coupon_code_evar, '')) IS NOT NULL THEN 1
      ELSE 0
    END AS has_coupon,
    coalesce(order_discount_amount, item_discount_total, 0) AS discount_amount,
    coalesce(order_value, 0) AS order_value,
    coupon_scope,
    discount_rate_band,
    promotion_name,
    currency_code,
    line_items,
    discounted_line_items
  FROM deduplicated_orders
  WHERE beacon_rank = 1
),

order_banded AS (
  SELECT
    order_date,
    transaction_id,
    coupon_code,
    has_coupon,
    discount_amount,
    order_value,
    coupon_scope,
    promotion_name,
    line_items,
    discounted_line_items,
    CASE
      WHEN discount_amount <= 0 THEN '0 | no discount'
      WHEN discount_amount < 10 THEN '1 | 0.01-9.99'
      WHEN discount_amount < 25 THEN '2 | 10-24.99'
      WHEN discount_amount < 50 THEN '3 | 25-49.99'
      WHEN discount_amount < 100 THEN '4 | 50-99.99'
      ELSE '5 | 100+'
    END AS discount_band
  FROM order_grain
),

base_population AS (
  SELECT order_date, count(DISTINCT transaction_id) AS total_orders
  FROM order_banded
  GROUP BY order_date
)

SELECT
  o.order_date,
  CASE WHEN grouping(o.coupon_code) = 1 THEN '(all orders)'
       ELSE coalesce(o.coupon_code, '(no code applied)') END AS coupon_code,
  CASE WHEN grouping(o.discount_band) = 1 THEN '(all bands)'
       ELSE o.discount_band END AS discount_amount_band,
  max(b.total_orders) AS total_orders,
  sum(o.has_coupon) AS orders_with_coupon,
  round(sum(o.has_coupon) * 100.0 / nullif(max(b.total_orders), 0), 2) AS coupon_redemption_rate_pct,
  round(sum(CASE WHEN o.has_coupon = 1 THEN o.discount_amount ELSE 0 END), 2) AS discount_amount_total,
  round(sum(CASE WHEN o.has_coupon = 1 THEN o.discount_amount ELSE 0 END) / nullif(sum(o.has_coupon), 0), 2) AS avg_discount_per_redeemed_order,
  round(sum(CASE WHEN o.has_coupon = 1 THEN o.order_value ELSE 0 END) / nullif(sum(o.has_coupon), 0), 2) AS avg_order_value_redeemed,
  sum(o.discounted_line_items) AS discounted_line_items,
  sum(o.line_items) AS line_items
FROM order_banded AS o
JOIN base_population AS b
  ON b.order_date = o.order_date
GROUP BY GROUPING SETS (
  (o.order_date),
  (o.order_date, o.coupon_code),
  (o.order_date, o.discount_band)
)
ORDER BY 1 DESC, 6 DESC, 2, 3;