0

I have a query which pulls information about unique openers in the last 30 days, but I would like to find this info for each month from the last 6 months.

Here's the query I wrote

Select o.subscriberkey from _open o
join _job j
on j.jobid = o.jobid
where o.eventdate > dateadd(d,-30,getdate())
and j.AccountID = ######

So I would like to get an output like this,

Jan 20 - XXX Dec 19 - XXX Nov 19 - XXX Oct 19 - XXX Sep 19 - XXX Aug 19 - XXX

Can someone show me how to do this date filtering?

Thanks, Siva

1
  • you want just the count, not the actual subscribers, right? Commented Jan 16, 2020 at 13:58

2 Answers 2

2

The below should give you what you need. Be careful though as depending on the volume of records in the Open DV, this can time out.

SELECT FORMAT(GetDate(), 'MMMM') as Month,
FORMAT(GetDate(), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(GetDate(), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(GetDate(), 'yyyy')

UNION

SELECT FORMAT(DateAdd(m,-1,GetDate()), 'MMMM') as Month,
FORMAT(DateAdd(m,-1,GetDate()), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(DateAdd(m,-1,GetDate()), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(DateAdd(m,-1,GetDate()), 'yyyy')

UNION

SELECT FORMAT(DateAdd(m,-2,GetDate()), 'MMMM') as Month,
FORMAT(DateAdd(m,-2,GetDate()), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(DateAdd(m,-2,GetDate()), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(DateAdd(m,-2,GetDate()), 'yyyy')

UNION

SELECT FORMAT(DateAdd(m,-3,GetDate()), 'MMMM') as Month,
FORMAT(DateAdd(m,-3,GetDate()), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(DateAdd(m,-3,GetDate()), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(DateAdd(m,-3,GetDate()), 'yyyy')

UNION

SELECT FORMAT(DateAdd(m,-4,GetDate()), 'MMMM') as Month,
FORMAT(DateAdd(m,-4,GetDate()), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(DateAdd(m,-4,GetDate()), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(DateAdd(m,-4,GetDate()), 'yyyy')

UNION

SELECT FORMAT(DateAdd(m,-5,GetDate()), 'MMMM') as Month,
FORMAT(DateAdd(m,-5,GetDate()), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
FROM _open
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(DateAdd(m,-5,GetDate()), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(DateAdd(m,-5,GetDate()), 'yyyy')

Basically this iterates through the past 6 months (including current month) via each unioned select query and counts the opens inside of that month.

By utilizing DateAdd with GetDate, this will be future facing and will not need to be edited each time you need a new month.

Sample Output:

Month     |   Year   |  Count
January   |   2020   |  20192
December  |   2019   |  238923
November  |   2019   |  234233
October   |   2019   |  233423
September |   2019   |  343323
August    |   2019   |  543323
0

I think this should work

SELECT
x.SentMonth,
x.EmailName
FROM
(
SELECT
datename(month,s.EventDate) as SentMonth,
1 as Emails_Sent,
j.EmailName as EmailName
FROM
_Sent s
LEFT JOIN _Open o ON o.JobID = s.JobID AND o.SubscriberID = s.SubscriberID AND o.IsUnique = 1
INNER JOIN _Job j ON j.JobID = s.JobID
WHERE s.EventDate between '06/01/2019 00:00:00.000' and '01/31/2020 23:59:00.000'
) x
GROUP BY
x.SentMonth,
x.EmailName

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .