1 Reply Latest reply on Aug 9, 2017 9:12 AM by Manuel Velasquez

    Retention rate calculation for subscription business

    Konrad Szkutak

      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 1st month, 2nd 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.

       

      1st active month of January acquisitions is January

      2nd active month of January acquisitions is February

      1st active month of February acquisitions is February

       

      If I simply divide number of users from the 2nd month by 1st 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.

        • 1. Re: Retention rate calculation for subscription business
          Manuel Velasquez

          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