Learn Introduction to Cohort Analysis | Cohort Analysis with DATE Functions

Cohort analysis is a powerful technique in marketing analytics that helps you understand how groups of users behave over time. In this approach, users are grouped together based on a shared characteristic within a defined time period—most commonly, their signup month. By analyzing these groups, or cohorts, you can uncover patterns in user retention, engagement, and response to marketing efforts.


              1234567
            
-- Assign users to cohorts based on their signup month
SELECT
  user_id,
  signup_date,
  DATE_TRUNC('month', signup_date) AS signup_cohort
FROM users
ORDER BY user_id;

Note

A cohort is a group of users who share a common characteristic within a defined time period.

By grouping users into cohorts, you gain the ability to track how each group interacts with your product or service after their initial engagement. This reveals retention trends—such as how many users from each signup month return and remain active in subsequent periods—and helps you measure the long-term effectiveness of your marketing campaigns. Instead of looking at all users as a single group, cohort analysis lets you see if users who joined during a particular campaign or season are more likely to stay engaged or make purchases compared to others.


              123456789
            
-- Count active users per cohort over time
SELECT
  DATE_TRUNC('month', u.signup_date) AS signup_cohort,
  DATE_TRUNC('month', e.event_timestamp) AS activity_month,
  COUNT(DISTINCT u.user_id) AS active_users
FROM users u
JOIN events e ON u.user_id = e.user_id
GROUP BY signup_cohort, activity_month
ORDER BY signup_cohort, activity_month;

Task

Swipe to start coding

Write a SQL query to calculate the number of purchases per signup cohort per month using the users and events tables.

Group users into cohorts by truncating their signup_date to the month using DATE_TRUNC('month', signup_date);
Only include events where event_type is 'purchase';
For each purchase, determine both the user's signup cohort and the month of the purchase (truncate event_timestamp to the month);
Count the number of purchases for each combination of signup cohort and purchase month;
Name the columns signup_cohort, purchase_month, and purchase_count.
Order the results by signup_cohort and purchase_month.

Solution

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain how to interpret the cohort analysis results?

What other metrics can I analyze using cohort analysis?

How can I visualize this cohort data?

Swipe to show menu


              1234567
            
-- Assign users to cohorts based on their signup month
SELECT
  user_id,
  signup_date,
  DATE_TRUNC('month', signup_date) AS signup_cohort
FROM users
ORDER BY user_id;