如何用Python按季度计算客户流失率与留存率（含日期列分组）

Hey there! Let's build on your existing code to get those quarterly churn and retention rates sorted out. First, let's align on standard definitions to make sure the numbers make sense:

• Churn Rate: Percentage of customers who left during the quarter, calculated as (churned customers ÷ total active customers in the quarter) × 100
• Retention Rate: Percentage of customers who stayed, which is either 100% minus churn rate, or (retained customers ÷ total active customers in the quarter) × 100

Step 1: Calculate Total Customers Per Quarter #

First, we need the total number of customers active each quarter to use as our denominator:

# Get total customers per quarter (assuming each row represents a unique customer's record for the quarter)
quarterly_total_customers = out.groupby(out["Date"].dt.quarter).size()
print("Total Customers per Quarter:")
print(quarterly_total_customers)

Step 2: Compute Quarterly Churn Rate #

Using your existing quarterly_churn_yes data, we can now calculate the churn rate as a percentage:

# Calculate churn rate (churned customers / total customers) * 100
quarterly_churn_rate = (quarterly_churn_yes["Churn"] / quarterly_total_customers) * 100
# Round to 2 decimal places for readability
quarterly_churn_rate = quarterly_churn_rate.round(2)

print("\nQuarterly Churn Rate (%):")
print(quarterly_churn_rate)

Step 3: Compute Quarterly Retention Rate #

You can calculate this two ways—either subtract churn rate from 100%, or directly compute retained customers vs total customers:

# Option 1: Retention rate = 100% - churn rate
quarterly_retention_rate_opt1 = 100 - quarterly_churn_rate

# Option 2: Calculate retained customers first, then compute rate
quarterly_retained_customers = quarterly_total_customers - quarterly_churn_yes["Churn"]
quarterly_retention_rate_opt2 = (quarterly_retained_customers / quarterly_total_customers) * 100
quarterly_retention_rate_opt2 = quarterly_retention_rate_opt2.round(2)

print("\nQuarterly Retention Rate (%):")
print(quarterly_retention_rate_opt2)

Bonus: Retention Rate vs Previous Quarter (Advanced) #

If you want to calculate retention based on the prior quarter's customer base (i.e., how many customers from Q1 stayed into Q2), you'll need to track individual customer presence across quarters. Here's a quick example assuming you have a CustomerID column:

# Create a table showing which customers were active in each quarter
customer_quarter_presence = out.groupby(['CustomerID', out["Date"].dt.quarter]).size().unstack(fill_value=0)

# Count customers who were active in the current quarter AND the previous one
retained_from_prev_quarter = (customer_quarter_presence.cumsum(axis=1) > 1).sum(axis=0)

# Get previous quarter's total customers (shift the total series)
prev_quarter_total = quarterly_total_customers.shift(1)

# Calculate retention rate vs previous quarter
quarterly_retention_vs_prev = (retained_from_prev_quarter / prev_quarter_total) * 100
quarterly_retention_vs_prev = quarterly_retention_vs_prev.round(2)

print("\nQuarterly Retention Rate (vs Previous Quarter) (%):")
print(quarterly_retention_vs_prev)

Example Context #

Using your sample churn numbers, if Q1 had 5000 total customers, the churn rate would be (1154/5000)*100 = 23.08%, and the retention rate would be 76.92%.

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