Skip to main content

SFMC SQL Debugging Email Sends

Make finding problems with email delivery in Salesforce Marketing Cloud great again. SQL with the Query Studio to the rescue.

Problem with straightforward Email Send analysis

Deliverability issues, unfortunately, are a standard both during the Salesforce Marketing Cloud implementation phase as well as after the go-live.

The test you sent to yourself during the Marketing Cloud implementation that never arrived in the inbox. The subscriber that asks where is his newsletter with the promo code.

You can try debugging it with Journey Builder data or Reports, but in most cases, those solutions provide either not enough or way too much for the specific check you need to make.

Solution

SELECT
s.SubscriberKey AS SubscriberKey
, sub.EmailAddress AS EmailAddress
, sub.Status AS SubscriptionStatus
, j.JourneyName AS JourneyName
, j.VersionNumber AS JourneyVersion
, job.EmailName AS EmailName
, s.EventDate AS SentDate
, job.DeliveredTime AS DeliveryDate
, o.EventDate AS OpenDate
, c.EventDate AS ClickDate
, b.EventDate AS BounceDate
, b.BounceCategory AS BounceCategory
, b.BounceSubcategory AS BounceSubcategory
, b.SMTPBounceReason AS BounceReason
, u.EventDate AS UnsubscribeDate
FROM _Sent AS s
LEFT JOIN _Job AS job
ON job.JobID = s.JobID
LEFT JOIN _Open AS o
ON o.JobID = s.JobID
AND o.ListID = s.ListID
AND o.BatchID = s.BatchID
AND o.SubscriberID = s.SubscriberID
AND o.IsUnique = 1
LEFT JOIN _Click AS c
ON s.JobID = c.JobID
AND c.ListID = s.ListID
AND c.BatchID = s.BatchID
AND c.SubscriberID = s.SubscriberID
AND c.IsUnique = 1
LEFT JOIN _Bounce AS b
ON s.JobID = b.JobID
AND b.ListID = s.ListID
AND b.BatchID = s.BatchID
AND b.SubscriberID = s.SubscriberID
AND b.IsUnique = 1
LEFT JOIN _Unsubscribe AS u
ON s.JobID = u.JobID
AND u.ListID = s.ListID
AND u.BatchID = s.BatchID
AND u.SubscriberID = s.SubscriberID
AND u.IsUnique = 1
LEFT JOIN _JourneyActivity AS ja
ON ja.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID
LEFT JOIN _Journey AS j
ON j.VersionID = ja.VersionID
LEFT JOIN _Subscribers AS sub
ON sub.SubscriberKey = s.SubscriberKey
WHERE s.EventDate >= DATEADD(DAY, -1, GETDATE())

Within a few seconds, you will see information about sends from the last day, with all the details that might be useful for debugging, like STMP Bounce Reason, Subscriber Status, Unsubscribe Date, etc.

Wondering what to do with that data? Check out Email Send Debugging Step-by-Step section below.

You Should Know

If you are running it from the child Business Unit and do not see EmailAddress, BounceCount and Status, add the Ent. prefix to _Subscribers Data View name in the last JOIN. It will pull the data from _Subscribers of the parent Business Unit.

Options

You can even adapt the above query to better suit your needs.

Change timeframe

My default is checking the sends from the last day. You can change it by modifying the DATEADD function.

Limit to a single subscriber

If you are looking for a problem related to only one specific subscriber, you can add s.SubscriberKey = 'SubscriberKeyOfThatContact' or sub.EmailAddress = '[email protected]' to the WHERE statement. Or replace the current one not to be limited by the timeframe.

Limit to a single domain

If you see problems with deliverability for a particular user, I highly recommend checking whether other users from the same domain also have such issues. Maybe it is related to corporate server policy? Add sub.Domain = 'Recipients.Domain' to the WHERE statement. Or replace the current one not to be limited by the timeframe.

Limit to a single email

If the problem is related to just one email asset, you can limit your outcomes by adding job.EmailName = 'ProblematicEmailName' to the WHERE statement. Or replace the current one not to be limited by the timeframe.

Limit to a single Journey

If you want to focus on a single Journey, you can limit your outcomes by adding j.JourneyName = 'ProblematicJourneyName' to the WHERE statement. Or replace the current one not to be limited by the timeframe. You can also pinpoint it further by filtering on j.JourneyVersion.

Limit to single engagement type

If you are interested only in bounces and do not need any other sends, add b.IsUnique = 1 to the WHERE statement.

