Q:

Unopened Query confusion

I am trying to query for unopen email subscribers in the last 180 using the _subscribers and _open data views.
Due to the open view being limited to showing only opens and not unopens. I am querying for the entire subscriber list and asking for the email addresses and subscriber keys not in the _open data view.
Query:
SELECT distinct
    s.SubscriberKey as Subscriber_Key,
    s.EmailAddress as Email_Address
FROM
    [_Subscribers] s
WHERE
    s.SubscriberID NOT IN (
        SELECT o.SubscriberID FROM [_Open] o WHERE EventDate > dateadd(d,-180,getdate())
and IsUnique = 'true' or IsUnique is null)*

Theoretically, this should return all the subscribers who have not been sent or opened an email in 180 days.
Problem: When the target data extension populates I test the results. I compare the unopens (Inactives) to our active list. But the two share over 100,000 records. How is this possible? Is the query wrong?

A:

I would recommend (to allow for increasing volumes) to split this into 3 different queries:
**In the below queries, you likely don't need the WHERE condition on the EventDate as the Data Views only retain records for a 6 month period (180 days).
Query 1:
This one collects all of the Opens inside of the _Open Dataview inside the last 180 days.
SELECT SubscriberKey
FROM [_Open]
WHERE CAST(EventDate as Date) > CAST(DATEADD(day,-180,getdate()) as Date)

/* Target: open180days*/
/* Action: Overwrite*/

This will take all the opens in 180 days and list the subkeys inside a DE
Query 2:
SELECT SubscriberKey
FROM [_Click]
WHERE CAST(EventDate as Date) > CAST(DATEADD(day,-180,getdate()) as Date)

/* Target: click180days*/
/* Action: Overwrite*/

To account for inferred opens (someone that opened, but had images off so pixel did not fire), we will also exclude people that have clicked in the past 180 days.
Query 3
SELECT s.SubscriberKey
FROM [_Subscribers] s
WHERE NOT EXISTS (
  SELECT TOP 1 o.Subscriberkey 
  FROM [open180days] o 
  WHERE o.SubscriberKey = s.SubscriberKey
)
AND NOT EXISTS (
  SELECT TOP 1 c.Subscriberkey 
  FROM [click180days] c 
  WHERE c.SubscriberKey = s.SubscriberKey
)

This one will give you everyone listed in Subscribers DV as you had that have not opened or clicked in past 180 days. I honestly would look to get a 'Master' DE or similar instead of using the Subscribers DV to help increase performance, etc.

ALTERNATIVE NOTE
This will also pull up people that you may not have sent to recently, so really this number is not really accurate if you are looking to do 'inactive' subscribers. I would instead target the Sent DV, which I would add one more query to collect this and then do a 4th to get final results.
Query 3b
SELECT SubscriberKey
FROM [_Sent]
WHERE CAST(EventDate as Date) < CAST(DATEADD(day,-180,getdate()) as Date)

/* Target: sent180days*/
/* Action: Overwrite*/

This will collect all those that were sent an email in the last 180 days (can exclude test sends, etc. if you want via the other fields in Sent DV)
Query 4b
SELECT s.SubscriberKey
FROM [sent180days] s
WHERE NOT EXISTS (
  SELECT TOP 1 o.Subscriberkey 
  FROM [open180days] o 
  WHERE o.SubscriberKey = s.SubscriberKey
)
AND NOT EXISTS (
  SELECT TOP 1 c.Subscriberkey 
  FROM [click180days] c 
  WHERE c.SubscriberKey = s.SubscriberKey
)

This will then give you all the subscribers that were sent an email in the past 180 days but did not open or click (inferred open) anything. Which is more of an accurate inactive audience.

