Shipping Info Added

{
  "pageInstanceID": "checkout-shipping-prod",
  "page": {
    "pageInfo": {
      "pageID": "checkout-shipping",
      "pageName": "Checkout: Shipping",
      "destinationURL": "https://www.example.com/checkout/shipping",
      "sysEnv": "mobile-web",
      "language": "en-US",
      "issueDate": "2026-09-17T14:52:31.000Z"
    },
    "category": {
      "primaryCategory": "checkout",
      "subCategory1": "shipping",
      "subCategory2": "delivery-option"
    },
    "attributes": {
      "checkoutStep": 3,
      "checkoutStepName": "shipping",
      "checkoutType": "guest"
    }
  },
  "cart": {
    "cartID": "CART-9F3B27C4",
    "price": {
      "basePrice": 208,
      "cartTotal": 187.2,
      "currency": "USD",
      "priceWithTax": 202.63,
      "shipping": 0,
      "shippingMethod": "Ground",
      "voucherCode": "SPRING10",
      "voucherDiscount": 20.8
    },
    "attributes": {
      "fulfillmentType": "ship_to_address",
      "shippingTier": "Ground",
      "deliveryOption": "standard_ground_3_5_day",
      "estimatedDeliveryWindow": "2026-09-22/2026-09-24",
      "cartSizeBand": "150-249",
      "lineItemCount": 2,
      "unitCount": 3
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-4471",
          "productName": "Merino Wool Crew Sweater",
          "manufacturer": "Northvale",
          "sku": "SKU-4471-CHR-M",
          "color": "Charcoal",
          "size": "M"
        },
        "category": {
          "primaryCategory": "Apparel",
          "subCategory1": "Knitwear"
        },
        "quantity": 1,
        "price": {
          "basePrice": 89,
          "voucherDiscount": 8.9,
          "priceWithTax": 86.71,
          "currency": "USD"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-8823",
          "productName": "Cotton Chino Pant",
          "manufacturer": "Northvale",
          "sku": "SKU-8823-KHK-32",
          "color": "Khaki",
          "size": "32"
        },
        "category": {
          "primaryCategory": "Apparel",
          "subCategory1": "Trousers"
        },
        "quantity": 2,
        "price": {
          "basePrice": 59.5,
          "voucherDiscount": 5.95,
          "priceWithTax": 57.96,
          "currency": "USD"
        }
      }
    ]
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "add_shipping_info",
        "eventAction": "Shipping Info Added",
        "type": "checkout",
        "timeStamp": "2026-09-17T14:52:31.000Z",
        "effect": "Checkout step 3 completed; delivery option selected"
      },
      "category": {
        "primaryCategory": "checkout"
      },
      "attributes": {
        "checkoutStep": 3,
        "checkoutStepName": "shipping",
        "shippingTier": "Ground",
        "deliveryOption": "standard_ground_3_5_day",
        "fulfillmentType": "ship_to_address",
        "checkoutType": "guest",
        "addressValidationStatus": "verified",
        "addressSource": "manual_entry",
        "resubmission": false,
        "currency": "USD",
        "value": 187.2
      }
    }
  ],
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "a7f1c9d2-4e88-4b31-9a05-6f2d0c7e51b3",
            "loggedIn": false
          },
          "attributes": {
            "customerType": "guest",
            "loyaltyTier": "none"
          }
        }
      ]
    }
  ],
  "version": "1.0"
}

