-1

I would like to create a query that allows me to build a data extension for a segment that has either entered our list in the past 30 days OR opened an email in the past 90 days.

I have the Query for people who have joined the list in the past 30 days (see below) but am struggling in adding an OR clause for people who opened in the past 90 days because the OpenDate demo lives in the All Subscribers panel, not in the list panel.

Can anyone help me in adding this clause to my query or building a query that allows me to meld these 2 clauses together?

select 
s.EmailAddress 
from _Subscribers s 
left outer join _Listsubscribers ls on (
    s.subscriberid = ls.subscriberid 
    and ls.listid = 170095
)
where DateDiff(day,ls.CreatedDate,GetDate()) <= 30 
2
  • Have you looked at the other System Data Views for the answer to your question? – Adam Spriggs Jan 22 '16 at 22:39
  • I tried EventDate but was told that's an invalid column name... may have been my implementation of it though: select s.EmailAddress from _Subscribers s left outer join _Listsubscribers ls on s.subscriberid = ls.subscriberid and ls.listid = 1546514 ) where DateDiff(day,ls.CreatedDate,GetDate()) <= 30 OR DateDiff(day,ls.EventDate,GetDate()) <= 90 – lana.ubm Jan 25 '16 at 16:20
1

For this scenario, I would recommend using a union statement to simulate the OR condition or break it up into two separate queries. Additionally, this approach will allow to use INNER JOIN, rather than LEFT JOIN, which should help with performance.

The following query has not been tested, but should meet you needs.

select 
s.EmailAddress 
from _Subscribers s 
inner join _Listsubscribers ls on (
    s.subscriberid = ls.subscriberid 
    and ls.listid = 170095
)
where DateDiff(day,ls.CreatedDate,GetDate()) >= 30 

union 

select 
s.EmailAddress 
from _Subscribers s 
inner join _open o on (
    s.subscriberid = o.subscriberid 
    and o.listid = 170095
)
where DateDiff(day,o.EventDate,GetDate()) >= 90 

I had some questions about the requirements. So, the query may need a few adjustments depending on how the following questions are answered.

Discovery Questions:

  • Are you interested in the create date of the subscriber or the date the the subscriber joined list 170095?
  • Are you interest in only open events relating to list 170095 or all open events?
4
  • Hi David, thank you so much! I am interested in the day they joined the list and all open events. – lana.ubm Jan 26 '16 at 20:53
  • If that's the case, then the "and o.listid = 170095" line in the query is not needed. – David Devoy Jan 28 '16 at 19:08
  • Hi David, I tested this without the "and o.listid" line and I find that it pulls people who aren't in my list. Do you think this may be because it's pulling anyone in my all subscribers who has opened in the past 90 days? I want people who are on this list, but for the open activity to pull from the all event activity. – lana.ubm Jan 29 '16 at 18:29
  • Yes, it is pulling all subscribers with an open event. If you are only interested in member of the for list 170095, the 'left outer join' needs to be changed to an inner join. reference: blog.sqlauthority.com/2009/04/13/… – David Devoy Feb 1 '16 at 17:31

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.