1

I need to exclude people who have not opened our emails in the 4 past months.

I have several ideas, can you please tell me the best solution and help me?

  1. Query: Any idea of how I can exctract them?

  2. Create a new D.E for exclusion I extracted Subsriber ID of non openers from several sends. I selected with excel the IDs of those that come out in all sends (-> I now have a list of all those who didnt open neither of them). I imported them as a D.E and would like to use it as an exlcusion list. --> i am not very convinced about this.

  3. Data Filters: They do not work for me. Data and number of non openers does not match with real data.

Thanks in advance,

Gina

1

Much of this answer will depend on performance. If we are talking about a list of 1 million subscribers, the solution is much different than a list of 40 million.

Performance considerations aside:

  1. Query the data view _open to find non-openers. Find all subscribers that are in _subscribers but not in _open. This is the simplest method, but might run into performance issues. Break it down into multiple queries and log a support case to have your query timeout increased if needed.

Example query(not tested):

SELECT subscriberid, subscriberkey, datejoined FROM ENT._subscribers 
LEFT JOIN ENT.open o ON s.subscriberid = o.subscriberid
WHERE o.subscriberid IS NULL 
AND o.datejoined >= getdate() - 120 
AND s.status = 'Active' 

If performance is still an issue, you'd have to work out something that operates on deltas and updates daily - adding new non openers and dropping off old non-openers one day at a time

  1. Place in an automation to refresh daily.
1
  • Hello, This seems like a good solution, but I'm not that good in SQL. For the moment teh SQL doesnt recognise the Datejoined column. Is there another variable I can use instead of it? Such as 'eventdate' SELECT s.subscriberID, s.SubscriberKey, s.EmailAddress, o.EventDate FROM ENT._Subscribers s LEFT JOIN ENT._Open o ON s.subscriberID = o.subscriberID WHERE o.subscriberID IS NULL AND o.EventDate >= getdate() - 90 AND s.status = 'Active'
    – Sol Arch
    Apr 28 '16 at 14:36
0

Have you looked into Measures? Quick search found this, explains it well:

http://help.exacttarget.com/en/documentation/exacttarget/subscribers/measures/

This lets you quantify subscriber behaviours like clicks and opens, as well as the opposite (not-opens and not-clicks). You can add time constraints, email ID's, API keys and other useful criteria to limit the scope of what's captured.

You can then use these Measures in your Data Filters to define the number of actions (or non-actions) you want to filter your subscribers by. Should do the trick for what you're looking to do.

1
  • 1
    Hello Rainer and thank you for your answer. Unfortunately I had tried this option but once I apply the DataFilter with the measures I need to try to create a group/list I always have 0 counts, as if it the measure don't apply properly to the subscribers.
    – Sol Arch
    Apr 28 '16 at 14:36

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.