I am trying to calculate retention %, which is number of players / number of players who registered.
I would like to group this by cohort (e.g. 0 days ago, 1 day ago, 2-6 days ago, etc.) and display this by date. For example:
| 1 May | 2 May |
Day 0 | 80% | 83% |
Day 1 | 20% | 22% |
Day 2-3 | 10% | 12% |
I have these player fields:
- player ID, date, reg date, days since registration (calculated)
Player ID | Date | Registration date | Days since registration = date - registration date |
1 | 1 May | 30 April | 1 |
2 | 1 May | 30 April | 1 |
3 | 1 May | 1 May | 0 |
And for registrations:
- reg date, no. registrations
Registration date | No. registrations |
30 April | 2,000 |
1 May | 1,000 |
I can get this:
Date | Player | Day since reg | Registrations
1 May | 1 | 1 | 2000
1 May | 2 | 1 | 2000
1 May | 3 | 0 | 1000
But I want to be able to group Days since reg (cohorts) together:
Date | Days since reg | Players | Reg | Retention |
1 May | 0, 1 | 3 | 3000 | 0.1% |
But I'm not sure how to group by days since reg first (doing a COUNTD of players and an AVG of registrations), then do a further calculation on this (i.e. SUM of COUNTD of players / SUM of AVG of registrations)
I can alter the data source a bit, but I'm not even sure what table structure would work.
I've attached an example workbook.
Thank you for any help you can give me.
Cohort example.twbx.zip 462.8 KB