May I ask if you have any ideas on how can I get or pull a list of people who have not opened any emails for the past 3 months?
1 Answer
You certainly can - either through using a Measure and a Filter or writing a SQL Statement e.g.
Examples of below statements selects all customers who have been sent an email in the past 90 days but do not have any open events in the past 90 days.
Select a.SubscriberKey, a.EmailAddress
From _Subscribers a
Left Join _Sent s on a.SubscriberKey = s.SubscriberKey
Left Join _Open o on a.SubscriberKey = o.SubscriberKey
Where DateDiff(d, s.EventDate, GetUTCDate()) <= 90
And o.SubscriberKey is null
This statement should also work :
select
subscriberkey,
EmailAddress
from _Subscribers
where subscriberkey in
(select subscriberkey from _sent WHERE s.EventDate > DATEADD(day, -90, GETDATE())
and
subscriberkey not in (select subscriberkey from _open WHERE s.EventDate > DATEADD(day, -90, GETDATE())
-
Thanks @iManage... Since using the sql sample you provided under automation studio. I just want to confirm if I have to create a new Data Extension to have the records/results on the sql? Does the field structure of the DE contains subscriberkey and emailaddress only? Or there are additional fields to add? Hoping for your reply. thanks again :)– AirvinCommented Oct 5, 2018 at 14:53
-
Correct - EmailAddress and SubscriberKey would your main two fields, but you. An join on other data sets if needed. Depending on your implementation, you should be able to get everything, including First Name etc from using the SubscriberKey and having your data model mapped– GravitaiCommented Oct 5, 2018 at 17:39