Heap SQL: Retention
This query calculates session to session retention on a monthly basis. Each user cohorted based on the month of their join date, and is counted in the total retained on a monthly basis if they have a session during each month.
In this graph, each user is assigned to a cohort based on their join date. Each cohort is represented by a different color (e.g. the users who joined in July 2015 ar in teh 2015-07 or orange cohort). It then traces the percentage of users who have at least one additional session in the following months. If we continue to look at the
orange cohort this graph shows us that .16 or 16% of users came back in July and had an additional session, .06 or 6% of users came back in August for an additional session, .04 or 4% came back in September and so on.
You can compare different cohorts by adjusting the query to contain the distance from the join date rather than the month.
This retention report cohorts users in the same way as the previous retention report; the
orange cohort is still mapped to users who first joined in July. However, rather than mapping the percentage of retained to a particular month, this queries uses relative time to track retention across periods (same month as join date, one month later, two months later, etc.). It then stacks each cohort to allow you to analyze how join date is correlated with retention. If you look at Month 0 you will see that users who joined July 2015 have a retention rate of 16% during that first month, where as users who joined in February 2016 have a 12% retention rate.
There is also a slight caveat in this view. You will notice that several cohorts have 0% retained users. In some cases this is because there is no data five months after May 2016.