10 Replies Latest reply on Nov 15, 2016 9:15 AM by Yuriy Fal

# Get percentage of cohort instead of simply 'percent of total'

Hi all,

I am working on a cohort analysis. I have two different tables, (1) user table with 'date_created' of each account and (2) login table with all logins per user with the specific login date. I have constructed already a table which shows the unique logins per period (per month in my example) but now I want to have this number divided by the # of created accounts in a specific period (cohort). Simply running percent of total will give a percentage of total unique logins instead of a percentage of the created account in a month.

The table below shows the number of Unique logins per period for specific cohorts (users who created an account in a specific period). I want to have the following percentage (%):

Unique logins for a certain period (COUNTD of logins, from login table) divided by the cohort for a specific month (COUNT of accounts created, from user table).

For October, it is not simply 10/426 = 2.35%, because there were 550 users who created an account in October 2016. So the outcome should be 426/550= 77.45% in Month 1 and 10/550=1.82% in month 2. But I don't know how to get the number divided by the specific cohort of 550.

Can someone help me to get the right formula?

• ###### 1. Re: Get percentage of cohort instead of simply 'percent of total'

Peter,

I don't understand where you are getting 550. Anyways, can you please post your sample data so we can get your accurate and fast answer? Thank you

Sherzod

• ###### 2. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Sherzodbek,

I hope my sample data below will clarifies my question a little bit. The data mentioned in the tables differ from the table above. First a table with sample data and second a picture with the table I would like to have with the calculations. Thanks in advance!

• ###### 3. Re: Get percentage of cohort instead of simply 'percent of total'

Peter,

Thanks for clarification. Now, it is very clear what you are trying to achieve. Next time, please also post your sample as excel or twbx, not as picture. I made up my own sample.

Out of 20 created ids, 5 of them logged in during October (7,8,9,10,11). Using Exclude LOD expression I was able to achieve your result:

Also, if you have any quick filters, make sure they are added to Context, otherwise you don't get the right answer. Go to filter box, right click and choose 'Add to Context'.

Sherzod

• ###### 4. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Sherzobek,

Thanks for your efforts so far. We are moving forward but this is not exactly what I am looking for, or it could be that I dont get it correct in my dataset.. There is a nice article about cohort analyses on the internet which exactly describes what table I would like to have (http://andrewchen.co/the-easiest-spreadsheet-for-churn-mrr-and-cohort-analysis-guest-post/ ).

The second table in the figure below shows exactly what I would like to have.. I tried to make this one based on the formulas you provided but I didn't get a table like this. Hope this clarifies it a little bit more what I would like to have.

• ###### 5. Re: Get percentage of cohort instead of simply 'percent of total'

Peter

if you still need help, please post your sample as twbx or excel not as picture to make it easy for us. If you got answer already then mark it as assumed answered.

Thanks

• ###### 6. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Peter,

Hope it could help.

Yours,

Yuri

• ###### 7. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Yuri,

Thanks. The numbers are correct but when we add additional months and figures the formula is not working correctly I think.

Please find attached an updated worksheet (v2) with 3 tabs, the cohorts, total logins and unique logins. The unique logins per period needs to be divided by the cohorts for the reference period. The worksheet "Retention" should than give a table with the percentages like this:

Hope this helps more with the added sample data.

Thanks!

Best,

Peter

• ###### 8. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Peter,

In the attached.

Yours,

Yuri

• ###### 9. Re: Get percentage of cohort instead of simply 'percent of total'

Hi Yuri,

Thanks! This is what I am looking for and it is working fine when applying it to my own dataset.

Kind regards,

Peter

• ###### 10. Re: Get percentage of cohort instead of simply 'percent of total'

Peter, you're welcome.