Count the number of total newsletter subscribers per month

Question

I'm banging my head for several hours now, trying to figure out how I could count the number of total newsletter subscribers per month using Redshift.

Base for the calculation is a events table that tracks every user action, specifically wether he subscribes or unsubscribes from the newsletter. Simplified it looks like this:

+----------------------+---------+---------------+
| timestamp            | user_id | action        |
+----------------------+---------+---------------+
| 2017-01-01T12:10:31Z |       1 | subscribed    |
| 2017-01-01T13:11:51Z |       2 | subscribed    |
| 2017-01-01T13:15:53Z |       3 | subscribed    |
| ...                  | ...     | ...           |
| 2017-02-17T09:42:33Z |       4 | subscribed    |
| ...                  | ...     | ...           |
| 2017-03-15T16:59:13Z |       1 | unsubscribed  |
| 2017-03-17T02:19:56Z |       2 | unsubscribed  |
| 2017-03-17T05:33:05Z |       2 | subscribed    |
| ...                  | ...     | ...           |

For every month I like to sum up the number of users that subscribed to the newsletter plus the number of users that were already subscribed and didn't unsubscribe. In the example above we would have 3 users in January, another one is added in February for a total of 4 subscribers. Then in March we lose one user while another one only unsubscribes temporarily. The number of total subscribers we have in March is 3.

The end result I'm looking for is something like this:

+------------+-------------+
| month      | subscribers |
+------------+-------------+
| 2017-01-01 |           3 |
| 2017-02-01 |           4 |
| 2017-03-01 |           3 |
| ...        |         ... |

Any ideas if and how this would be solvable using an SQL query (preferably working in Redshift or Postgres)?

Answer 1

You can use a recursive CTE to create each of the desired months. Then match subscriptions with unsubscriptions (another CTE for simplicity). Note the lateral join used to pick the top 1 matching unsubscribe. Finally, get the count of distinct user_id's for each month.

This is Postgres. Here is the SQL Fiddle where you can run this, adjust the data set, etc.

WITH RECURSIVE months(start_date, end_date) AS (
    select 
      timestamp '2017-01-01',/*change this date to adjust range*/
      (date_trunc('MONTH', timestamp '2017-01-01') + INTERVAL '1 MONTH')::DATE/*change this date to adjust range*/
  UNION ALL
    SELECT 
      start_date + interval '1 month',
      (date_trunc('MONTH', start_date + interval '1 month') + INTERVAL '1 MONTH')::DATE
    FROM 
      months 
    WHERE 
      start_date < timestamp '2017-12-01' /*change this date to adjust range*/
),

subscription_months(start_date, end_date, user_id) as(
  select
    months.start_date::DATE,
    months.end_date,
    initial_subscription.user_id
   from
     subscription initial_subscription
     left join lateral ( 
       select
         cancellation.timestamp
       from
         subscription cancellation
       where
         cancellation.user_id = initial_subscription.user_id
         and cancellation.timestamp >= initial_subscription.timestamp
         and cancellation.action = 'unsubscribed'
        order by
         cancellation.timestamp asc
        limit 1 
     ) as cancellation on true
     inner join months on
       initial_subscription.timestamp <= months.end_date
       and (
           cancellation.timestamp is null
           or cancellation.timestamp >= months.end_date
       )
    where
      initial_subscription.action = 'subscribed'
 )

 select 
   start_date,
   end_date,
   count(distinct user_id)
 from 
   subscription_months
 group by
   start_date,
   end_date
 order by 
   start_date

Answer 2

This seems to require bunch of joins that could take a long time to converge depending on your table size. If space was not an issue and these type of queries were frequent, I would add a third column with a (binary) flag that shows latest action which you can filter on. My attempt: SQL Fiddle

-- get starting month
WITH start_month AS(
  SELECT MIN(CAST(DATE_TRUNC('month', ts) AS DATE)) AS earliest
FROM test
),
-- bucket each date into months
month_buckets AS(
  SELECT CAST(DATE_TRUNC('month', ts) AS DATE) AS month_bucket
  FROM test
  GROUP BY 1
),
-- for each month bucket, find all actions taken by each user upto that month
master AS (SELECT mb.month_bucket, user_id, actions, ts
FROM month_buckets mb
LEFT JOIN test
ON CAST(DATE_TRUNC('month', test.ts) AS DATE) <= mb.month_bucket
)
-- for each user, get the latest action and timestamp
-- group by month_bucket, count
SELECT m1.month_bucket AS month, 
    COUNT(m1.user_id) AS subscribers
FROM master m1
JOIN (SELECT month_bucket, user_id, MAX(ts) AS ts
    FROM master
    GROUP BY 1, 2
    ) m2
ON m1.month_bucket = m2.month_bucket
AND m1.user_id = m2.user_id
AND m1.ts = m2.ts
AND m1.actions = 'subscribed'
GROUP BY 1
ORDER BY 1;

Answer 3

The solution is:

1) create a calendar table that stores dates (a row in the table is a unique date), see more info in this question. This is very handy for most of BI queries.

2) write the query that consists of the following steps:

2a) based on subscribe/unsubscribe events, construct time ranges of subscribed status for every user (first identify the next event for every given event using lead function and pick up the necessary pairs). If the user has only one subscribe event, set date_to to current date using coalesce.

2b) join these ranges to the calendar table so a row is a date/user

2c) count the rows using one or another method (unique ID, average daily, first date of month, last date of month)

the query would look like this:

with
next_events as (
    select
     user_id
    ,"timestamp"::date as date_from
    ,action
    ,lead(timestamp) over (partition by user_id order by timestamp) ::date as date_to
    ,lead(action) over (partition by user_id order by timestamp) as next_action
    from your_table
    where action in ('subscribed','unsubscribed')
)
,ranges as (
    select 
     user_id
    ,date_from
    ,coalesce(date_to,current_date) as date_to
    from next_events
    where (action='subscribed' and next_action='unsubscribed')
    or (action='subscribed' and next_action is null)
)
,subscriber_days as (
    select 
     t1.user_id
    ,t2.date
    from ranges t1
    join calendar t2
    on t2.date between t1.date_from and t1.date_to
)
-- use whatever method needed to identify monthly N from daily N (first day, last day, average, etc.)
-- below is the unique count
select
 date_trunc('month',date) as date
,count(distinct user_id) as subscribers
from subscriber_days
group by 1
order by 1

Answer 4

the total number of subscribed users is:

select count(*) 
from
(
  select distinct id
  from subscribers
  group by id
  having count(*) in  (1, 3, 5...) -- here you can use a table function to return odd numbers
) a

the number of subscribed in some period of time:

select count(distinct a.id) 
from
(
  select distinct id
  from subscribers
  group by id
  having count(*)  in  (1, 3, 5...) -- here you can use a table function to return odd numbers
) a join
 subscribers s on a.id = s.id
where timestamp between @date1 and @date2

note: i didn't try it in Redshift or Postgres