SaaS Metrics: MRR, ARR, Churn, LTV Optimization

#!/usr/bin/env python3
"""SaaS metrics calculation."""

import pandas as pd
from datetime import datetime, timedelta

class SaaSMetrics:
    def __init__(self, subscriptions_df, events_df):
        self.subscriptions = subscriptions_df
        self.events = events_df
        
    def calculate_mrr(self, date):
        """Calculate Monthly Recurring Revenue."""
        
        # Get active subscriptions at date
        active_subs = self.subscriptions[
            (self.subscriptions['start_date'] <= date) &
            (self.subscriptions['end_date'] >= date) |
            (self.subscriptions['end_date'].isna())
        ]
        
        # Calculate MRR from subscriptions
        mrr = 0
        for _, sub in active_subs.iterrows():
            if sub['billing_period'] == 'monthly':
                mrr += sub['amount']
            elif sub['billing_period'] == 'yearly':
                mrr += sub['amount'] / 12
            elif sub['billing_period'] == 'hourly':
                mrr += sub['amount'] * 730  # ~730 hours/month
        
        # Add recurring add-ons
        addons = self.subscriptions[
            (self.subscriptions['type'] == 'addon') &
            (self.subscriptions['start_date'] <= date) &
            (self.subscriptions['end_date'] >= date)
        ]
        
        mrr += addons['amount'].sum()
        
        return mrr
    
    def calculate_arr(self, date):
        """Calculate Annual Recurring Revenue."""
        
        return self.calculate_mrr(date) * 12
    
    def calculate_mrr_growth(self, current_mrr, previous_mrr):
        """Calculate MRR growth rate."""
        
        if previous_mrr == 0:
            return float('inf')
        
        growth = (current_mrr - previous_mrr) / previous_mrr
        return growth * 100  # As percentage
    
    def breakdown_mrr_by_component(self, date):
        """Break down MRR into components."""
        
        new_mrr = self.get_new_mrr(date)
        expansion_mrr = self.get_expansion_mrr(date)
        contraction_mrr = self.get_contraction_mrr(date)
        churn_mrr = self.get_churn_mrr(date)
        
        return {
            'new': new_mrr,
            'expansion': expansion_mrr,
            'contraction': -contraction_mrr,
            'churn': -churn_mrr,
            'net_new': new_mrr + expansion_mrr - contraction_mrr - churn_mrr
        }
    
    def get_new_mrr(self, date):
        """Get MRR from new customers."""
        
        month_start = date.replace(day=1)
        
        new_subs = self.subscriptions[
            self.subscriptions['start_date'].dt.to_period('M') == month_start.to_period('M')
        ]
        
        return new_subs['mrr'].sum()

-- SQL: Calculate ARR
SELECT 
    DATE_TRUNC('month', subscription_date) as month,
    SUM(
        CASE 
            WHEN billing_period = 'monthly' THEN amount
            WHEN billing_period = 'yearly' THEN amount / 12
            WHEN billing_period = 'quarterly' THEN amount / 3
        END
    ) * 12 as arr
FROM subscriptions
WHERE status = 'active'
GROUP BY DATE_TRUNC('month', subscription_date)
ORDER BY month;

def calculate_churn_metrics(subscriptions, date):
    """Calculate various churn metrics."""
    
    # Customer Churn Rate
    start_customers = get_customers_at_date(subscriptions, date - timedelta(days=30))
    end_customers = get_customers_at_date(subscriptions, date)
    churned_customers = get_churned_customers(subscriptions, date)
    
    customer_churn = len(churned_customers) / start_customers if start_customers > 0 else 0
    
    # Revenue Churn Rate
    start_mrr = calculate_mrr(subscriptions, date - timedelta(days=30))
    churned_mrr = get_churned_mrr(subscriptions, date)
    
    revenue_churn = churned_mrr / start_mrr if start_mrr > 0 else 0
    
    # Net Revenue Churn (including expansion)
    expansion_mrr = get_expansion_mrr(subscriptions, date)
    
    net_revenue_churn = (churned_mrr - expansion_mrr) / start_mrr if start_mrr > 0 else 0
    
    return {
        'customer_churn_rate': customer_churn * 100,
        'revenue_churn_rate': revenue_churn * 100,
        'net_revenue_churn_rate': net_revenue_churn * 100
    }

