cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
Our blog has a post about calculating retention, but this assumes I have a table with user activity from every single day per user, which is, more often than not, not the case. So I put together a version that uses just the start date and end date, and generates the in between:
A: (this equals new_user_activity from the blog)
select subscriptionid, create month

B:
select Subscriptionid, end month

C:
A.subsciption id, A.create month, B.end month from a left join b on A.subsciption id=B.subsciption id

D:
select *, case when c.end month is not null
then datediff('month', start month, end month)
        else datediff('month', start month, dateadd('hour', -6, getdate()))
      end as months_lasted
from C

E: (This generates a series of months, to help us get those "middle" dates)
select
      date_trunc('month', dateadd(month, (-1 * row_number() over(order by true)), getdate()::date)) as n
    from
      subscription
    limit 60

F: (this equals activity in the blog)
select * from D join E on 1=1
where E.n >= D.start_ month
and E.n <= nvl(d.end_month,dateadd('hour',-6,getdate()))

G: (this equals cohort_active_user_count in the blog)
select start month, count(distinct id)
from A
group by 1

H:

select
A.start_month as date
datediff(month, a.start_month, f.n) as period
      , max(g.count) as new_users
      , count(distinct f.subscription_id) as retained_users
      , count(distinct f.subscription_id) / max(g.count)::float as retention
from
      A
      left join F on
     A.subscription_id = F.subscription_id
      and A.start_month < F.n
    left join G on
    A.start_month =G.start_month
group by
  1
  , 2

I: (final query to format as a cohort)
select date
  , new_users
  , period
  , new_users-retained_users as churned_users
  , retained_users
  , retention as retention_percentage
where period is not null 
This uses month over month retention, in Redshift Pseudocode, but you can edit it to use any period, or even the aggregation filter. 

Rate this article:
Version history
Last update:
‎03-02-2023 08:58 AM
Updated by:
Contributors