10 Replies Latest reply on Feb 2, 2018 1:06 AM by Yuriy Fal

    Calculate retention rates

    Stefano Paratore

      To calculate retention rates on an account basis I would like to look up account names in the previous and following month for any given month. The question I want to answer is how many (and perhaps which) accounts can be found in the following and previous month.

      Going a step further it would be great if I can identify how many accounts which have a contract end date in a certain month are still found in the following month. For example an account that has a contract end date of 6/30/2016, can that account be found in July 2017? Which means we retained that account.

      Attached you will also find the Excel sheet that would be the data source for Tableau. It is a list of all the accounts in each month and their contract end dates.

      In Sheet 2 of the Tableau file you will find one account for example that can be found in all the months but in Sheet 3 for example an account that we "lost" in March.

      I am using Tableau 10.1.3 Professional Desktop Edition.

        • 1. Re: Calculate retention rates
          Shinichiro Murakami

          Hi Stefano,

           

          Not sure I understand your request well or not, anyways.

           

          [Flag Retained]

          if {fixed [Account + End]:min(date([Contract EndDate]))}

          <={fixed [Account + End],[Month]:min(date(dateadd('month',-1,[Month])))}

          then "Retaied" else "Lost" end

           

          // There are couple of accounts which has multiple end dates, so make the line item unique I created this field

          [Account + End]

          [Account Name]+"---"+str(date([Contract EndDate]))

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Calculate retention rates
            Jim Dehner

            Hi

            Attached is a Tableau 10.1.3. workbook

             

            Does this approach make sense to you

            1 - create a calculated field based on date difference

            2 use date diff > 0 as under contract and <0 as lost

            3 you can then use a logical filters and or parameters based on when the customer is under contract

             

            Let me know if this worked for you

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Calculate retention rates
              Stefano Paratore

              Hi Jim,

               

              that was helpful so far. Thank you!

              I do have a couple of questions now though. I would like to get a percentage of the still active accounts for each month.

              So to make it more clear:

              For each month I want to count the number of accounts that have an ending contract date in that month, and then of those how many do I still find in the following month? Meaning they have resigned/extended a contract?

              Secondly, I would like to count the number of accounts that are new and don't already exist the month before.

              Lastly, is it possible to make these calculations flexible. So that you can look at it from a year to year, quarter to quarter or month to month basis?

               

              Sorry but I am still new to Tableau and its capabilities

               

              Best,

              Stefano

              • 4. Re: Calculate retention rates
                Yuriy Fal

                Hi Stephano,

                 

                Using the Account 630 as an example,

                could you please explain the logic of marking

                an Account as New / Retained / Lost ?

                 

                It's still unclear to me how to bin the Accounts

                having Null or epoch date as their Contract EndDate.

                 

                With that I would like to help.

                 

                Yours,

                Yuri

                 

                PS Some time ago I've published the workbook in TWL:

                Cohort Analysis : Reactivation, Retention, Churn

                 

                The logic explained there is straightforward,

                though the implementation is not so :-)

                • 5. Re: Calculate retention rates
                  Stefano Paratore

                  Hi Yuri,

                   

                  sorry about that. I thought I had cleaned up the data first. Anyway, now every account should have a correct contract end date.

                  In the "All accounts" sheet of the attached file I have filtered on Account 630. In that specific case I would count Account 630 to the retained accounts for the months of Feb16 and Mar16.

                  Then for Apr16 it counts towards the lost accounts.

                  In Jul16 it counts as a new account and thereafter again as a retained account. (This is if you look at it from a month to month perspective)

                  Basically what I am asking is whether or not you can look up an account in the previous month.

                  If you find it, it counts towards the number of retained accounts if not then it counts towards lost accounts. That way there you could then divide these numbers by the total number of accounts in the previous period and calculate the percentage of accounts retained vs. lost.

                  To try to make it more clear:

                  Let's say we have 100 accounts in Jan16 that have their contract end date in the month of Jan16. Of these 100 accounts how many do I find in Feb16? If I can find 60 of those accounts again in Feb16 I would calculate 60% retained accounts and 40% lost.

                   

                  Ideally this whole procedure would be flexible, meaning you can calculate it from year to year (e.g. Jan16 to Jan17), quarter to quarter (e.g. Q1 2016 to Q1 2017 or even Q3 2016 to Q4 2016) and of course from month to month.

                  • 6. Re: Calculate retention rates
                    Yuriy Fal

                    Hi Stephano,

                     

                    I've applied the logic from my Cohort Analysis wb to your data.

                    Please find the attached.

                     

                    I'm still not sure how the [Contract EndDate] field

                    should be taken into account.

                     

                    If an Account record is found in the current period --

                    regardless of whether [Contract EndDate] is due or not --

                    the Account could be easily evaluated based on

                    the current, previous & next period records.

                     

                    Please explain.

                     

                    Yours,

                    Yuri

                    • 7. Re: Calculate retention rates
                      Stefano Paratore

                      Hi Yuri,

                       

                      wow, this is amazing but also a bit overwhelming for me. I love the parameter Period

                      Maybe I just need a little more time to fully understand all your steps and calculations. I am also not so familiar or not so sure if I understand the terms Churn, Retained, Reactivated, First and Last correctly.

                       

                      To answer your question, you are right that you could just look for the account name in the current, previous and next period, however if I understand it correctly an account that let's say has an contract end date in Feb16 and you find it in Mar16 will count towards the percentage of retained accounts but so does an account that you find in Feb16 and Mar16 but has an contract end date in Jun16. What I am saying is that in the percentage you are including accounts that had a contract end date of Jun16 all along as well as the accounts that had an contract end date in Feb16 but just expanded or resigned.

                      Did I make it clearer now?

                      I'll try to explain in other terms:

                      Let's say you have an account that has a contract end date of Jun16 and one account that has a contract end date in Feb16 and both can be found in the months of Feb16 and Mar16.

                      Looking at the accounts from Feb16 to Mar16 both accounts would be part of the calculations of retained accounts since both of them appear in Feb16 and Mar16. But the interesting difference between the two accounts is that the one with the Feb16 due date obviously resigned or extended their contract (otherwise it would not show up in Mar16 anymore) where as the one with the Jun16 due date no action has been done. Nothing happened to that account since it is still an active account.

                      So the question that I will need to answer (in addition to what we already have) is, how many of the accounts that are going to expire in any given month are we resigning?

                       

                      I hope I made it more clear. I will also continue looking at your workbook and let it sink in...

                       

                      Thanks so much!

                      Stefano

                      1 of 1 people found this helpful
                      • 8. Re: Calculate retention rates
                        Yuriy Fal

                        OK, then it is just a matter of filtering, imho.

                        So I would filter only those Accounts which have

                        the [Contract EndDate] within the [Period].

                         

                        Please find the attached (with modifications).

                         

                        Yours,

                        Yuri

                         

                        PS Churn means Lost (Account). It's a slang.

                         

                        The key point in my calculation logic is

                        that the Churn recognition occurs

                        in the current Period -- when the row

                        for the churned Account still exists.

                        With that one could trace each Account directly.

                         

                        That's why First and Last Periods should be specified

                        for Reactivation and Churn, respectively.

                        One could not calculate Reactivation Rate in the First

                        and Churn Rate in the Last Period, respectively.

                         

                        Last but not least, the core calculations here are FIXED LODs,

                        so they're calculated "outside" of the view (ignoring VizLOD).

                        If you'd like to restrict the scope (such as set [Period] range)

                        you should make all those "scope" filters the Context ones.

                         

                        I understand that the topic is rather complicated.

                        If you have any questions, please reply.

                        1 of 1 people found this helpful
                        • 9. Re: Calculate retention rates
                          Brad Maxwell

                          How would you modify this to calculate Churn the opposite way?

                           

                          For example, if an Account is active in September, October, November, but not in December, the Churn would be counted on December.  Seems the current workbook counts the Churn in November.

                          • 10. Re: Calculate retention rates
                            Yuriy Fal

                            Hi Brad,

                             

                            For that you wouldn't change the calc logic,

                            but rather create a modified date like this:

                             

                            DATEADD('month', 1, DATETRUNC('month', [your_date]))

                             

                            and nd use it on a view instead of the original [your_date].

                             

                            Hope it helps.

                             

                            Yours,

                            Yuri