[SalesForce] Marketing Cloud (MC) Connect – Syncing Email Unsubscribes With Sales Cloud (SC)

SC update MC to unsubscribe where HasOptedOutOfEmail = True

Description:

A process to synchronise Salesforce Leads & Contacts where they are subscribed in MC but have been unsubscribed in SC

Requires building an automation with the following steps:

1. create Data Extensions

   A. Create a data extension (QueryTargetDataExtension) to collect the resulting from the query below.

   • SubscriberKey :: Text :: 18 :: Nullable,
   • EmailAddress :: EmailAddress :: 254 :: Nullable,
   • Status :: Text :: 50 :: Nullable,
   • HasOptedOutOfEmail :: Boolean :: 50 :: Nullable

   B. Create a data extension (LoggingDataExtension) to log the results, including status and timestamp

   • SubscriberKey :: Text :: 18 :: Nullable,
   • EmailAddress :: EmailAddress :: 254 :: Nullable,
   • Status :: Text :: 50 :: Nullable,
   • HasOptedOutOfEmail :: Boolean :: 50 :: Nullable
   • Timestamp :: Date :: Nullable

2. SQL query

The query finds all leads & contacts where they are subscribed in MC but have been unsubscribed in SC where HasOptedOutOfEmail = 'TRUE'

SELECT sub.SubscriberKey, sub.EmailAddress, sub.Status, sfob.HasOptedOutOfEmail
FROM _Subscribers sub
LEFT JOIN
    (SELECT con.Id, con.Email, con.HasOptedOutOfEmail FROM Contact_Salesforce con
    UNION
    SELECT lead.Id, lead.Email, lead.HasOptedOutOfEmail  FROM Lead_Salesforce lead
    ) sfob ON sub.SubscriberKey = sfob.Id
WHERE sub.Status NOT IN ('unsubscribed') AND sfob.HasOptedOutOfEmail = 'TRUE'

3. SSJS Script:

This script retrieves the data from the targeted data extension, cycles through each row, unsubscribes from MC and creates a log on the on the LoggingDataExtensionId

<script runat="server" type="text/javascript">
Platform.Load("core", "1");
var unsubDE = DataExtension.Init("QueryTargetDataExtensionId");
var data = unsubDE.Rows.Retrieve();
var logging = [];
if(data.length > 0) {
        for (var i = 0; i < data.length; i++)
        {
            var SubId = data[i].SubscriberKey;
                try {   
                    var subObj = Subscriber.Init(SubId);
                    var status = subObj.Unsubscribe();
                } 
                catch (e) {
                    var status = "error";
                    var output = logging.push({SubscriberKey: SubId, EmailAddress: data[i].EmailAddress, Status: 'Error Processing Optout', HasOptedOutOfEmail: data[i].HasOptedOutOfEmail, Timestamp: Date.now()});
                }
            if (status != "error") {      
            var output = logging.push({SubscriberKey: SubId, EmailAddress: data[i].EmailAddress, Status: 'Unsubscribe Complete', HasOptedOutOfEmail: data[i].HasOptedOutOfEmail, Timestamp: Date.now()});
            }
        };
        var loggingDE = DataExtension.Init("LoggingDataExtensionId");
        loggingDE.Rows.Add(logging);
    };
</script>

*Key:*
QueryTargetDataExtensionId = "Place the ID of the QueryTargetDataExtensionId in here"
LoggingDataExtensionId = "Place the ID of the LoggingDataExtensionId in here"

MC update SC with unsubscribe status where HasOptedOutOfEmail = False

Description:

A process to synchronise Salesforce Leads & Contacts where they are unsubscribed in MC but subscribed in SC

Requires building an automation with the following steps:

1. create Data Extensions

   A. Create a data extension (QueryTargetDataExtension) to collect the resulting from the query below.

   • SubscriberKey :: Text :: 18 :: Primary Key,
   • EmailAddress :: EmailAddress :: 254 :: Nullable,
   • Status :: Text :: 50 :: Nullable,
   • HasOptedOutOfEmail :: Boolean :: 50 :: nullable

   B. Create a data extension (LoggingDataExtension) to log the results, including status and timestamp

   • SubscriberKey :: Text :: 18 :: Nullable,
   • EmailAddress :: EmailAddress :: 254 :: Nullable,
   • Status :: Text :: 50 :: Nullable,
   • HasOptedOutOfEmail :: Boolean :: 50 :: nullable
   • Timestamp :: Date :: Nullable

2. SQL query

The query finds all leads & contacts where they are unsubscribed in MC but have been subscribed in SC where HasOptedOutOfEmail = 'FALSE'

SELECT sub.SubscriberKey, sub.EmailAddress, sub.Status, sfob.HasOptedOutOfEmail
FROM _Subscribers sub
LEFT JOIN
    (SELECT con.Id, con.Email, con.HasOptedOutOfEmail FROM Contact_Salesforce con
    UNION
    SELECT lead.Id, lead.Email, lead.HasOptedOutOfEmail  FROM Lead_Salesforce lead
    ) sfob ON sub.SubscriberKey = sfob.Id
WHERE sub.Status IN ('unsubscribed') AND sfob.HasOptedOutOfEmail = 'FALSE'

3. AMPscript content Block:

This AMPscript retrieves the data from the targeted data extension, cycles through each row, updates the related record in SC with HasOptedOutOfEmail = 'True' and creates a log on the LoggingDataExtensionId

    %%[
var @rows, @row, @rowCount, @subscriptionStatus, @I
set @subscriptionStatus = False
set @rows = LookupRows("MCtoSC_unsubscribeSync","HasOptedOutOfEmail", @subscriptionStatus)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
  for @i=1 to @rowCount do
    var @Id, @emailAddress, @status, @updateSF, @objType
    set @row = row(@rows, @i) /* get row based on counter */
    set @Id = field(@row,"SubscriberKey")
    set @emailAddress = field(@row,"EmailAddress")
    set @status = field(@row,"Status")
    set @objType = field(@row,"ObjType")
    set @updateSF = UpdateSingleSalesforceObject(@objType, @Id,
                                                "HasOptedOutOfEmail", "true"
                                                )
        if @updateSF == 1 then
            InsertDE("MCtoSC_unsubscribeSyncLogging",
                    "SubscriberKey", @Id,
                    "EmailAddress", @emailAddress,
                    "Status", "unsubscribed",
                    "TimeStamp", now()
                    )
        else 
            InsertDE("MCtoSC_unsubscribeSyncLogging",
                    "SubscriberKey", @Id,
                    "EmailAddress", @emailAddress,
                    "Status", "error",
                    "TimeStamp", now()
                    )
        endif
  next @i
endif
]%%


*Key:*
QueryTargetDataExtensionName = "Place the Name of the QueryTargetDataExtensionId in here"
LoggingDataExtensionName = "Place the Name of the LoggingDataExtensionId in here"

4. SSJS script

A SSJS script is required to execute the AMPscript within the automation. This is because AMPscript cannot be directly added to an automation

<script runat="server">
    Platform.Load("Core","1.1.1");
        var content = Platform.Function.ContentBlockByKey('contentkey');
        var stream = Platform.Function.TreatAsContent(content);
</script>

*Key:*
contentkey = "Place the ID key of the AMPscript content block in here"