8 Replies Latest reply on May 22, 2019 6:44 AM by Bhanupriya Purohit

# Retention Ratio Calculation

Hi,

I am using tableau to present retention day wise retention ratios.

For sample dataset, I have managed to get days since registration in my dataset and created that as dimension in tableau. Then I was able to plot how many users under day 0, day 1 etc using CNTD for user_id. That works fine.

Next I need to calculate ratio (i.e how many users could have logged in possibly).

For example, if we are looking for day 2 ratio, then all users who have value 2 or higher in measure Date diff from Today should be used as divider. I need to perform this for each day (Day since Reg on X axis).

Problem I faced with this that when I drop this dimension on sheet, it act like a filter. Is there any way I can define a condition in measure that for each column, it use column value and then check in whole data set for count of users who have days since registration >= current column value.

In attached sheet I have used a fixed value for measure Users could participate as 20. Basically I would like it to be dynamic, relative to the column as mentioned above.

Thanks

• ###### 1. Re: Retention Ratio Calculation

Hi Bhanu

I have changed your calc into

COUNTD([User Id]) / total(COUNTD([User Id]))

total(COUNTD([User Id])) will give overall total users.

Thanks & Regards

Sankarmagesh

9894170397

BI,Aspire Systems

• ###### 2. Re: Retention Ratio Calculation

Hi,

Thanks for your reply but total(COUNTD([User Id])) will give me overall totals all the time, I want only those users who could participate, for eg. I added two more users in data set, in that one registered yesterday and another one registered today, user who registered yesterday can't participate for day2 to day 5 divisor because for that users these days are future day same for user who registered today can't participate for day 1 to day 5. So result should be
Day 0 = 22/22

Day 1 = 14/21

Day 2 = 11/20

Day 3 = 08/20

Day 4 = 04/20

Day 5 = 02/20

• ###### 3. Re: Retention Ratio Calculation

Hi, Bhanupriya

Can you help understand how is 14/21 being calculated? I understand 14, but don't understand where the 21 come from?

ZZ

• ###### 4. Re: Retention Ratio Calculation

Hi ZZ,

Look at the sample table attached, it basically how many user come on a particular day and divide by how many could have come on that day. N/A means that the difference b/w user's signup date and current date is less than that day. For eg look at User7 and you see that he signup on today (May 22) and therefore can't have returned on his Day 1 (Day 0 + 1 day) since that is the future whereas User5 could have returned on his Day1 because his signup was 2 days ago.

• ###### 5. Re: Retention Ratio Calculation

Hi, Bhanupriya

Below is the sample output

the calculation is complicated, but have a look first and if you have question, please do come back and ask, I will explain more details.

ZZ

• ###### 6. Re: Retention Ratio Calculation

Hey ZZ,

Really thanks for the work, I should have been more clear about my problem. I also was able to get the retention like you showed but my problem is when I applied filters. Lets say from the dataset above that 60% of signups are male, spread randomly among a signup days and 40% are female and I want to compare the retention of male v/s female e.g when I apply a Gender filter the results are strange. I get 157% retention for male on Day0. This is the problem that is really crushing my head.

Thanks,

Bhanupriya

• ###### 7. Re: Retention Ratio Calculation

Hi, Bhanupriya

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Retention Ratio Calculation

Hi ZZ,

First of all thank you very much for your help, it works perfectly when I added just a gender filter but we are not there yet. When I added Reg Date filter too I got the results shown in the chart. If you look at Day1 you see it is shows 11 users as the possible returnees when in reality all 12 users could have return on that day so the divisor should be 12 not 11 (because for all filtered users difference b/w reg date and today's date > 1).

I am also trying to figure this out but if you could take a look at this I would appreciate it.