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?