I would like to have an extracted data extension from the following tables below:
Want to get the following and put it in a data extension using a date range as condition.
- Email Address (who sent the spam complaint)
- List Name
- Subscriber Status
- Email Name
- Event Date (when the spam complaint occur)
- Bounce Reason*
- Bounce Type*
- Account who created the email (if there's a way to do this)*
- Subscriber Last Name and First Name*
Query that I made which I can't add the asterisked information above.
SELECT DISTINCT L.EmailAddress as [Email Address], L.ListName as [ListName], L.Status as [SubscriberStatus], J.EmailName as [EmailName], C.EventDate as [ComplaintDate], C.Domain as [Domain] FROM _ListSubscribers L INNER JOIN _Complaint C ON L.SubscriberKey = C.SubscriberKey INNER JOIN _JOB J ON C.JobID = J.JobID WHERE L.listID = C.listID AND C.EventDate >= '2016-01-01' AND C.EventDate < '2016-07-01'
Thank you in advance.