Loading

Marketing Cloud Tracking Extract for Bounce data has line breaks

게시 일자: Dec 17, 2024
상세 설명
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.  
솔루션
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.

Knowledge 기사 번호

000380266

 
로드 중
Salesforce Help | Article