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

Improve this question
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, 2021 at 14:52

2 Answers 2

Reset to default
3

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.

Querying _Bounce as b and _Subscribers as s in the following SQL

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 s ON b.SubscriberKey = s.SubscriberKey
WHERE b.EventDate > DATEADD(D, -91, GETDATE())
Improve this answer
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())

Improve this answer
0

You must log in to answer this question.

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