[SalesForce] SQL-Bounce data in Marketing Cloud

I am an not a pro when it comes to SQL.I am looking for help write a single query that gives me bounce data for my sends in last 90 days. When I try the below I hit an error "Unable to create temporary data extension: Error: options.uri is a required argument"

I want to be able to see hard/soft bounces.

SELECT DISTINCT UPPER(EmailAddress) as EmailAddress, SubscriberKey
FROM _Subscribers
WHERE Status = 'bounced'

Thanks
Esha

Best Answer

Totally agree with other people mentioning _Bounce Data View. It is indeed better to have an overview an email bounces and a reason for them. Read this for more information. Here is also how SQL would look like, adjust it based on your needs.

SELECT
    b.AccountID,
    b.OYBAccountID,
    b.JobID ,
    b.ListID,
    b.BatchID,
    b.SubscriberID,
    b.SubscriberKey,
    s.EmailAddress,
    b.EventDate,
    b.IsUnique,
    b.Domain,
    b.BounceCategoryID,
    b.BounceCategory,
    b.BounceSubcategoryID,
    b.BounceSubcategory,
    b.BounceTypeID,
    b.BounceType,
    b.SMTPBounceReason,
    b.SMTPMessage,
    b.SMTPCode,
    b.TriggererSendDefinitionObjectID,
    b.TriggeredSendCustomerKey
FROM _Bounce b
LEFT JOIN _Subscribers ON b.SubscriberKey = s.SubscriberKey
WHERE b.EventDate > DATEADD(D, -91, GETDATE())
Related Topic