Cohort Analysis Foundations

In this lesson, you will learn how to organize your users into temporal cohorts to track how their behavior evolves over time.

Understanding Cohorts

A "cohort" is simply a group of users who share a common characteristic during a specific timeframe—typically the date they first interacted with your system (e.g., their sign-up date). By grouping users into these buckets, you can see if users who joined in January behave differently than those who joined in February.

The Implementation Strategy

To perform cohort analysis, you need two distinct time points for every user:

1. The Cohort Month: The month the user first appeared in the database.
2. The Activity Month: The month in which a specific action occurred.

You calculate these by truncating your timestamps to the start of the month. Once you have these, you can calculate the "Age" or "Tenure" of the user relative to their start date.

Example: Calculating Retention

Imagine you have a user_activity table. We first identify the cohort by finding the minimum date for every user, then join it back to activity records to calculate the month offset:

WITH user_cohorts AS (
    SELECT 
        user_id, 
        DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM user_activity
    GROUP BY user_id
)
SELECT 
    c.cohort_month,
    DATE_TRUNC('month', a.activity_date) AS activity_month,
    COUNT(DISTINCT a.user_id) AS active_users
FROM user_activity a
JOIN user_cohorts c ON a.user_id = c.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

In this query, cohort_month acts as our fixed dimension. By grouping by both the cohort_month and the activity_month, you create a timeline that shows exactly how many users from a specific signup month returned in subsequent months. This is far more insightful than looking at global monthly active users, as it allows you to spot patterns—like whether a specific product update successfully improved retention for new users compared to your legacy base.

Key Takeaway: Cohort analysis transforms raw longitudinal data into structured groups, allowing you to isolate the behavior of specific user segments based on their entry point in time.