Cohorts are useful to determine a grouping of data across a date dimension. For example, how many users remain active n months after initial signup, etc.
Cohort Analytics are notoriously difficult to do directly within the database. Knowi provides a simple mechanism to calculate and visualize cohorts using Cloud9QL, a SQL-like post processor on your database query to determine
We currently support 2 types of input data.
select COHORT(<Date Column>, <Cohort Date Definition>, <Cohort Period Definition>), <Cohort Operation>
group by <Cohort Date>, <Cohort Period>
Note: Input data needs to be sorted by Date ascending order.
Example 1: If we already have the cohort date populated
Example 2: If we only have transactional events like the following example:
Example 3: Cohorts can be used in combination with transpose to pivot/flatten the result based on date.
Example 4: A common cohort is retention in percentage format which can be computed as follows: