I am trying to figure out flexible user cohort analysis on week by week, but I have trouble getting my head around it, so I could use a nudge in the right direction
Let’s say I have defined a user cohort based on two aspects:
- The duration since the user account was created
- The duration since last user transaction
In this case I want to know the amount of users where 1 is above 3 months and 2 is below 3 months.
Calculating the amount of users in this cohort based on all the data is no problem. However I would like to know how this group evolved over time. Meaning I want to compare the status today to last week to the week before etc. In those previous weeks the duration calculation would not compare to the current day but to the defined end date in the dimension to calculate the duration, thus limiting the data used for the calculation of group size. And I would like to see all weeks next to each other at the same time in 1 table that shows the amount of users in a group based on that date dimension.
For example. My account was registered on 1-1-2019 based on last Sunday (10-2) the duration between creation and query date = 41 days. The Sunday prior to that it would be 34 days. That would be two rows in a table, one with the date dimension and the other with the duration. If I wanted to know how many users have 40+ days since account creation my account would be counted this week, but not the previous week.
I hope this makes sense. Any ideas on where to start?