{
  "event": "add_shipping_info",
  "checkout_step": 3,
  "checkout_step_name": "shipping",
  "checkout_type": "guest",
  "fulfillment_type": "ship_to_address",
  "delivery_option": "standard_ground_3_5_day",
  "estimated_delivery_days": 4,
  "address_validation_status": "verified",
  "address_source": "manual_entry",
  "is_resubmission": false,
  "cart_id": "CART-9F3B27C4",
  "checkout_id": "CHK-2026-0917-88214",
  "cart_size_band": "150-249",
  "ecommerce": {
    "currency": "USD",
    "value": 187.2,
    "coupon": "SPRING10",
    "shipping_tier": "Ground",
    "items": [
      {
        "item_id": "SKU-4471",
        "item_name": "Merino Wool Crew Sweater",
        "index": 1,
        "item_brand": "Northvale",
        "item_category": "Apparel",
        "item_category2": "Knitwear",
        "item_variant": "Charcoal / M",
        "item_list_id": "cart",
        "item_list_name": "Shopping Cart",
        "affiliation": "Northvale Online Store",
        "coupon": "SPRING10",
        "price": 89,
        "discount": 8.9,
        "quantity": 1
      },
      {
        "item_id": "SKU-8823",
        "item_name": "Cotton Chino Pant",
        "index": 2,
        "item_brand": "Northvale",
        "item_category": "Apparel",
        "item_category2": "Trousers",
        "item_variant": "Khaki / 32",
        "item_list_id": "cart",
        "item_list_name": "Shopping Cart",
        "affiliation": "Northvale Online Store",
        "coupon": "SPRING10",
        "price": 59.5,
        "discount": 5.95,
        "quantity": 2
      }
    ]
  }
}

{
  "event": "addShippingInfo",
  "eventInfo": {
    "eventName": "Checkout: Shipping Info Added",
    "path": "commerce.checkouts",
    "timestamp": "2026-09-17T14:52:31.000Z"
  },
  "commerce": {
    "checkouts": {
      "value": 1,
      "id": "CHK-2026-0917-88214"
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 187.2,
      "discountAmount": 20.8
    },
    "shipping": {
      "shippingMethod": "Ground"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4471",
      "name": "Merino Wool Crew Sweater",
      "quantity": 1,
      "priceTotal": 80.1,
      "currencyCode": "USD",
      "discountAmount": 8.9,
      "productCategory": "Apparel",
      "productBrand": "Northvale",
      "selectedOptions": [
        {
          "attribute": "color",
          "value": "Charcoal"
        },
        {
          "attribute": "size",
          "value": "M"
        }
      ]
    },
    {
      "SKU": "SKU-8823",
      "name": "Cotton Chino Pant",
      "quantity": 2,
      "priceTotal": 107.1,
      "currencyCode": "USD",
      "discountAmount": 11.9,
      "productCategory": "Apparel",
      "productBrand": "Northvale",
      "selectedOptions": [
        {
          "attribute": "color",
          "value": "Khaki"
        },
        {
          "attribute": "size",
          "value": "32"
        }
      ]
    }
  ],
  "checkout": {
    "cartId": "CART-9F3B27C4",
    "step": 3,
    "stepName": "shipping",
    "checkoutType": "guest",
    "shippingTier": "Ground",
    "deliveryOption": "standard_ground_3_5_day",
    "fulfillmentType": "ship_to_address",
    "addressValidationStatus": "verified",
    "isResubmission": false
  },
  "web": {
    "webPageDetails": {
      "name": "Checkout: Shipping",
      "siteSection": "checkout",
      "URL": "https://www.example.com/checkout/shipping"
    }
  }
}

