📊 Data Analysis Methodology Report

Executive Summary

The SaaS Revenue Lifecycle Analyzer is a comprehensive revenue intelligence platform that employs advanced data analysis techniques to identify revenue leakage and optimization opportunities across the customer lifecycle. This report documents the complete analytical methodology, data sources, statistical techniques, and machine learning models that power the platform's insights.

1. Data Architecture

Database Technology

DuckDB Analytical Database

• Type: In-process OLAP database
• Storage: Columnar format optimized for analytics
• Rationale: Fast analytical queries, easy deployment, no external dependencies
• Performance: Sub-second queries on millions of rows

Schema Design

The platform tracks 10 interconnected entities across the customer lifecycle:

├── Acquisition Layer
│   ├── leads (3,000+ records)
│   ├── sales_reps (30 records)
│   └── marketing_spend (500+ records)
│
├── Conversion Layer
│   ├── opportunities (3,000+ records)
│   └── stage_transitions (15,000+ records)
│
├── Revenue Layer
│   ├── customers (1,600+ records)
│   ├── mrr_movements (8,000+ records)
│   └── expansion_opportunities (500+ records)
│
└── Engagement Layer
    ├── usage_events (50,000+ records)
    └── nps_surveys (5,000+ records)

Index Strategy

Key Indexed Fields:

• customer_id, opportunity_id, lead_id (primary lookups)
• status, health_score, churn_probability (filtering)
• created_date, start_date, movement_date (time-based queries)
• Compound indexes on (status, health_score), (customer_id, event_date), etc.

2. Data Sources & Generation

Synthetic Data Generation

Methodology: Controlled pseudo-random generation with realistic distributions

Data Generator: backend/data/generator.py

Generation Parameters

Seeded Random Generation:

Faker.seed(42)
random.seed(42)
np.random.seed(42)

Ensures reproducible, consistent data across runs while maintaining realistic variability.

Industry-Calibrated Assumptions

Source: backend/data/assumptions.py

Revenue Metrics by Segment

Realistic Patterns Implemented

1. Seasonal Variation: Lead volume fluctuates 20% around baseline
2. Segment-Based Behavior: Conversion rates vary by company size
3. Usage-Churn Correlation: Low usage (< 25th percentile) → 80%+ churn risk
4. Rep Performance Distribution: Top 20% of reps generate 40% of revenue
5. NPS-Health Correlation: NPS 9-10 (Promoters) → Green health score

3. Analysis Modules

Module Architecture

Location: backend/analysis/

3.1 Churn Analysis (churn.py)

Purpose: Predict and analyze customer churn risk

Key Functions

1. get_churn_summary()
   • Calculates overall churn rate, ARR at risk, average churn probability
   • Aggregates active vs churned customer metrics
   • Returns comprehensive churn health indicators
2. get_at_risk_customers(risk_threshold=0.5, min_mrr=0)
   • Identifies customers exceeding churn risk threshold
   • Filters by minimum MRR for prioritization
   • Returns sorted list by churn probability descending
3. get_churn_by_segment(segment_field)
   • Segments churn analysis by company_size, industry, or channel
   • Calculates segment-specific churn rates and ARR at risk
   • Enables targeted retention strategies

3.2 Health Score Analysis (health_score.py)

Purpose: Calculate composite customer health scores

Methodology: Multi-factor weighted scoring model

Health Score Components

Health Classification

• Green (Healthy) Score > 70
• Yellow (At Risk) Score 40-70
• Red (Critical) Score < 40

3.3 Funnel Analysis (funnel.py)

Purpose: Analyze sales funnel performance and conversion optimization

Key Metrics

1. Funnel Summary
   • Total Opportunities
   • Total Pipeline Value
   • Overall Conversion Rate (Lead → Customer)
   • Closed Won Value
   • Average Deal Size
2. Stage-by-Stage Conversion
   • Stages: Lead → Qualified → Discovery → Proposal → Negotiation → Closed Won/Lost
   • Count conversion rate
   • Dollar conversion rate
   • Average time in stage
3. Velocity Metrics
   • Median days in each stage
   • P75 (75th percentile) benchmark
   • Slow deal identification (> P75)

