Debugging email sends with SQL | Cleon

Something went sideways. A Send was supposed to reach 50,000 subscribers and only 32,000 saw it. Or it was supposed to suppress competitors and a competitor address opened it. Or the report shows a delivered count that doesn't match the audience DE row count. The diagnostic flow is the same every time: walk the funnel from audience to delivered, find where the row count drops, and read the DE that owns that drop.

This page is the five-query playbook. Run them in order. The one that returns "off by 18,000" is the one that owns the bug.

The funnel

[ Audience DE row count ]
         ↓ exclusions in the Send Definition
[ Eligible after suppressions / unsubs / bounce-out ]
         ↓ Send Activity execution
[ _Sent rows for the JobID ]
         ↓ delivery to MTA
[ _Sent minus _Bounce ]

Each arrow is a place where rows can disappear. The diagnostic queries below check each level.

Step 1 — Audience DE row count

Start at the source. How many rows does the audience DE actually contain at the moment the Send was triggered?

SELECT
  COUNT(*)                AS TotalRows,
  COUNT(DISTINCT SubscriberKey) AS UniqueSubs,
  COUNT(CASE WHEN EmailAddress IS NULL OR EmailAddress = '' THEN 1 END) AS MissingEmail
FROM de_send_audience_<send_name>;

Three numbers, one query:

TotalRows: what the Send Activity sees as input.
UniqueSubs: if this is lower than TotalRows, the audience has duplicates and the Send Definition's "deduplicate by SubscriberKey" setting decides whether you sent twice or once.
MissingEmail: subscribers in the audience but unreachable. They're "in the audience" for reporting but never make it past the Send.

If TotalRows is lower than expected, the bug is upstream of the Send — in the audience-build SQL Activity, not in the Send itself. Stop here and audit the audience DE's rebuild logic.

Step 2 — Suppression overlap

How many of those audience rows would have been excluded by the Send Definition's exclusion lists, suppression DEs, or All Subscribers status?

-- Replace 'master_suppression' with your actual suppression DE name.
-- Add additional LEFT JOINs for each exclusion list configured on
-- the Send Definition.
SELECT
  COUNT(*)                                   AS WouldSend,
  COUNT(s.SubscriberKey)                     AS ExcludedBySuppression,
  COUNT(*) - COUNT(s.SubscriberKey)          AS NetEligible
FROM de_send_audience_<send_name> a
LEFT JOIN master_suppression s
  ON a.SubscriberKey = s.SubscriberKey;

If ExcludedBySuppression jumped versus the previous send (compare against de_log_send_runs), an addition to the suppression list is the cause. Check who edited it and when.

Step 3 — `_Sent` row count for the JobID

Compare what the Send Activity actually emitted against what step 2 said was eligible.

-- Replace 12345 with the JobID from the Send Definition's run history
SELECT
  COUNT(*)                       AS SentRows,
  COUNT(DISTINCT SubscriberKey)  AS UniqueSubsSent,
  MIN(EventDate)                 AS FirstEventAt,
  MAX(EventDate)                 AS LastEventAt
FROM _Sent
WHERE JobID = 12345;

Three failure shapes here:

SentRows is lower than NetEligible from step 2: the Send was throttled, paused, or the IP reputation triggered a delivery hold. Cross-reference with the Send Definition's run log timestamp range against LastEventAt — if LastEventAt is hours after the Send's recorded end, the IP throttling is the cause.
SentRows is higher than NetEligible: an Append-mode audience-build re-ran without truncating, or the Send Definition is firing on an old + new audience union. Audit the audience DE's Activity target action.
SentRows = 0: the Send didn't execute against the JobID. Verify the JobID is correct and the Send Definition is enabled.

Step 4 — Bounce + error breakdown

Of the _Sent rows, how many actually delivered? _Sent records the attempt, not the delivery.

SELECT
  s.JobID,
  COUNT(*)                                   AS Attempted,
  COUNT(b.SubscriberKey)                     AS Bounced,
  COUNT(*) - COUNT(b.SubscriberKey)          AS Delivered,
  CAST(COUNT(b.SubscriberKey) * 100.0 / COUNT(*) AS DECIMAL(5,2))
                                             AS BouncePct
FROM _Sent s
LEFT JOIN _Bounce b
  ON s.JobID = b.JobID
  AND s.SubscriberKey = b.SubscriberKey
WHERE s.JobID = 12345
GROUP BY s.JobID;

A BouncePct above ~3% is a deliverability red flag — investigate the audience freshness (subscribers from imports older than 90 days are typically dirty) and the IP warm-up state.

Step 5 — Reconciliation snapshot

Once the bug is found, write the funnel snapshot into a de_log_send_diagnostics so the next time something looks off, you have historical baselines to compare against.

INSERT INTO de_log_send_diagnostics
SELECT
  GETDATE()                       AS DiagnosticAt,
  '<send_name>'                   AS SendName,
  12345                           AS JobID,
  (SELECT COUNT(*) FROM de_send_audience_<send_name>)        AS AudienceTotal,
  (SELECT COUNT(*) FROM _Sent WHERE JobID = 12345)           AS SentTotal,
  (SELECT COUNT(*) FROM _Bounce WHERE JobID = 12345)         AS BounceTotal,
  (SELECT COUNT(DISTINCT SubscriberKey) FROM _Open WHERE JobID = 12345) AS UniqueOpens;

Run this after every Send (manually or as part of the Automation that owns the Send). Six months from now when a stakeholder asks "is this Send under-performing", you have the data to answer "compared to which baseline".

Common causes ranked by frequency

| Cause | How to spot | Where | |---|---|---| | Audience DE didn't rebuild this run | Step 1 returns 0 or stale row count | Audit the audience-build Activity logs | | Suppression list grew | Step 2's ExcludedBySuppression jumped vs prior runs | Compare against de_log_send_runs baseline | | Send Definition disabled / wrong JobID | Step 3 returns 0 rows | Check Send Definition status + Activity output | | IP reputation throttling | Step 3's LastEventAt runs hours past the Send window | Marketing Cloud Setup → Email Studio → Sender Authentication | | Bounce rate spike | Step 4's BouncePct > 3% | Audit audience freshness; check for recent import | | Audience contained NULL emails | Step 1's MissingEmail > 0 | Add WHERE EmailAddress IS NOT NULL to the audience-build SQL | | Update-mode Activity missing PK | Audience DE has duplicates not deduped | Verify destination DE has SubscriberKey as PK |

FROM — why you snapshot SDVs (_Sent, _Bounce, _Open) before reading them in production
JOIN — anti-join pattern for the suppression overlap query
Aggregate functions — COUNT patterns used throughout
INSERT INTO — writing the diagnostic snapshot
MC SQL gotchas — see #6 (SDV rotation), #9 (SubscriberKey casting if joining external data)
Style Guide — the discipline checklist that prevents this debug session from happening twice