Skip to main content

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), SMSSendLog and 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:

NameTypeLengthPrimary KeyNullable
JobIDNumericYes
ListIDNumericYes
BatchIDNumericYes
SubIDNumericYes
TriggeredSendIDString36Yes
ErrorCodeNumericYes

JobID and 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.

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?

  1. If you want to capture the recipient's Email Address in the SendLog, you can add the EmailAddr field, and it will be automatically filled thanks to %%emailaddr%% personalisation string.

  2. 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 SubscriberKey field 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.

  3. 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.

You Should Know

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:

NameTypeLengthPrimary KeyNullable
JobIDNumericYes
ListIDNumericYes
BatchIDNumericYes
SubIDNumericYes
TriggeredSendIDString36Yes
ErrorCodeNumericYes
SubscriberKeyString18Yes
EmailAddrEmail254Yes
EmailName_String150Yes
BusinessSpecificField1Yes
BusinessSpecificField2Yes

I always add the SubscriberKey field (filled from a column of the same name in the Data Extension used for send) along with EmailAddr and 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.

You Should Know

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: %%_DataSourceName%%. But...

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:

NameTypeLengthPrimary KeyNullable
SubscriberKeyString18YesNo
SubscriberIDNumericYes
EmailAddressEmail254Yes
BusinessSpecificField1Yes
BusinessSpecificField2Yes
EmailNameString150Yes
JourneyNameString100Yes
JourneyVersionString100Yes
SentDateDateYes
DeliveredString20Yes
FirstOpenDateDateYes
FirstClickDateDateYes
BounceDateDateYes
JobIDNumericYes
ListIDNumericYes
BatchIDNumericYes
TriggeredSendIDString36Yes
ErrorCodeNumericYes

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.

SELECT      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    , CASE        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'        ELSE 'True'      END                       AS Delivered    , CASE        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 ErrorCodeFROM _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.VersionIDWHERE    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.

In the 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:

Error CodeNameDescription
1UnsubscribedThe subscriber status is unsubscribed.
2HeldThe subscriber status is held.
3Partner UnsubscribedThe subscriber partner system status is unsubscribed.
4Missing Email AddressThe subscriber record or the API call that initiated the send doesn't contain an email address for the subscriber.
5Invalid Email AddressThe subscriber email address is invalid.
6Duplicate EmailThe subscriber email is identical to another subscriber email in this send.
7Invalid SubscriberID ProvidedThe subscriber ID is invalid.
8Missing Subscriber IDThe subscriber key and subscriber ID values are empty or null.
9Missing Owner IDThe business unit ID for the child client account is missing for this enterprise account.
10Missing Required AttributesThe subscriber is missing required attribute values.
11No All Subscribers List EntryThe subscriber isn't on the AllSubscribers list for the account.
12Invalid Owner ID ProvidedThe child client account isn't a member of the enterprise account.
13Subscriber Key MismatchThe subscriber key in the system doesn't match the subscriber key provided.
14Email Address MismatchThe email address in the system doesn't match the email address provided for this subscriber.
15Unspecified ErrorThe subscriber didn't pass validation. Contact your Marketing Cloud account representative for more information.
16Invalid Attribute ValueThe value specified for a subscriber attribute doesn't match the attribute's data type.
17Attribute Value Max Length ErrThe value specified for a subscriber attribute is over the maximum length allowed for the attribute.
18Invalid Attribute Value CountThe number of subscriber attribute values doesn't match the expected count.
19Missing Required FieldsRequired data extension field values are missing for the subscriber.
20Invalid Field ValueThe value specified for a data extension field doesn't match the field type.
21Duplicate Data Extension RowA row in the data extension was identified as a duplicate. The triggered send data extension doesn't allow duplicate rows.
22Data Extension Insert FailedThe send failed to insert a row into the triggered send data extension.
23Domain ExclusionA domain exclusion list excluded the subscriber.
24List Detective ExclusionList Detective excluded the subscriber.
25Subscriber Blacked OutThe subscriber is ineligible to receive the email. Marketing Cloud reschedules the triggered send.
26Build Email ErrorAn error occurred when attempting to build an email for the subscriber.
27Suppression List ExclusionThe subscriber is on a suppression list and is ineligible to receive the email.
28Opt Out List ExclusionThe subscriber is on an opt-out list and ineligible to receive the email.
29Missing Subscriber Key ValueThe subscriber key value is null or empty.
30Subscriber Key Too LongThe subscriber key value exceeds the character limit.
37Queued Transaction DeletedThe queued email subscriber was deleted from the Triggered Send Queue using Clear Queue
39Subscriber Queue ClearedThe queued SMS subscriber was deleted from the Triggered Send Queue.
40Subscriber DeletedSubscriber or contact is deleted and can't be added to Marketing Cloud.
138Exceeded Expiration PolicyPaused triggered sends that were later resumed without clearing the queue sent old messages that were no longer relevant.