3.4 Revenue Intelligence (revenue.py)

Purpose: Calculate advanced revenue metrics and projections

MRR Waterfall (12-month)

Starting MRR (12 months ago)
+ New MRR (new customers)
+ Expansion MRR (upgrades/upsells)
- Contraction MRR (downgrades)
- Churn MRR (lost customers)
= Ending MRR (current)

Net Revenue Retention (NRR)

Formula:
NRR = (Starting MRR + Expansion - Contraction - Churn) / Starting MRR

Interpretation:
  < 100%: Losing revenue from existing customers
  100-110%: Good retention with modest expansion
  > 120%: Excellent - strong expansion revenue

4. Statistical Methodologies

4.1 Descriptive Statistics

Measures of Central Tendency:

• Mean (average) for normally distributed metrics
• Median for skewed distributions (deal size, time-to-close)
• Mode for categorical analysis (most common loss reason)

Measures of Dispersion:

• Standard deviation for variability assessment
• Percentiles (P25, P50/median, P75, P90) for outlier detection
• Interquartile range (IQR) for robust variance

4.2 Time Series Analysis

Trend Analysis:

# 30-day moving average for smoothing
rolling_avg = df['metric'].rolling(window=30).mean()

# Linear regression for trend direction
from scipy.stats import linregress
slope, intercept, r_value, p_value, std_err = linregress(x, y)

4.3 Cohort Analysis

Retention Curve Example:

4.4 Correlation Analysis

Usage-Churn Correlation:

from scipy.stats import pearsonr

# Calculate correlation coefficient
correlation, p_value = pearsonr(usage_score, churn_probability)
# Typical result: r = -0.65 (strong negative correlation)

NPS-Expansion Correlation:

• Promoters (NPS 9-10): 3x higher expansion rate
• Detractors (NPS 0-6): 5x higher churn rate

5. Machine Learning Models

5.1 Churn Prediction Model

Algorithm: XGBoost (Gradient Boosted Decision Trees)

Model Architecture

from xgboost import XGBClassifier

model = XGBClassifier(
    n_estimators=100,
    max_depth=6,
    learning_rate=0.1,
    subsample=0.8,
    colsample_bytree=0.8,
    random_state=42
)

Feature Engineering (15 features)

Model Performance Metrics

Feature Importance (SHAP values)

Top 5 drivers of churn:

1. Usage trend (declining usage) - 28%
2. NPS score (detractors) - 22%
3. Payment issues - 15%
4. Tenure (new customers) - 12%
5. Last login recency - 10%

Model Interpretability

import shap

# SHAP explainer for model transparency
explainer = shap.TreeExplainer(model)
shap_values = explainer.shap_values(X)

# Waterfall plot for individual predictions
shap.waterfall_plot(shap_values[i])

Provides breakdown of which features contribute to each customer's churn risk.

6. Business Metrics Calculations

6.1 Acquisition Metrics

Lead Velocity Rate (LVR)

LVR = (Leads This Month - Leads Last Month) / Leads Last Month × 100%

Cost Per Lead (CPL)

CPL = Marketing Spend / Leads Generated

6.2 Sales Metrics

Win Rate

Win Rate = Closed Won / (Closed Won + Closed Lost) × 100%

Sales Efficiency (Magic Number)

Magic Number = (ARR Growth This Quarter) / (Sales & Marketing Spend Last Quarter)

Interpretation:
  > 1.0: Very efficient
  0.75-1.0: Good
  < 0.5: Inefficient

6.3 Revenue Metrics

Annual Recurring Revenue (ARR)

ARR = MRR × 12

Components:
- New ARR: From new customers
- Expansion ARR: Upsells and cross-sells
- Contraction ARR: Downgrades
- Churned ARR: Lost customers

Net Revenue Retention (NRR)

NRR = ((Starting ARR + Expansion ARR - Contraction ARR - Churned ARR) / Starting ARR) × 100%

Cohort: Customers active 12 months ago

6.4 Customer Metrics

Customer Lifetime Value (LTV)

LTV = (Average Monthly Revenue per Customer × Gross Margin %) / Monthly Churn Rate

