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:
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
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'
- 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:
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
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'
- 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"
- 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"
Best Answer
As far as I know, the only way to log a "correct" unsubscription (= log an Event in Unsubscribe Table + set Status in All Subscribers) in Marketing Cloud is by using the LogUnsubEvent method.
Checking the Box "hasOptedOutOfEmail" (Label: Email Opt Out) on Lead or Contact alone does not leverage LogUnsubEvent. There should be no visible "cross cloud" effect by simply checking that box in Sales Cloud on the Contact or Lead. I just reconfirmed that in a test, as this would indeed be surprising and somewhat "new".
Clicking the custom button "Marketing Cloud Unsubscribe" on Lead / Contact Page Layout, which is part of the MC Connect setup instructions DOES leverage it. In my interpretation, the sole reason why this button exists is the point above - The checkbox alone doesn't cut it.
Hope this helps :)