Return to Question

source code formatting, added marketing-cloud tag

Source Link

edited Dec 1, 2015 at 15:42

31.1k
6
44
88

I am trying to identify a segmented group of individuals who have opened at least one of my emails within the last 30 days, but have not clicked on a single one. The query syntax is verified however, when the query runs it does not pull any results even though there should be.

select s.EmailAddress from _Subscribers s left outer join _Sent se on s.SubscriberID = se.SubscriberID where se.EventDate > dateadd(day,-30,getdate()) AND ListID=1458345 AND se.SubscriberID IN (SELECT se.SubscriberID FROM _Open WHERE se.EventDate > dateadd(day,-30,getdate())) AND se.SubscriberID NOT IN (SELECT se.SubscriberID FROM _Click WHERE se.EventDate > dateadd(day,-30,getdate()))

select 
    s.EmailAddress
from _Subscribers s
left outer join _Sent se on (s.SubscriberID = se.SubscriberID)
where 
se.EventDate > dateadd(day,-30,getdate()) 
AND ListID = 1458345 
AND se.SubscriberID IN (
    SELECT se.SubscriberID 
    FROM _Open 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
) 
AND se.SubscriberID NOT IN (
    SELECT se.SubscriberID 
    FROM _Click 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
)

select 
    s.EmailAddress
from _Subscribers s
left outer join _Sent se on (s.SubscriberID = se.SubscriberID)
where 
se.EventDate > dateadd(day,-30,getdate()) 
AND ListID = 1458345 
AND se.SubscriberID IN (
    SELECT se.SubscriberID 
    FROM _Open 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
) 
AND se.SubscriberID NOT IN (
    SELECT se.SubscriberID 
    FROM _Click 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
)

marketing-cloud query

Source Link

asked Dec 1, 2015 at 14:34

Alexa

Individuals who have opened at least one email within a 30 day period, but have not clicked a single one

query