8 Replies Latest reply on May 22, 2019 6:44 AM by Bhanupriya Purohit

    Retention Ratio Calculation

    Bhanupriya Purohit

      Hi,

       

      I am using tableau to present retention day wise retention ratios.

       

      For sample dataset, I have managed to get days since registration in my dataset and created that as dimension in tableau. Then I was able to plot how many users under day 0, day 1 etc using CNTD for user_id. That works fine.

       

       

      Next I need to calculate ratio (i.e how many users could have logged in possibly).

       

      For example, if we are looking for day 2 ratio, then all users who have value 2 or higher in measure Date diff from Today should be used as divider. I need to perform this for each day (Day since Reg on X axis).

       

      Problem I faced with this that when I drop this dimension on sheet, it act like a filter. Is there any way I can define a condition in measure that for each column, it use column value and then check in whole data set for count of users who have days since registration >= current column value.

       

      In attached sheet I have used a fixed value for measure Users could participate as 20. Basically I would like it to be dynamic, relative to the column as mentioned above.

       

      Thanks

        • 1. Re: Retention Ratio Calculation
          Sankarmagesh Rajan

          Hi Bhanu

          I have changed your calc into

           

          COUNTD([User Id]) / total(COUNTD([User Id]))

           

          total(COUNTD([User Id])) will give overall total users.

           

           

          Thanks & Regards

          Sankarmagesh

          9894170397

          BI,Aspire Systems

          • 2. Re: Retention Ratio Calculation
            Bhanupriya Purohit

            Hi,

             

            Thanks for your reply but total(COUNTD([User Id])) will give me overall totals all the time, I want only those users who could participate, for eg. I added two more users in data set, in that one registered yesterday and another one registered today, user who registered yesterday can't participate for day2 to day 5 divisor because for that users these days are future day same for user who registered today can't participate for day 1 to day 5. So result should be
            Day 0 = 22/22

            Day 1 = 14/21

            Day 2 = 11/20

            Day 3 = 08/20

            Day 4 = 04/20

            Day 5 = 02/20

             

             

            • 3. Re: Retention Ratio Calculation
              Zhouyi Zhang

              Hi, Bhanupriya

               

              Can you help understand how is 14/21 being calculated? I understand 14, but don't understand where the 21 come from?

               

              ZZ

              • 4. Re: Retention Ratio Calculation
                Bhanupriya Purohit

                Hi ZZ,

                 

                Look at the sample table attached, it basically how many user come on a particular day and divide by how many could have come on that day. N/A means that the difference b/w user's signup date and current date is less than that day. For eg look at User7 and you see that he signup on today (May 22) and therefore can't have returned on his Day 1 (Day 0 + 1 day) since that is the future whereas User5 could have returned on his Day1 because his signup was 2 days ago.

                 

                Hope this answers your question.

                • 5. Re: Retention Ratio Calculation
                  Zhouyi Zhang

                  Hi, Bhanupriya

                   

                  Please find my solution attached.

                   

                  Below is the sample output

                   

                  the calculation is complicated, but have a look first and if you have question, please do come back and ask, I will explain more details.

                   

                  ZZ

                  • 6. Re: Retention Ratio Calculation
                    Bhanupriya Purohit

                    Hey ZZ,

                     

                    Really thanks for the work, I should have been more clear about my problem. I also was able to get the retention like you showed but my problem is when I applied filters. Lets say from the dataset above that 60% of signups are male, spread randomly among a signup days and 40% are female and I want to compare the retention of male v/s female e.g when I apply a Gender filter the results are strange. I get 157% retention for male on Day0. This is the problem that is really crushing my head.

                     

                    I would appreciate any help you can give.

                     

                    Thanks,

                    Bhanupriya

                    • 7. Re: Retention Ratio Calculation
                      Zhouyi Zhang

                      Hi, Bhanupriya

                       

                      Please find my updates below to solve your issue about the filter

                       

                      If this solves your question, please don't forget to mark my answer either helpful or correct to close the thread.

                       

                      ZZ

                      1 of 1 people found this helpful
                      • 8. Re: Retention Ratio Calculation
                        Bhanupriya Purohit

                        Hi ZZ,

                         

                        First of all thank you very much for your help, it works perfectly when I added just a gender filter but we are not there yet. When I added Reg Date filter too I got the results shown in the chart. If you look at Day1 you see it is shows 11 users as the possible returnees when in reality all 12 users could have return on that day so the divisor should be 12 not 11 (because for all filtered users difference b/w reg date and today's date > 1).

                         

                        I am also trying to figure this out but if you could take a look at this I would appreciate it.

                         

                        Again thanks for your help.