Restore Mailgun Unsubscribe Supressions

Mailgun unsubscribes are stored per domain. They can either be universal (represented by an asterisk) or apply to particular Mailgun tags. To restore unsubscribes in Mailgun from BQ, we've tackled the universal unsubscribes separately from the tag unsubscribes. Either way the steps are the same for both. You'll want to:

1. Pull a snapshot of unsusbscribes from mailgun_events.mailgun_events in BQ and export this data as a CSV file
2. Import the CSV file into Mailgun

The template should have headers that align with Mailgun expectations. As of 11/1/24, the expectation was a 3-column CSV with the headers address, tags, and created_at. Before uploading these CSVs into Mailgun, double-check that these are the current expectations and change the queries below as needed to accommodate any changes.

Queries for pulling unsubscribe snapshots from BQ

These queries over-index on unsubscribes as they do not consider later resubscribes. Historically, we have preferred the approach of over-unsubscribing versus under-unsubscribing when restoring this data in Mailgun.

Please be sure to replace @EMAIL_DOMAIN with the email domain that you are targeting.

Query for universal unsubscribes

SELECT
  DISTINCT recipient as address,
  '*' as tags,
  created_at as created_at
FROM
  `mailgun_events.mailgun_events` AS allunsub
WHERE
  allunsub.event = 'unsubscribed'
  AND allunsub.tag = '*'
  AND allunsub.domain = @EMAIL_DOMAIN
  AND allunsub.created_at > '2010-01-01'

Query for tag unsubscribes

Note: This query is storing each tag unsubscribe for a recipient as an individual row

WITH
  all_unsubs AS (
  SELECT
    recipient
  FROM
    `mailgun_events.mailgun_events` AS allunsub
  WHERE
    allunsub.event = 'unsubscribed'
    AND allunsub.tag = '*'
    AND allunsub.domain = @EMAIL_DOMAIN
    AND allunsub.created_at > '2010-01-01' )
SELECT
  mailgun_events.recipient as address,
  TRIM(tag) as tags,
  created_at as created_at
FROM
  `mailgun_events.mailgun_events` AS mailgun_events,
  UNNEST(SPLIT(tag, ',')) AS tag
LEFT JOIN
  all_unsubs
ON
  (mailgun_events.recipient = all_unsubs.recipient)
WHERE
  event = 'unsubscribed'
  AND domain = @EMAIL_DOMAIN
  AND created_at > '2010-01-01'
  AND all_unsubs.recipient IS null

Uploading CSVs into Mailgun

Download BQ Query results as a CSV

Import CSV into Mailgun