1

I want a data extension which has the latest six e-mail sent events from the Sent data view based on Eventdate, only when the subscriber has a minimum of six records in the Sent Data view I want to retrieve them and overwrite them in my target DE.

The outcome needs to be 6 rows for one subscriber with newest 6 events based on Eventdate

I am Struggeling with the query to build this segment. So far:

select COUNT(SubscriberKey) as TotalSent, SubscriberKey, JobID from Sent group by SubscriberKey,JobID HAVING COUNT(SubscriberKey) > 5

Data extension:

enter image description here

Any ideas how I can create my segment? or the functions to use in Marketing Cloud SQL activity.

Rob

1 Answer 1

1

I would do this in two stages. You can try and mash em together in one query, but for clarity's sake, let's keep it in two stages.

SELECT SubscriberKey,COUNT(Subscriberkey)
FROM _sent
GROUP BY Subscriberkey
HAVING COUNT(Subscriberkey) >= 6
/* into "myStagingDE" DE */

These are all your subscriberkeys from _Sent data view - limited to Subscribers with min 6 sends total (in the last 6 months, as that's where the Data view cuts off).

Result will be, e.g. 200 rows.

In other words, everyone with less than 6 sends is out of your stagingDE.

With the resulting DE ("myStagingDE") , you set up an inner Join on the _Sent Data view. Simple, as you want all the people that are in both tables.

Now in order to get the right 6 rows from the resulting cross-checking of two tables, you want to order by date, and limit the result. You can give each resulting row a number using Row_Number() function, and then select only those with rows numbers 1 through 6.

By specifying you want the newest listed first (ORDER BY eventdate DESC) you ascertain you get the right records.

SELECT q.SubscriberKey, q.EventDate
FROM (
    SELECT 
    sent.Subscriberkey,sent.EventDate,
    ROW_NUMBER() OVER(Partition by sent.SubscriberKey ORDER BY sent.Eventdate DESC) AS Row_Number 
    FROM [myStagingDE] staging
    INNER JOIN 
    _Sent sent
    on staging.SubscriberKey = sent.Subscriberkey) q
WHERE Row_Number BETWEEN 1 AND 6

Result will be 200 x 6 = 1200 rows, sorted by user and date, newest at the top.

You can see the "raw" Row_Number() pattern, which is borrowed from deduplication described here: Adding contacts and leads to a DE and receive a Violation of PRIMARY KEY constraint error message when running query

hope this helps!

1
  • Hi Jonas, Thanks for the great explenation. This solved my issue! I used the deduplication method before. Have a nice day.
    – RobSource
    Mar 3, 2021 at 11:34

You must log in to answer this question.

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