markus.codes

Today I had a call with Salesforce MVP Eliot Harper, who kindly pointed out, that the email change process I proposed could be extended to also handle a status reset for bounced and held subscribers. As the email address was changed this cloud be a clever thing to do, as the held subscriber can possibly be reached again. So thats what I’m going to show you in this post.

For all of you who don’t know which process I’m talking about, you can find the post here: How to sync email address changes from Sales/Service Cloud to All Subscribers list in Salesforce Marketing Cloud?

SQL Query Activity

This is the only activity that needs to be adjusted. Instead of preserving the status as it is, we set it to ‘Active’ if it currently set to ‘Held’ or ‘Bounced’. If you are familiar with the knowledge article “Change Subscriber status from ‘Held’ to ‘Active’”, you might wonder how this should be possible without additional steps that temporarily set the status to ‘Unsubscribed’. After reading this article I was wondering too, as I remebered already having perfomed such updates. Therefore I tried it out just to make sure nothing has changed since I last tried and it worked without a problem - both for manual imports in Email Studio and import activities in Automation Studio. If you happen to know a case/scenario where this doesn’t work, please let me know via email!

So this is our enhanced version of the SQL query activity:

SELECT
    sfdc.Id as SubscriberKey,
    sfdc.Email as EmailAddress,
    CASE
    	WHEN allsub.Status = 'held' THEN 'active'
    	WHEN allsub.Status = 'bounced' THEN 'active'
    	ELSE allsub.Status
    END as Status
FROM
    Contact_Salesforce as sfdc
RIGHT JOIN
    _subscribers as allsub
ON sfdc.Id = allsub.SubscriberKey
WHERE sfdc.Email != allsub.EmailAddress