2 Replies Latest reply on Mar 13, 2015 1:20 PM by James Sweeney

    Calculating Overall Retention Rate

    James Sweeney

      Hello,

       

      I hope someone can help me with this.  I have a report that calculates the percentage of users that return on a particular day, relative to their install date.  The underlying data looks like this:

       

      User IDInstall DatePlay DateDays Since Install
      ABC12301-Mar-1501-Mar-150
      ABC12301-Mar-1502-Mar-151
      ABC12301-Mar-1505-Mar-154
      GHJ12308-Mar-1508-Mar-150

       

      I can calculate the retention rate by Install Date by Days Since Install easily enough in Tableau (please see attached workbook).  The challenge I am having is that when calculating the overall retention rate on day x since install, as the standard Tableau calculation gives me the wrong answer. Let me illustrate with an example:

       

      • Today is 13th March 2015
      • The most recent install date for which I could calculate the day 7 retention rate is the 5th March.  In other words, I know the proportion of users that installed on 5th March that came back 7 days later (12th March, which was yesterday).  The rate is 9.3% (13 out of 140 users) as you can see in the workbook.
      • Now, when determining the overall day 7 retention rate I need to calculate the (total number of users that returned on day 7)/(total number of users on day 0) where enough time has elapsed for them to have returned
      • This condition is causing the issue, because Tableau is counting all of the users that returned on day 7 (77 users) and dividing that by all day 0 users (2,033) to give a day 7 retention rate of 3.3%.
      • The denominator of all day 0 users is wrong, because it includes users that installed after the 5th March that haven’t had time to come back on day 7 (as today is 13th March).  So the overall day 7 retention rate should in fact be the number of users that returned on day 7 (77 users) divided by all day 0 users that installed between 1st and 5th March (or put another way, up to today-8 days) which is 741. The overall day 7 retention rate should therefore be (77 / 741) = 10.4%.

       

      I’ve illustrated this in the attached spreadsheet in case that is unclear.

       

      Can anyone please explain to me how to perform this calculation in the attached Tableau workbook?

       

      Many thanks

      James