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

# Calculating Overall Retention Rate

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

• ###### 1. Re: Calculating Overall Retention Rate

James,

Because the Total computes the measures without taking the Install Date granularity into account, and because to compute the 741 you need a table calc that depends on Install Date , I don`t see how you can get those numbers in a single sheet.

But still you could compute the overall on a separate sheet and bring both on a Dashboard.

see in the attached. (check validateData before overall)

Michel

• ###### 2. Re: Calculating Overall Retention Rate

That's brilliant!  Thank you so much, Michel.  I did think that the overall calculation would need to be done in a separate sheet, but I just couldn't work out how to get there.

Thanks again

James