I would like to have an extracted data extension from the following tables below:
- _ListSubscribers
- _Complaint
- _JOB
- _Bounce
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)
- Domain
- 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.