0

I need a query to count the number of active subscribers on specific dates, January 31, February 28, etc. throughout the year into a data extension. I know this will be using the _subscribers data view. Does anyone know how to structure this query to pull count on a specific date?

1
  • 3
    I removed my answer, since i re-read your question, there is not a way to determine number of subscribers active on a certain date, you the datview has a joindate and an unsub date
    – EazyE
    Mar 21, 2022 at 16:41

1 Answer 1

1

I don’t see an easy way to get historic data you’re looking for using SQL in Marketing Cloud. Maybe it would be possible if you download all your subscribers and analyze it in Excel?

Anyway, if you need this for future as well, you’d have to start logging it first to be able to use that data later. Start with a daily query to write the daily count of subscribers into a simple DE and once you start gathering data, you will be able to further use it and analyze it.

SQL query:

SELECT 
COUNT(SubscriberId) AS NumberOfSubscribers
FROM _subscribers
WHERE Status = ‘Active’
GROUP BY Status

Data Extension:

  • Field 1, “NumberOfSubscribers”, type: number, non-nullable
  • Field 2, “Date”, type: date, non-nullable, default value: today’s date

You need to set this up in Automation Studio and schedule to run on a daily basis.

You must log in to answer this question.

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