Loading

Marketing Cloud Tracking Extract for Bounce data has line breaks

Date de publication: Dec 17, 2024
Description
Bounce data that is fed back from various IPSs has the potential to have a wide variety of characters included within it.  Due to this some exporting options/data in options may be affected due to the inclusion of unexpected delimiter characters that throw off overall file formatting.  This can be noticed in the Standard Tracking Extract output as it causes unexpected line feeds/breaks in data.

This is specifically caused by the SMTP BounceReason field that is returned in _bounce/stats.bounce data.  
Résolution
For Standard Tracking Extracts, this is expected behavior due to the way that they are designed.  As a workaround the data can be passed through a Query Activity to a Data Extension from the _bounce data view, set up a Data Extension Extract to pull the data out, and finally add a File Transfer activity to transfer the data to their SFTP. 

Below is an example Query Activity that would pull out unexpected line feeds (char(10) and char(13)) and remove them: 
SELECT AccountID, 
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
IsUnique,
Domain,
BounceCategoryID,
BounceCategory,
BounceSubcategory,
BounceType,
CASE WHEN SMTPBounceReason LIKE '5.1.1 Undelivered Mail Returned to Sender%' THEN '5.1.1 Undelivered Mail Returned to Sender'
ELSE Substring(Replace(Replace(SMTPBounceReason, Char(10), ''), Char(13), ''), 1, 4000)
END AS SMTPBounceReason,
SMTPMessage,
SMTPCode,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _bounce WITH (nolock)
WHERE SMTPBounceReason LIKE '%' + Char(13) + '%'
OR SMTPBounceReason LIKE '%' + Char(10) + '%'
Below is an example flow of how the Automation Structure would look as described at the beginning of this article: 
  1. SQL Query: DATAVIEW_BOUNCE
  2. DataExtract: DATAVIEW_BOUNCE
  3. FileTransfer: DATAVIEW_BOUNCE


Do note that this does not solve for all problematic situations that may arise with SMTP Bounce Reason data but is a common piece of data that causes problems.   Further customization may be needed sanitize this output as the characters that are being searched for above are by no means comprehensive.

Numéro d’article de la base de connaissances

000380266

 
Chargement
Salesforce Help | Article