0

I want to get a list of Subscriber that has received 13+ email in July and know their aggregate unique Opens. Here is what I did but there is no result returned.

SELECT 
se.SubscriberKey,
sub.EmailAddress,
se.EventDate
From _Sent se
INNER JOIN _Subscribers sub
ON se.SubscriberKey = sub.SubscriberKey
WHERE se.EventDate BETWEEN '2023-07-01 12:00:00' AND '2023-07-31 23:59:59'
AND se.SubscriberKey IN (
   SELECT sub.SubscriberKey AS SubscriberKey
   FROM _Subscribers sub
   GROUP BY sub.SubscriberKey
   HAVING COUNT (Sub.SubscriberKey) >= 13
)
2
  • are you doing this in a child BU? if so add ent._subscribers
    – EazyE
    Commented Aug 18, 2023 at 20:03
  • I am doing this in the parent BU but in fact I want to have the numbers in a specific BU, should I add AND oybaccountid = 1234567 under the WHERE?
    – Emily714
    Commented Aug 18, 2023 at 21:44

1 Answer 1

0

The logic was correct, you had only the wrong Source DE for the grouping. Since you want to find subscribers who received at least 13 emails then we need to group not _Subscribers Data View but _Sent and then also while grouping we can also apply the timeframe to have more efficient filtering that would give us already fewer records to join with other tables.

SELECT 
se.SubscriberKey,
sub.EmailAddress,
se.EventDate
From _Sent se
INNER JOIN _Subscribers sub
ON se.SubscriberKey = sub.SubscriberKey
INNER JOIN (
    SELECT SubscriberKey
    FROM _Sent
    WHERE EventDate BETWEEN '2023-07-01 12:00:00' AND '2023-07-31 23:59:59'
    GROUP BY SubscriberKey
    HAVING COUNT(*) >= 13
) x
ON x.SubscriberKey = sub.SubscriberKey

Note: it might be a heavy SQL Query and it has a good chance to time out because we use Data Views as a source 3 times. Consider splitting the query into 2 pieces or using preloaded Data Views into Data Extensions for instance.

You must log in to answer this question.

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