We would like to pull all the bounces from a business unit via SQL to a single target data extension. The target DE has the following data columns from 3 data views DEs (_Subscribers, _Job, _Bounce):

SubscriberKey (Text, 50),
Email_Address (Email_Address, 254),
EventDate (Date),
BounceCategory(text, 100),
SMTPBounceReason(text, 500),
Event_ID(text, 100)

It also has 2 data columns from a BU-specific data extension called "Organisations", which are the following:

Organisation_Internal_Code (number),
Contact_Key (text, 100)

The target DE does not have a primary key. The BU-specific data extension's Primary Key is Contact_Key. To re-summarize, we would like to do a query joining data from 3 data views and one BU-specific DE to get the bounces in one place. Thanks

1 Answer 1


You could try the following, given the Contact_Key in Organizations DE is equal to SubscriberKey (if it's not, how do they relate?):


s.EmailAddress AS Email_Address,
j.Event_ID AS EventID,

_subscribers s
LEFT JOIN _bounce b ON s.SubscriberID = b.SubscriberID
LEFT JOIN _job j ON b.JobID = j.JobID
LEFT JOIN Organisations d ON s.SubscriberKey = d.Contact_Key

You would also need to decide which type of join would work best for your use case and mark all fields in your target Data Extension as nullable. Here you can review different join types: https://images.app.goo.gl/vUzzjdwbCJ2513jQ7

I am also not sure why for some fields you decided to use their original name (like EventDate) and you changed names for others (for example Event_ID instead of EventID). You also changed field lengths for some of them, which is not necessary - best to keep the same data types and lengths as specified in the Data Views documentation.

  • Unfortunately, the code does not work :( It gets pass the validation part, but no results (0) is showns in the resulting DE.
    – nix9247
    Jul 30, 2019 at 9:14
  • did you make all fields nullable in your target DE? can you maybe try without the last DE - Organizations?
    – zuzannamj
    Jul 30, 2019 at 10:37
  • Maybe you should try "from ent._subscribers "? - your subscribers are at your parent BU
    – jc_mc
    Aug 5, 2019 at 14:22

You must log in to answer this question.

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