1

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),
EmailID(number),
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

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

SELECT

s.SubscriberKey,
s.EmailAddress AS Email_Address,
b.EventDate,
j.EmailID,
b.BounceCategory,
b.SMTPBounceReason,
j.Event_ID AS EventID,
d.Organisation_Internal_Code,
d.Contact_Key

FROM 
_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.

|improve this answer|||||
  • 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 '19 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 '19 at 10:37
  • Maybe you should try "from ent._subscribers "? - your subscribers are at your parent BU – jclobro Aug 5 '19 at 14:22

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.