1

I need to extract Subscriber details and the List details they are on.

My below query is working but how can I narrow it to see who has signed up on the website in 2017 and has never been sent an email? 

select l.ListID, l.ListName, l.EmailAddress, l.AddMethod,l.Addedby, l.CreatedDate, a.first_name_o, a.last_name_o, l.DateUnsubscribed, l.Status,a.job_title_o, a.library_type_o, S.EventDate

from ent._EnterpriseAttribute a
LEFT join _ListSubscribers l on a._SubscriberID = l.SubscriberID
LEFT JOIN _Sent S on  a._SubscriberID = S.SubscriberID
where ListName <> 'All Subscribers'
Improve this question

1 Answer 1

Reset to default
1

As stated in this doc you can use the DateJoined field from the Subscribers data view to filter your results:

select l.ListID, l.ListName, l.EmailAddress, l.AddMethod,l.Addedby, l.CreatedDate, a.first_name_o, a.last_name_o, l.DateUnsubscribed, l.Status,a.job_title_o, a.library_type_o, S.EventDate

from ent._EnterpriseAttribute a
LEFT join _ListSubscribers l on a._SubscriberID = l.SubscriberID
LEFT JOIN _Sent S on  a._SubscriberID = S.SubscriberID
LEFT JOIN _Subscribers  subs on  a._SubscriberID = subs.SubscriberID
where ListName <> 'All Subscribers' AND DATEPART (year ,subs.DateJoined) > 2017
Improve this answer
3
  • Actually it's not populating 'DateJoined' value at all for any Subscribers. '_Subscriber' is vanilla System view right. Is there any possibility that we are not using it in our Account?
    – Amulya
    Commented Oct 11, 2017 at 18:15
  • @Amulya, according to the documentation DateJoined can be nullable. Try this one CreatedDate on _ListSubscribers.
    – Eduard
    Commented Oct 12, 2017 at 7:07
  • It was my mistake. I am running this query on child BU and forgot to prefix with ent. on _Subscribers. Now it's working, thanks for the help.!!
    – Amulya
    Commented Oct 12, 2017 at 13:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .