0

I have a data issue where we have duplicate emails under different Subscriberkey. I'm trying to build a query now to check how many of this duplicate emails have received the same email multiple times. I have a Data Extension that looks like this so I have all the duplicated emails.

Email Address      SubscriberKey
[email protected]    1234567890
[email protected]    0987654321
[email protected]    4567890123

I wrote this query to return a list of all the emails that the email address has received more than once, along with the count of those sends but I'm getting 0 results and can't figure out where the issue is.

    SELECT
    s.SubscriberKey,
    s.JobID,
    s.BatchID,
    s.TriggererSendDefinitionObjectID,
    COUNT(*) AS NumSends
FROM
    _Sent s
INNER JOIN
    _Job j
ON
    s.JobID = j.JobID
WHERE
    s.EventDate >= '2022-12-01'
    AND s.SubscriberKey IN (SELECT EmailAddress FROM [DuplicatesDE])
GROUP BY
    s.SubscriberKey,
    s.JobID,
    s.BatchID,
    s.TriggererSendDefinitionObjectID
HAVING
    COUNT(*) > 1
1
  • 1
    I think you're selecting the wrong field from the DuplicatesDE?
    – Ted Chan
    May 8, 2023 at 1:52

1 Answer 1

0

It looks like the IN statement should be pulling SubscriberKey instead of EmailAddress? Based on the layout of the Data Extension you provided with EmailAddress and SubscriberKey fields.

AND s.SubscriberKey IN (SELECT EmailAddress FROM [DuplicatesDE])

Use this instead?

AND s.SubscriberKey IN (SELECT SubscriberKey FROM [DuplicatesDE])

You must log in to answer this question.

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