Delivery Notifications Opened

{
  "event": [
    {
      "eventInfo": {
        "eventName": "notification_open",
        "type": "notificationOpen",
        "timeStamp": "2026-09-27T09:15:00Z"
      },
      "attributes": {
        "message_id": "msg_8842217",
        "notification_type": "out_for_delivery",
        "message_channel": "email",
        "machine_open": false,
        "message_time": "2026-09-27T09:15:00Z",
        "traffic_type": "customer",
        "carrier": "UPS"
      }
    }
  ]
}

{
  "event": "notification_open",
  "message_id": "msg_8842217",
  "notification_type": "out_for_delivery",
  "message_channel": "email",
  "machine_open": false,
  "message_time": "2026-09-27T09:15:00Z",
  "traffic_type": "customer",
  "carrier": "UPS"
}

{
  "event": "notificationOpen",
  "notification": {
    "message_id": "msg_8842217",
    "notification_type": "out_for_delivery",
    "message_channel": "email",
    "machine_open": false,
    "message_time": "2026-09-27T09:15:00Z",
    "traffic_type": "customer",
    "carrier": "UPS"
  }
}

{
  "eventType": "notification.open",
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "msg_8842217",
          "eVar2": "out_for_delivery",
          "eVar3": "email",
          "eVar4": "UPS"
        }
      }
    }
  },
  "_openkpis": {
    "notification": {
      "messageId": "msg_8842217",
      "notificationType": "out_for_delivery",
      "messageChannel": "email",
      "machineOpen": false,
      "messageTime": "2026-09-27T09:15:00Z",
      "trafficType": "customer",
      "carrier": "UPS"
    }
  }
}

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

-- Pivot notification_open parameters; scan 7 days past end_date so late opens of in-window sends are kept
WITH opens AS (
  SELECT
    TIMESTAMP_MICROS(event_timestamp) AS open_ts,
    MAX(IF(ep.key = 'message_id', ep.value.string_value, NULL)) AS message_id,
    MAX(IF(ep.key = 'notification_type', ep.value.string_value, NULL)) AS notification_type,
    MAX(IF(ep.key = 'message_channel', ep.value.string_value, NULL)) AS message_channel,
    MAX(IF(ep.key = 'machine_open', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS machine_open,
    MAX(IF(ep.key = 'message_time', SAFE_CAST(ep.value.string_value AS TIMESTAMP), NULL)) AS message_ts,
    MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type
  FROM `your-project.analytics_123456789.events_*`, UNNEST(event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 7 DAY))
    AND event_name = 'notification_open'
  GROUP BY user_pseudo_id, event_timestamp, event_bundle_sequence_id
)
-- Unique delivery-status notifications opened (email and push, human opens, within 7 days of send), dated by send date
SELECT
  DATE(message_ts) AS send_date,
  COUNT(DISTINCT message_id) AS delivery_notifications_opened
FROM opens
WHERE message_id IS NOT NULL
  AND notification_type IN ('shipped', 'out_for_delivery', 'delivered', 'delayed')
  AND message_channel IN ('email', 'push')
  AND COALESCE(machine_open, 'false') NOT IN ('true', '1')
  AND COALESCE(traffic_type, '') != 'internal'
  AND DATE(message_ts) BETWEEN start_date AND end_date
  AND open_ts <= TIMESTAMP_ADD(message_ts, INTERVAL 7 DAY)
GROUP BY send_date
ORDER BY send_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), 90) AS start_date,
    DATE_SUB(CURRENT_DATE(), 1) AS end_date
),
-- Notification open fields; scan 7 days past end_date so late opens of in-window sends are kept
opens AS (
  SELECT
    e.timestamp AS open_ts,
    e._openkpis.notification.messageId AS message_id,
    e._openkpis.notification.notificationType AS notification_type,
    e._openkpis.notification.messageChannel AS message_channel,
    e._openkpis.notification.machineOpen AS machine_open,
    TO_TIMESTAMP(e._openkpis.notification.messageTime) AS message_ts,
    e._openkpis.notification.trafficType AS traffic_type
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE e.eventType = 'notification.open'
    AND TO_DATE(e.timestamp) BETWEEN p.start_date AND DATE_ADD(p.end_date, 7)
)
-- Unique delivery-status notifications opened (email and push, human opens, within 7 days of send), dated by send date
SELECT
  TO_DATE(o.message_ts) AS send_date,
  COUNT(DISTINCT o.message_id) AS delivery_notifications_opened
FROM opens o
CROSS JOIN params p
WHERE o.message_id IS NOT NULL
  AND o.notification_type IN ('shipped', 'out_for_delivery', 'delivered', 'delayed')
  AND o.message_channel IN ('email', 'push')
  AND COALESCE(o.machine_open, false) = false
  AND COALESCE(o.traffic_type, '') != 'internal'
  AND TO_DATE(o.message_ts) BETWEEN p.start_date AND p.end_date
  AND o.open_ts <= o.message_ts + INTERVAL 7 DAYS
GROUP BY TO_DATE(o.message_ts)
ORDER BY send_date;