5 Replies Latest reply on Nov 18, 2017 11:59 AM by Jonathan Drummey Branched from an earlier discussion.

    Retention Cohort Analysis

    Jatin Kakani

      Hi Jonathan Drummey,

       

      I can see that this is an old thread but wanted to check if you could help me.

      I am doing a retention cohort analysis to see what percentage of merchants activated in a month expired in 0 months after activation , 1 month after activation, and so on.

      However at each point the total number of active merchants should change to the number in previous month less the merchants expired in previous month.

      For example,

      For Jan 2015, there were total 376 active merchants for channel local. 11 of them churned in month 1. So churn percent would be 11/376 = 2.9%
      In month 2, 11 more merchants churned. Now churn percentage should be 11/(376-11) = 3.01%.

       

      I have created dummy data and attached the packaged workbook.
      Could you please check and help?

       

      Thanks.

      Regards,

      Jatin

        • 1. Re: Retention Cohort Analysis
          Jonathan Drummey

          Hi, I have a question - you have hidden the Null value of months_since_activation_exp which does not remove it from the table calculation total of 376. Do you really want 11/376 or 11/336 (where 336 is the number of merchants with the Null value filtered out)?

           

          Jonathan

          • 2. Re: Retention Cohort Analysis
            Jatin Kakani

            Hi Jonathan,

             

            Thanks for replying.

            I want 11/376. I am looking to calculate the percentage of expired merchants over the number of active merchants at that point of time.

             

            Jatin

            • 3. Re: Retention Cohort Analysis
              Patrick A Van Der Hyde

              Jatin Kakani and Jonathan Drummey

               

              I went ahead and branched this question off as a new discussion since it was added to the a question from several years ago.   i also gave it a meaningful title for the question.   This way other members of the community are more likely to see and contribute.

               

              Patrick 

              • 4. Re: Retention Cohort Analysis
                Jatin Kakani

                Thanks Patrick !

                • 5. Re: Retention Cohort Analysis
                  Jonathan Drummey

                  Here's a solution, it's a nice example of when we need to use a custom sort on a table calculation:

                   

                   

                  Screen Shot 2017-11-18 at 2.42.16 PM.png

                   

                  Here's the workout view:

                   

                  Screen Shot 2017-11-18 at 2.42.53 PM.png

                   

                  I'll walk through the calcs I added & changed:

                   

                  date_filter jtd removes the IF/THEN from the date_filter calc you'd built, that is faster for Tableau to process and simpler to right.

                   

                  Instead of using Hide I added a Null months_since filter calculation with the formula NOT ISNULL(LOOKUP(MIN([months_since_activation_exp]),0)). This is a table calculation so it will act like the Hide in terms of the display and since it's a filter it will ultimately end up on the Filters Shelf so we can see it there. It's got a second use as a flag.

                   

                  RS Merchants has the formula RUNNING_SUM([No. of merchants]) and a customized Compute Using that uses a filter on months_since_activation... in Descending order. This acts as the descending total of merchants. NOTE - this running sum depends on the fact that each Merchant ID only has a single months_since... value for each Year & Month of Activation date. If for some reason a Merchant ID could have multiple months_since.. .values for a single Year & Month of Activation date then a different technique would need to be used since the view would then need something closer to a running count distinct and that's not a native function.

                   

                  Null merchants has the formula PREVIOUS_VALUE(ZN(WINDOW_MAX(IF NOT [Null months_since filter] THEN [No. of merchants] END))). The PREVIOUS_VALUE() is there is an optimization, what the rest of the calc does is pad out the No. of merchants value of the Null months_since... to the rest of the months_since... values so we can add that to the running sum. This has a compute using on months_since....

                   

                  Total merchants jtd has the formula [RS merchants] + [Null merchants].

                   

                  Finally, churn percent jtd has the formula [No. of merchants]/[Total merchants jtd].

                   

                  I then duplicated the workout view and moved pills around to build the crosstab, heatmap, and lines views.

                   

                  Jonathan