0

I am trying to write a query in SFMC where i want create a data extension with people who have not clicked any emails and we have sent them at least 3 emails.

I have the below query but i am getting 0 results. Can anyone correct me?

Select distinct s.SubscriberKey,count(c.EventDate) as total_clicks,count(s.EventDate) as total_sends
from [_sent] s
left join [_click] c
on s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
left join [_job] j
on j.JobID=c.JobID
where c.SubscriberID is NULL and j.EmailName like '%abcd%'
group by s.SubscriberKey
Having count(s.EventDate)>=3 and  count(c.EventDate)<1
2
  • I would get rid of the last condition and give it a try: and count(c.EventDate)<1
    – zuzannamj
    Nov 9, 2021 at 10:56
  • Tried this, still i get 0 results
    – rtys
    Nov 9, 2021 at 11:07

1 Answer 1

2

Even though the count(c.EventDate)<1 part looks indeed suspicious, in reality, it is a valid condition, yet really unnecessary here, since you already have c.SubscriberID is NULL for the Left Exclusion Join.

The problem is within your second LEFT JOIN which should be between _Job and _Sent because you want to find records that are not in the _Click Data View and thus the connection to it will result in empty values from _Job and that is why finally condition j.EmailName like '%abcd%' cannot be met.

The correct query should look like this -

SELECT DISTINCT
s.SubscriberKey,
count(c.EventDate) AS total_clicks,
count(s.EventDate) AS total_sends
FROM [_sent] s
LEFT JOIN [_click] c
ON s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID AND c.IsUnique = 1
LEFT JOIN [_job] j
ON j.JobID = s.JobID
WHERE c.SubscriberID IS NULL AND j.EmailName like '%abcd%'
GROUP BY s.SubscriberKey
HAVING count(s.EventDate) >= 3

You must log in to answer this question.

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