1

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.

2
  • Welcome to SF.SE Kel. What you've posted looks a lot like SQL, not SOQL. I suggest you look at the SOQL Reference and clarify your question using the edit button at the bottom left of your post.
    – crmprogdev
    Jul 4, 2016 at 14:58
  • Marketing Cloud uses Transact-SQL. I've updated the tag. Jul 4, 2016 at 17:26

1 Answer 1

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.

2
  • Thank you for the help guys, sorry I'm just new here and this is my first post. I'll just try first your recommendation @Adam
    – Kel Abdon
    Jul 5, 2016 at 12:36
  • I tried the one that was suggested and I successfully got the results. I would like to ask if there's away that we could extract the results across business units that we have (including parent and child business units)?
    – Kel Abdon
    Jul 12, 2016 at 5:53

You must log in to answer this question.

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