Example:
- ARPA: $5,000/month
- Gross Margin: 80%
- Churn Rate: 2%/month
- LTV = ($5,000 × 0.80) / 0.02 = $200,000

LTV:CAC Ratio

Ratio = LTV / CAC

Benchmarks:
  < 1x: Unsustainable
  1-3x: Growth stage, monitor closely
  3-5x: Healthy unit economics
  > 5x: Excellent, consider accelerating growth

6.5 Efficiency Metrics

Rule of 40

Rule of 40 = Revenue Growth Rate % + Profit Margin %

Benchmark: ≥ 40% indicates healthy balance of growth and profitability

Burn Multiple

Burn Multiple = Net Cash Burned / Net New ARR

Interpretation:
  < 1.0x: Exceptional capital efficiency
  1.0-1.5x: Great
  1.5-3.0x: Good
  > 3.0x: Poor efficiency

7. Data Quality & Validation

7.1 Input Validation

SQL Injection Prevention:

All user inputs validated using regex patterns:

def validate_customer_id(customer_id: str) -> bool:
    """Validate customer ID format."""
    pattern = r'^CUST_[A-Z0-9]{8}$'
    return bool(re.match(pattern, customer_id))

def validate_date_string(date_str: str) -> str:
    """Validate date format (YYYY-MM-DD)."""
    if not re.match(r'^\d{4}-\d{2}-\d{2}$', date_str):
        raise ValueError("Invalid date format")
    datetime.strptime(date_str, '%Y-%m-%d')  # Verify valid date
    return date_str

def validate_segment_field(segment: str) -> str:
    """Whitelist segment fields."""
    valid = {'company_size', 'industry', 'channel'}
    if segment not in valid:
        raise ValueError(f"Invalid segment: {segment}")
    return segment

7.2 Data Integrity Checks

Business Logic Validation:

# MRR cannot be negative
assert mrr >= 0, "MRR must be non-negative"

# Churn date must be after start date
if churn_date:
    assert churn_date > start_date, "Invalid churn date"

# NPS score must be 0-10
assert 0 <= nps_score <= 10, "NPS must be 0-10"

# Conversion rate must be 0-1
assert 0 <= conversion_rate <= 1, "Invalid conversion rate"

7.3 Outlier Detection

Statistical Methods:

def detect_outliers_iqr(data):
    """Detect outliers using IQR method."""
    Q1 = data.quantile(0.25)
    Q3 = data.quantile(0.75)
    IQR = Q3 - Q1

    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR

    return (data < lower_bound) | (data > upper_bound)

Domain-Specific Rules:

• MRR > $100K flagged for review (potential enterprise)
• Deal cycles > 365 days flagged as stale
• Churn probability > 0.95 reviewed for model validity

8. Performance Optimization

8.1 Query Optimization

Index Strategy:

23 indexes created based on query patterns:

-- Frequent filters
CREATE INDEX idx_customers_status ON customers(status);
CREATE INDEX idx_customers_health ON customers(health_score);

-- Time-based queries
CREATE INDEX idx_customers_start ON customers(start_date);
CREATE INDEX idx_mrr_date ON mrr_movements(movement_date);

-- Compound indexes for multi-column filters
CREATE INDEX idx_customers_status_health ON customers(status, health_score);
CREATE INDEX idx_mrr_customer_date ON mrr_movements(customer_id, movement_date);

8.2 Caching Strategy

API Response Caching:

In-memory LRU cache with TTL:

from functools import lru_cache

@cached(ttl=60, key_prefix="summary")
async def get_executive_summary():
    """Cached for 60 seconds."""
    # Expensive calculations
    return results

Cache Configuration

8.3 Parallel Processing

Concurrent Query Execution:

import asyncio

async def fetch_all_metrics():
    """Fetch metrics in parallel."""
    results = await asyncio.gather(
        get_revenue_summary(),
        get_churn_summary(),
        get_funnel_summary(),
        get_health_distribution()
    )
    return results

9. AI-Powered Insights

The platform integrates Claude AI (by Anthropic) to provide on-demand, context-aware analysis across every dashboard page. Rather than pre-computed static text, insights are generated in real-time using live data from the database, giving users actionable intelligence tailored to the current state of their business.