You can use the same approach to output only opens or clicks by changing the prefix from b to the alias representing the needed System Data View.

Email Send Debugging Step-by-Step

Now you know the SFMC Email Send Debugging query in and out. But how to work with the outcomes? And what are the next steps?

As they say, with great power comes great complexity. And that's definitely true for finding the root cause of the undelivered email. The deliverability by itself is already a beautiful mess, and then there are various features and options you can use directly in Salesforce Marketing Cloud. Let's dive in.

You Should Know

You don't have to follow the steps below in the same order as presented. You can mix and match them, as long as you leave the Support Ticket creation for when you tried all other options.

I personally debug in that order because, while I wait for the Query Studio to return the output from query, I can check the following steps in parallel.

1. Query System Data Views

I like to start with the query described in this guide - filtered down to problematic subscriber - to get a high-level picture of the situation. Free Salesforce Query Studio is the best place to execute that little exploratory analysis.

Once you see the outcome, there are few possibilities:

  • There is no data for that subscriber/send » It's time go to to the next step.
  • BounceDate is filled » Either the email is invalid (hence the bounce), or the recipient's inbox Admin blocked the email (they sometimes use fake bounces to fight spam - especially in enterprise B2B and public markets).
  • OpenDate or ClickDate is filled » It's time to compare their timestamps with DeliveryDate. If those are within a single second and there are no later subsequent opens/clicks, the email has been checked by an antispam bot and flagged for no delivery to the final recipient (again, B2B and Public). If the timestamps are spread out (especially engagement ones from the DeliveryDate), the email got delivered to the right person, and they just forgot or lost it.
  • DeliveryDate is filled » If there is no Bounce, Open or Click timestamps, but Delivery is filled in, the email should be in the subscriber's Spam folder or Promotions tab.
  • DeliveryDate is not filled » It's time go to to the next step.

2. Check Send Log

If System Data Views don't have the data or the DeliveryDate is not filled, the email hasn't reached the recipient's inbox. Time to investigate the Send Log.

Check whether an ErrorCode column value is filled for the send and subscriber you are debugging.

  • There is no data for that subscriber/send » It's time go to to the next step.
  • ErrorCode is filled » Check the Error Code value meaning to find the root cause.
  • ErrorCode is not filled » The email was sent but not delivered to the recipient - you have deliverability issues. Check your configuration (From Address Management in SFMC + SPF/DKIM/DMARC in DNS) and deliverability metrics (Salesforce offers Reputation Audit Test and Sender Rating as features-on-demand).

3. Check All Subscribers List

If there is no trace of the send in System Data Views and Send Log, it means that Marketing Cloud was not able to attempt sending. There are few possible reasons for that. Let's start with potential subscriber issues by searching for problematic Contact in Email Studio All Subscribers.

  • There is no subscriber » The most probable reason is subscriber block by SFMC List Detective that protects your lists from nasty emails. What frequently trips people is that it scrubs various group addresses like [email protected], [email protected] and more.
  • Subscriber Status is Unsubscribed or Held » Contact is not eligible for receiving the email. Should they want to get it - push them through the official subscription process to stay compliant.

If you are sending through Journey Builder and in the Journey configuration selected to use Email Address from the Entry Source instead of Contacts, be sure to also check whether the Email Address field in the Data Extension selected for Entry stores correct email for that subscriber.

4. Check Personalised Email Preview

If everything was fine with the Subscriber, another option is an issue with the Email Asset. To check that, go to Content Builder, find the email, go to Preview and Test and select the subscriber in the same context as the one used for sending (for example, in the sendable Data Extension used as an Entry to the Journey). Check also a few other subscribers from the same place.

  • Email Preview errors out for all Subscribers » There is an issue with your email (probably with some programmatic content) that errored out during send.
  • Email Preview errors out only for that Subscriber » There is an issue with the personalisation data of that subscriber that clashes with your programmatic content.

5. Check Suppressions and Exclusions

If there are no errors within the Preview, the next potential culprit can be Exclusion or Suppression. There are quite a lot of them, and not all will be relevant for you, depending on the SFMC configuration and method of sending, so be sure to check applicable ones from the Exclusion List, Exclusion Script, Suppression List, Auto-Suppression List.

6. Create Support Ticket

If, after all those checks, there is still no answer to the root cause - create a Support Ticket. Sometimes, it can be a master unsubscribe, server-related queue build-up or a bug on the Marketing Cloud backend.

Email Send Debugging Flowchart

Prefer visualisation for complex process steps? Got you covered: