Create Cohort Analysis

info

This is part of our beta expression language AQL. Learn more. Request beta.

Introduction

Cohort Analysis is a methodology that involves studying and analyzing groups of users or customers who share a common characteristic or behavior.

We will provide a step-by-step walkthrough on how to build a report in Holistics that demonstrates the classic cohort analysis.

Setup

In order to gain insights into the lifetime value of groups of users who were acquired for each particular period of time, our objective is to define and analyze Acquisition Cohorts.

For this tutorial, we will be using an e-commerce dataset that consists of two models: orders and users.

Before diving into the implementation, let's take a quick look at our dataset setup. Additionally, the dataset also includes a pre-defined metric called revenue, which will be utilized in this tutorial for further analysis.

Initial Setup

// orders.model.aml
Model orders {
  ...
  dimension id {...}
  dimension user_id {}
  dimension created_at {}
}
// users.model.aml
Model users {
  ...
  dimension id {...}
  dimension name {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [orders, users]
  relationships: [
    relationship(orders.user_id > users.id, true)
  ]

  metric revenue {...}
}

Final Setup

// orders.model.aml
Model orders {
  ...
  dimension id {...}
  dimension user_id {}
  dimension created_at {}
}
// users.model.aml
Model users {
  ...
  dimension id {...}
  dimension name {...}
}

// e_commerce.dataset.aml
Dataset e_commerce {
  ...
  models: [orders, users]
  relationships: [
    relationship(orders.user_id > users.id, true)
  ]

  metric revenue {...}
    
  dimension acquisition_year_cohort {
    model: users 
    type: 'date'
    label: 'Acquisition Year Cohort'
    definition: @aql orders
      | min(orders.created_at | year())
      | exact_grains(users.id) 
    ;;
  }
  
  metric percent_revenue {
    label: 'Percent of Revenue'
    definition: @aql (revenue*1.0) / (revenue | of_all(users.acquisition_year_cohort));;
  }
}

Sample data

High-level flow

1. Define Acquisition Cohort: Determine the period and timeframe when each customer was acquired. This period could be daily, monthly, yearly, or any other suitable duration based on your business needs.
2. Define the metric: Define the metric you want to observe for each cohort
3. Bring it together: Visualize the metrics with each cohort in the pivot table

Implementation

1. Define Acquisition Cohort

Let’s define the acquisition cohort as the year that users made their first purchase.

Since created_at in model orders is involved in the expression, orders is needed to be placed before pipe further calculations. This allows orders left join users based on the relationship we've already defined in the dataset setup.

exact_grains() is also applied to group users within the same acquisition period together

Dataset e_commerce {
  (...)

  dimension acquisition_year_cohort {
    model: users 
    type: 'date'
    label: 'Acquisition Year Cohort'
    definition: @aql orders
      | min(orders.created_at | year())
      | exact_grains(users.id) 
    ;;
  }

  metric revenue {...}
}

2. Define the Measures

Create a metric percent_revenue which is the percent of total revenue per each acquisition cohort. This metric can also be defined directly on the reporting layer

Dataset e_commerce {
  ...
  dimension acquisition_year_cohort {...}
  metric revenue {...}

  metric percent_revenue {
    label: 'Percent of Revenue'
    definition: @aql (revenue*1.0) / (revenue | of_all(users.acquisition_year_cohort));;
  }
}

3. Bring it together

We will look at how each Acquision Cohort performs over the years. Revenue and Percent of Revenue metrics will be used to measure their performance

---

We have covered all the foundational concepts required to calculate measures for the acquisition cohort, and you are now equipped to create even more powerful cohort analysis reports to present to your stakeholders in Holistics.