0

I am trying to get a count of all subscribers (irrespective of BU) - hence I am running the query in parent BU who received ONLY 1 email in previous month.

This is my query but it is not returning any count back - any ideas what is wrong with the query / logic?

select 
'1' AS ID_Tag,
left(datename(month, dateadd(month,-1,getdate())), 3) AS Month_Tag,
count(DISTINCT s.SubscriberID) as EmailSent_Count
from 
_job j
inner join
_sent s
on j.jobid = s.jobid
WHERE DATEDIFF(month, cast (s.eventdate as date), GETDATE()) = 1
and j.suppresstracking = 'False'
/*group by s.SubscriberID */
having count(distinct j.JobID) = 1

Goal is to get 1 row like this:

1 Mar XXXX

UPDATE - Ok, so realizing that _job data view is local to BU, I have updated the query logic and it seems to be working:

select 
'1' AS ID_Tag,
left(datename(month, dateadd(month,-1,getdate())), 3) AS Month_Tag,
count(cnt.unique_subs) as EmailSent_Count
from
(
    SELECT DISTINCT s.SubscriberID as unique_subs
    from 
    _sent s
    WHERE DATEDIFF(month, cast (s.eventdate as date), GETDATE()) = 1
    group by s.SubscriberID
    having count(s.JobID) = 1
) AS cnt

Just looking for some feedback if my logic is accurate?

Goal is to get a count of subscribers who received ONLY 1 email (irrespective of BU):

3
  • _job dataview only shows that BU data
    – EazyE
    Apr 15 at 14:57
  • In that case should I not join with job? How can I get count of all subscribers who received 1 email only in last month (irrespective of the BU)?
    – kuml2
    Apr 15 at 15:22
  • hi @EazyE I have updated my query logic - can you please review and provide feedback if my new logic is correct?
    – kuml2
    Apr 15 at 17:26

0

Your Answer

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

Browse other questions tagged or ask your own question.