Query for Engagement Campaign

Question

I'm trying to confirm that the query I have written is correct. I'm trying to pull back people from the 3 lists in the query that have not opened an email within the last 6 months to start a re-engagement/inactive campaign. Also, can Salesforce look past 6 months if I wanted to do the same for clicks? Thanks! (Note - we use subscriber key as email)

SELECT distinct SubscriberKey
FROM _Sent
WHERE EventDate > dateadd(day, -180, getdate())
AND SubscriberKey NOT IN (
   SELECT SubscriberKey
   FROM _Open
   WHERE EventDate > dateadd(day, -180, getdate())
)
AND ListID IN ('3672' , '3673' , '3671')

Answer 1

Yes, but if you have a decent amount of sends, then it'll likely not complete before it hits the 30 minute timeout window. The System Data Views are very slow.

I generally get a list of jobs by pickuptime (target: DE1), find all of the sends for jobs in DE1 (target: DE2), find all of the opens for jobs in DE1 and then do a sub-query exclusion or left-join-where-target-is-null to find those sent an email but with no opens (target: DE3).

Answer 2

Adam's answer trumps mine by far but another possible approach that might be applicable for you would be to look at the "EmailName"

Let's say you'd want to target everyone has not opened an email in the last 6 months across all your automated campaigns.

You could try the below:

SELECT _Sent.SubscriberKey
FROM _Sent
INNER JOIN _Job 
ON _Sent.JobID = _Job.JobID
WHERE _Sent.EventDate > dateadd(day, -180, getdate())
AND SubscriberKey NOT IN (
   SELECT SubscriberKey
   FROM _Open
   WHERE EventDate > dateadd(day, -180, getdate())
AND (_Job.EmailName = 'EmailName1'
OR _Job.EmailName = 'EmailName2')

If anyone has any issues on my answer please feel free to let me know as I'd appreciate the feedback.

Answer 3

J. Donders, in your question, you had also asked if Salesforce can look past 6 month.

Although, the system data views only reference the last 6 months of data. You can run tracking extracts on data ranges over 6 month. The tracking extracts contain historically date dating back to the account setup.

In order to query this data it requires much more setup of course. You would need to first export the data and import it back into a data extensions. Before you could query against it.