I have the issue that the customer wants to see an overview of sent email count and bounced count by Business Unit (and they will have thousands).
So I created Queries for that which work fine for _Bounce but they don't for _Sent and I have no idea where the difference would be. Here comes the two queries:
Bounce (works fine):
SELECT OYBAccountID, count(SubscriberKey) as BounceCount
FROM _Bounce
WHERE AccountID = '6270127' AND BounceType = 'Hard bounce'
GROUP BY OYBAccountID
Sent (fails):
SELECT OYBAccountID, count(SubscriberKey) as SentCount
FROM _Sent
WHERE AccountID = '6270127'
GROUP BY OYBAccountID
Any idea?
Best Answer
Actually, the bounce query should include 'BounceCategory', and not 'BounceType' as it does not provide info on Hard or Soft bounces but instead will report on whether the bounce occured as 'delayed' or 'immediate'. The help documentation on bounces illustrates 'BounceCategory' as the field which will record the 5 specific bounce type categories.
As for the SentCount per OYBAcountID, the query looks fine and should work if you use the correct data types for the fields (OYBAccountID and SentCount) in the resultant data extensions. If your query's 'update type' is set to 'overwrite' and number is set as the datatype for both fields, make both fields as 'nullable' in the data extension.