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 at the inbox. The subscriber that asks where is his newsletter with the promo code.

You can try to debug 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​

My favourite way to debug email sends is by using a SQL Query. Open your Query Studio and copy-paste the below code:

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.

You Should Know

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

If you don't see the send in the outcomes at all - check whether it errored out in the Send Log and confirm the subscriber status in Email Studio.

Options​

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

Change timeframe​

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

Limit to 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 single domain​

If you see problems with deliverability for a particular user, I highly recommend checking whether any other users from the same domain are also having 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 single email​

If the problem is related to just one particular 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 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 even 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.