Hi all,
I work for a company that offers a monthly subscription for their product.
One of the measures we use is Active_month which tells you how many month a user has been already with us. We use Active_month to calculate retention rate between months i.e how many users (%) stay with us after the 1^{st} month, 2^{nd} month and so on.
To calculate the retention rate I created the below table that counts user id’s in each active month and the idea is to calculate retention rate by dividing number of users from month 2 by number of users from month 1 and so on.
However, there is one thing that I cannot handle. Let’s imagine that I want to calculate retention rate of acquisitions from January 2016 and February 2016 altogether.
1^{st} active month of January acquisitions is January
2^{nd} active month of January acquisitions is February
1^{st} active month of February acquisitions is February
If I simply divide number of users from the 2^{nd} month by 1^{st} month I will divide January acquisitions that stayed for February by (January acquisitions + February acquisitions) which is not the right way to calculate retention rate. I can’t really calculate it month by month as we have already 40+active months in the company therefore I need to find a way that will calculate all months at the same time.
I’m attaching my table layout and the formula I use. Does anyone have any idea how I could proceed in this case? The first number is count of users, the other one is retention rate%(wrongly calculated). The formula is COUNTD([user_id])/LOOKUP(COUNTD([user_id]),-1) calculated across Activemonth.
Hi Konrad
As I understand to calculate retention rate we need three pieces
Retention Rate = ((CE-CN)/CS)) X 100
CE = number of customers at the end of period
CN = number of new customers acquired during period
CS = number of customers at start of period
Is this the formula you are using?
Also, it would be easy to help you if you provide a mock up data and a .twbx file, telling us what the expected result is
Manuel Velasquez