# Cohort Retention

> This step-by-step tutorial helps you understand and apply AQL for solving the Cohort Retention Analysis use case

:::info Options to build a retention heatmap in Holistics
We support multiple ways to build a retention heatmap. Here's a quick comparison to help you pick the right one:

| Option | Custom colors | Auto color scale* | Maintenance |
|--------|---------------|-------------------|-------------|
| [Built-in, legacy Retention Heatmap](/docs/charts/cohort-retention) | No | Yes | Low -- built-in visualization |
| **Pivot Table** with conditional formatting *(this page, recommended)* | Yes | Coming soon | Low -- built-in visualization |
| [Dynamic Content Block](/docs/charts/dynamic-content-blocks/gallery/retention-heatmap) | Yes | Yes | Higher -- you maintain the HTML/CSS yourself |

**(*) Auto color scale**: Color intensity adjusts automatically to the current data range. Without it, a heatmap built for a max of 1,000 will look washed out when a user's data only goes up to 80, or when a time filter reduces the range.
:::

## Introduction

**Cohort Retention Analysis** analyzes the continued engagement of specific user groups over time, helping businesses understand and improve customer loyalty and long-term user activity.

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

## 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.

```aml
// 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)
  ]
	
  // Define the Cohort
  dimension acquisition_month_cohort {
    model: users 
    type: 'date'
    label: 'Acquisition Month Cohort'
    // highlight-next-line
    definition: @aql min(orders.created_at | month()) | dimensionalize(users.id);;
  }

  // Calculate the Total Users are in this Cohort
  metric total_users {
    label: "Untitled metric"
    type: "number"
    // highlight-next-line
    definition: @aql count(users.id);;
  }

  // Define the Month Number
  dimension month_no {
    model: orders
    label: 'Month Number'
    type: 'number'
    // highlight-next-line
    definition: @aql date_diff('month', orders.cohort_month, orders.created_at | month());;
  }
  
  // How many users of the cohort are still active in consecutive months
  metric retention {
    label: 'Retention'
    type: 'number'
    // highlight-next-line
    definition: @aql (total_users*1.0) / (total_users | of_all(orders.month_no));;
  }
}
```

_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. **Define the retention logic:** How many users of the cohort are still active in subsequent months

## Implementation

<figure class='video-container'>
  <iframe width="100%" height="412" src="https://www.youtube.com/embed/ZomIayb8kis?si=JzP09lIyyUxChctp" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
</figure>

### 1. Define Acquisition Cohort

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

Since 1 user can make multiple purchases on different days, to find each user's first order date, obtain the earliest order created date (in the `Orders` model) and dimensionalize it by associating it with the User ID in the `Users` model. This can be done by using `min()` function and [`dimensionalize()`](/reference/aql/dimensionalize).

```aml
Dataset e_commerce {
  (...)

  dimension acquisition_month_cohort {
    model: users 
    type: 'date'
    label: 'Acquisition Month Cohort'
    // highlight-next-line
    definition: @aql min(orders.created_at | month()) | dimensionalize(users.id);;
  }
}
```

![Acquisition Cohort](https://cdn.holistics.io/product/aql-demo-dimensionalized-acquisition-20240724-157.png)

### 2. Define the Metrics (or the Cohort Size)

You will then be able to find out how many users in the Acquisition Cohort.

First, calculate the `Total Number of Users` by simply using `count()` function.

```aml
Dataset e_commerce {
  (...)

  dimension acquisition_month_cohort {...}

  metric total_users {
    label: 'Total Users'
    type: 'number'
    // highlight-next-line
    definition: @aql count(users.id);;
  }

}
```

And then, to find out how many users are in this Acquisition Cohort, you will use the combination of **dimension** `Acquisition Month Cohort` with the **metric** `Total Users`

![Total Users Cohort](https://cdn.holistics.io/product/aql-aql-total-users-cohort-20240722-151.png)

### 3. Define the retention logic

:::info

**What is considered retention**: 

* If first-time user A made an order on Week 1, and returns to buy more stuffs the next weeks, she is a returned user. 
* If user B also made an order on Week 1 and does not return the following weeks, she's a bounced user, that basically means you lose her as a user.

:::

First, calculate the **Month since Acquisition** by using the [`date_diff()`](/reference/aql/time-intelligence-functions#date_diff) function.

This dimension is necessary to determine how many users from a specific acquisition cohort return in subsequent months.

```aml
Dataset e_commerce {
  (...)

  dimension acquisition_month_cohort {...}

  metric total_users {...}

  dimension month_no {
    model: orders
    label: 'Month Number'
    type: 'number'
    // highlight-next-line
    definition: @aql date_diff('month', users.acquisition_month_cohort, (orders.created_at | month()));;
  }

}
```

This can be best illustrated by using Pivot Table where
- The **Row** is the Dimension `Acquisition Month Cohort`
- The **Column** is the Dimension `Month Number`
- The **Values** is the Metric `Total Users`

![Retained Users](https://cdn.holistics.io/product/aql-aql-retained-users-20240723-153.png)

`Month Number 0` represents the `Cohort Size`, which calculates the total number of users who made their first purchase in the `Acquisition Month Cohort`.

`Month Numbers 1, 2, and 3` show how many users from that cohort continue to make purchases in the `subsequent months 1, 2, and 3`, respectively.

Finally, create a `retention` metric to measure the [percentage of total](/as-code/aql/cookbook/aql-percent-of-total) users retained in each _acquisition cohort_ for each month.

```aml
Dataset e_commerce {
  (...)

  dimension acquisition_month_cohort {...}

  metric total_users {...}

  dimension month_no {...}

  metric retention {
    label: 'Retention'
    type: 'number'
    // highlight-next-line
    definition: @aql (total_users*1.0) / (total_users | of_all(orders.month_no));;
  }

}
```

![Percent Cohort Retention](https://cdn.holistics.io/product/aql-pct-retention-20240723-155.png)

---

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