Enterprise Bounces

PHOTO

Tue Jan 24 2023 15:07:38 GMT+0000 (Coordinated Universal Time)

SELECT Bounce.SubscriberKey as ContactID, 
       Contact.Email AS EmailAddress,
       Bounce.Domain

FROM _Bounce Bounce 

INNER JOIN Contact_Salesforce_1 Contact
        ON Bounce.SubscriberKey = Contact.Id

INNER JOIN ContactPointConsent_Salesforce AS "ContactPointConsent"
        ON Contact.Id = ContactPointConsent.Contact__c

INNER JOIN DataUsePurpose_Salesforce AS "DataUsePurpose"
        ON ContactPointConsent.DataUsePurposeId = DataUsePurpose.Id

WHERE  ContactPointConsent.CaptureContactPointType = 'Email'
       AND ContactPointConsent.MATM_Owner__c = 'Caterpillar'
       AND DataUsePurpose.Name = 'Caterpillar Marketing'
	   AND Bounce.BounceCategory IN ('Hard Bounce','Block Bounce')
	   AND Bounce.OYBAccountID IN ('100040185','100018781','100029745','515002115','515005731','515004347',
'100015249','100014591','100036023','100014588','100022919','100015250','515008285','100010259','100015251')

COPY