Delivery Notification Open Rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "notification_open",
        "eventAction": "open",
        "type": "transactional_message",
        "timeStamp": "2026-09-18T14:05:00Z"
      },
      "category": {
        "primaryCategory": "post_purchase_messaging"
      },
      "attributes": {
        "messageType": "shipping_notification",
        "notificationType": "out_for_delivery",
        "channel": "push",
        "messageId": "msg_7f3a91c2",
        "campaignId": "tx_shipping_ofd_v3",
        "orderId": "ORD-100245",
        "shipmentId": "SHP-558812",
        "carrier": "UPS",
        "openType": "direct"
      }
    }
  ],
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "hashed_c1a9e0b4"
          }
        }
      ]
    }
  ]
}

{
  "event": "notification_open",
  "message_type": "shipping_notification",
  "notification_type": "out_for_delivery",
  "channel": "push",
  "message_id": "msg_7f3a91c2",
  "campaign_id": "tx_shipping_ofd_v3",
  "order_id": "ORD-100245",
  "shipment_id": "SHP-558812",
  "carrier": "UPS",
  "open_type": "direct"
}

{
  "event": "notification_open",
  "eventInfo": {
    "interactionType": "open"
  },
  "message": {
    "messageType": "shipping_notification",
    "notificationType": "out_for_delivery",
    "channel": "push",
    "messageId": "msg_7f3a91c2",
    "campaignId": "tx_shipping_ofd_v3",
    "openType": "direct"
  },
  "order": {
    "orderId": "ORD-100245",
    "shipmentId": "SHP-558812",
    "carrier": "UPS"
  }
}

{
  "eventType": "message.tracking",
  "timestamp": "2026-09-18T14:05:00Z",
  "_experience": {
    "customerJourneyManagement": {
      "messageInteraction": {
        "interactionType": "open"
      },
      "messageExecution": {
        "messageExecutionID": "msg_7f3a91c2",
        "messageID": "tx_shipping_ofd_v3",
        "messageType": "transactional"
      },
      "messageProfile": {
        "channel": {
          "_id": "https://ns.adobe.com/xdm/channels/push"
        }
      }
    },
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar40": "shipping_notification",
          "eVar41": "out_for_delivery",
          "eVar42": "push",
          "eVar43": "ORD-100245"
        }
      }
    }
  },
  "commerce": {
    "order": {
      "purchaseID": "ORD-100245"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Delivery Notification Open Rate (MPP-adjusted), closed 7-day cohorts, by delivery date x channel x notification_type
-- Denominator: shipping notifications delivered (ESP / push provider delivery log)
-- Numerator: distinct delivered message_ids with a qualifying open within 7 days (push direct opens from GA4, email opens from the ESP engagement log)
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 97 DAY) AS start_date,
    DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 8 DAY) AS end_date
),
delivered_raw AS (
  SELECT
    d.message_id,
    d.channel,
    d.notification_type,
    d.shipment_id,
    d.delivered_at,
    DATE(d.delivered_at, 'America/New_York') AS cohort_date,
    LAG(d.delivered_at) OVER (PARTITION BY d.shipment_id, d.notification_type, d.channel ORDER BY d.delivered_at) AS prev_delivered_at
  FROM `your-project.esp.message_delivery_log` AS d
  CROSS JOIN params AS p
  WHERE d.message_type = 'shipping_notification'
    AND d.notification_type IN ('shipment_confirmation', 'in_transit', 'out_for_delivery', 'delivered', 'delivery_delay')
    AND d.channel IN ('email', 'push')
    AND d.delivery_status = 'delivered'
    AND d.is_test_recipient = FALSE
    AND d.is_partner_address = FALSE
    AND DATE(d.delivered_at, 'America/New_York') BETWEEN p.start_date AND p.end_date
),
-- 24-hour resend collapse: a new chain starts when the previous copy for the same shipment_id + notification_type + channel is 24h or more earlier
delivered_chain AS (
  SELECT
    dr.*,
    SUM(IF(dr.prev_delivered_at IS NULL OR TIMESTAMP_DIFF(dr.delivered_at, dr.prev_delivered_at, HOUR) >= 24, 1, 0))
      OVER (PARTITION BY dr.shipment_id, dr.notification_type, dr.channel ORDER BY dr.delivered_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS chain_no
  FROM delivered_raw AS dr
),
-- Every delivered copy (kept or resent) mapped to the kept message_id (first copy in its 24h chain)
message_map AS (
  SELECT
    dc.message_id,
    dc.channel,
    dc.delivered_at AS copy_delivered_at,
    FIRST_VALUE(dc.message_id) OVER (PARTITION BY dc.shipment_id, dc.notification_type, dc.channel, dc.chain_no ORDER BY dc.delivered_at) AS kept_message_id
  FROM delivered_chain AS dc
),
delivered AS (
  SELECT dc.message_id, dc.channel, dc.notification_type, dc.delivered_at, dc.cohort_date
  FROM delivered_chain AS dc
  WHERE (dc.prev_delivered_at IS NULL OR TIMESTAMP_DIFF(dc.delivered_at, dc.prev_delivered_at, HOUR) >= 24)
    AND NOT EXISTS (
      SELECT 1
      FROM `your-project.oms.shipments` AS s
      WHERE s.shipment_id = dc.shipment_id
        AND (s.is_fraud = TRUE OR s.cancelled_before_first_scan = TRUE)
    )
),
ga4_push_opens AS (
  SELECT
    (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'message_id') AS message_id,
    'push' AS channel,
    TIMESTAMP_MICROS(e.event_timestamp) AS opened_at,
    FALSE AS is_machine_open
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 98 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE('America/New_York'))
    AND e.event_name = 'notification_open'
    AND (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'message_type') = 'shipping_notification'
    AND (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'channel') = 'push'
    AND (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'open_type') = 'direct'
),
esp_email_opens AS (
  SELECT
    o.message_id,
    'email' AS channel,
    o.opened_at,
    COALESCE(o.is_machine_open, FALSE) AS is_machine_open
  FROM `your-project.esp.message_engagement_log` AS o
  WHERE o.engagement_type = 'open'
    AND o.message_type = 'shipping_notification'
    AND o.channel = 'email'
    AND DATE(o.opened_at, 'America/New_York') >= DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 98 DAY)
),
all_opens AS (
  SELECT message_id, channel, opened_at, is_machine_open FROM ga4_push_opens WHERE message_id IS NOT NULL
  UNION ALL
  SELECT message_id, channel, opened_at, is_machine_open FROM esp_email_opens
),
-- Opens on a resent copy are credited to the kept message_id; the scanner check uses the copy's own delivery time
opens_mapped AS (
  SELECT
    mm.kept_message_id AS message_id,
    o.channel,
    o.opened_at,
    mm.copy_delivered_at,
    o.is_machine_open
  FROM all_opens AS o
  JOIN message_map AS mm
    ON mm.message_id = o.message_id
   AND mm.channel = o.channel
),
message_status AS (
  SELECT
    d.message_id,
    LOGICAL_OR(o.message_id IS NOT NULL) AS opened_raw,
    LOGICAL_OR(
      o.message_id IS NOT NULL
      AND o.is_machine_open = FALSE
      AND (d.channel = 'push' OR TIMESTAMP_DIFF(o.opened_at, o.copy_delivered_at, SECOND) > 10)
    ) AS opened_adjusted
  FROM delivered AS d
  LEFT JOIN opens_mapped AS o
    ON o.message_id = d.message_id
   AND o.channel = d.channel
   AND o.opened_at >= o.copy_delivered_at
   AND o.opened_at < TIMESTAMP_ADD(d.delivered_at, INTERVAL 7 DAY)
  GROUP BY d.message_id
)
SELECT
  d.cohort_date,
  COALESCE(d.channel, 'all_channels') AS channel,
  COALESCE(d.notification_type, 'all_types') AS notification_type,
  COUNT(DISTINCT d.message_id) AS delivered_notifications,
  COUNT(DISTINCT IF(m.opened_raw, d.message_id, NULL)) AS unique_opens_raw,
  COUNT(DISTINCT IF(m.opened_adjusted, d.message_id, NULL)) AS unique_opens_adjusted,
  ROUND(100 * SAFE_DIVIDE(COUNT(DISTINCT IF(m.opened_raw, d.message_id, NULL)), COUNT(DISTINCT d.message_id)), 2) AS open_rate_raw_pct,
  ROUND(100 * SAFE_DIVIDE(COUNT(DISTINCT IF(m.opened_adjusted, d.message_id, NULL)), COUNT(DISTINCT d.message_id)), 2) AS delivery_notification_open_rate_pct
FROM delivered AS d
JOIN message_status AS m
  ON m.message_id = d.message_id
GROUP BY GROUPING SETS (
  (d.cohort_date, d.channel, d.notification_type),
  (d.cohort_date, d.channel),
  (d.cohort_date)
)
ORDER BY d.cohort_date DESC, channel, notification_type;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Delivery Notification Open Rate (MPP-adjusted) from Journey Optimizer system datasets, closed 7-day cohorts
-- Denominator: shipping notification executions with a sent/delivered feedback status and no bounce, exclusion or error (AJO Message Feedback Event Dataset)
-- Numerator: distinct delivered executions with a qualifying open within 7 days (AJO Email Tracking and Push Tracking Experience Event Datasets)
-- shipping_notification_templates maps AJO messageID (template / campaign id) to message_type and notification_type
-- shipping_notification_send_map maps each AJO messageExecutionID to its order_id / shipment_id and recipient flags (order and send mapping ingested from OMS / ESP)
-- Window bounds are computed in America/New_York to match cohort_date
WITH params AS (
  SELECT
    date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 97) AS start_date,
    date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 8) AS end_date,
    date_sub(to_date(from_utc_timestamp(current_timestamp(), 'America/New_York')), 98) AS lookback_date
),
send_map AS (
  SELECT
    sm.message_execution_id AS message_id,
    sm.shipment_id
  FROM shipping_notification_send_map AS sm
  WHERE sm.is_test_recipient = false
    AND sm.is_partner_address = false
),
templates AS (
  SELECT message_id AS template_id, notification_type
  FROM shipping_notification_templates
  WHERE message_type = 'shipping_notification'
    AND notification_type IN ('shipment_confirmation', 'in_transit', 'out_for_delivery', 'delivered', 'delivery_delay')
),
feedback AS (
  SELECT
    f._experience.customerJourneyManagement.messageExecution.messageExecutionID AS message_id,
    f._experience.customerJourneyManagement.messageExecution.messageID AS template_id,
    CASE
      WHEN f._experience.customerJourneyManagement.messageProfile.channel._id LIKE '%/channels/email' THEN 'email'
      WHEN f._experience.customerJourneyManagement.messageProfile.channel._id LIKE '%/channels/push' THEN 'push'
    END AS channel,
    lower(f._experience.customerJourneyManagement.messageDeliveryfeedback.feedbackStatus) AS feedback_status,
    f.timestamp AS feedback_ts
  FROM ajo_message_feedback_event_dataset AS f
  CROSS JOIN params AS p
  WHERE f.eventType = 'message.feedback'
    AND to_date(from_utc_timestamp(f.timestamp, 'America/New_York')) >= p.lookback_date
),
delivered AS (
  SELECT
    fb.message_id,
    fb.channel,
    t.notification_type,
    sm.shipment_id,
    MIN(CASE WHEN fb.feedback_status IN ('sent', 'delivered') THEN fb.feedback_ts END) AS delivered_at
  FROM feedback AS fb
  JOIN templates AS t
    ON t.template_id = fb.template_id
  JOIN send_map AS sm
    ON sm.message_id = fb.message_id
  WHERE fb.channel IN ('email', 'push')
  GROUP BY fb.message_id, fb.channel, t.notification_type, sm.shipment_id
  HAVING SUM(CASE WHEN fb.feedback_status IN ('sent', 'delivered') THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN fb.feedback_status IN ('bounce', 'exclude', 'error') THEN 1 ELSE 0 END) = 0
),
delivered_raw AS (
  SELECT
    d.message_id,
    d.channel,
    d.notification_type,
    d.shipment_id,
    d.delivered_at,
    to_date(from_utc_timestamp(d.delivered_at, 'America/New_York')) AS cohort_date,
    LAG(d.delivered_at) OVER (PARTITION BY d.shipment_id, d.notification_type, d.channel ORDER BY d.delivered_at) AS prev_delivered_at
  FROM delivered AS d
  CROSS JOIN params AS p
  WHERE to_date(from_utc_timestamp(d.delivered_at, 'America/New_York')) BETWEEN p.start_date AND p.end_date
),
-- 24-hour resend collapse: a new chain starts when the previous copy for the same shipment_id + notification_type + channel is 24h or more earlier
delivered_chain AS (
  SELECT
    dr.*,
    SUM(CASE WHEN dr.prev_delivered_at IS NULL OR unix_timestamp(dr.delivered_at) - unix_timestamp(dr.prev_delivered_at) >= 86400 THEN 1 ELSE 0 END)
      OVER (PARTITION BY dr.shipment_id, dr.notification_type, dr.channel ORDER BY dr.delivered_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS chain_no
  FROM delivered_raw AS dr
),
-- Every delivered copy (kept or resent) mapped to the kept message_id (first copy in its 24h chain)
message_map AS (
  SELECT
    dc.message_id,
    dc.channel,
    dc.delivered_at AS copy_delivered_at,
    first_value(dc.message_id) OVER (PARTITION BY dc.shipment_id, dc.notification_type, dc.channel, dc.chain_no ORDER BY dc.delivered_at) AS kept_message_id
  FROM delivered_chain AS dc
),
cohort AS (
  SELECT
    dc.message_id,
    dc.channel,
    dc.notification_type,
    dc.delivered_at,
    dc.cohort_date
  FROM delivered_chain AS dc
  WHERE (dc.prev_delivered_at IS NULL OR unix_timestamp(dc.delivered_at) - unix_timestamp(dc.prev_delivered_at) >= 86400)
    AND NOT EXISTS (
      SELECT 1
      FROM oms_shipments AS s
      WHERE s.shipment_id = dc.shipment_id
        AND (s.is_fraud = true OR s.cancelled_before_first_scan = true)
    )
),
opens AS (
  SELECT
    e._experience.customerJourneyManagement.messageExecution.messageExecutionID AS message_id,
    'email' AS channel,
    e.timestamp AS opened_at,
    CASE WHEN e.environment.browserDetails.userAgent = 'Mozilla/5.0' THEN 1 ELSE 0 END AS is_machine_open
  FROM ajo_email_tracking_experience_event_dataset AS e
  CROSS JOIN params AS p
  WHERE e._experience.customerJourneyManagement.messageInteraction.interactionType = 'open'
    AND to_date(from_utc_timestamp(e.timestamp, 'America/New_York')) >= p.lookback_date
  UNION ALL
  SELECT
    pt._experience.customerJourneyManagement.messageExecution.messageExecutionID AS message_id,
    'push' AS channel,
    pt.timestamp AS opened_at,
    0 AS is_machine_open
  FROM ajo_push_tracking_experience_event_dataset AS pt
  CROSS JOIN params AS p
  WHERE pt.eventType = 'pushTracking.applicationOpened'
    AND to_date(from_utc_timestamp(pt.timestamp, 'America/New_York')) >= p.lookback_date
),
-- Opens on a resent copy are credited to the kept message_id; the scanner check uses the copy's own delivery time
opens_mapped AS (
  SELECT
    mm.kept_message_id AS message_id,
    o.channel,
    o.opened_at,
    mm.copy_delivered_at,
    o.is_machine_open
  FROM opens AS o
  JOIN message_map AS mm
    ON mm.message_id = o.message_id
   AND mm.channel = o.channel
),
message_status AS (
  SELECT
    c.message_id,
    MAX(CASE WHEN o.message_id IS NOT NULL THEN 1 ELSE 0 END) AS opened_raw,
    MAX(CASE
          WHEN o.message_id IS NOT NULL
           AND o.is_machine_open = 0
           AND (c.channel = 'push' OR unix_timestamp(o.opened_at) - unix_timestamp(o.copy_delivered_at) > 10)
          THEN 1 ELSE 0
        END) AS opened_adjusted
  FROM cohort AS c
  LEFT JOIN opens_mapped AS o
    ON o.message_id = c.message_id
   AND o.channel = c.channel
   AND o.opened_at >= o.copy_delivered_at
   AND o.opened_at < c.delivered_at + INTERVAL 7 DAYS
  GROUP BY c.message_id
)
SELECT
  c.cohort_date,
  COALESCE(c.channel, 'all_channels') AS channel,
  COALESCE(c.notification_type, 'all_types') AS notification_type,
  COUNT(DISTINCT c.message_id) AS delivered_notifications,
  COUNT(DISTINCT CASE WHEN m.opened_raw = 1 THEN c.message_id END) AS unique_opens_raw,
  COUNT(DISTINCT CASE WHEN m.opened_adjusted = 1 THEN c.message_id END) AS unique_opens_adjusted,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN m.opened_raw = 1 THEN c.message_id END) / NULLIF(COUNT(DISTINCT c.message_id), 0), 2) AS open_rate_raw_pct,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN m.opened_adjusted = 1 THEN c.message_id END) / NULLIF(COUNT(DISTINCT c.message_id), 0), 2) AS delivery_notification_open_rate_pct
FROM cohort AS c
JOIN message_status AS m
  ON m.message_id = c.message_id
GROUP BY GROUPING SETS (
  (c.cohort_date, c.channel, c.notification_type),
  (c.cohort_date, c.channel),
  (c.cohort_date)
)
ORDER BY c.cohort_date DESC, channel, notification_type;