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
There are various other ways how Contacts are created. MC Connect synchronization is likely the most basic one to describe, because it is completely standardized. Still, the logics around it can get complex.
So, let's focus on MC Connect:
a) Data Stream
Synchronizing ...
...creates contacts at the time of synchronization, in case the respective record's 18-digit SF ID does not yet exist on All Contacts.
There are NO cross checks across objects. If the same person exists as lead and contact, that consumes two billable contacts.
Hence the recommendation here: https://help.salesforce.com/s/articleView?id=sf.icx_b2c_crosscloudengagement_modeling_considerations.htm&type=5 to generally decide for one of those two objects in cross cloud scenarios, not both.
b) Journey Injection
If you do not synchronize via data stream, contact creation can happen through Journey Builder Salesforce Data events.
Injection into a journey creates the contact at that point in time. Which is consistent with the fact that only "persons" (user, leads, contacts) can be injected into the journey.
==
c) Special mention:
Note how Account is NOT among these three objects. You can get around this timing logic with Person Accounts. By synching accounts, but 0 contacts, and pulling data from the account object, you can postpone contact creation to occur later. Still, you should not use Account ID (001xxxxxxxxxxxxxxx) as the Id denoting the person, as this won't be consistent with how Journey Builder works. You need to use PersonContactId (003xxxxxxxxxxxxxxx) off the Account object.
==
Filtering the synch
As established above, in the Connector the contact is created at time of synch / injection, the way to prevent this is to not synch / inject.
That all sounds more or less logical, but it's not a general rule but specific to the MC Connect Synch.
Other ways that create contacts do not necessarily do this at the time the data enters the system. The reason for this is that only through the aforementioned standardization the system can "know" that each record actually constitutes a person, and should cost you a contact.
E.g. an import to a sendable data extension does NOT create the contact right away (similarly, the synch through Accounts described in c ), but only does so once a sendout adds the person to All Subscribers list, duplicate-checking using the ID specified in the Send Relationship field of the DE used for sending. Which should best always be an SF Contact Id (003...) in a connected setup.
==
Considerations for setting up the connector for filtering
Understand your contractual contact limit, which has as a baseline your SFMC edition's value plus any additional contacts. Determine a logic that keeps your count below it, or be ready to renegotiate your contract at some point. In a perfect world, from a process view it is definitely easiest to have a contract that at least theoretically allows you to synch all of your person database into SFMC.
As a best practice, anyway create a boolean field like "synchToSFMC__c" on each of the above objects that you can use as a filter, before you synchronize data for the first time and decide then. Set it to false for all, then think.
Now, I would second what is in the first link above, best choose one of the above objects, then keep the other two to "synchToSFMC__c" = false for all records on them . Now they technically synch. That allows also dependent objects into the connector (Campaign, Campaign Member which are "available with synch prerequisites" == require contact, lead, user to be in synch), but create zero contacts until you decide to do so. Hopefully with good reasons to back up the potential added complexity and/or cost.
Now, and pick the records on the remaining one to synch. In Salesforce, you could then set up a rule that only sets that field to "true" if some condition is true (could be "all". Could be something like, "ispersonaccount = true", if that is what you want. I am not saying this is good or bad, this is totally usecase driven). Notably, in this setup you can also, with due diligence, change that rule later without touching the connector but by altering the logical rule.
Which is also why the standard filter options ("created since X", "email not empty"...) are not recommended, this will get really clumsy: e.g. you'd have to remove legit emails from Contacts to change the synchronization. Maybe the email is totally fine though and should stay on the contact?
Use a dedicated boolean field as an on/off switch, so that you keep the ability to control (turn on / off / change) synchronization through logics that fit your usecase. Requirements simply do have a tendency to change, be prepared for that.
At the risk of becoming redundant, let me be clear:
Choose a generic condition that you control; do not make Contacts being created or removed a side effect to something else. Costs should occur with intent, not as side effects. Keep this in mind also for deletion, see further below. E.g. Do not use unsubscription as a filter condition. That will fragment your view on the data (some contacts will be in both systems, some only in one, but not completely cleared out from the other. all will be billable). This will just create confusion in a potentially sensitive and costly area.
Filtering the synch does NOT delete data in SFMC and reduce the contact count.
Do not mistake the filter as a usecase driven selection tool. Setting up logic in two systems via just one boolean condition is one of the most inflexible ways to make a selection.
So, with all this in place in your SFSC - both pathways are then administered inside SFMC.
Contact Builder / Data Sources / Synchronized / [your object] / Filter settings. here is where you choose the synch filter.
Journey Builder: use SF Data Events with clear cut criteria, don't "inject all contacts" and then filter in your journey - both from a performance, and a contact count consideration.
check also this: https://help.salesforce.com/s/articleView?id=sf.mc_cab_contact_definition_and_count_determination.htm&type=5
Considerations for cross cloud deletion
We'll try this with a crude mental image.- While it isn't perfect, I hope it illustrates the point:
SFMC is a sink with faucets
The data coming in through the faucet(s) is colored ink.
There is a thin drain pipe leading out of the sink. If data stops coming in, the ink level drops.
The faucets keep enough data coming in that the ink level in the sink stays up, despite the little pipe.
Data in the sytem, say, in data extensions, is the ink. Your contact count is the stain that the ink level leaves on the porcelain at the highest level, not the ink.
So if he ink level drops (contacts leave data extensions), the stain STAYS, unless you decide to scrub it off. (Data that is not in data extensions can still be in data views, All Subscribers, All Contacts, and cost against your limit).
Scrubbing the sink wall is Contact Deletion. It is the only way to get rid of the stain on the sink wall. So, drop the ink level first, then scrub.
By having the boolean condition on each record, you essentially have one faucet per contact and can really precisely control the flow of data coming in, not just through one big on/off.
Understand that you might even have several faucets for the same record into your sink (other data sources, more than one object containing the same person...). Make sure you know them all and they all have a way to be turned off on an individual record level before you bother scrubbing.
So a logical order to delete would be:
Stop synching the relevant contact (turn off the relevant faucet). e.g. set the synch condition in SFSC to false on one record. Send the information to delete through another way to SFMC (e.g. API, or a custom object that streams into SFMC, costs no contact but contains the Contact ID in some field).
Procedurally ensure all sources have stopped synching that record into SFMC (turn off all other relevant faucets that send that record).
Now the ink level drops a little. Contact Delete the contact in SFMC (scrub the stain off the sink wall).
Inform the source system(s) that you're done deleting in SFMC. To sustainably keep this record out, they should now understand to not synch this record again.
Decide if you want to delete in the source system(s) as well, and if yes, do that.
Why not just delete in the source system first and set up a simple chain of deletions? Because you should keep deletion in each system an OPTION to you decide upon separately. Otherwise you are tying separate things together and will risk making deletion a side effect.
To make that point:
What if we are not talking about a GDPR-rooted "delete me everywhere", but some other usecase, e.g. saving money on SFMC contact count? Then maybe the data cannot be deleted in SFSC for business reasons, but needs to stay for other purposes. Still it MUST leave SFMC because of Contact Count limitations. This gets worse when there are not just two systems, but say, a Commerce Cloud, too.
Hence the most future proof way to set this up would be to set up a "handover" as described, which can also be reused for other stakeholders and can grow with the complexity of the architecture.
(Stop synch from SFSC to SFMC >> Tell SFMC to delete >> SFMC contact delete >> Confirm the successful deletion from SFMC back to SFSC >> once all systems report success, decide about deletion in SFSC >> if you want that to happen, perform deletion in SFSC.)