Investigating Subscription Churn at StaffWise

Diagnosed and quantified $1.99M in revenue leakage for a SaaS company by analyzing churn and payment failure patterns, providing a data-driven roadmap to recover lost income.

  • Google Collab
  • Python
  • Pandas
  • Numpy
  • Microsoft Excel
  • Power Query
  • Data Modeling
  • DAX

At a Glance

  • StaffWise, a SaaS platform, faced stagnating revenue despite new customer sign-ups, with leadership suspecting significant losses from customer churn and payment failures.
  • Analysis revealed a 69.4% churn rate and a $1.99M revenue gap, driven primarily by failed payments ($1.05M) and a critical lack of retention among monthly subscribers.
  • Built an interactive Excel dashboard to pinpoint high-churn customer segments and quantify loss drivers, providing actionable strategies to improve retention and payment processes.

Dashboard

Business Problem

StaffWise’s revenue was stagnating despite healthy customer acquisition. Leadership needed to understand the scale and source of revenue leakage: How much was lost to churn versus payment failures? Which customer segments were most at risk, and what was the financial impact?

Approach & Analysis

Step 1: Data Simulation & Engineering

  • Engineered a realistic, multi-year subscription dataset from scratch using Python to model customer behavior, payment outcomes, and churn.
  • Created robust dimension tables (Date, Customer, Subscription Plan) to ensure analytical integrity.

Step 2: ETL & Data Modeling in Excel

  • Performed ETL using Power Query to merge fact and dimension tables, clean data, and create a star schema.
  • Built a custom Customer Status column with advanced M logic to programmatically classify customers as Active, Trial, or Churned.
  • Engineered calculated columns for Expected Revenue, Actual Revenue, and Coverage End Date.

Step 3: DAX & Measure Building

  • Developed a suite of custom DAX measures to calculate core KPIs:
    • [Churn Rate], [Active Customers], [Average Tenure]
    • [Failed Payments Loss], [Refunds Loss], [Free Trial Loss], [Revenue Gap]

Step 4: Analysis & Visualization

  • Utilized PivotTables and advanced Excel formulas to dynamically analyze churn and revenue loss by key segments:
    • Subscription Plan, Billing Cycle, Customer Tenure.
  • Built an interactive Excel dashboard featuring:
    • KPI Cards: Revenue Gap, Total Churn Rate, Active Customers.
    • Segmented Charts: Churn rate by plan and billing cycle.
    • Trend Analysis: Revenue leakage by month.
    • Driver Analysis: Tables identifying top loss contributors.

Key Findings

  • Crippling Churn: A 69.4% customer churn rate was the core issue, far outweighing acquisition gains.

  • $2M Revenue Gap: The company was leaving $1.99M on the table, representing 14.5% of expected revenue.

  • Primary Loss Driver: Failed payments were the largest source of leakage, accounting for $1.05M (53% of the gap).

  • High-Risk Segment: Customers on monthly billing plans churned at an alarming 83.6% rate, identifying a critical vulnerability.

  • Long-Term Value at Risk: 70% of revenue loss came from customers who churned after 16+ months, proving it wasn’t just a new-customer problem.

Recommendations

  1. Revamp Payment Systems: Implement automated payment retry systems and proactive dunning emails to recapture the $1.05M lost from failures.

  2. Incentivize Annual Plans: Create financial incentives to move customers from high-churn monthly plans to more stable annual subscriptions.

  3. Launch Targeted Retention Campaigns: Develop proactive outreach for high-risk segments (e.g., Core HR monthly users) at the 12-month mark, before the peak churn period.

  4. Improve Onboarding: Enhance initial customer experience for entry-level plans to demonstrate value faster and reduce early churn.

Explore the Project

Read the 6-Part Blog Series on this Project Explore the Data Simulation Code
Scroll to Top