0

I currently manually update a database by exporting bounced email addresses using the code below:

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

We want to change this so we only get hard bounces and can still email addresses with a soft bounce. The BounceCategory column appears to be attributed to another table - how do I update the code?

Apologies if something similar has been asked, I'm brand new to this and very lost.

1 Answer 1

1

You will need to join up to the _Bounce data view.

SELECT DISTINCT UPPER(s.EmailAddress) as EmailAddress, 
s.SubscriberKey, 
b.BounceCategory
FROM _Subscribers as s
INNER JOIN _Bounce as b
ON b.SubscriberKey = s.SubscriberKey
WHERE b.BounceCategory = 'Hard bounce'

To add in a column from an additional data extension you can just add another join in the query.

SELECT DISTINCT UPPER(s.EmailAddress) as EmailAddress, 
s.SubscriberKey, 
b.BounceCategory,
bh.PROMO_KEY
FROM _Subscribers as s
INNER JOIN _Bounce as b
ON b.SubscriberKey = s.SubscriberKey
INNER JOIN BounceHeld as bh
ON bh.SubscriberKey = s.SubscriberKey
WHERE b.BounceCategory = 'Hard bounce'
4
  • Thank you! This works. I hate to ask another question here but I just learned we need to bring in a custom field from a data extension. Field is PROMO_KEY from an extension called "BounceHeld" and I'm not sure how to incorporate. Would you be able to help? Thanks again.
    – L. Tracy
    Nov 15, 2018 at 15:06
  • Ok so you have a DE called BounceHeld with a column called PROMO_KEY. Is that the only field in the Data Extension? You will need a subscriber key or email or something that will be used to join the data together. Nov 15, 2018 at 22:44
  • The DE also has EmailAddress, SubscriberKey and BounceCategory.
    – L. Tracy
    Nov 16, 2018 at 16:18
  • @L.Tracy, I have updated my answer with a query that should work for you. Keep in mind that the query will only include subscribers that are in all locations (INNER JOIN), _Subscribers, _Bounce and the BounceHeld DE, which is what I have assumed is your use case. Nov 19, 2018 at 1:53

You must log in to answer this question.

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