SFMC Enhanced Send Log
Create, customise and enhance SFMC Send Log for debugging and reporting.
What is Send Log
Send Log is a table that captures relevant data about sends happening in the platform. In the case of Salesforce Marketing Cloud, it is a unique Data Extension that automatically captures the data (no need for AMPScript or SSJS to populate it).
SFMC doesn't have Send Log out of the box, but you can make one yourself. On the first screen of Data Extension creation, select
Create From Template in the
Creation Method field. You will see a picklist with multiple templates, including
SendLog (for Emails),
PushSendLog. If you do not see any of those templates - you might need to request this as a free feature on demand.
Once you create it, it will automatically populate with each attempted send originating from its Business Unit.
Send Log Data Extension Template
The Send Log template comes with six default fields:
BatchID allow you to differentiate the send if you push the same email on multiple days or campaigns.
ListID contains the ID of the list used for the send (for example, All Subscribers list).
SubID contains Subscriber ID - a unique Subscriber identifier, different from Subscriber Key. Subscriber ID is a backend system number that we cannot change.
TriggeredSendID stores the ID of a Triggered Send Definition if it was used for the specific send.
ErrorCode captures an error that stopped a specific send. I have added the list of all possible codes with an explanation at the bottom.
If your send was not delivered, but you can see it in the Send Log without any
ErrorCode, leverage Email Sends Debugging Query to learn what happened with the message.
Send Log vs _Sent System Data View
You might wonder - why use email Send Log when _Sent Data View captures the same data out of the box? There are two reasons.
1. Captured data is not precisely the same.
It's not only about the
ErrorCode field in the Send Log, and multiple additional fields in the _Sent Data View. It's also about when the data is captured.
Send Log is populated when SFMC prepares the send. That's why it can log ErrorCode if the send fails. The Data View is populated later when SFMC pushes a correctly constructed email out to the Email Service Providers.
It means that Send Log is an incredible tool to check why some emails did not go out of SFMC.
2. You can customise the Send Log
Data Views are set in stone (unless you purchase a custom one), but you can add fields to the Send Log to align it better with your business needs. Let's dive into how it works and why you should use it.
Custom Send Log
The true power of Send Log lies in the possibility to customise it. The default six columns are just beginning - you can add additional ones per your business needs. With performance in mind, I would recommend adding up to 5 custom fields.
Columns added to Send Log are unique because you can make them fill in automatically. How does it work?
When Marketing Cloud prepares the email for sending, Send Log evaluates all available personalisation strings, Sendable Data Extension fields and AMPScript variables. Each value that shares a name with Send Log column is automatically filled. Examples?
If you want to capture the recipient's Email Address in the SendLog, you can add the
EmailAddrfield, and it will be automatically filled thanks to
Capturing Subscriber Key is a bit tricky, as the personalisation string is
%%_subscriberkey%%- you cannot add a field starting with the underscore to Data Extension. There are two ways to capture this information. In most cases, you will have the
SubscriberKeyfield in the Data Extension used for sending - you can use the same column name in the Send Log. Alternatively, you can create an AMPScript variable using the personalisation string:
SET @SubscriberKey = AttributeValue('_subscriberkey'). Send Log will pick up your AMPScript variable and fill the column.
You can also use AMPScript variables for other interesting things, like capturing information from Additional Email Attributes or custom values manually configured within the email body.
In most cases, you can dry-test automatic filling by adding
%%[Output(Concat('SendLogColumnName: ', @SendLogColumnName))]%% to the bottom of the email (where
SendLogColumnName is equal to the Send Log field you want to test). This AMPScript one-liner should output
SendLogColumnName: along with the value that SFMC would add to the Send Log. If it doesn't - there might not be an appropriate variable, or it is not assigned a value.
Customised Send Log Example
Here you can see sample Customised Send Log that I like to use:
I always add the
SubscriberKey field (filled from a column of the same name in the Data Extension used for send) along with
EmailName_ fields filled in from personalisation strings.
As Send Log will show the data as it is at the moment of email preparation, I frequently extend it with customer-specific fields that are crucial for send-time data checks. For example - VIP status, Contact vs Lead status, assigned Account Executive.
All those data points can be beneficial during debugging. VIP offer sent to the standard customer? Send Log will tell you whether the Subscriber was VIP at the moment of send or it is a bug in your Journey criteria.
Another handy field that I would love to capture in the Send Log would be the name of the Data Extension used for the send. It should be possible, as there is a dedicated personalisation string:
You can add the
DataSourceName field to Send Log and
SET @DataSourceName = AttributeValue('_datasourcename') snippet to the email. However, it will work correctly only for the Send Flow.
Unfortunately, it currently does not support Journey Builder Sends. Learn more and vote for a fix here.
Enhanced Send Log
Custom Send Log is already a fantastic tool, but it's more suited for administrators with its technical field names and lack of post-send data. However, you can easily leverage it to create something perfect for your more business-oriented SFMC users.
To do it, you will need additional Data Extension and simple Automation with single Query Activity.
Enhanced Send Log Example
Create standard Data Extension with more user-friendly names and extend it with journey and engagement data points:
Enhanced Send Log SQL Query Activity
Create Automation with Query Activity that will take data from your technical Send Log and System Data Views to populate your Enhanced Send Log DE with actionable data.
sl.SubscriberKey AS SubscriberKey
, sl.SubID AS SubscriberID
, sl.EmailAddr AS EmailAddress
, sl.BusinessSpecificField1 AS BusinessSpecificField1
, sl.BusinessSpecificField2 AS BusinessSpecificField2
, sl.EmailName_ AS EmailName
, j.JourneyName AS JourneyName
, j.VersionNumber AS JourneyVersion
, s.EventDate AS SentDate
WHEN job.DeliveredTime IS NOT NULL THEN CONVERT(NVARCHAR, job.DeliveredTime)
WHEN b.EventDate IS NOT NULL THEN 'False'
WHEN o.EventDate IS NOT NULL OR c.EventDate IS NOT NULL THEN 'True'
WHEN s.EventDate > DATEADD(MINUTE, -15, GETDATE()) THEN 'Pending'
END AS Delivered
WHEN o.EventDate IS NOT NULL THEN o.EventDate
WHEN c.EventDate IS NOT NULL THEN c.EventDate
END AS FirstOpenDate
, c.EventDate AS FirstClickDate
, b.EventDate AS BounceDate
, sl.JobID AS JobID
, sl.ListID AS ListID
, sl.BatchID AS BatchID
, sl.TriggeredSendID AS TriggeredSendID
, sl.ErrorCode AS ErrorCode
FROM _Sent AS s
LEFT JOIN _Job job
ON s.JobID = job.JobID
LEFT JOIN _Open AS o
ON s.JobID = o.JobID
AND s.ListID = o.ListID
AND s.BatchID = o.BatchID
AND s.SubscriberID = o.SubscriberID
AND o.IsUnique = 1
LEFT JOIN _Click AS c
ON s.JobID = c.JobID
AND s.ListID = c.ListID
AND s.BatchID = c.BatchID
AND s.SubscriberID = c.SubscriberID
AND c.IsUnique = 1
LEFT JOIN _Bounce AS b
ON s.JobID = b.JobID
AND s.ListID = b.ListID
AND s.BatchID = b.BatchID
AND s.SubscriberID = b.SubscriberID
AND b.IsUnique = 1
LEFT JOIN TechnicalSendLog AS sl
ON s.JobID = sl.JobID
AND s.ListID = sl.ListID
AND s.BatchID = sl.BatchID
AND s.SubscriberID = sl.SubID
LEFT JOIN _JourneyActivity AS ja
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey AS j
ON ja.VersionID = j.VersionID
sl.SubscriberKey IS NOT NULL
AND CONVERT(DATE, s.EventDate) >= DATEADD(MONTH, -1, CONVERT(DATE, GETDATE()))
In the above query, we pull Journey Builder information and Sent, Delivered, Open, Click, Bounce information. We are also adding a custom logic that will provide more real Delivery and Open data using the fundamental assumption that if the recipient clicked something - the email had to be delivered and opened.
WHERE section, we filter the data to pull only updates for sends from the last month to optimise the query performance.
Schedule that Automation nightly and share the Enhanced Send Log with your users.
Send Log ErrorCode Values
List of email Send Log errorCode values from the official SFMC Documentation:
|1||Unsubscribed||The subscriber status is unsubscribed.|
|2||Held||The subscriber status is held.|
|3||Partner Unsubscribed||The subscriber partner system status is unsubscribed.|
|4||Missing Email Address||The subscriber record or the API call that initiated the send doesn't contain an email address for the subscriber.|
|5||Invalid Email Address||The subscriber email address is invalid.|
|6||Duplicate Email||The subscriber email is identical to another subscriber email in this send.|
|7||Invalid SubscriberID Provided||The subscriber ID is invalid.|
|8||Missing Subscriber ID||The subscriber key and subscriber ID values are empty or null.|
|9||Missing Owner ID||The business unit ID for the child client account is missing for this enterprise account.|
|10||Missing Required Attributes||The subscriber is missing required attribute values.|
|11||No All Subscribers List Entry||The subscriber isn't on the AllSubscribers list for the account.|
|12||Invalid Owner ID Provided||The child client account isn't a member of the enterprise account.|
|13||Subscriber Key Mismatch||The subscriber key in the system doesn't match the subscriber key provided.|
|14||Email Address Mismatch||The email address in the system doesn't match the email address provided for this subscriber.|
|15||Unspecified Error||The subscriber didn't pass validation. Contact your Marketing Cloud account representative for more information.|
|16||Invalid Attribute Value||The value specified for a subscriber attribute doesn't match the attribute's data type.|
|17||Attribute Value Max Length Err||The value specified for a subscriber attribute is over the maximum length allowed for the attribute.|
|18||Invalid Attribute Value Count||The number of subscriber attribute values doesn't match the expected count.|
|19||Missing Required Fields||Required data extension field values are missing for the subscriber.|
|20||Invalid Field Value||The value specified for a data extension field doesn't match the field type.|
|21||Duplicate Data Extension Row||A row in the data extension was identified as a duplicate. The triggered send data extension doesn't allow duplicate rows.|
|22||Data Extension Insert Failed||The send failed to insert a row into the triggered send data extension.|
|23||Domain Exclusion||A domain exclusion list excluded the subscriber.|
|24||List Detective Exclusion||List Detective excluded the subscriber.|
|25||Subscriber Blacked Out||The subscriber is ineligible to receive the email. Marketing Cloud reschedules the triggered send.|
|26||Build Email Error||An error occurred when attempting to build an email for the subscriber.|
|27||Suppression List Exclusion||The subscriber is on a suppression list and is ineligible to receive the email.|
|28||Opt Out List Exclusion||The subscriber is on an opt-out list and ineligible to receive the email.|
|29||Missing Subscriber Key Value||The subscriber key value is null or empty.|
|30||Subscriber Key Too Long||The subscriber key value exceeds the character limit.|
|37||Queued Transaction Deleted||The queued email subscriber was deleted from the Triggered Send Queue using Clear Queue|
|39||Subscriber Queue Cleared||The queued SMS subscriber was deleted from the Triggered Send Queue.|
|40||Subscriber Deleted||Subscriber or contact is deleted and can't be added to Marketing Cloud.|
|138||Exceeded Expiration Policy||Paused triggered sends that were later resumed without clearing the queue sent old messages that were no longer relevant.|