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'
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
  • 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 Oct 11 '17 at 18:15
  • @Amulya, according to the documentation DateJoined can be nullable. Try this one CreatedDate on _ListSubscribers. – Eduard Oct 12 '17 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 Oct 12 '17 at 13:15

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.