WISMO Rate

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "support:contact",
        "pageType": "customer_support"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "submit_form",
          "eventAction": "form_submit",
          "type": "form"
        },
        "attributes": {
          "form_id": "contact-support",
          "form_type": "contact_support",
          "contact_topic": "order_status",
          "support_channel": "web_form",
          "order_id": "ORD-100245"
        }
      }
    ]
  }
}

{
  "event": "submit_form",
  "form_id": "contact-support",
  "form_type": "contact_support",
  "contact_topic": "order_status",
  "support_channel": "web_form",
  "order_id": "ORD-100245"
}

{
  "event": "submit_form",
  "eventInfo": {
    "eventName": "submit_form",
    "type": "form"
  },
  "form": {
    "id": "contact-support",
    "type": "contact_support",
    "topic": "order_status",
    "channel": "web_form"
  },
  "order": {
    "id": "ORD-100245"
  }
}

{
  "eventType": "web.formFilledOut",
  "web": {
    "webInteraction": {
      "name": "submit_form",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar40": "contact_support",
          "eVar41": "order_status",
          "eVar42": "web_form",
          "eVar43": "ORD-100245"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- WISMO Rate by ship-date cohort week.
-- Denominator: every eligible order whose first carrier shipment falls in the period (OMS).
-- Numerator: the subset of those same orders with >= 1 WISMO contact (helpdesk UNION web submit_form)
-- between order placement and delivered_at + 7 days (or first shipment + 30 days when undelivered).
-- Reporting window: the last complete calendar month relative to the run date.
-- Contacts are scanned 31 days before the cohort start and 37 days after its end (ship + 30 + 7),
-- so no contact inside a cohort order's attribution window is lost.
DECLARE start_date DATE DEFAULT DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH);
DECLARE end_date   DATE DEFAULT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY);
DECLARE contact_scan_start DATE DEFAULT DATE_SUB(start_date, INTERVAL 31 DAY);
DECLARE contact_scan_end   DATE DEFAULT DATE_ADD(end_date, INTERVAL 37 DAY);

WITH shipped_orders AS (
  SELECT
    s.order_id,
    MIN(s.shipped_at)            AS first_shipped_at,
    MAX(s.delivered_at)          AS delivered_at,
    ANY_VALUE(o.order_created_at) AS order_created_at,
    ANY_VALUE(s.carrier)         AS carrier
  FROM `your-project.oms.shipments` AS s
  JOIN `your-project.oms.orders` AS o
    ON o.order_id = s.order_id
  WHERE s.tracking_number IS NOT NULL
    AND COALESCE(o.is_test, FALSE) = FALSE
    AND COALESCE(o.is_employee, FALSE) = FALSE
    AND COALESCE(o.fraud_hold, FALSE) = FALSE
    AND LOWER(o.fulfillment_method) NOT IN ('bopis', 'curbside', 'digital', 'gift_card')
    AND LOWER(COALESCE(o.support_owner, 'merchant')) <> 'marketplace_seller'
  GROUP BY s.order_id
  HAVING DATE(MIN(s.shipped_at)) BETWEEN start_date AND end_date
),
helpdesk_wismo AS (
  SELECT
    t.order_id,
    t.created_at AS contact_ts
  FROM `your-project.helpdesk.tickets` AS t
  WHERE t.order_id IS NOT NULL
    AND (
      EXISTS (
        SELECT 1 FROM UNNEST(t.tags) AS tag
        WHERE LOWER(tag) IN ('wismo', 'order_status', 'tracking', 'delivery_delay')
      )
      OR LOWER(t.contact_reason) IN ('wismo', 'order_status', 'tracking', 'delivery_delay')
    )
    AND LOWER(COALESCE(t.final_disposition, 'order_status')) NOT IN ('damaged_item', 'wrong_item', 'return', 'cancellation')
    AND NOT (LOWER(t.channel) = 'chatbot' AND COALESCE(t.escalated_to_agent, FALSE) = FALSE)
    AND DATE(t.created_at) BETWEEN contact_scan_start AND contact_scan_end
),
web_form_events AS (
  SELECT
    TIMESTAMP_MICROS(e.event_timestamp) AS contact_ts,
    (SELECT MAX(IF(ep.key = 'form_type', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS form_type,
    (SELECT MAX(IF(ep.key = 'contact_topic', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS contact_topic,
    (SELECT MAX(IF(ep.key = 'order_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL))
       FROM UNNEST(e.event_params) AS ep) AS order_id
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', contact_scan_start) AND FORMAT_DATE('%Y%m%d', contact_scan_end)
    AND e.event_name = 'submit_form'
),
web_wismo AS (
  SELECT order_id, contact_ts
  FROM web_form_events
  WHERE form_type = 'contact_support'
    AND contact_topic = 'order_status'
    AND order_id IS NOT NULL
),
wismo_contacts AS (
  SELECT order_id, contact_ts FROM helpdesk_wismo
  UNION ALL
  SELECT order_id, contact_ts FROM web_wismo
),
cohort AS (
  SELECT
    so.order_id,
    DATE_TRUNC(DATE(so.first_shipped_at), WEEK(MONDAY)) AS ship_cohort_week,
    LOGICAL_OR(wc.order_id IS NOT NULL) AS has_wismo_contact
  FROM shipped_orders AS so
  LEFT JOIN wismo_contacts AS wc
    ON wc.order_id = so.order_id
   AND wc.contact_ts >= so.order_created_at
   AND wc.contact_ts < COALESCE(
         TIMESTAMP_ADD(so.delivered_at, INTERVAL 7 DAY),
         TIMESTAMP_ADD(so.first_shipped_at, INTERVAL 30 DAY))
  GROUP BY so.order_id, ship_cohort_week
)
SELECT
  ship_cohort_week,
  COUNT(DISTINCT order_id)                                   AS shipped_orders,
  COUNT(DISTINCT IF(has_wismo_contact, order_id, NULL))      AS wismo_orders,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(has_wismo_contact, order_id, NULL)),
    COUNT(DISTINCT order_id)) * 100, 2)                      AS wismo_rate_pct
FROM cohort
GROUP BY ship_cohort_week
ORDER BY ship_cohort_week;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- WISMO Rate by ship-date cohort week.
-- Denominator: every eligible order whose first carrier shipment falls in the period (OMS dataset).
-- Numerator: the subset of those same orders with >= 1 WISMO contact (helpdesk dataset UNION web formFilledOut)
-- between order placement and delivered_at + 7 days (or first shipment + 30 days when undelivered).
-- Reporting window: the last complete calendar month relative to the run date.
-- Contacts are scanned 31 days before the cohort start and 37 days after its end (ship + 30 + 7),
-- so no contact inside a cohort order's attribution window is lost.
WITH date_window AS (
  -- Ship cohort bounds: first and last day of the last complete calendar month
  SELECT
    TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MM') AS ship_start,
    DATE_SUB(TRUNC(CURRENT_DATE, 'MM'), 1)    AS ship_end
),
shipped_orders AS (
  SELECT
    s.order_id,
    MIN(s.shipped_at)        AS first_shipped_at,
    MAX(s.delivered_at)      AS delivered_at,
    MIN(o.order_created_at)  AS order_created_at
  FROM oms_shipments_dataset s
  JOIN oms_orders_dataset o
    ON o.order_id = s.order_id
  CROSS JOIN date_window dw
  WHERE s.tracking_number IS NOT NULL
    AND COALESCE(o.is_test, false) = false
    AND COALESCE(o.is_employee, false) = false
    AND COALESCE(o.fraud_hold, false) = false
    AND LOWER(o.fulfillment_method) NOT IN ('bopis', 'curbside', 'digital', 'gift_card')
    AND LOWER(COALESCE(o.support_owner, 'merchant')) <> 'marketplace_seller'
  GROUP BY s.order_id
  HAVING CAST(MIN(s.shipped_at) AS DATE) BETWEEN MIN(dw.ship_start) AND MIN(dw.ship_end)
),
helpdesk_wismo AS (
  SELECT
    t.order_id,
    t.created_at AS contact_ts
  FROM helpdesk_tickets_dataset t
  CROSS JOIN date_window dw
  WHERE t.order_id IS NOT NULL
    AND (
      EXISTS(t.tags, tag -> LOWER(tag) IN ('wismo', 'order_status', 'tracking', 'delivery_delay'))
      OR LOWER(t.contact_reason) IN ('wismo', 'order_status', 'tracking', 'delivery_delay')
    )
    AND LOWER(COALESCE(t.final_disposition, 'order_status')) NOT IN ('damaged_item', 'wrong_item', 'return', 'cancellation')
    AND NOT (LOWER(t.channel) = 'chatbot' AND COALESCE(t.escalated_to_agent, false) = false)
    AND CAST(t.created_at AS DATE) BETWEEN DATE_SUB(dw.ship_start, 31) AND DATE_ADD(dw.ship_end, 37)
),
web_wismo AS (
  SELECT
    _experience.analytics.customDimensions.eVars.eVar43 AS order_id,
    timestamp AS contact_ts
  FROM web_events_dataset
  CROSS JOIN date_window dw
  WHERE eventType = 'web.formFilledOut'
    AND web.webInteraction.name = 'submit_form'
    AND _experience.analytics.customDimensions.eVars.eVar40 = 'contact_support'
    AND _experience.analytics.customDimensions.eVars.eVar41 = 'order_status'
    AND _experience.analytics.customDimensions.eVars.eVar43 IS NOT NULL
    AND CAST(timestamp AS DATE) BETWEEN DATE_SUB(dw.ship_start, 31) AND DATE_ADD(dw.ship_end, 37)
),
wismo_contacts AS (
  SELECT order_id, contact_ts FROM helpdesk_wismo
  UNION ALL
  SELECT order_id, contact_ts FROM web_wismo
),
cohort AS (
  SELECT
    so.order_id,
    CAST(DATE_TRUNC('WEEK', so.first_shipped_at) AS DATE) AS ship_cohort_week,
    MAX(CASE WHEN wc.order_id IS NOT NULL THEN 1 ELSE 0 END) AS has_wismo_contact
  FROM shipped_orders so
  LEFT JOIN wismo_contacts wc
    ON wc.order_id = so.order_id
   AND wc.contact_ts >= so.order_created_at
   AND wc.contact_ts < COALESCE(
         so.delivered_at + INTERVAL 7 DAYS,
         so.first_shipped_at + INTERVAL 30 DAYS)
  GROUP BY so.order_id, CAST(DATE_TRUNC('WEEK', so.first_shipped_at) AS DATE)
)
SELECT
  ship_cohort_week,
  COUNT(DISTINCT order_id)                                             AS shipped_orders,
  COUNT(DISTINCT CASE WHEN has_wismo_contact = 1 THEN order_id END)   AS wismo_orders,
  ROUND(
    CAST(COUNT(DISTINCT CASE WHEN has_wismo_contact = 1 THEN order_id END) AS DOUBLE)
    / NULLIF(COUNT(DISTINCT order_id), 0) * 100, 2)                    AS wismo_rate_pct
FROM cohort
GROUP BY ship_cohort_week
ORDER BY ship_cohort_week;