Cohort Retention

IntroductionCopy Anchor Link

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 of the metrics behind a classic cohort retention table.

SetupCopy Anchor Link

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.

// 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 flowCopy Anchor Link

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

ImplementationCopy Anchor Link

1. Define Acquisition CohortCopy Anchor Link

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().

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);;
  }
}

2. Define the Metrics (or the Cohort Size)Copy Anchor Link

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.

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

3. Define the retention logicCopy Anchor Link

First, calculate the Month since Acquisition by using the date_diff() function.

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

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

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 users retained in each acquisition cohort for each month.

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));;
  }

}

We have covered all the foundational concepts required to calculate metrics for the acquisition cohort, and you are now equipped to build more powerful cohort analyses on top of them.