2

I am trying to write a query that gives me all of the subscribers keys from the all subscribers where at least one of these keys has been sent to.

I have written the following syntax:

select DISTINCT(s.SubscriberKey),s.EmailAddress,se.EventDate,se.OYBAccountID from ent._Subscribers s
JOIN _Sent se on s.SubscriberID=se.SubscriberID
where s.SubscriberKey is not null and se.OYBAccountID='XXXXXX'

However this only gives me the instances of the key when it was sent.

So lets say i have one email address with 2 subscriber key; if i amend the query for those 2 keys it will only show me the one that has been sent to which I kind of get because i do the join with the _Sent data view.

Is there a way to tweak the syntax to achieve what i need?

Thank you, Giulietta

  • Where s.SubscriberKey is Null is moot. It they are on the All Sub List then they have an SubscribeKey. I would take that out – Samuel Dare May 15 '18 at 8:04
  • If they are in the All subscribers list, then it is implicit that they have been sent emails. – Samuel Dare May 15 '18 at 8:32
2

If you contacts are in the All Subscribers List then they have been sent an email. Are you trying to find duplicates? To do this I would use a subquery to order them and filter on conditions where there is more than one occurrence.

eg

SELECT
SubscriberKey 
,EmailAddress
,EventDate
,OYBAccountID
FROM 

(
SELECT

FROM
 s.SubscriberKey
,s.EmailAddress
,se.EventDate
,se.OYBAccountID 
,ROW_NUMBER() OVER(PARTITION BY s.EmailAddress ORDER BY  s.SubscriberKey DESC) as x
from ent._Subscribers s
JOIN _Sent se on s.SubscriberID=se.SubscriberID
where se.OYBAccountID='XXXXXX') t 

WHERE t.x = 2
| improve this answer | |
  • 1
    Thank you @Data_Kid. I have encountered the error message: An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. I managed to bypass that. SELECT SubscriberKey ,EmailAddress ,EventDate ,OYBAccountID FROM (select s.SubscriberKey,s.EmailAddress,se.EventDate,se.OYBAccountID,ROW_NUMBER() OVER(PARTITION BY s.EmailAddress ORDER BY s.SubscriberKey DESC) x from ent._Subscribers s JOIN _Sent se on s.SubscriberID=se.SubscriberID where se.OYBAccountID='XXXXXX') t where t.x >1 i still dont get the information I need. – Giulietta May 16 '18 at 14:44
0

I managed to resolve this using the following syntax:

select top 100 percent subscriberkey, emailaddress from ent._Subscribers where emailaddress in ( SELECT EmailAddress FROM _Sent se Join ent._Subscribers s on s.subscriberid = se.subscriberid where se.OYBAccountID='XXXXXX' ) order by emailaddress

| 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.