Delivery Notifications Sent

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE reporting_tz STRING DEFAULT 'America/New_York';

-- Delivery notifications sent per day: one per message_id per channel, dated by send timestamp in the reporting timezone
SELECT
  DATE(sent_at, reporting_tz) AS send_date,
  COUNT(DISTINCT CONCAT(message_id, '|', channel)) AS delivery_notifications_sent
FROM `your-project.crm.message_sends`
WHERE message_category = 'delivery_notification'
  -- In-scope shipment status templates only
  AND template_status IN ('shipped', 'out_for_delivery', 'delivered', 'delivery_exception')
  -- Counted at dispatch: suppressed, opted-out and failed-at-send rows excluded; later bounces stay in
  AND send_status = 'sent'
  AND channel IN ('email', 'sms', 'push')
  -- Exclude seed/test recipients and internal test orders
  AND is_seed_recipient = FALSE
  AND is_test_order = FALSE
  AND DATE(sent_at, reporting_tz) BETWEEN start_date AND end_date
GROUP BY send_date
ORDER BY send_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window and reporting timezone
WITH params AS (
  SELECT
    DATE_SUB(CURRENT_DATE(), 90) AS start_date,
    CURRENT_DATE() AS end_date,
    'America/New_York' AS reporting_tz
)
-- Delivery notifications sent per day: one per message_id per channel, dated by send timestamp in the reporting timezone
SELECT
  TO_DATE(FROM_UTC_TIMESTAMP(s.sent_at, p.reporting_tz)) AS send_date,
  COUNT(DISTINCT CONCAT(s.message_id, '|', s.channel)) AS delivery_notifications_sent
FROM aep_backend_dataset s
CROSS JOIN params p
WHERE s.message_category = 'delivery_notification'
  -- In-scope shipment status templates only
  AND s.template_status IN ('shipped', 'out_for_delivery', 'delivered', 'delivery_exception')
  -- Counted at dispatch: suppressed, opted-out and failed-at-send rows excluded; later bounces stay in
  AND s.send_status = 'sent'
  AND s.channel IN ('email', 'sms', 'push')
  -- Exclude seed/test recipients and internal test orders
  AND s.is_seed_recipient = FALSE
  AND s.is_test_order = FALSE
  AND TO_DATE(FROM_UTC_TIMESTAMP(s.sent_at, p.reporting_tz)) BETWEEN p.start_date AND p.end_date
GROUP BY TO_DATE(FROM_UTC_TIMESTAMP(s.sent_at, p.reporting_tz))
ORDER BY send_date;