3

I am trying to automate updates to the All Subscribers list whenever the preferred e-mail in Salesforce changes. I created a SQL query activity that compares our Salesforce data extension to the e-mail in the All Subscribers list and populates a data extension with the results. However, I am getting a ton of false positive matches. This is the query:

SELECT
sfdc.Id as "Subscriber Key",
sfdc.Email as "Email Address"
FROM Contact_Salesforce as sfdc
INNER JOIN _subscribers as allsub ON sfdc.Id = allsub.SubscriberKey
WHERE sfdc.Email != allsub.EmailAddress
AND CAST(sfdc.TE_Email_Info_Updated__c as DATE) >= '2018-12-01'
AND sfdc.TE_Email_Info_Updated__c != NULL

In many cases I'll get a result where the e-mail address is the exact same in both the All Subscribers list and the synchronized data extension. It's entirely possible that I have an extra space or something in either place, but from the manual reviews I've done, that doesn't appear to be the case.

On a related note, since you can't just run a query against your dataset in MC, is there a resource or best practice for debugging issues with SQL queries? Normally, I think I'd like to run counts to debug my queries (such as counting the lengths of both e-mails to ensure there's not an extra space for false-positive matches), but updating a DE every time I want to run a query seems like overkill.

At any rate, thank you for your help. Let me know if you have any questions about anything or need more info.

Improve this question

1 Answer 1

Reset to default
1

Make sure you're comparing field lengths of the same size between your two Email Address fields. If they're mismatched they may always evaluate as false. One other thing to rule out the issue of blank spaces is to add the following clause that will throw out length matches between email addresses: 

AND WHERE LEN(sfdc.Email) != LEN(allsub.EmailAddress)

Finally, are you sending emails to these contacts from data extensions that are linked properly to the subscriber record? If I remember correctly, when you send an email to a subscriber key that has recently had its address changed, it will update the record in All Subscribers, resulting in what looks like a matching email address. Anyone that hasn't had email sent to them after their address was changed will not having a matching address in All Subscribers.

Improve this answer
5
  • your comment regarding a changed e-mail that has not been sent to is very interesting. Is there further documentation that I can read about how email address updates in All Subscribers is handled? I assume the email field length in All Subscribers is 254, but I can't see for sure. The email field length in our sync'd data extension is 80, but I don't know if there's anything I can do to change it. From what I read, the sync'd tables are created from the Salesforce data model.
    – justinpdove
    Commented Dec 17, 2018 at 18:21
  • If they are not the same length, then CAST them to make sure they're the same length before comparing, like NVARCHAR(254)
    – Matthew Wash
    Commented Dec 17, 2018 at 18:31
  • Man, these queries act so weird. If I add the CAST to sfdc.Email, I get 0 results. If I then remove the WHERE sfdc.TE_Email_Info_Updated__c != NULL restriction, I get results. However, those results still have a lot of false positives. I guess I'll have to alter my DE to gather some data that will hopefully let me know what the issue is.
    – justinpdove
    Commented Dec 17, 2018 at 19:13
  • 1
    To discount any spaces causing mismatching, you can always use a replace. E.g. REPLACE(sfdc.Email, ' ', '') != REPLACE(allsub.EmailAddress, ' ', ''). You can also use TRIM(), but that only will take spaces from beginning and end of the string. To ensure I remove spaces as a possibility for mismatch, I usually default to REPLACE as I know it will change ALL spaces.
    – Gortonington
    Commented Dec 17, 2018 at 21:16
  • Have y'all ever issues viewing results? I added a bunch of fields for metadata to check e-mail address lengths and show the sfdc vs allsub emails and my results are completely different. The first 2 or 3 results from my previous false-positive attempts aren't even in my new results. Also, my new results are definitively positive matches (i.e., the e-mail address don't match). I do not understand what's happening here.
    – justinpdove
    Commented Dec 18, 2018 at 20:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .