how to get the list detective data for emails sent from a journey with source as data extension

Question

We use data extensions as entry source in a journey. These data extensions are populated from automations using SQL activities where we query synchronised DE that we get from Salesforce(like Lead, Case etc). In this whole process of populating data extension, we can not detect if email value in any record is valid or not as list detective checks this when we SEND from a data extension. Hence, when email is sent from a journey, only at that time list detective triggers into action.

Now we want this data as a report or in any other format, i.e, the email addresses which were skipped due to list detective in journey. How can this be achieved?

Is there something like a 'List Detective data view'? I know there is no UI provided by SFMC for this. Is there any way we can get this skipped email address list, without much manual intervention?

Answer 1

I believe the 'Not Sent' Tracking extract may be what you are looking for.

This list contains information on send jobs and messages that did not send.

This will provide a list of those that were 'not sent' as part of a job, whether due to List Detective, an error, etc.

It is not real time as you would need to extract it every time you need an update, but it should provide what you need at a scheduled interval.

Sample of csv:

Otherwise for more real time/specific tracking of it, you can utilize the 'NotSentEvent' object for the SOAP API.

The NotSentEvent object contains information on when email message failed to be sent.

Example SOAP body:

<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
    <RetrieveRequest>
        <ObjectType>NotSentEvent</ObjectType>
        <Properties>EventDate</Properties>
        <Properties>EventType</Properties>
        <Properties>SendID</Properties>
        <Properties>SubscriberKey</Properties>
        <Properties>TriggeredSendDefinitionObjectID</Properties>
        <Filter xsi:type="SimpleFilterPart">
            <Property>EventDate</Property>
            <SimpleOperator>greaterThan</SimpleOperator>
            <DateValue>2019-03-01T06:47:07.083Z</DateValue>
        </Filter>
    </RetrieveRequest>
</RetrieveRequestMsg>

You can change your filter to fit your need.

Sample of Response:

    <RetrieveResponseMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <OverallStatus>OK</OverallStatus>
        <RequestID>XXXXXXXXXXXXXXXXXXXXXXXXXX</RequestID>
        <Results xsi:type="NotSentEvent">
            <PartnerKey xsi:nil="true" />
            <ObjectID xsi:nil="true" />
            <SendID>109203</SendID>
            <SubscriberKey>anemail@notreal.com</SubscriberKey>
            <EventDate>2019-06-01T06:16:54.387</EventDate>
            <EventType>NotSent</EventType>
            <TriggeredSendDefinitionObjectID />
        </Results>
    </RetrieveResponseMsg>

As has been mentioned in other answers, for pre-send, your best bet is the REST API validateEmail endpoint. But this will not guarantee it still passes at send time, as there are other validations done prior to send that are not on this endpoint.

So I usually use this as a first level of protection then add in the NotSent Report to find those that slipped through and correct accordingly.

Answer 2

You can use REST API.

• Rest API validateEmail

I would use this endpoint on Salesforce side to validate email for records created with trigger that hits the API and stores information on flag.

Answer 3

I see 3 main options (assuming you want to do this inside of SFMC):

1. Rest API: validateEmail (as mentioned before by Mateusz), though this is no doubt the most technical solution.
2. SQL: Since you're already querying using SQL you could write an email validation query, something like SELECT * FROM Contacts WHERE email NOT LIKE '%_@__%.__%'. You could do the inverse to create a DE that contains the email addresses you wanted to filter out and report on (your second question).
3. Solution with a UI: To my knowledge there's nothing in the SFMC UI that offers this kind of feature, but a third party app like DESelect might be able to solve it (and avoids you having to write SQL in the first place...)

Let me know if this helps!

Answer 4

You could define AMPScript block and embed one into your emails (leveraging string functions) and write exceptions to data extensions when spotted incorrect email addresses? (kinda reflecting the inner workings of the "list detective" logic)