I would like to manage the commercial pressure in SFMC and to set a query that feed a DATA extension.

This DE will be used as exluded data extension when sending the email.

So some question :

1-is there a query for subscribers who receive an email in the last 7 days for one BU and alsso a query for all Business unit?

2- I set this query but marketing cloud doesn't recognize this syntax (issue with the dd - DAY in the query):

      s.EventDate AS Sent_Date,
      'False' AS Not_Opened
    FROM _Sent s
    LEFT JOIN _Open o
    ON s.SubscriberKey = o.SubscriberKey
      WHERE o.SubscriberKey IS NULL 
            CAST(s.EventDate AS DATE) >= CAST(DATEADD(dd, -60, CURRENT_TIMESTAMP) AS DATE) 

Thanks for your help


To answer your first question, there is not the capability to query each BU's _Sent data view from a single location.

What I would do is have each BU have a scheduled repeatable query that pulls the sent emails for the past 7 days and have them all put into a shared DE. I would then do a UNION query to pull all of these DEs into a single AllSentDE.

You then use this shared DE as your exclusion list across all BUs.

As to your second question, For DateAdd(), dd should be day, dy, y to mean Day (ref).

Also CURRENT_TIMESTAMP should be GETDATE() to gather the current server date/time.

  • Thanks @Gortonington for your answer. So if a make a query from the parent Bu it does'nt query all send view in the other Bu? So, if i understand the send view and open and the others ones are specially restricted to the current Bu? – Ab Fly Dec 18 '18 at 17:37
  • Correct. Only certain data views, like _Subscribers provide enterprise level info. Clicks, opens, sent etc. Are BU level only – Gortonington Dec 18 '18 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.