So our Salesforce database contains over 700k contacts, with about 100k of those being duplicate email addresses with different Salesforce ID's attached. This happens due to injecting in data from multiple sources where they do not have any way to ingest the contact as part of an existing identity (ie: it takes every input from that source as a new account). In addition, we have a business rule that allows for 1 contact to become a part of two separate classes of contacts, thus creating 2 different contacts (as these contacts can convert with us as two separate types of customers).
Our Salesforce data is ingested into Marketing Cloud as Synchronized Data Extensions. I've then created several segments and exclusionary ones via filters of the Synchronized DE's, and we have mailings that go to different audience types (so reliably segmenting the data is important).
An example Data Extension:
Extension A (by ID, Email): AAC [email protected]; AAE [email protected], DFF . [email protected]; DEO . [email protected]; EWS . [email protected]
Extension B (by ID, Email): AAC [email protected]; DEO . [email protected]; REW [email protected]
However, I suspect that if some of our duplicate addresses conform to the filter in both Data Extension, we may send the user multiple mailings (even after excluding the other Data Extension as the user has 2 separate ID's for the same email). So in the above example, if Extension A is sent while B is excluded (since it was part of an earlier send), AAC and DEO were excluded, but not REW (since the send DE has an ID of AAE and will be ignored)
Is this wrong and excluding a Data Extension also includes any email associated (even if it's under a different ID) or it only excludes by ID?
If it does only exclude by ID, is there a way to exclude any contact without manually export/import to create a new Data Extension? For now, I've been manually pulling lists once spooled and then matching them in Excel (which takes 30-40min each...and would be impossible once our list reaches 1m contacts). I thought about building SQL queries into Automation Studio instead of Filtered Data Extensions, but we would still run into the same issue as the ID's would live in different Data Extensions just like the above example (you could dedupe from a query by selecting DISTINCT but you would still run into the same problem when excluding from a send). Would definitely appreciate a much more sustainable way of doing this...
