Checking for an email address using Journey Builder Decision Splits

Question

How do i need set up my Contact Model in oder to check a DE of email_address.
I am working with the following constraints/rules.

1) CRM (Salesforce) allows for multiple records to have the same email_address

2) Using Salesforce Entry event to inject records in real-time.

3) I need to put duplication email address down a new Journey Path, so that the customer can take a real-time action on these duplicate records. For example, if email is found my SFMC DE, then create a task in the CRM for the sales team Admin to merge the record.

ContactKey Look-ups fail b/c the new record has a new contactkey.

all solutions I have come up with result in automation running hourly, and break my real-time requirement.

Thanks Pat

Answer 1

You can't add your synchronised data extensions back into the contact model, as they're already part of it, so you'd need to create some sort of reference table, "email_duplicates", to do your lookup against. Here are the fields:

Create yourself an Automation with an SQL Query Activity to update this table periodically. The query's body should look like this:

SELECT
  email,
  COUNT(1) AS address_count
FROM
  Contact_Salesforce
GROUP BY
  email

Add your reference table to Contact Builder's attribute groups and link your reference table based on Contact.EmailAddress = email_duplicates.email.

Use your reference table for decision splits based on email_duplicates.email_count is not null.

Observe how new contacts with email addresses already in your Contact_Salesforce synchronised data extension go down one path and totally new contacts (based on email address) go down another: