计算月度留存：SaaS公司Cohort Analysis中遇同期群留存查询问题

Hey there! Let’s work through that cohort retention snag you’re hitting with your SaaS data. I’ve helped troubleshoot similar issues before, so let’s break this down clearly—since you referenced Greg Rada's examples, this approach aligns with the standard cohort workflow he outlines, but we’ll tailor it to your setup.

First off, I notice your current DataFrame only includes the Customer_ID column. For cohort analysis, we need two more critical pieces of data: a user's first interaction date (to define which cohort they belong to) and their subsequent activity dates (to measure retention over time). Let’s fix that first, then walk through the full process.

Start by ensuring your data has these core fields. Here’s an example of what your expanded DataFrame should look like (replace with your actual data):

import numpy as np
import pandas as pd
from pandas import DataFrame, Series
import matplotlib.pyplot as plt
import matplotlib as mpl

pd.set_option('max_columns', 50)
mpl.rcParams['lines.linewidth'] = 2
%matplotlib inline

# Sample data (swap this with your real user activity data)
df = DataFrame({
    'Customer_ID': ['QWT19CLG2QQ', 'URL99FXP9VV', 'EJ45KLP0MN', 'QWT19CLG2QQ', 'URL99FXP9VV', 'EJ45KLP0MN'],
    'Signup_Date': pd.to_datetime(['2023-01-05', '2023-01-12', '2023-02-01', '2023-01-05', '2023-01-12', '2023-02-01']),
    'Activity_Date': pd.to_datetime(['2023-01-05', '2023-01-12', '2023-02-01', '2023-01-12', '2023-02-05', '2023-02-10'])
})

Next, we’ll label each user with their cohort (based on their signup month, though you can use week/day if your business needs finer granularity) and calculate how many months after signup each activity occurred:

# Extract the signup month as the cohort label
df['Cohort_Month'] = df['Signup_Date'].dt.to_period('M')

# Calculate the number of months between activity date and signup date
df['Month_Offset'] = df['Activity_Date'].dt.to_period('M') - df['Cohort_Month']
# Convert the period difference to an integer for easier grouping
df['Month_Offset'] = df['Month_Offset'].apply(lambda x: x.n)

Now we’ll group the data by cohort and month offset to count unique users, then convert those counts to retention rates (relative to the cohort’s initial size):

# Count unique users per cohort and month offset
cohort_user_counts = df.groupby(['Cohort_Month', 'Month_Offset'])['Customer_ID'].nunique().reset_index()

# Pivot the data to get cohorts as rows and month offsets as columns
cohort_pivot = cohort_user_counts.pivot(index='Cohort_Month', columns='Month_Offset', values='Customer_ID')

# Calculate retention rate: divide each cell by the cohort's initial user count (Month_Offset = 0)
cohort_retention = cohort_pivot.divide(cohort_pivot.iloc[:, 0], axis=0)

To make the insights actionable, visualize the retention rates with a heatmap (this is where Greg Rada’s examples often shine):

import seaborn as sns  # Make sure you have seaborn installed for better heatmaps

plt.figure(figsize=(12, 8))
plt.title('SaaS User Cohort Retention Rates')
sns.heatmap(cohort_retention, annot=True, fmt='.0%', cmap='Blues', vmin=0, vmax=1)
plt.xlabel('Months After Signup')
plt.ylabel('Cohort Month')
plt.show()

If you’re still running into issues, check these common pitfalls:

• Date type mismatch: Ensure Signup_Date and Activity_Date are datetime objects (use df.dtypes to verify; convert with pd.to_datetime() if needed)
• Duplicate records: Remove duplicate user-activity entries with df.drop_duplicates(subset=['Customer_ID', 'Activity_Date'])
• Granularity issues: If your user base is small, switch from monthly cohorts to weekly/daily to avoid sparse data

内容的提问来源于stack exchange，提问作者Kbbm