members table is a way to group together recipients for fine tuning your email campaigns and it is a sub-resource of lists table.Question
What is my email subscriber list growth? What is the rate at which my email list is growing over time?
Email list growth as an important metric. A way to measure the cumulative and monthly growth of an email subscriber list is the following.
Schema
Blendo will sync all of our Mailchimp data into your data warehouse properly re-modeled for it and ready to be used for our analytics purposes. Check this post about the resources Mailchimp exposes and should be replicated on our database, in case you want to dig in more.
List Members
Output
SQL Query
Email List Growth
select sub.*, sum(count) OVER (ORDER BY sub.timestamp_opt)
from (
SELECT date_trunc('month', timestamp_opt) AS timestamp_opt, count(*) AS count
FROM mailchimp_list_members
WHERE (status = 'subscribed'
AND CAST(extract(year from timestamp_opt) AS integer) = CAST(extract(year from now()) AS integer))
GROUP BY date_trunc('month', timestamp_opt)
ORDER BY date_trunc('month', timestamp_opt) ASC ) sub
