Marketing Cloud (MC) Connect - Syncing Email Unsubscribes With Sales Cloud (SC)

Question

The Marketing Cloud Connector only sync's email subscription data back to Sales Cloud (SC) under specific circumstances defined here

How can we ensure that these remained updated correctly if a lead or contact is unsubscribed in either system with their opt-out status reflected in the other system?

Answer 1

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"