6 Replies Latest reply on Aug 25, 2018 11:30 AM by Shinichiro Murakami

    Need to calculate 30 day rolling retention

    Nikhar Patel

      Hi All,

       

      I have a data-set which is at the below level :

      User : Date : Bookings

       

      Problem statement :

       

      Find out 30 day rolling retention :

       

      Formula : For an given date , Out of the Users having bookings>0 in last 60 days to 30 days , how many have bookings>0 in last 30 days .

       

      Attaching the sample workbook for reference. Please help me out.

       

      Thanks!

      Nikhar

        • 1. Re: Need to calculate 30 day rolling retention
          Aaron Dobbins

          LODs should work here...

           

          First, flag rows where booking date is in last 30-60 days

          [Booking in Last 30-60 Days]

          IF DATEDIFF('day',[Date],TODAY()) > 30 AND DATEDIFF('day',[Date],TODAY()) <= 60 THEN 1 END

           

          Next, flag rows where booking date is in last 30 days

          [Booking in Last 30 Days]

          IF DATEDIFF('day',[date],TODAY()) <= 30 THEN 1 END

           

          [User # of Bookings Last 30-60 Days]

          { FIXED [User] : SUM([Booking in Last 30-60 Days]) }

           

          [User # of Bookings Last 30 Days]

          { FIXED [User] : SUM([Booking in Last 30 Days]) }

           

          [User Has Both Bookings]

          [User # of Bookings Last 30-60 Days] > 0 AND [User # of Bookings Last 30 Days] > 0

           

          With the [User Has Both] field you can filter or categorize them, or put it on color depending on how you want your output to look.

          • 2. Re: Need to calculate 30 day rolling retention
            Nikhar Patel

            Thank you Aaron Dobbins   for the support, really appreciate it.

             

            I think this is almost what I wanted, but there is a challenge here.

             

            The above formula that you helped me with calculates the last 30 days and 30-60 days trips as of today.

             

            However, I need the last 30 days and 30-60 days trips for a user for each date.

             

            Please ignore the values of the last 30 day bookings and last 30-60 day bookings, they are totally random just to improve imagination.

             

            The point I want to highlight from the above table is the part highlighted in "YELLOW".

             

            Lets say if a user is inactive in last 30 days, if he is active in last 30-60 days, then he is a churned user as of that date.

             

            If i take each date and check how many users churned and divide it by users active in last 30-60 days, it would give me a rolling retention curve of the below form :

             

             

            Hope this clarifies my question further.

             

            Looking forward to your response.

             

            Thanks!

            Nikhar

            • 3. Re: Need to calculate 30 day rolling retention
              Aaron Dobbins

              Hi Nikhar,

               

              I am not sure I follow you.  What dates are you comparing when you say "last 30 days" and "last 30-60 days" looking for churn?  Is it from a selected date on a prompt?  The formulas should still work as long as you replace TODAY() with the date you want to calculate from.  If that date is attached to the user in some way, another FIXED calculation to get that date should work.

              • 4. Re: Need to calculate 30 day rolling retention
                Nikhar Patel

                Aaron Dobbins - Basically if you see the chart below :

                 

                 

                It means for each of the dates in the data set :

                 

                Users who were active 30-60 days from the date and were also active in last 30 days from the date.

                 

                For example :

                 

                Retention as of 5-nov = Users active between 5-Sep to 5-oct, out of them users active between 5-oct and 5-nov.

                Like wise for other subsequent dates.

                 

                Churn as of 5-nov = 1 - retention as of 5-nov.

                 

                However in the data set, we don't have an entry for every user for every date, but every distinct date does exist .

                 

                It seems challenging to do with this dataset.

                 

                Hope this clarifies my question further.

                Smeet Patel FYI

                 

                Thanks!

                Nikhar

                • 5. Re: Need to calculate 30 day rolling retention
                  Nikhar Patel

                  Hi All,

                   

                  Need help on this problem statement.

                   

                  Thanks!

                  Nikhar

                  • 6. Re: Need to calculate 30 day rolling retention
                    Shinichiro Murakami

                    Theoretically, it requires multiple layers of table calculation.

                    However, this kind of table calc query requires huge PC memory consumption and it takes really long time or even time out

                    even though I reduced your file size very small like 16,000.

                    (originally it's 500,000)

                     

                    Which means you need to create another data source in between.

                     

                    Anyways as logic.

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                     

                    Thanks,

                    Shin