0

I'm using SSMS and trying to identify contacts that have received 10 or more emails in the past 1 year and have not clicked or open any. When I run the query, some contacts fit the parameters, but I also see contacts in the list the opened or clicked within the last month or two

SELECT S.SubscriberKey, COUNT(*) AS count
FROM [SFMC].[dbo].[SentEvent] S
WHERE CAST(S.EVENTDATE AS DATE) > '07/25/2022'
  AND S.SubscriberKey IN (
    SELECT O.SubscriberKey
    FROM [SFMC].[dbo].[OpenEvent] O
    WHERE CAST(O.EVENTDATE AS DATE) > '08/04/2022'
  )
  AND S.SubscriberKey NOT IN (
    SELECT C.SubscriberKey
    FROM [SFMC].[dbo].[ClickEvent] C
    WHERE CAST(C.EVENTDATE AS DATE) > '08/04/2022'
  )
GROUP BY S.SubscriberKey
HAVING COUNT(*) > 10;
2
  • You are not using NOT IN for the OpenEvent table. Aug 4, 2023 at 22:37
  • Thanks @VijayaKapadam. You're right! Aug 8, 2023 at 21:36

0

You must log in to answer this question.

Browse other questions tagged .