9.1 API Endpoints

All AI endpoints are registered under the /api/ai prefix and accept POST requests. Each endpoint queries live data from the database and constructs a specialized prompt for Claude.

9.2 Prompt Engineering

Each endpoint uses a specialized system prompt that establishes a domain-expert persona. The system dynamically selects between two prompt strategies based on user input:

Default Mode (No Custom Question)

The system prompt is highly specific to the page context with structured output instructions:

// Executive Summary example:
System: "Provide a Monday morning briefing. Focus on:
  1) Overall health narrative
  2) Key anomalies
  3) Top 3 priorities with expected impact
  4) One metric deserving deeper investigation.
  Use numbers."

User: [Full data context from database queries]

Custom Question Mode

When the user provides a specific question, the system prompt becomes a flexible domain expert:

// Executive Summary example:
System: "You are an expert SaaS business strategist.
  Answer using provided data.
  Be concise, data-driven, actionable."

User: [Data context + user's specific question]

System Roles by Page

9.3 Backend Implementation

Location: backend/api/routes/ai_insights.py

Shared Helpers

_get_api_key()      # Retrieves ANTHROPIC_API_KEY from environment
_call_claude()      # Core API wrapper: system prompt + user message → insight + model name
_format_dict()      # JSON formatter for context data in prompts
get_health_category()  # Maps health_score (0-100) to Green/Yellow/Red
get_nps_category()     # Maps NPS score to Promoter/Passive/Detractor

Model Configuration

from anthropic import Anthropic

client = Anthropic(api_key=api_key)
response = client.messages.create(
    model="claude-sonnet-4-20250514",
    max_tokens=1024,
    system=system_prompt,
    messages=[{"role": "user", "content": user_message}]
)

9.4 Frontend Components

Location: frontend/components/ai/

PageInsights (Reusable)

A shared component used on 5 dashboard pages (Executive, Revenue, Risk, Funnel, Simulator):

• Collapsible card UI with expand/collapse toggle
• "Generate Insights" button for default analysis
• Custom question textarea with Enter-to-send support
• Suggested questions displayed as clickable buttons
• Loading spinner during API calls
• Result display with model name attribution
• Clear and Regenerate controls

// Props interface
interface PageInsightsProps {
  pageId: string;           // e.g. "executive", "revenue"
  pageTitle: string;        // Display name
  apiEndpoint: string;      // e.g. "/api/ai/executive-insights"
  buttonLabel?: string;     // e.g. "Generate Executive Briefing"
  suggestedQuestions: string[];
  contextData?: Record<string, any>;
}

CustomerInsights (Dedicated)

A dedicated component for individual customer analysis on the Customer Detail page:

• Accepts customerId and customerName props
• Hardcoded suggested questions (e.g., "Why is this customer at risk?")
• Calls /api/ai/customer-insights endpoint

9.5 Configuration

ANTHROPIC_API_KEY=your_api_key_here

Appendix A: Glossary

Appendix B: References

Industry Benchmarks

• KeyBanc Capital Markets SaaS Survey
• OpenView SaaS Benchmarks
• SaaStr Annual Reports
• ChartMogul SaaS Metrics Reports

Statistical Methods

• "Applied Predictive Modeling" - Kuhn & Johnson
• "An Introduction to Statistical Learning" - James et al.
• SciPy Documentation: scipy.stats

Machine Learning

• XGBoost Documentation
• SHAP (SHapley Additive exPlanations) Library
• scikit-learn User Guide

Appendix C: Technology Stack

Backend

• Python 3.11+
• FastAPI (API framework)
• DuckDB 0.9+ (analytical database)
• Pandas 2.1+ (data manipulation)
• NumPy 1.26+ (numerical computing)
• scikit-learn 1.4+ (machine learning)
• XGBoost 2.0+ (gradient boosting)
• SHAP 0.44+ (model interpretability)
• Anthropic SDK 0.18+ (Claude AI integration)

Frontend

• Next.js 14 (React framework)
• TypeScript 5.3+ (type safety)
• TailwindCSS (styling)