ExactTarget SentData View: Sent query failing

Question

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?

Answer 1

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.

SELECT OYBAccountID, count(SubscriberKey) as BounceCount 
FROM _Bounce 
WHERE AccountID = '6270127' AND BounceCategory = 'Hard bounce'
GROUP BY OYBAccountID

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.