A cohort analysis breaks your users into groups based on some shared characteristic. You can then perform analyses on each cohort and draw conclusions based on the differences.
Let's say you want to see what percentage of users who signed up to your app in a given month have upgraded after 1, 2, 3...6 months.
Comparing the results of each cohort will help you determine whether you're improving your upgrade rate or not.
You'll need two things:
- The date they signed up
- The date they upgraded.
(1) Join the relevant data together (e.g. the users table and the table containing the upgrade date).
Note: Measuring something users can do multiple times and only need the earliest occurrence (e.g. a real example from us: the first time our users created a dashboard)? Learn how here.
(2) You should have a table with one row per user, containing the date they signed up, and the date they upgraded.
(3) Click the new column button.
(4) Call your column diff in months.
(5) Add the following formula and click Done:
diff_in_months(name of upgrade date column, name of sign up date column)
(6) Now you have a new column containing the difference (in months) between when a person signed up and when they upgraded.
(7) Create a filter to limit the data to where the diff in months < 7.
(8) Click calculate
(9) In the Calculation(s) section, select count(users)
(10) In the Group by section, select the sign up date column (choosing month in the dropdown) and then the diff in months column.
(11) Click Save and give your insight a name.
You now have a table showing a count of the users who upgraded within 6 months of signing up, broken down by the month in which they signed up and how many months it took them to upgrade. Hope that's clear, but do message us if it's not 🙂
The next step is to calculate the count as a percentage of the total users in that cohort (i.e. the total users who signed up that month). To do this, you need to create a separate insight that returns a count of your users by month.
(12) Head to your users table.
(13) Click Calculate.
(14) Count users and group the results by sign up month.
(15) Click Save and give your Insight a name.
Next, we're going to join the two insights together. This will enable us to calculate the count of people who upgraded as a percentage of the people who signed up in the same month.
(16) Open the first Insight you saved.
(17) Click the Join button.
(18) Click the Custom Join Wizard link.
(19) Where it says Choose a table (or insight) to join with, click on the word insight.
(20) Choose the second insight you saved above.
(21) You'll need to specify how you'd like to join the tables. In the two columns that appear, select the month column in both.
You now have a table containing both insights. Next, you need to calculate the count of people who upgraded as a percentage of the people who signed up in the same month.
(22) Click the new column button.
(23) Name your column Percentage.
(24) Add the following formula and then click Done:
(users who upgraded column * 100)/ total users column
(25) Click the "+" icon above the table.
(25) Select hide column.
(26) Hide every column except the sign up month, diff_in_months, and percentage columns, and click Done.
(27) Click the "+" icon again.
(26) Select sort rows.
(27) Sort by the signed up date column (in ascending order) and the diff_in_months column (in descending order).
(28) Click Save.
In terms of getting the result, that's it! 👨🚀
Format your results
Traditional cohort table
(with the sign up date in the left column, the diff_in_months values along the top, and the percentages contained within)
- Click Share
- Select Export as Live Feed.
- Download or Export the CSV (2-dimensions) option.
We hope to add native support for this soon.
Line and stacked charts in Trevor
- Click the "+" icon above the table.
- Click Reorder.
- Make your diff_in_months column the first column.
All done! 🚀
Have a question or need help?
We're always happy to help. Just ping us a message via Intercom 🙂