0

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

1
  • There's nothing wrong with your query. It sounds like Query Studio was having a bad day. It does get a bit buggy at times. Your query will just give you people who've had a soft bounce on their last email, though. That bounce could have taken place 180 days ago and you'll miss those that hard bounced from a trusted ISP... they'll have a Status of "held". As alluded to below, if you want to know about the last 90 days, you'll need to look at the _Bounce Data View and join to _Subscribers to get the email addresses of the subscribers who bounced.
    – Macca
    May 13 at 14:52
0

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())
0

You might want to make it like documented here Email address is not a column in Bounce data view so if Email Address is related to SubscriberKey then you might want to modify your query as below and join _subscribers in case you want further details but it also does not contain email address

SELECT SubscriberKey from _bounce where o.EventDate > dateadd(d,-90,getdate())

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.