Katkat
Back

Build cohort 30-day retention from signup date

IntuitData Scientist · Technical Screen
MediumData ScientistTechnical ScreenAnalytics & Experimentation

Last updated

This problem tests your ability to manipulate time-series data to calculate cohort-based user retention rates while handling data censoring constraints.

Problem Statement

You are given a dataset user_activity containing two columns: user_id and activity_date. Assume a user's signup_date is defined as the minimum activity_date associated with their user_id in the dataset.

A user is considered "retained" at day 30 if they have at least one activity record exactly 30 days after their signup_date (i.e., activity_date == signup_date + 30 days).

Write a SQL query or a Python function using pandas to calculate the 30-day retention rate for each signup cohort (grouped by the month of signup_date). The retention rate is defined as: (Number of users active on signup_date + 30) / (Total users in the cohort).

Constraints

  • Ignore users whose signup date is within the last 30 days relative to the maximum date in the dataset (to account for right-censoring).
  • Input: A DataFrame df with columns user_id (int) and activity_date (datetime).

Example

If User A signs up on 2023-01-01, they are part of the '2023-01' cohort. If they have an activity on 2023-01-31, they are retained for that cohort.

def calculate_retention(df):
    # Return a DataFrame with columns: ['cohort_month', 'retention_rate']
    pass

Hints

Hint 1

First, create a summary table that finds the minimum date for every user to establish their signup cohort.

Hint 2

Filter out users who signed up so recently that they haven't had the opportunity to reach their 30-day mark yet.

Hint 3

Use a self-join or a membership check (e.g., `isin`) to identify which users have an activity record exactly 30 days post-signup.

Follow-up questions

  • How would you modify your approach if 'retained' meant being active *at any point* between day 25 and day 35?
  • If the dataset were billions of rows, how would you optimize the join operation?
  • How do you distinguish between a user who churned and a user who simply hasn't reached the 30-day threshold yet?