Cohort Analysis Exposes Donor Retention Risks

Aggregate Retention Masks Leaky Bathtub Dynamics

Standard donor retention—proportion of last year's donors giving again—rises from 26.7% in 2017 to 42.2% in 2025, with total donors doubling from 646 to 1,261. But it's a lagging indicator, sustained by long-time supporters while new donor conversion thins, creating a 'leaky bathtub' where losses outpace retention despite stable water levels. Filter out regular giving first to avoid inflation:

import pandas as pd
df_opps_filtered = df_opps[df_opps['campaign'] != 'Regular Giving'].copy()
df_years = df_opps_filtered[['contact_id', 'year']].drop_duplicates()
df_years['prev_year'] = df_years.groupby('contact_id')['year'].shift(1)
df_years['is_retained'] = (df_years['year'] == df_years['prev_year'] + 1)
results = df_years.groupby('year').agg(total_donors=('contact_id', 'count'), retained_donors=('is_retained', 'sum')).reset_index()
results['donors_last_year'] = results['total_donors'].shift(1)
results['retention_rate'] = results['retained_donors'] / results['donors_last_year']

This yields healthy-looking trends but ignores cohort composition.

Second-Gift Rate Flags Early Conversion Failures

Track first-time donors making a second gift within 12 months: rates hover 29-35% (e.g., 31.2% for 2016 cohort, 33.0% for 2024), stable but below industry benchmarks. This threshold turns one-offs into supporters, predicting long-term loyalty. Compute via:

df_sorted = df_opps_filtered.sort_values(['contact_id', 'close_date'])
first_and_second_gifts = df_sorted.groupby('contact_id')['close_date'].agg(['first', lambda x: x.iloc[1] if len(x)>1 else pd.NaT])
first_and_second_gifts['months_lapsed'] = (first_and_second_gifts['second_gift_date'] - first_and_second_gifts['first_gift_date']).dt.days / 30.4375
first_and_second_gifts['is_converted'] = first_and_second_gifts['months_lapsed'] <= 12
grouped = first_and_second_gifts.groupby('first_gift_year').agg(total_new_donors=('is_converted', 'count'), second_gift_conversions=('is_converted', 'sum'))
grouped['conversion_rate'] = (grouped['second_gift_conversions'] / grouped['total_new_donors']) * 100

Stable rates suggest no immediate alarm, but don't reveal multi-year trajectories.

Cohort Heatmaps Reveal Declining Longevity

Full cohort analysis groups by first-gift year (cohort_year), tracks retention as years elapsed (year_number) relative to original size. Year 1 retention improves from 27% (2016) to 34% (2023), but all cohorts drop sharply post-Year 1 (e.g., 2016: 27% → 15% → 10%), stabilizing low at 8-11%. Occasional upticks reflect lapsed-then-returning donors. Build via:

cohort_map = first_and_second_gifts['first_gift_year'].to_dict()
df_opps_filtered_summary = df_opps_filtered.groupby(['year', 'contact_id']).agg(total_amount=('amount', 'sum')).reset_index()
df_opps_filtered_summary['cohort_year'] = df_opps_filtered_summary['contact_id'].map(cohort_map)
df_opps_filtered_summary['year_number'] = df_opps_filtered_summary['year'] - df_opps_filtered_summary['cohort_year']
cohort_counts = df_opps_filtered_summary.groupby(['cohort_year', 'year_number']).agg(retained_donors=('contact_id', 'count'), total_amount=('total_amount', 'sum')).reset_index()
cohort_sizes = cohort_counts[cohort_counts['year_number']==0][['cohort_year', 'retained_donors']].rename(columns={'retained_donors': 'original_cohort_size'})
df_cohorts = cohort_counts.merge(cohort_sizes, on='cohort_year')
df_cohorts['retention_rate'] = df_cohorts['retained_donors'] / df_cohorts['original_cohort_size']

Visualize with seaborn heatmap (cohort_year rows, year_number columns, retention_rate values) to compare trajectories.

Revenue Mix Exposes Over-Reliance on New Cohorts

In 2025, 75% revenue from 2024-2025 cohorts (each 37-38%), while 2016-2019 cohorts contribute <2% each despite loyalty. No major gift skew: average gifts similar across cohorts ($500-700). Filter df_cohorts[cohort_year + year_number == 2025], compute pct_of_total = (total_amount / total_2025_amt) * 100 and avg_gift = total_amount / retained_donors. This recency bias means no fallback depth—economic shocks could crater budgets, as older cohorts aren't scaling to stabilize base.