1

Consider a scenario where all contacts from DataExtension receives an Email. I want a query to retrieve all contacts who opens the Email.

9
  • For more details please go through Data VIEWS ... Example: query you can fine here to find Opens help.salesforce.com/… Dec 23 '19 at 9:07
  • What are the column names you would like to retrieve from your DataExtension?
    – zuzannamj
    Dec 23 '19 at 9:33
  • column names like FirstName and LastName. Dec 23 '19 at 10:02
  • Do you have a SubscriberKey column in your DE?
    – zuzannamj
    Dec 23 '19 at 10:18
  • @zuzannamj What to do if there is no subscriberkey column in DE? Dec 24 '19 at 8:44
6

Depending on which fields you want to retrieve from your Data Extension, you could do something like this. This will pull Sent Date, Open Date and Click Date for each subscriber in your Data Extension. I assume you have some sort of SubscriberKey column in your Data Extension:

SELECT 
de.EmployeeId,
de.FirstName,
de.LastName,
s.EventDate as SentDate, 
o.EventDate as OpenDate,
c.EventDate as ClickDate
FROM {{Data Extension Name}} de LEFT JOIN _Sent s ON de.EmployeeId = s.SubscriberKey
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1

To make the above work, insert the name of your Data Extension to where it says {{Data Extension Name}}. For your convenience, you can run this query in Query Studio.

Here's an article about Data Views which explains what data is available in Marketing Cloud and how to correctly pull it.

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.