0

Sorry in advance if the question is repeated, but i have a goal that i want to know how many emails the subscribers from a specific DE received in last and each month.

I tried to use the sintax below, but the numbers of records that the SQL is get it is very different to the number of total sent;

The primary key in target De is Month and SubscriberKey to get the number each month send for subscribers

SELECT x.*
FROM (
SELECT count(*) AS TotalSent, 
DATENAME(month, p.EventDate) as [MONTH],
row_number()over(partition by p.SubscriberKey ORDER BY P.EventDate DESC) AS row,
'XX' as [Business_Unit],
p.SubscriberKey as SubscriberKey
FROM _Sent p 
INNER JOIN [Clients_XX] B ON p.SubscriberKey = B.AccountID
WHERE p.EventDate BETWEEN DATEADD(day, -365, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
GROUP BY p.EventDate, p.SubscriberKey
HAVING COUNT(*) > 0
)
x
WHERE x.row = 1
3
  • First thing the _sent data view only has 6mths worth of data, which would be Jan 23 to today – EazyE Jul 23 '19 at 21:18
  • Yes, but this could occasionated this numbers so different? – Joao Marano Jul 23 '19 at 21:24
  • try doing the group by on DATENAME(month, p.EventDate) – Maneesh Jul 24 '19 at 3:19
1

I don't think you need the ROW_NUMBER function, the issue's with your grouping as p.EventDate is a time stamp (down to the millisecond). You'll get one row returned in your query for the most recent send to each Subscriber, effectively.

Try this:

SELECT
    COUNT(1) AS TotalSent, 
    DATENAME(month, p.EventDate) as [MONTH],
    'XX' as [Business_Unit],
    p.SubscriberKey as SubscriberKey
FROM
    _Sent p INNER JOIN
    [Clients_XX] B ON
        p.SubscriberKey = B.AccountID
GROUP BY
    DATENAME(month, p.EventDate),
    p.SubscriberKey
HAVING COUNT(1) > 0

As EazyE points out, your WHERE clause doesn't hurt, particularly, but it's redundant... you only have the last six months of data available in the _Sent data view, in any case.

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.