Compare Asana Retention Cohorts by Channel

Task

Asana’s growth team wants to compare early retention for users acquired through ChatGPT versus all other acquisition channels. Write a PostgreSQL query that builds weekly signup cohorts and reports week 0 through week 4 retention for each cohort and acquisition segment.

Requirements

1. Define each user’s cohort by the week of signup from users.signup_date.
2. Split users into two acquisition segments:
   • ChatGPT when acquisition_channel = 'ChatGPT'
   • Other for every other non-null channel Exclude users with a null acquisition channel.
3. Count a user as retained in week n if they had at least one activity in Asana during the nth week after signup, using activity_date from user_activity.
4. Return one row per cohort_week, acquisition_segment, and week_number for weeks 0-4.
5. Include cohort_size, retained_users, and retention_rate rounded to 4 decimal places.
6. Order the final output by cohort_week, acquisition_segment, and week_number.

Table Definitions

users

user_activity

Sample Data

users

user_activity

Expected Output