Help Query Spam Complaint Data

Question

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.

Answer 1

Not all of the SFMC activity data is available in the System Data Views via Query Activity (e.g. bounce reason). There is, however, some additional data available from a Data Extract of Tracking Data.

I'd suggest building an Automation that contains the following steps to import Tracking data into Data Extensions:

1. Data Extract (to your FTP Import directory)
2. File Transfer to unzip (tracking extracts are always zipped)
3. Import of Bounce data (Bounce.txt)
4. Import of Complaint data (Complaint.txt)
5. Import of Attribute data (Attributes.txt)

You'll need a Date Extension for each of the Imports.

Once you have all of that data into Data Extensions, your Query Activity can tie it all together.