I have a challenge, I need to create a Cohort Analysis for Saas company.
There are two tables:
Both linked by a subscription_id.
In one table, there is the register_date (dd/mm/YYYY) of each user and in the other table, there is the subscription_start_date (dd/mm/YYYY) that is the date that the user starts paying (not all users start paying).
Right now I have this table:
And I need to create this table two tables:
- Table one where:
The first column is the month of register,
The second column is the number of registered users on that month
The third column is the number of registered users on the month of the first column who starts paying in his first month (month 0)
The fourth column is the number of registered users on the month of the first column who starts paying in second month (month 1) plus the users paying in previous months, in this case month 0.
The next columns work the same than the fourth column just adding paying users month after month
In the second table is the same information just getting the percentage (paying users / users registers) ex:
I think you can create a new table to extract all column types with the corresponding index, then you can create a matrix table with use new table column and row table rows categories. After these steps, you can add two measures: one of them uses the current data label of row/column to lookup and summary values; the other one checks the previous measure result and setting up conditional color formatting based on 'summary value' measure.
If you are confused about coding formula, please share some dummy data to test. Regards,
Community Support Team _ Xiaoxin If this post helps, please consider accept as solution to help other members find it more quickly.