Find active subscribers for each month for last 6 months

Question

I am trying to find out the number of active subscribers who opened at least one email in the last 30 days, I would like to find this info for last 6 or 12 months, to monitor the growth of active subscribers and would like to keep track of the same in the future.

I tried this SQL query below, but I believe the output is wrong

SELECT
    s.SubscriberKey,
    s.EmailAddress
FROM
    [_Subscribers] s
WHERE

s.SubscriberID IN (
        SELECT d.SubscriberID FROM [_Sent] d WHERE EventDate > dateadd(d,-30,getdate())
)

AND

s.SubscriberID IN (
        SELECT o.SubscriberID FROM [_Sent] o WHERE EventDate > dateadd(d,-30,getdate())
)

I also tried this query as below but got the system timeout error :(

SELECT 
  s.SubscriberKey,
  s.EventDate AS Sent_Date,
  'True' AS Opened
FROM _Sent s
LEFT JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
  WHERE o.SubscriberKey IS NOT NULL 
        AND
        CAST(s.EventDate AS DATE) >= CAST(DATEADD(dd, -30, CURRENT_TIMESTAMP) AS DATE) 
        AND
        CAST(s.EventDate AS DATE) < CAST(CURRENT_TIMESTAMP AS DATE)

I would like to create a report something like this

November active subscribers - 10,000
October active subscribers - 9000
September active subscribers - 9,500

Can someone help me with this?

Answer 1

Your first query does not include the _Open data view, so it won't work for your use case.

If you want to display all subscribers who opened an email, you can use the following, bearing in mind what Lukas said - Data Views only hold last 6 months worth of tracking data:

SELECT 
s.SubscriberKey, 
j.EmailName, 
s.EventDate as SentDate, 
o.EventDate as OpenDate
FROM _Open o INNER JOIN _Sent s ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Job as j ON s.JobID = j.JobID

You could then run a second query on the results of the first one to get opens by month:

SELECT
DATEPART(month, OpenDate),
COUNT(OpenDate)
FROM [...]
GROUP BY DATEPART(month, OpenDate)

Breaking this into two queries should also help you to avoid timeouts if you're working with a large amount of data (Best Practices for Data Extensions and Query Activities).