Cohort

A cohort is a group of records that share a common starting point, such as users who signed up in the same month, tracked over time across a date dimension. Cohort analysis answers questions like how many users are still active three, six, or twelve months after signing up.

This kind of analysis is notoriously difficult to do directly in a database. Knowi makes it straightforward: using Cloud9QL, a SQL-like post-processor that runs on top of your query results, you can calculate and visualize cohorts without writing complex native SQL.

Knowi supports two types of input data, shown in the examples below.

select COHORT(<Date Column>, <Cohort Date Definition>, <Cohort Period Definition>), <Cohort Operation>
group by <Cohort Date>, <Cohort Period>

Note:

1. Input data needs to be sorted by Date ascending order.

2. Cohort Period returns a number (ie: the period) or a date. Example:

   a. 1m: Cohort Period as number

   b. (1m): Cohort Period as Date

Example 1: If we already have the cohort date populated:

select 
  cohort(
      Transaction Date, 
      Register Date as Cohort Date, 
      1m as Cohort Period),
    sum(Amount) as Total Amount
group by Cohort Date, Cohort Period;

select 
  cohort(
      Transaction Date, 
      Transaction Date as Cohort Date where Event Type = Sign Up group by User ID, 
      1m as Cohort Period),
   sum(Amount) as Total Amount
where Event Type = Purchase
group by Cohort Date, Cohort Period;

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

select 
    cohort(
        Transaction Date, 
        Transaction Date as Cohort Date where Event Type = Sign Up group by User ID, 
        1m as Cohort Period),
    sum(Amount) as Total Amount
where Event Type = Purchase
group by Cohort Date, Cohort Period;
select transpose(Cohort Period, Total Amount, Cohort Date);

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

select 
  cohort(
      Transaction Date,
      Transaction Date as Cohort Date where Event Type = Sign Up group by User ID,
      1m as Cohort Period,
      Cohort Count),
  count(distinct(User ID)) as Retention
where Event Type = Purchase and Cohort Date is not null
group by Cohort Date, Cohort Period;

select Cohort Date, Cohort Period, Cohort Count, 
Retention * 100 / Cohort Count as Retention Percent;

COHORT_PERCENT

Expresses each cohort's value as a percentage of that cohort's initial value. For each cohort (keyed by the cohort date), the first period is treated as 100%, and every later period is that period's value divided by the initial value, multiplied by 100. Input must be sorted by date in ascending order.

COHORT_PERCENT(<cohort date field>, <cohort value field>)

select Cohort Date, Cohort Period, COHORT_PERCENT(Cohort Date, Retention) as Retention Percent