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 ID||Install Date||Play Date||Days Since Install|
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?