0

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?

0

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.

| improve this answer | |
  • Thank you! I think the alternative note is actually the way it should be done. In the 3rd query is "and not exist" suppose to select from [click180days] c? – TehGoose Aug 12 at 20:15
  • yes! Silly copy/paste! Edited and corrected. Thanks for noticing! – Gortonington Aug 12 at 21:12
  • Strangely, no query I run will populate results for opens in the last 180 days. I have used the query above with no luck. The DE I created mirrors click180days and sent180days. Both of which have populated properly. – TehGoose Aug 13 at 18:15
0

I'm very new to this... but my thought would be that would run against everyone on your Subscribers List whether they were sent an email or not?

I would imagine running it against your '_Sent' Data View would be a better representation of people who were sent emails over the last 180 days and have not clicked in 180 days?

I'm hacking away figuring this out but I'll share if I can get it to run... :X

Your Event Date needs to be tied to Sends, not opens if they are unopened, I believe.

| improve this answer | |

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.