{
  "eventType": "commerce.checkouts",
  "timestamp": "2026-09-17T14:52:31.000Z",
  "commerce": {
    "checkouts": {
      "value": 1,
      "id": "CHK-2026-0917-88214"
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 187.2,
      "discountAmount": 20.8
    },
    "shipping": {
      "shippingMethod": "Ground"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4471",
      "name": "Merino Wool Crew Sweater",
      "quantity": 1,
      "priceTotal": 80.1,
      "currencyCode": "USD",
      "discountAmount": 8.9,
      "productCategory": "Apparel",
      "productBrand": "Northvale"
    },
    {
      "SKU": "SKU-8823",
      "name": "Cotton Chino Pant",
      "quantity": 2,
      "priceTotal": 107.1,
      "currencyCode": "USD",
      "discountAmount": 11.9,
      "productCategory": "Apparel",
      "productBrand": "Northvale"
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "Checkout: Shipping",
      "siteSection": "checkout",
      "URL": "https://www.example.com/checkout/shipping"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar41": "Ground",
          "eVar42": "ship_to_address",
          "eVar43": "standard_ground_3_5_day",
          "eVar44": "guest",
          "eVar45": "checkout_step_3_shipping"
        },
        "props": {
          "prop41": "checkout:shipping",
          "prop42": "Ground",
          "prop43": "verified"
        }
      }
    }
  },
  "_openkpis": {
    "checkout": {
      "cartId": "CART-9F3B27C4",
      "step": 3,
      "stepName": "shipping",
      "shippingTier": "Ground",
      "deliveryOption": "standard_ground_3_5_day",
      "fulfillmentType": "ship_to_address",
      "checkoutType": "guest",
      "addressValidationStatus": "verified",
      "isResubmission": false,
      "cartSizeBand": "150-249"
    },
    "trafficType": "external"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Shipping Info Added: every qualifying add_shipping_info occurrence in the period.
-- The headline number is the raw occurrence count, so an address edit or a delivery-option switch that is
-- resubmitted increments it again; the session-deduplicated variant (one row per session x checkout instance)
-- is published beside it and is the number that must feed step-to-step funnel rates.
-- Event parameters are pivoted once per event with UNNEST + MAX(IF(key = ...)) so shipping_tier,
-- delivery_option, fulfillment_type, checkout_step / checkout_step_name, checkout_type,
-- address_validation_status, is_resubmission and cart_size_band can be read side by side.
-- Qualification: a submitted shipping destination or a selected delivery option carrying a non-null
-- shipping_tier. Field focus, autocomplete suggestions and client-side validation failures never qualify.
-- Exclusions: carts that bypass the shipping step (fulfillment_type 'digital' or 'none'; pickup / BOPIS / curbside /
-- locker selections carrying a shipping_tier stay in and are split by fulfillment_type), internal and QA traffic_type, staging-QA-preview-dev hostnames, declared bots, agent-keyed checkouts and
-- sessions without analytics consent under consent mode.
-- value and currency are contextual qualifiers only - this is a funnel-behaviour count, never shipping revenue
-- or any carrier cost figure.
-- event_date is already in the reporting-property timezone; the trailing 3 days stay provisional (72h late-hit window).
WITH raw_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date)                                  AS event_date,
    e.event_timestamp                                                   AS event_timestamp,
    e.user_pseudo_id                                                    AS user_pseudo_id,
    COALESCE(e.device.category, '(not set)')                            AS device_category,
    LOWER(COALESCE(e.device.web_info.hostname, ''))                     AS hostname,
    COALESCE(e.geo.country, '(not set)')                                AS country,
    COALESCE(e.traffic_source.medium, '(not set)')                      AS traffic_medium,
    COALESCE(e.traffic_source.source, '(not set)')                      AS traffic_source,
    COALESCE(e.privacy_info.analytics_storage, 'Yes')                   AS analytics_storage,
    e.event_params                                                      AS event_params,
    (SELECT COALESCE(SUM(it.quantity), 0) FROM UNNEST(e.items) AS it)   AS item_units,
    ARRAY_LENGTH(e.items)                                               AS item_line_count
  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 = 'add_shipping_info'
),
pivoted AS (
  SELECT
    r.event_date,
    r.event_timestamp,
    r.user_pseudo_id,
    r.device_category,
    r.hostname,
    r.country,
    r.traffic_medium,
    r.traffic_source,
    r.analytics_storage,
    r.item_units,
    r.item_line_count,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL))                                                      AS ga_session_id,
    MAX(IF(ep.key = 'checkout_id', ep.value.string_value, NULL))                                                     AS checkout_id,
    MAX(IF(ep.key = 'cart_id', ep.value.string_value, NULL))                                                         AS cart_id,
    MAX(IF(ep.key = 'checkout_step', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS checkout_step,
    LOWER(MAX(IF(ep.key = 'checkout_step_name', ep.value.string_value, NULL)))                                       AS checkout_step_name,
    LOWER(MAX(IF(ep.key = 'checkout_type', ep.value.string_value, NULL)))                                            AS checkout_type,
    MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL))                                                   AS shipping_tier,
    LOWER(MAX(IF(ep.key = 'delivery_option', ep.value.string_value, NULL)))                                          AS delivery_option,
    LOWER(MAX(IF(ep.key = 'fulfillment_type', ep.value.string_value, NULL)))                                         AS fulfillment_type,
    LOWER(MAX(IF(ep.key = 'address_validation_status', ep.value.string_value, NULL)))                                AS address_validation_status,
    MAX(IF(ep.key = 'is_resubmission',
           COALESCE(ep.value.int_value,
                    SAFE_CAST(LOWER(ep.value.string_value) IN ('true', 'yes', '1') AS INT64)), NULL))                AS is_resubmission_flag,
    MAX(IF(ep.key = 'cart_size_band', ep.value.string_value, NULL))                                                  AS cart_size_band,
    UPPER(MAX(IF(ep.key = 'currency', ep.value.string_value, NULL)))                                                 AS currency_code,
    MAX(IF(ep.key = 'value',
           COALESCE(ep.value.double_value, ep.value.float_value, SAFE_CAST(ep.value.int_value AS FLOAT64)), NULL))   AS cart_value_local,
    LOWER(MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)))                                             AS traffic_type
  FROM raw_events AS r, UNNEST(r.event_params) AS ep
  GROUP BY
    r.event_date, r.event_timestamp, r.user_pseudo_id, r.device_category, r.hostname,
    r.country, r.traffic_medium, r.traffic_source, r.analytics_storage, r.item_units, r.item_line_count
),
qualified AS (
  SELECT
    p.*,
    CONCAT(p.user_pseudo_id, '-', CAST(COALESCE(p.ga_session_id, 0) AS STRING))           AS session_key,
    COALESCE(p.checkout_id, p.cart_id,
             CONCAT('sess:', p.user_pseudo_id, '-', CAST(COALESCE(p.ga_session_id, 0) AS STRING))) AS checkout_instance_key,
    (p.checkout_id IS NULL AND p.cart_id IS NULL)                                         AS is_degraded_serialization,
    COALESCE(p.is_resubmission_flag, 0) = 1                                               AS is_resubmission,
    CASE
      WHEN p.delivery_option LIKE '%same_day%' OR p.delivery_option LIKE '%next_day%'
        OR p.delivery_option LIKE '%express%' OR p.delivery_option LIKE '%overnight%' THEN 'expedited'
      WHEN p.delivery_option LIKE '%economy%' OR p.delivery_option LIKE '%ground%'
        OR p.delivery_option LIKE '%standard%'                                       THEN 'standard'
      ELSE 'other'
    END                                                                                   AS delivery_speed_band
  FROM pivoted AS p
  WHERE p.shipping_tier IS NOT NULL
    AND COALESCE(p.address_validation_status, 'verified') NOT IN ('failed', 'invalid', 'rejected')
    AND COALESCE(p.fulfillment_type, 'ship_to_address') NOT IN ('digital', 'none')
    AND COALESCE(p.checkout_type, '') NOT IN ('agent_assisted', 'call_center', 'customer_service')
    AND COALESCE(p.traffic_type, '') NOT IN ('internal', 'qa', 'test', 'bot')
    AND NOT REGEXP_CONTAINS(p.hostname, r'^(staging|stage|qa|uat|preview|dev)[.-]|localhost')
    AND p.analytics_storage = 'Yes'
),
serialized AS (
  SELECT
    q.*,
    ROW_NUMBER() OVER (
      PARTITION BY q.session_key, q.checkout_instance_key
      ORDER BY q.event_timestamp
    ) AS occurrence_rank
  FROM qualified AS q
),
daily AS (
  SELECT
    s.event_date,
    s.device_category,
    s.checkout_type,
    s.fulfillment_type,
    s.shipping_tier,
    s.delivery_speed_band,
    s.cart_size_band,
    s.country,
    COUNT(*)                                                                    AS shipping_info_added,
    COUNTIF(s.occurrence_rank = 1)                                              AS shipping_info_added_deduplicated,
    COUNTIF(s.is_resubmission)                                                  AS resubmission_events,
    COUNT(DISTINCT s.session_key)                                               AS sessions_with_shipping_info,
    COUNT(DISTINCT s.user_pseudo_id)                                            AS users_with_shipping_info,
    COUNT(DISTINCT s.checkout_instance_key)                                     AS checkout_instances_with_shipping_info,
    SUM(s.item_units)                                                           AS units_at_shipping_step,
    SUM(s.item_line_count)                                                      AS lines_at_shipping_step,
    COUNTIF(s.address_validation_status = 'verified')                           AS events_with_verified_address,
    COUNTIF(s.is_degraded_serialization)                                        AS events_missing_checkout_key,
    ROUND(SUM(s.cart_value_local *
              CASE WHEN s.currency_code = 'USD' THEN 1.0 ELSE fx.rate_to_usd END), 2) AS cart_context_value_usd,
    COUNTIF(COALESCE(s.currency_code, '') <> 'USD' AND fx.rate_to_usd IS NULL)  AS events_missing_fx_rate
  FROM serialized AS s
  LEFT JOIN `your-project.finance.fx_daily_rates` AS fx
    ON fx.rate_date = s.event_date
   AND UPPER(fx.currency_code) = s.currency_code
  GROUP BY
    s.event_date, s.device_category, s.checkout_type, s.fulfillment_type,
    s.shipping_tier, s.delivery_speed_band, s.cart_size_band, s.country
)
SELECT
  d.event_date,
  d.device_category,
  d.checkout_type,
  d.fulfillment_type,
  d.shipping_tier,
  d.delivery_speed_band,
  d.cart_size_band,
  d.country,
  d.shipping_info_added,
  d.shipping_info_added_deduplicated,
  d.resubmission_events,
  SAFE_DIVIDE(d.resubmission_events, d.shipping_info_added)                     AS resubmission_share,
  d.sessions_with_shipping_info,
  d.users_with_shipping_info,
  d.checkout_instances_with_shipping_info,
  SAFE_DIVIDE(d.shipping_info_added, d.checkout_instances_with_shipping_info)   AS submissions_per_checkout_instance,
  d.units_at_shipping_step,
  d.lines_at_shipping_step,
  d.events_with_verified_address,
  SAFE_DIVIDE(d.events_with_verified_address, d.shipping_info_added)            AS verified_address_share,
  d.cart_context_value_usd,
  d.events_missing_checkout_key,
  SAFE_DIVIDE(d.events_missing_checkout_key, d.shipping_info_added)             AS degraded_serialization_share,
  d.events_missing_fx_rate,
  d.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)                      AS is_provisional
