Shipment Mode

{
  "digitalData": {
    "transaction": {
      "transactionID": "T-100245",
      "total": {
        "basePrice": 121,
        "shipping": 8.99,
        "shippingMethod": "expedited",
        "transactionTotal": 129.99,
        "currency": "USD"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "purchase"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "shipment_mode": "expedited",
  "ecommerce": {
    "transaction_id": "T-100245",
    "value": 129.99,
    "currency": "USD",
    "shipping": 8.99,
    "items": [
      {
        "item_id": "SKU-1234",
        "item_name": "Trail Running Shoe",
        "price": 121,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100245",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    }
  },
  "shipping": {
    "shippingMethod": "expedited",
    "shippingAmount": 8.99
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100245",
      "priceTotal": 129.99,
      "currencyCode": "USD"
    }
  },
  "shipping": {
    "shippingMethod": "expedited",
    "shippingAmount": 8.99
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Orders, revenue and order share by normalized Shipment Mode (value confirmed at purchase), last 30 complete days
WITH purchases AS (
  SELECT
    e.ecommerce.transaction_id AS transaction_id,
    MAX(e.ecommerce.purchase_revenue) AS purchase_revenue,
    MAX(IF(ep.key = 'shipment_mode', ep.value.string_value, NULL)) AS shipment_mode_raw
  FROM `your-project.analytics_123456789.events_*` AS e
  LEFT JOIN UNNEST(e.event_params) AS ep
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                            AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'purchase'
    AND e.ecommerce.transaction_id IS NOT NULL
    AND e.ecommerce.transaction_id != '(not set)'
  GROUP BY transaction_id
),
normalized AS (
  SELECT
    transaction_id,
    purchase_revenue,
    CASE
      WHEN shipment_mode_raw IS NULL OR TRIM(shipment_mode_raw) = '' THEN 'Unknown'
      -- store pickup and digital delivery have no shipment service (Fulfillment Method dimension): excluded below
      WHEN REGEXP_CONTAINS(LOWER(shipment_mode_raw), r'pick[ _-]?up|bopis|click[ _-]?(and|&)[ _-]?collect|in[ _-]?store|digital|download|e-?delivery') THEN NULL
      WHEN REGEXP_CONTAINS(LOWER(shipment_mode_raw), r'mixed|multiple') THEN 'Mixed'
      WHEN REGEXP_CONTAINS(LOWER(shipment_mode_raw), r'same[ _-]?day') THEN 'Same-Day'
      WHEN REGEXP_CONTAINS(LOWER(shipment_mode_raw), r'express|expedited|overnight|next[ _-]?day|priority|2[ _-]?day|two[ _-]?day') THEN 'Expedited'
      WHEN REGEXP_CONTAINS(LOWER(shipment_mode_raw), r'standard|ground|economy|free') THEN 'Standard'
      ELSE 'Unknown'
    END AS shipment_mode
  FROM purchases
)
SELECT
  shipment_mode,
  COUNT(DISTINCT transaction_id) AS orders,
  SUM(purchase_revenue) AS revenue,
  SAFE_DIVIDE(COUNT(DISTINCT transaction_id), SUM(COUNT(DISTINCT transaction_id)) OVER ()) AS order_share
FROM normalized
WHERE shipment_mode IS NOT NULL
GROUP BY shipment_mode
ORDER BY orders DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Orders, revenue and order share by normalized Shipment Mode (value confirmed at purchase), last 30 complete days
WITH purchases AS (
  SELECT
    commerce.order.purchaseID AS purchase_id,
    MAX(commerce.order.priceTotal) AS purchase_revenue,
    MAX(shipping.shippingMethod) AS shipping_method_raw
  FROM commerce_experience_event_dataset
  WHERE commerce.purchases.value = 1
    AND commerce.order.purchaseID IS NOT NULL
    AND timestamp >= date_sub(current_date(), 30)
    AND timestamp < current_date()
  GROUP BY commerce.order.purchaseID
),
normalized AS (
  SELECT
    purchase_id,
    purchase_revenue,
    CASE
      WHEN shipping_method_raw IS NULL OR TRIM(shipping_method_raw) = '' THEN 'Unknown'
      -- store pickup and digital delivery have no shipment service (Fulfillment Method dimension): excluded below
      WHEN LOWER(shipping_method_raw) RLIKE 'pick[ _-]?up|bopis|click[ _-]?(and|&)[ _-]?collect|in[ _-]?store|digital|download|e-?delivery' THEN NULL
      WHEN LOWER(shipping_method_raw) RLIKE 'mixed|multiple' THEN 'Mixed'
      WHEN LOWER(shipping_method_raw) RLIKE 'same[ _-]?day' THEN 'Same-Day'
      WHEN LOWER(shipping_method_raw) RLIKE 'express|expedited|overnight|next[ _-]?day|priority|2[ _-]?day|two[ _-]?day' THEN 'Expedited'
      WHEN LOWER(shipping_method_raw) RLIKE 'standard|ground|economy|free' THEN 'Standard'
      ELSE 'Unknown'
    END AS shipment_mode
  FROM purchases
)
SELECT
  shipment_mode,
  COUNT(DISTINCT purchase_id) AS orders,
  SUM(purchase_revenue) AS revenue,
  COUNT(DISTINCT purchase_id) * 1.0 / NULLIF(SUM(COUNT(DISTINCT purchase_id)) OVER (), 0) AS order_share
FROM normalized
WHERE shipment_mode IS NOT NULL
GROUP BY shipment_mode
ORDER BY orders DESC;