Calculate Monthly Churn and Retention Cohorts in Pandas

Question

Given `active_users` (user_id, activity_month), write a function to calculate the churn rate percentage for each month.

Accepted Answer

Optimal Solution:
import pandas as pd

def calculate_monthly_churn(df: pd.DataFrame) -> pd.DataFrame:
    df['date'] = pd.to_datetime(df['month'])
    
    # Self-merge to check if user returned in next month (date + 1 month)
    df_next = df.copy()
    df_next['prev_month'] = df_next['date'] - pd.DateOffset(months=1)
    
    merged = pd.merge(
        df,
        df_next[['user_id', 'prev_month']],
        left_on=['user_id', 'date'],
        right_on=['user_id', 'prev_month'],
        how='left'
    )
    
    # Churned users have null prev_month in the next month's records
    churn_summary = merged.groupby('month').agg(
        total_users=('user_id', 'nunique'),
        retained_users=('prev_month', 'count')
    ).reset_index()
    
    churn_summary['churn_rate_pct'] = (
        (churn_summary['total_users'] - churn_summary['retained_users']) 
        / churn_summary['total_users'] * 100
    ).round(2)
    
    return churn_summary

Technical Explanation:
Offsetting dates by 1 month and performing a left join identifies which users failed to return in the subsequent period.

user_id	month
1	2026-01
2	2026-01
1	2026-02