FROM daily AS d
ORDER BY d.event_date DESC, d.shipping_info_added DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Shipping Info Added: every qualifying commerce.checkouts ExperienceEvent that carries the shipping step.
-- The headline number is the raw occurrence count, so an address edit or a delivery-option switch that is
-- resubmitted increments it again; the visit-deduplicated variant (one row per visit x checkout instance)
-- is published beside it and is the number that must feed step-to-step funnel rates.
-- Shipping context is read from the _openkpis tenant object with the Analytics eVars/props as fallback:
-- shippingTier (eVar41), fulfillmentType (eVar42), deliveryOption (eVar43), checkoutType (eVar44),
-- step context (eVar45 / prop41) and addressValidationStatus (prop43).
-- Qualification: a submitted shipping destination or a selected delivery option carrying a non-null
-- shipping tier. Field focus, autocomplete suggestions and client-side validation failures never qualify.
-- Exclusions: carts that bypass the shipping step (fulfillmentType 'digital' or 'none'; pickup / BOPIS / curbside /
-- locker selections carrying a shipping tier stay in and are split by fulfillment_type),
-- internal and QA traffic (_openkpis.trafficType), staging-QA-preview-dev hosts, bot and synthetic user agents,
-- agent-keyed checkouts and unconsented hits (consents.collect.val <> 'y').
-- Only the shipping step of commerce.checkouts qualifies (_openkpis.checkout.stepName = 'shipping' or step = 3).
-- commerce.order.priceTotal is a contextual qualifier only - this is a funnel-behaviour count, never shipping
-- revenue or any carrier cost figure. Non-USD carts are converted with the finance daily booking rate.
-- Dates use the reporting timezone; the trailing 3 days stay provisional (72h late-hit window).
WITH base AS (
  SELECT
    to_date(from_utc_timestamp(e.timestamp, 'America/New_York'))                         AS event_date,
    e.timestamp                                                                          AS event_ts,
    COALESCE(e.endUserIDs._experience.ecid.id, e.identityMap['ECID'][0].id)               AS ecid,
    e._experience.analytics.session.num                                                   AS visit_num,
    COALESCE(e.device.type, 'unknown')                                                    AS device_type,
    COALESCE(e.placeContext.geo.countryCode, 'unknown')                                   AS country,
    COALESCE(e.marketing.trackingCode, 'direct')                                          AS tracking_code,
    COALESCE(e.commerce.checkouts.id,
             e._openkpis.checkout.cartId)                                                 AS checkout_instance_id,
    COALESCE(e._openkpis.checkout.shippingTier,
             e.commerce.shipping.shippingMethod,
             e._experience.analytics.customDimensions.eVars.eVar41)                        AS shipping_tier,
    lower(COALESCE(e._openkpis.checkout.deliveryOption,
             e._experience.analytics.customDimensions.eVars.eVar43))                       AS delivery_option,
    lower(COALESCE(e._openkpis.checkout.fulfillmentType,
             e._experience.analytics.customDimensions.eVars.eVar42, 'ship_to_address'))    AS fulfillment_type,
    lower(COALESCE(e._openkpis.checkout.checkoutType,
             e._experience.analytics.customDimensions.eVars.eVar44, 'unknown'))            AS checkout_type,
    lower(COALESCE(e._openkpis.checkout.stepName,
             e._experience.analytics.customDimensions.props.prop41, 'unknown'))            AS checkout_step_name,
    CAST(e._openkpis.checkout.step AS INT)                                                 AS checkout_step,
    lower(COALESCE(e._openkpis.checkout.addressValidationStatus,
             e._experience.analytics.customDimensions.props.prop43))                       AS address_validation_status,
    COALESCE(CAST(e._openkpis.checkout.isResubmission AS BOOLEAN), false)                  AS is_resubmission,
    COALESCE(e._openkpis.checkout.cartSizeBand, 'unknown')                                 AS cart_size_band,
    upper(COALESCE(e.commerce.order.currencyCode, 'USD'))                                  AS currency_code,
    CAST(COALESCE(e.commerce.order.priceTotal, 0) AS DOUBLE)                               AS cart_value_local,
    CASE WHEN e.productListItems IS NULL THEN 0
         ELSE aggregate(e.productListItems, 0, (acc, x) -> acc + COALESCE(CAST(x.quantity AS INT), 0)) END AS item_units,
    CASE WHEN e.productListItems IS NULL THEN 0 ELSE size(e.productListItems) END          AS item_line_count
  FROM web_experience_events_dataset AS e
  WHERE e.timestamp >= date_sub(current_date(), 91)
    AND e.timestamp <  current_date()
    AND e.eventType = 'commerce.checkouts'
    AND COALESCE(e.commerce.checkouts.value, 0) > 0
    AND (lower(COALESCE(e._openkpis.checkout.stepName, '')) = 'shipping'
         OR CAST(e._openkpis.checkout.step AS INT) = 3)
    AND lower(COALESCE(e.consents.collect.val, 'y')) = 'y'
    AND lower(COALESCE(e._openkpis.trafficType, 'external')) NOT IN ('internal', 'qa', 'test', 'bot')
    AND lower(COALESCE(e.web.webPageDetails.URL, '')) NOT RLIKE '^https?://(staging|stage|qa|uat|preview|dev)[.-]|localhost'
    AND lower(COALESCE(e.environment.browserDetails.userAgent, '')) NOT RLIKE 'bot|spider|crawler|headless|synthetic'
),
qualified AS (
  SELECT
    b.*,
    concat(b.ecid, '-', CAST(b.visit_num AS STRING))                                       AS visit_key,
    COALESCE(b.checkout_instance_id,
             concat('visit:', b.ecid, '-', CAST(b.visit_num AS STRING)))                   AS checkout_instance_key,
    b.checkout_instance_id IS NULL                                                         AS is_degraded_serialization,
    CASE
      WHEN b.delivery_option RLIKE 'same_day|next_day|express|overnight' THEN 'expedited'
      WHEN b.delivery_option RLIKE 'economy|ground|standard'             THEN 'standard'
      ELSE 'other'
    END                                                                                    AS delivery_speed_band
  FROM base AS b
  WHERE b.event_date >= date_sub(current_date(), 90)
    AND b.shipping_tier IS NOT NULL
    AND (b.delivery_option IS NOT NULL OR b.address_validation_status IS NOT NULL OR b.checkout_step_name RLIKE 'shipping')
    AND COALESCE(b.address_validation_status, 'verified') NOT IN ('failed', 'invalid', 'rejected')
    AND b.fulfillment_type NOT IN ('digital', 'none')
    AND b.checkout_type NOT IN ('agent_assisted', 'call_center', 'customer_service')
),
serialized AS (
  SELECT
    q.*,
    ROW_NUMBER() OVER (
      PARTITION BY q.visit_key, q.checkout_instance_key
      ORDER BY q.event_ts
    ) AS occurrence_rank
  FROM qualified AS q
),
daily AS (
  SELECT
    s.event_date,
    s.device_type,
    s.checkout_type,
    s.fulfillment_type,
    s.shipping_tier,
    s.delivery_speed_band,
    s.cart_size_band,
    s.country,
    COUNT(*)                                                                          AS shipping_info_added,
    SUM(CASE WHEN s.occurrence_rank = 1 THEN 1 ELSE 0 END)                             AS shipping_info_added_deduplicated,
    SUM(CASE WHEN s.is_resubmission THEN 1 ELSE 0 END)                                 AS resubmission_events,
    COUNT(DISTINCT s.visit_key)                                                        AS visits_with_shipping_info,
    COUNT(DISTINCT s.ecid)                                                             AS visitors_with_shipping_info,
    COUNT(DISTINCT s.checkout_instance_key)                                            AS checkout_instances_with_shipping_info,
    SUM(s.item_units)                                                                  AS units_at_shipping_step,
    SUM(s.item_line_count)                                                             AS lines_at_shipping_step,
    SUM(CASE WHEN s.address_validation_status = 'verified' THEN 1 ELSE 0 END)          AS events_with_verified_address,
    SUM(CASE WHEN s.is_degraded_serialization THEN 1 ELSE 0 END)                       AS events_missing_checkout_key,
    ROUND(SUM(s.cart_value_local *
              CASE WHEN s.currency_code = 'USD' THEN CAST(1.0 AS DOUBLE) ELSE fx.rate_to_usd END), 2) AS cart_context_value_usd,
    SUM(CASE WHEN s.currency_code <> 'USD' AND fx.rate_to_usd IS NULL THEN 1 ELSE 0 END) AS events_missing_fx_rate
  FROM serialized AS s
  LEFT JOIN fx_daily_rates_dataset AS fx
    ON fx.rate_date = s.event_date
   AND upper(fx.currency_code) = s.currency_code
  GROUP BY
    s.event_date, s.device_type, s.checkout_type, s.fulfillment_type,
    s.shipping_tier, s.delivery_speed_band, s.cart_size_band, s.country
)
SELECT
  d.event_date,
  d.device_type,
  d.checkout_type,
  d.fulfillment_type,
  d.shipping_tier,
  d.delivery_speed_band,
  d.cart_size_band,
  d.country,
  d.shipping_info_added,
  d.shipping_info_added_deduplicated,
  d.resubmission_events,
  CAST(d.resubmission_events AS DOUBLE) / NULLIF(d.shipping_info_added, 0)              AS resubmission_share,
  d.visits_with_shipping_info,
  d.visitors_with_shipping_info,
  d.checkout_instances_with_shipping_info,
  CAST(d.shipping_info_added AS DOUBLE)
    / NULLIF(d.checkout_instances_with_shipping_info, 0)                                AS submissions_per_checkout_instance,
  d.units_at_shipping_step,
  d.lines_at_shipping_step,
  d.events_with_verified_address,
  CAST(d.events_with_verified_address AS DOUBLE) / NULLIF(d.shipping_info_added, 0)     AS verified_address_share,
  d.cart_context_value_usd,
  d.events_missing_checkout_key,
  CAST(d.events_missing_checkout_key AS DOUBLE) / NULLIF(d.shipping_info_added, 0)      AS degraded_serialization_share,
  d.events_missing_fx_rate,
  d.event_date >= date_sub(current_date(), 3)                                           AS is_provisional
FROM daily AS d
ORDER BY d.event_date DESC, d.shipping_info_added DESC;