Retention Rate cohort analysis

Question & Answer
, , , , ,
1

I want to create a cohort analysis for retention rate.
I have 3 fields, activation_date, cancel_date and usernumber.

For this analysis, I have considered user’s who activated the service in Q1 2024 and what is their retention until 08/2024.

And I want output similar to below in Quicksight.

Column 1 Column 2 Column 3 Column 4 E F G H I
1 2 3 4 5 6 7 8
24-Jan 1000 900 800 700 600 500 400 300
24-Feb 899 766 765 750 600 400 300
24-Mar 700 600 500 456 400 370

I need calculated fields that would help me achieve this goal.

2

Hi @xyz27 ,

welcome to the community :tada:

Do I get this right? Your visual shows all users that activated in Q1. The activation month is in the first column.

And as an example 1000 of the users that activated in Jan also canceled in Jan?

Can you offer us some kind of example data and store it in Amazon Arena?

I created an example on Amazon Arena:
PivotTable

If this is what you want, there is no need for a calcualtion. Only a Pivot Table and the aggreagtion to Month.

Best regards,
Nico

1 Like
3

No, 1000 joined in January.
In Feb, there are 900 so 100 cancelled the service. So, basically we retained 900 out of 1000 in Feb and so on.

4

Hi @xyz27,
It’s been awhile since last communication took place on this thread. Do you have any additional questions or were you able to find a work around?

Looking at your example; what fields are you utilizing to show how many users you have/lost in the prior month?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

5

Hi @xyz27
sorry for the late reply.

I think it is not possible to receive this outcome from your data how it is.

To solve this you need to pre calculate it with other tools or change the data source. You would need a additional calender table and cross join it.

Best regards,
Nico

1 Like