0

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?

  • First of all - the data views you query, only hold data 6 months back. Hence you will never be able to identify consumers active 12 months ago. Also, when you write: "I believe the output is wrong" - can you be more specific in terms of what is wrong? – Lukas Lunow Dec 10 '19 at 8:29
  • Hey Lukas I got the output as 5K subscribers which can't be true as we get a lot more unique opens for a single campaign, so 5K subscribers for 30days seems to be incorrect. – Siva Dec 10 '19 at 8:39
4

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).

  • Thank you, I think I am getting the numbers for parent business unit as well, Is there a way I can get the active subscriber's count for emails sent & opened by subscribes only from child business unit? – Siva Dec 11 '19 at 14:34
  • Hi @Siva - data views only return tracking data for the BU where you run the query in – zuzannamj Dec 11 '19 at 15:06
  • I think this query pulls the total number of unique opens & not the total number of subscribers who opened an email in 30 days, I say this because the numbers for each month is greater than the total subscribers. – Siva Dec 12 '19 at 13:30

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.