1 Reply Latest reply on Apr 2, 2016 3:35 AM by G Marc Turner

    Retention Manipulation and Triangle Graph

    Phillip Black

      I'm trying to create the fabeled retention triangle that looks like this.percent_of_engaged_cohort_retention.png

       

       

      This is the closeted tableau example here. Unfortunately that will not connect to Tableau for Desktop for Mac (9.2), so I can not reproduce it. Ultimately, my goal is to create create a retention chart that can be filtered on unique characteristics associated with the Customer ID, like what ad channel brought them in. Any advice would be greatly appreciated.

       

       

      My data is in the attached workbook.

        • 1. Re: Retention Manipulation and Triangle Graph
          G Marc Turner

          Here's one approach that I've used... for illustration I'm using years instead of weeks though to keep the

           

          Start by creating a calculated field that determines the number of years an order is placed since the first order was placed using this formula:

           

          datediff('year',[Customer Acquisition Date],[Order Date])

           

          You can adjust this to months, weeks, etc. by changing the date part in the function. Next, create a calculated field to count either the number in the cohort or the percent retained based on the years since acquisition like this:

           

          if sum([years since acquisition])=0 then

              countd([Customer ID])

          else

              -(( LOOKUP(countd([Customer ID]), FIRST()) - countd([Customer ID]) ) / LOOKUP(countd([Customer ID]), FIRST()))

          end

           

          You'll notice that for the percentage, I actually made it a negative. This is so you can use custom formatting of the number to show the counts and percentages differently using custom formatting. Here is the custom formatting code to get that to work:

           

          #,###; #0.00%

           

          I'm currently using Tableau Public so I can't save a copy of the packaged workbook, but here's a link to how things turned out which should allow you to download the workbook. In this example I also created a separate calculated field to relabel the years since acquisition as either size (for year 0) or "year " + years for future years. One potential issue with this, which might or might not be of concern, is that it is possible for retention to go up in later years if the customers missed a year and later returned.

           

          https://public.tableau.com/views/ExampleSalesforceRetention/Sheet2?:embed=y&:useGuest=true&:display_count=yes&:showTabs=…

           

          Hope this helps

          -Marc