I'm trying to get information about bounce reason from Marketing Cloud to integrated Sales Cloud (I'd like to have this information on Individual Email Result record). Is there any way to do this without API? I tried to do it with _bounce Data View and then use AMPscript to update IER records but I'm not able to link a record from my output Data Extension based on _bounce with IER record...
I found this quite an interesting question. I think I found an approach that you could try:
- The Individual Email Result object has an external identifier field called MergeId that you can use as an identifier
- This MergeId is a concatenation of EmailSend Id + ContactId/LeadId (the 18 digit variant)
Based on this you can do the following thing:
- Sync the Email Send object (ET4AE5__SENDDEFINITION__C) to Marketing Cloud and include JobId (et4ae5__JobId__c) and for good measure EmailName (et4ae5__EmailName__c)
- Create a data extension with MergeId (Text,50) as PrimaryKey and the bounce detail field(s) you need
- Create a join query that uses the Job Id to pull together the _bounce data view and the Email Send to create a concatenation of Email Send SF Id and the Subscriberkey
It should look something likes this (you at least have to adjust for the bounce field names and you should include a WHERE condition (e.g. bounce events from the last X days):
SELECT b.[insert the needed bounce detail field name], CONCAT(e.Id,b.Subscriberkey) as MergeId, b.SubscriberKey FROM _bounce b INNER JOIN ET4AE5__SENDDEFINITION__C e ON b.JobId = et4ae5__JobId__c WHERE ...
The resulting data extension can then be used in two ways:
- Export as CSV to import to Salesforce for larger data volumes (hard to automate without coding or SFTP to SF automation tools)
- If you have Journey Builder and your data volumes tend to be low (<10.000 per run) you can also use this data extension as an entry source for a Journey that finds and updates the Individual Email Result Sales Cloud Object. Run this nightly at a time that doesn't impact your normal send operations.
Both methods will use the MergeId to identify the correct Email Result record in Salesforce.
This approach is probably not exactly best practice but it should work.
P.S.: If any of the SQL experts find syntax errors in my SQL example please feel free to correct (SQL isn't my specialty ...)