def cohort_retention_analysis(subscriptions):
    """Analyze retention by cohort."""
    
    # Create cohorts by signup month
    subscriptions['cohort'] = subscriptions['start_date'].dt.to_period('M')
    
    retention_matrix = []
    
    for cohort in subscriptions['cohort'].unique():
        cohort_subs = subscriptions[subscriptions['cohort'] == cohort]
        cohort_size = len(cohort_subs)
        
        retention = []
        for month_offset in range(13):
            month = cohort + month_offset
            active = len(cohort_subs[
                (cohort_subs['end_date'].isna()) |
                (cohort_subs['end_date'].dt.to_period('M') >= month)
            ])
            
            retention_rate = (active / cohort_size * 100) if cohort_size > 0 else 0
            retention.append(retention_rate)
        
        retention_matrix.append({
            'cohort': str(cohort),
            'size': cohort_size,
            'retention': retention
        })
    
    return retention_matrix

def calculate_ltv(subscriptions, customers, date):
    """Calculate Customer Lifetime Value."""
    
    # Average Revenue Per Account (ARPA)
    arpa = calculate_mrr(subscription, date) / len(customers)
    
    # Gross Margin (typically 70-85% for SaaS)
    gross_margin = 0.80
    
    # Average Customer Lifespan
    avg_churn = calculate_avg_churn_rate(subscriptions)
    avg_lifespan = 1 / avg_churn  # months
    
    # LTV = ARPA × Gross Margin × Lifespan
    ltv = arpa * gross_margin * avg_lifespan
    
    return ltv

def calculate_cac(sales_marketing_costs, new_customers, period='month'):
    """Calculate Customer Acquisition Cost."""
    
    total_costs = sales_marketing_costs[period].sum()
    new_customers_count = new_customers[period].count()
    
    cac = total_costs / new_customers_count if new_customers_count > 0 else 0
    
    return cac

def calculate_ltv_cac_ratio(ltv, cac):
    """Calculate LTV:CAC ratio."""
    
    return ltv / cac if cac > 0 else 0

def calculate_cac_payback_period(cac, arpa, gross_margin):
    """Calculate CAC payback period in months."""
    
    monthly_revenue = arpa * gross_margin
    monthly_contribution = monthly_revenue - get_monthly_costs_per_customer()
    
    payback_months = cac / monthly_contribution if monthly_contribution > 0 else float('inf')
    
    return payback_months

def calculate_nrr(subscriptions, date):
    """Calculate Net Revenue Retention."""
    
    # Get MRR at start of period
    start_mrr = calculate_mrr(subscriptions, date - timedelta(days=30))
    
    # Calculate changes during period
    expansion = get_expansion_mrr(subscriptions, date)
    contraction = get_contraction_mrr(subscriptions, date)
    churn = get_churn_mrr(subscriptions, date)
    
    # NRR = (Start MRR + Expansion - Contraction - Churn) / Start MRR
    nrr = ((start_mrr + expansion - contraction - churn) / start_mrr * 100) if start_mrr > 0 else 0
    
    return nrr

# Grafana SaaS Metrics Dashboard
{
  "dashboard": {
    "title": "SaaS Metrics Dashboard",
    "panels": [
      {
        "title": "MRR Trend",
        "type": "timeseries",
        "targets": [
          {
            "expr": "mrr",
            "legendFormat": "MRR"
          },
          {
            "expr": "mrr - churn_mrr",
            "legendFormat": "Net MRR"
          }
        ]
      },
      {
        "title": "Customer Growth",
        "type": "stat",
        "targets": [
          {
            "expr": "total_customers",
            "legendFormat": "Total"
          },
          {
            "expr": "new_customers_monthly",
            "legendFormat": "New"
          }
        ]
      },
      {
        "title": "Churn Rate",
        "type": "gauge",
        "targets": [
          {
            "expr": "churn_rate",
            "thresholds": {
              "mode": "absolute",
              "steps": [
                {"color": "green", "value": null},
                {"color": "yellow", "value": 5},
                {"color": "red", "value": 10}
              ]
            }
          }
        ]
      },
      {
        "title": "Unit Economics",
        "type": "table",
        "targets": [
          {
            "expr": "ltv_cac_ratio"
          },
          {
            "expr": "cac_payback_months"
          },
          {
            "expr": "nrr"
          }
        ]
      }
    ]
  }
}