0

My goal is create a Data Extension in the Parent BU with a column for my 5 BUs where the record will populate by the total number of sent last 30 days from each BU i have.

I utilize only a sender profile if it's help anyone to know how i can do this.

I thinks i can SQL, but how can i segment for each BU?

EDIT: I tried to used this sintax below, but the same number goes to all fields. How i can solved this?

My thought the arquitecture it's like the image attached

SELECT COUNT(CASE WHEN AccountID ='100000001' THEN 1 ELSE 0 END) as [PARENT_BU], 
 COUNT(CASE WHEN AccountID = '100000002' THEN 1 ELSE 0 END) AS [BU1],
COUNT(CASE WHEN AccountID = '100000003' THEN 1 ELSE 0 END) AS [BU2],
COUNT(CASE WHEN AccountID = '100000004' THEN 1 ELSE 0 END) AS [BU3],
DATENAME(month, PickupTime) AS [MONTH]
FROM _Job
WHERE PickupTime BETWEEN GETDATE()-38 AND GETDATE()
GROUP BY DATENAME(month, PickupTime)

enter image description here

2

You will need to create an automation on your top level business unit using the Data View: Sent

https://help.salesforce.com/articleView?id=mc_as_data_view_sent.htm&type=5

Let's say for this example, you have 3 business units.

  • 10000001 <-- Parent BU
  • 10000002 <-- Child BU
  • 10000003 <-- Child BU

You can use the following SQL below to accomplish what you are needing.

SELECT SUM(CASE WHEN s.AccountID = 10000001 THEN 1 ELSE 0 END) AS [10000001]
      ,SUM(CASE WHEN s.AccountID = 10000001 THEN 1 ELSE 0 END) AS [10000002]
      ,SUM(CASE WHEN s.AccountID = 10000001 THEN 1 ELSE 0 END) AS [10000003]
      ,DATENAME(month, s.EventDate) AS [Month]
FROM _Sent AS s WITH (NOLOCK)
WHERE s.EventDate = DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, -2, GETDATE())), 0)
GROUP BY DATENAME(month, s.EventDate)

Data Views contain 6 months worth of data. I added a function in the WHERE clause to only get data from the past 2 months, max. This way, you sent summary does not get overwritten by data that drops off of the view

  • Hello, @Spencer. So thanks for your help and understand the sintax. But i have a problem with the WHERE clause: "An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'WHERE'." What you think to solve this? – Joao Marano Jul 3 at 18:15
  • Move the GROUP BY expression to the end of the query. That should resolve your problem – Rachid Mamai Jul 3 at 19:02
  • Great catch, @RachidMamai. I've made that edit. – Spencer Curtis Jul 3 at 19:43
  • @SpencerCurtis, RachidMamais, unfortunately, it's not working, not populated the DE. I tried to use Count(Case WHEN..) and change Event Date and the only changes that happen it's the same number for the all fields. – Joao Marano Jul 4 at 17:50

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.