Finding contacts in Marketing Cloud that are no longer in sync with CRM

Question

We have a field in Salesforce CRM that determines if a lead should sync with Marketing Cloud or not. This field however can be updated by users to FALSE which causes the lead to stop being updated in Marketing Cloud. How can we then find those in Marketing Cloud to be deleted? Is there a way to query the last sync date/time for example?

Answer 1

If you send an email to all the leads then it will be available in All subscriber list. You can use SQL query to compare between all subscriber list & Lead syncronized data extension and get the remaining leads.

But if you don't send an email for some leads then it will only available in Contacts which we don't have the provision to check via SQL Query.

Simplest way is, just import the lead record from salesforce with the respective field set it to 'False' into Marketing cloud data extension.

Enable contact deletion and delete the contacts.

Reference - https://help.salesforce.com/articleView?id=mc_cab_contact_deletion.htm&type=5

Let me know if this helps.

Answer 2

Import all your leads into an SFMC Data Extension using an Import Interaction. Useful instructions on how to do this can be found in this Knowledge Base Article. Ensure you include your "SyncToSFMC" field in the configuration(or whatever the name of the field is in Sales Cloud).

You'll end up with a Data Extension in your Root data extension folder containing all your leads that you can now run a query on to determine leads to delete, something like this:

SELECT 
    ls.Id
FROM 
    MyImportedData i INNER JOIN 
    Lead_Salesforce ls 
        ON  i.Id = ls.Id 
WHERE
    i.SyncToSFMC = 0