Free Shipping Eligibility

{
  "transaction": {
    "transactionID": "txn-1",
    "total": {
      "basePrice": 100,
      "currency": "USD",
      "shipping": 0,
      "shippingMethod": "Standard",
      "transactionTotal": 100
    },
    "attributes": {
      "freeShippingEligibility": "Threshold Met"
    }
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "txn-1",
    "value": 100,
    "currency": "USD",
    "shipping": 0,
    "shipping_tier": "Standard",
    "items": [
      {
        "item_id": "SKU-1",
        "item_name": "item",
        "price": 100,
        "quantity": 1
      }
    ]
  },
  "free_shipping_eligibility": "Threshold Met"
}

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

{
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "txn-1",
      "currencyCode": "USD"
    }
  },
  "_tenantId": {
    "freeShippingEligibility": "Threshold Met"
  }
}

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

-- One value per order: the free_shipping_eligibility sent on the first purchase hit of each transaction_id (refunds never rewrite it)
WITH orders AS (
  SELECT
    ecommerce.transaction_id AS transaction_id,
    COALESCE(NULLIF(TRIM((SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'free_shipping_eligibility')), ''), '(not set)') AS free_shipping_eligibility
  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'
    AND ecommerce.transaction_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ecommerce.transaction_id ORDER BY event_timestamp) = 1
)
-- Orders by free shipping basis
SELECT
  free_shipping_eligibility,
  COUNT(*) AS orders
FROM orders
GROUP BY free_shipping_eligibility
ORDER BY orders DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date,
         date_sub(current_date(), 1) AS end_date
),
-- One value per order: the freeShippingEligibility sent on the first purchase hit of each purchaseID (refunds never rewrite it)
orders AS (
  SELECT
    commerce.order.purchaseID AS transaction_id,
    COALESCE(NULLIF(TRIM(MIN_BY(_tenantId.freeShippingEligibility, timestamp)), ''), '(not set)') AS free_shipping_eligibility
  FROM aep_web_events_dataset, params
  WHERE commerce.purchases.value > 0
    AND commerce.order.purchaseID IS NOT NULL
    AND to_date(timestamp) BETWEEN params.start_date AND params.end_date
  GROUP BY commerce.order.purchaseID
)
-- Orders by free shipping basis
SELECT
  free_shipping_eligibility,
  COUNT(*) AS orders
FROM orders
GROUP BY free_shipping_eligibility
ORDER BY orders DESC;