Unsubscribes

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

-- Unique subscribers with a recipient-initiated global email-marketing opt-out, dated by opt-out time
SELECT
  COUNT(DISTINCT subscriber_id) AS unsubscribes
FROM `your-project.esp.email_subscription_events`
WHERE DATE(unsubscribed_at) BETWEEN start_date AND end_date
  -- Unsubscribes only: spam complaints and hard bounces are separate suppression types
  AND event_type = 'unsubscribe'
  -- Marketing email only: transactional email, SMS and push are out of scope
  AND channel = 'email'
  AND message_stream = 'marketing'
  -- Footer link, one-click List-Unsubscribe and preference-center opt-outs
  AND unsubscribe_method IN ('footer_link', 'list_unsubscribe_one_click', 'preference_center')
  -- Loss of all email marketing consent; topic-level opt-downs are excluded
  AND consent_scope = 'all_email_marketing'
  -- Recipient-initiated only: admin suppressions, CRM-sync imports and GDPR erasures are excluded
  AND initiated_by = 'recipient';

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window: last 90 days through yesterday
WITH params AS (
  SELECT
    CAST(DATE_SUB(CURRENT_DATE, 90) AS DATE) AS start_date,
    CAST(DATE_SUB(CURRENT_DATE, 1) AS DATE) AS end_date
)
-- Unique subscribers with a recipient-initiated global email-marketing opt-out, dated by opt-out time
SELECT
  COUNT(DISTINCT e.subscriber_id) AS unsubscribes
FROM esp_email_subscription_events e
CROSS JOIN params p
WHERE CAST(e.unsubscribed_at AS DATE) BETWEEN p.start_date AND p.end_date
  -- Unsubscribes only: spam complaints and hard bounces are separate suppression types
  AND e.event_type = 'unsubscribe'
  -- Marketing email only: transactional email, SMS and push are out of scope
  AND e.channel = 'email'
  AND e.message_stream = 'marketing'
  -- Footer link, one-click List-Unsubscribe and preference-center opt-outs
  AND e.unsubscribe_method IN ('footer_link', 'list_unsubscribe_one_click', 'preference_center')
  -- Loss of all email marketing consent; topic-level opt-downs are excluded
  AND e.consent_scope = 'all_email_marketing'
  -- Recipient-initiated only: admin suppressions, CRM-sync imports and GDPR erasures are excluded
  AND e.initiated_by = 'recipient';