Cohorts are a good tool to review retention of users over a defined period of time.
To create your own cohort report, you'll need to define your cohort time frame, population and activeness of users.
Here is an example for a cohort query in PostgreSQL, step by step:
WITHclause in this example -
WITHclauses allow you to name a sub-query block, this way your query is modular (and sometimes runs faster), it can later be referred to inside the main query instead of making a large main query with many aliases,
JOINs or other complexities. You can have multiple sub-queries, just be sure to have a coma between them.
Select the time frame you want to investigate (usually a between a week and a month)
WITH time_frame AS ( SELECT CURRENT_DATE - 14 ),
Define your population relatively to the cohort date, for each following day
population AS ( select created_at::DATE AS cohort_date, id AS unique_id FROM users WHERE created_at > (SELECT * FROM time_frame) ),
Define what's an active user to you - what event interest you to examine
activity AS ( SELECT created_at::DATE AS activity_date, org_id AS unique_id, cohort_date FROM events JOIN population ON population.unique_id = org_id WHERE created_at > (SELECT * FROM time_frame) ),
Aggregate your population by cohort date (day 1, day 2...)
population_agg AS ( SELECT cohort_date, COUNT(distinct unique_id) AS total FROM population GROUP BY 1 )
Write your query to show your population % by cohort dates
SELECT activity.cohort_date AS DATE, date_part('day',age(activity_date, activity.cohort_date)) AS day_number, COUNT(distinct unique_id) AS value, total FROM activity JOIN population_agg ON activity.cohort_date = population_agg.cohort_date GROUP BY 1 , 2, 4
Add a cohort visualization to your query and you're done!