Cohort Analytics

Cohorts are useful to determine a grouping of data across a date dimension. For example, how many users remain active n months after initial sign up, 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 cohort data.

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

Transaction Date Register Date Amount
01/01/2011 10/01/2010 88
02/03/2011 10/01/2010 74
02/15/2011 10/01/2010 55
03/19/2011 11/01/2010 36
03/31/2011 10/01/2010 100
05/06/2011 10/01/2010 77
05/08/2011 11/01/2010 42
06/06/2011 11/01/2010 88
06/26/2011 10/01/2010 41
10/28/2011 11/01/2010 52
Click to Paste your own Excel/CSV/Delimited/JSON data

Example 2: If we only have transactional events like the following example:

User ID Event Type Transaction Date Amount
101 Sign Up 10/01/2010 0
101 Purchase 01/01/2011 88
101 Purchase 02/03/2011 74
101 Purchase 02/15/2011 55
102 Sign Up 11/01/2010 0
102 Purchase 03/19/2011 36
101 Purchase 03/31/2011 100
101 Purchase 05/06/2011 77
102 Purchase 05/08/2011 42
102 Purchase 06/06/2011 88
101 Purchase 06/26/2011 41
102 Purchase 10/28/2011 52
Click to Paste your own Excel/CSV/Delimited/JSON data

Example 3: Cohorts can be used in combination with transpose to pivot/flatten the result based on date.

User ID Event Type Transaction Date Amount
101 Sign Up 10/01/2010 0
101 Purchase 01/01/2011 88
101 Purchase 02/03/2011 74
101 Purchase 02/15/2011 55
102 Sign Up 11/01/2010 0
102 Purchase 03/19/2011 36
101 Purchase 03/31/2011 100
101 Purchase 05/06/2011 77
102 Purchase 05/08/2011 42
102 Purchase 06/06/2011 88
101 Purchase 06/26/2011 41
102 Purchase 10/28/2011 52
Click to Paste your own Excel/CSV/Delimited/JSON data

Example 4: A common cohort is retention in percentage format which can be computed as follows:

User ID Event Type Transaction Date
101 Sign Up 10/01/2010
102 Sign Up 10/1/2010
100 Purchase 1/1/2011
101 Purchase 1/1/2011
101 Purchase 2/3/2011
101 Purchase 2/15/2011
102 Purchase 2/19/2011
102 Purchase 3/27/2011
Click to Paste your own Excel/CSV/Delimited/JSON data