Hello gurus,
I am trying to create a chart which will allow the users to better understand if retention on cohort months 1,2,etc is improving every month.
To better achieve this instead of displaying a simple COUNT DISTINCT on CustomerID I would like to show a percentage of how many players are still active in cohort month N versus the ones that began in month 0.
Below I have attached a simple example of what I am trying to achieve in table format.
| Registration Year Month | Month Cohort | Active Customers |
|---|
| 2015-01 | 0 | 100% |
| 2015-01 | 1 | 50% |
| 2015-01 | 2 | 45% |
| 2015-02 | 0 | 100% |
| 2015-02 | 1 | 60% |
| 2015-03 | 0 | 100% |
| | |
Registration Year Month and Month Cohort are precalculated and exist already in the Data model.
To achieve this I am trying to create a temporary second column which no matter the Month Cohort will always return the number of active customers in Month Cohort 0 for users of that registered period. Table below shows what I am trying to achieve for now.
| Month Cohort | Active Customers | Active Customers (Month 0) |
|---|
| 2015-01 | 0 | 1000 | 1000 |
| 2015-01 | 1 | 500 | 1000 |
| 2015-01 | 2 | 450 | 1000 |
| 2015-02 | 0 | 2000 | 2000 |
| 2015-02 | 1 | 1200 | 2000 |
| 2015-03 | 0 | 3000 | 3000 |
Active Customers is calculated using the simple expression "COUNT(DISTINCT ActiveCustomer)"
I attempted to achieve the second column using the expression "COUNT({<[MonthCohort]={0}>} DISTINCT ActiveCustomer)" but this is not having the desired result. The result I am getting can be seen below.
| Month Cohort | Active Customers | Active Customers (Month 0) |
|---|
| 2015-01 | 0 | 1000 | 1000 |
| 2015-01 | 1 | 500 | 0 |
| 2015-01 | 2 | 450 | 0 |
| 2015-02 | 0 | 2000 | 2000 |
| 2015-02 | 1 | 1200 | 0 |
| 2015-03 | 0 | 3000 | 3000 |
I am trying to calculate the second column so then I can just do the division to get the precentage of retained customers.
I appreciate any help any of you can give me.
Thanks,
Malcolm
