Cro Advisor

#!/usr/bin/env python3
"""
Pipeline Coverage Calculator - Calculate coverage ratios and analyze pipeline health.

Calculates pipeline coverage by quarter position, analyzes stage distribution,
detects deal aging risks, and generates adequacy assessments with action recommendations.

Usage:
    python pipeline_coverage_calculator.py --input pipeline_data.json
    python pipeline_coverage_calculator.py --input pipeline_data.json --json
"""

import argparse
import json
import sys
from datetime import datetime, date


COVERAGE_TARGETS = {
    "q_minus_1": {"target": 4.0, "label": "Planning (Q-1)"},
    "q_start": {"target": 3.0, "label": "Quarter Start"},
    "mid_quarter": {"target": 2.0, "label": "Mid-Quarter"},
    "q_end": {"target": 1.5, "label": "Quarter End"},
}

STAGE_BENCHMARKS = {
    "lead": {"typical_conversion": 0.25, "max_age_days": 14},
    "discovery": {"typical_conversion": 0.55, "max_age_days": 21},
    "evaluation": {"typical_conversion": 0.45, "max_age_days": 30},
    "proposal": {"typical_conversion": 0.55, "max_age_days": 21},
    "negotiation": {"typical_conversion": 0.75, "max_age_days": 14},
}


def load_data(path):
    with open(path, "r") as f:
        return json.load(f)


def calculate_coverage(pipeline_value, quota):
    """Calculate pipeline coverage ratio."""
    if quota <= 0:
        return 0
    return round(pipeline_value / quota, 2)


def assess_coverage(coverage, quarter_position):
    """Assess coverage adequacy based on quarter position."""
    target_config = COVERAGE_TARGETS.get(quarter_position, COVERAGE_TARGETS["q_start"])
    target = target_config["target"]

    if coverage >= target:
        return {"status": "Adequate", "gap": 0, "action": "Maintain pipeline generation pace"}
    elif coverage >= target * 0.75:
        gap = round((target - coverage) * 100, 0)  # as percentage of quota
        return {"status": "At Risk", "gap": gap, "action": "Accelerate top-of-funnel activity"}
    else:
        gap = round((target - coverage) * 100, 0)
        return {"status": "Critical", "gap": gap, "action": "Emergency pipeline generation -- all hands"}


def analyze_stage_distribution(deals, stages):
    """Analyze pipeline health by stage."""
    distribution = {}
    total_value = 0

    for stage_name in stages:
        stage_deals = [d for d in deals if d.get("stage", "").lower() == stage_name.lower()]
        stage_value = sum(d.get("value", 0) for d in stage_deals)
        total_value += stage_value

        # Aging analysis
        aged_deals = []
        benchmark = STAGE_BENCHMARKS.get(stage_name.lower(), {})
        max_age = benchmark.get("max_age_days", 30)

        for d in stage_deals:
            age = d.get("age_days", 0)
            if age > max_age:
                aged_deals.append({
                    "name": d.get("name", "Unknown"),
                    "value": d.get("value", 0),
                    "age_days": age,
                    "over_by": age - max_age,
                })

        distribution[stage_name] = {
            "deal_count": len(stage_deals),
            "total_value": stage_value,
            "avg_value": round(stage_value / max(len(stage_deals), 1), 0),
            "aged_deals": sorted(aged_deals, key=lambda x: x["over_by"], reverse=True),
            "aged_deal_count": len(aged_deals),
            "typical_conversion": benchmark.get("typical_conversion", 0),
        }

    # Add percentages
    for stage_name, info in distribution.items():
        info["value_pct"] = round((info["total_value"] / max(total_value, 1)) * 100, 1)

    return distribution, total_value


def calculate_weighted_pipeline(distribution):
    """Calculate probability-weighted pipeline value."""
    weighted = 0
    for stage_name, info in distribution.items():
        weighted += info["total_value"] * info["typical_conversion"]
    return round(weighted, 0)


def detect_concentration_risk(deals, threshold_pct=25):
    """Detect deal concentration risks."""
    total_value = sum(d.get("value", 0) for d in deals)
    if total_value == 0:
        return []

    risks = []
    for d in deals:
        pct = (d.get("value", 0) / total_value) * 100
        if pct >= threshold_pct:
            risks.append({
                "deal": d.get("name", "Unknown"),
                "value": d.get("value", 0),
                "pct_of_pipeline": round(pct, 1),
                "risk": f"Single deal is {round(pct, 1)}% of pipeline",
            })

    return risks


def analyze_pipeline(data):
    """Run full pipeline analysis."""
    deals = data.get("deals", [])
    quota = data.get("quota", 0)
    quarter_position = data.get("quarter_position", "q_start")
    stages = data.get("stages", ["lead", "discovery", "evaluation", "proposal", "negotiation"])
    company = data.get("company", "Company")

    total_pipeline = sum(d.get("value", 0) for d in deals)
    coverage = calculate_coverage(total_pipeline, quota)
    adequacy = assess_coverage(coverage, quarter_position)

    stage_distribution, _ = analyze_stage_distribution(deals, stages)
    weighted_pipeline = calculate_weighted_pipeline(stage_distribution)
    weighted_coverage = calculate_coverage(weighted_pipeline, quota)

    concentration_risks = detect_concentration_risk(deals)

    # Total aged deals
    total_aged = sum(info["aged_deal_count"] for info in stage_distribution.values())
    aged_value = sum(
        sum(d["value"] for d in info["aged_deals"])
        for info in stage_distribution.values()
    )

    results = {
        "timestamp": datetime.now().isoformat(),
        "company": company,
        "quarter_position": quarter_position,
        "quota": quota,
        "total_pipeline": total_pipeline,
        "coverage_ratio": coverage,
        "coverage_target": COVERAGE_TARGETS.get(quarter_position, {}).get("target", 3.0),
        "adequacy": adequacy,
        "weighted_pipeline": weighted_pipeline,
        "weighted_coverage": weighted_coverage,
        "deal_count": len(deals),
        "stage_distribution": stage_distribution,
        "aged_deals_count": total_aged,
        "aged_deals_value": aged_value,
        "concentration_risks": concentration_risks,
        "recommendations": [],
    }

    # Recommendations
    recs = results["recommendations"]
    if adequacy["status"] == "Critical":
        recs.append(f"CRITICAL: Coverage at {coverage}x vs {results['coverage_target']}x target -- emergency pipeline generation needed")
    elif adequacy["status"] == "At Risk":
        recs.append(f"AT RISK: Coverage at {coverage}x vs {results['coverage_target']}x target -- increase top-of-funnel activity")

    if weighted_coverage < 1.0:
        recs.append(f"Weighted coverage {weighted_coverage}x suggests likely quota miss even with all deals progressing normally")

    if total_aged > 0:
        recs.append(f"{total_aged} deals ({aged_value:,.0f} value) past stage age limits -- review for deal acceleration or removal")

    if concentration_risks:
        recs.append(f"{len(concentration_risks)} deal(s) represent concentration risk -- diversify pipeline")

    # Stage health
    for stage, info in stage_distribution.items():
        if info["deal_count"] == 0 and stage in ("discovery", "evaluation"):
            recs.append(f"No deals in {stage} stage -- pipeline gap developing")

    return results


def format_text(results):
    lines = [
        "=" * 60,
        "PIPELINE COVERAGE ANALYSIS",
        "=" * 60,
        f"Company: {results['company']}",
        f"Quarter Position: {results['quarter_position']}",
        f"Analysis Date: {results['timestamp'][:10]}",
        "",
        "COVERAGE",
        f"  Quota: ${results['quota']:,.0f}",
        f"  Total Pipeline: ${results['total_pipeline']:,.0f}",
        f"  Coverage Ratio: {results['coverage_ratio']}x (target: {results['coverage_target']}x)",
        f"  Status: {results['adequacy']['status']}",
        f"  Weighted Pipeline: ${results['weighted_pipeline']:,.0f}",
        f"  Weighted Coverage: {results['weighted_coverage']}x",
        "",
        "STAGE DISTRIBUTION",
    ]

    for stage, info in results["stage_distribution"].items():
        lines.append(
            f"  {stage}: {info['deal_count']} deals, ${info['total_value']:,.0f} "
            f"({info['value_pct']}%), conv={info['typical_conversion']*100:.0f}%, "
            f"aged={info['aged_deal_count']}"
        )

    if results["aged_deals_count"] > 0:
        lines.append("")
        lines.append(f"AGING RISKS ({results['aged_deals_count']} deals, ${results['aged_deals_value']:,.0f})")
        for stage, info in results["stage_distribution"].items():
            for d in info["aged_deals"][:3]:
                lines.append(f"  {stage}: {d['name']} -- ${d['value']:,.0f}, {d['age_days']}d (over by {d['over_by']}d)")

    if results["concentration_risks"]:
        lines.append("")
        lines.append("CONCENTRATION RISKS")
        for r in results["concentration_risks"]:
            lines.append(f"  {r['deal']}: ${r['value']:,.0f} ({r['pct_of_pipeline']}% of pipeline)")

    if results["recommendations"]:
        lines.append("")
        lines.append("RECOMMENDATIONS")
        for rec in results["recommendations"]:
            lines.append(f"  * {rec}")

    return "\n".join(lines)


def main():
    parser = argparse.ArgumentParser(description="Calculate pipeline coverage ratios and analyze pipeline health")
    parser.add_argument("--input", required=True, help="Path to JSON pipeline data file")
    parser.add_argument("--json", action="store_true", help="Output in JSON format")
    args = parser.parse_args()

    try:
        data = load_data(args.input)
    except (FileNotFoundError, json.JSONDecodeError) as e:
        print(f"Error loading input file: {e}", file=sys.stderr)
        sys.exit(1)

    results = analyze_pipeline(data)

    if args.json:
        print(json.dumps(results, indent=2))
    else:
        print(format_text(results))


if __name__ == "__main__":
    main()

#!/usr/bin/env python3
"""
Revenue Waterfall Analyzer - Analyze ARR waterfall with NRR/GRR and trend detection.

Calculates net new ARR, NRR, GRR across multiple periods. Detects retention trends,
flags risk signals, and benchmarks against SaaS industry standards.

Usage:
    python revenue_waterfall_analyzer.py --input revenue_data.json
    python revenue_waterfall_analyzer.py --input revenue_data.json --json
"""

import argparse
import json
import sys
from datetime import datetime


NRR_BENCHMARKS = {
    "world_class": {"min": 130, "label": "World-class (Snowflake/Twilio tier)"},
    "excellent": {"min": 110, "label": "Excellent"},
    "healthy": {"min": 100, "label": "Healthy"},
    "concerning": {"min": 90, "label": "Concerning"},
    "critical": {"min": 0, "label": "Critical -- leaky bucket"},
}

GRR_BENCHMARKS = {
    "excellent": {"min": 95, "label": "Excellent"},
    "good": {"min": 90, "label": "Good"},
    "acceptable": {"min": 85, "label": "Acceptable"},
    "concerning": {"min": 80, "label": "Concerning"},
    "critical": {"min": 0, "label": "Critical"},
}


def load_data(path):
    with open(path, "r") as f:
        return json.load(f)


def get_benchmark_label(value, benchmarks):
    """Get benchmark label for a value."""
    for level, config in benchmarks.items():
        if value >= config["min"]:
            return config["label"]
    return "Below all benchmarks"


def analyze_period(period):
    """Analyze a single period's revenue waterfall."""
    opening = period.get("opening_arr", 0)
    new_logo = period.get("new_logo_arr", 0)
    expansion = period.get("expansion_arr", 0)
    contraction = period.get("contraction_arr", 0)
    churned = period.get("churned_arr", 0)

    closing = opening + new_logo + expansion - contraction - churned
    net_new = new_logo + expansion - contraction - churned

    nrr = round(((opening + expansion - contraction - churned) / max(opening, 1)) * 100, 1)
    grr = round(((opening - contraction - churned) / max(opening, 1)) * 100, 1)

    expansion_rate = round((expansion / max(opening, 1)) * 100, 1)
    churn_rate = round((churned / max(opening, 1)) * 100, 1)
    contraction_rate = round((contraction / max(opening, 1)) * 100, 1)

    return {
        "period": period.get("period", "Unknown"),
        "opening_arr": opening,
        "new_logo_arr": new_logo,
        "expansion_arr": expansion,
        "contraction_arr": contraction,
        "churned_arr": churned,
        "closing_arr": round(closing, 0),
        "net_new_arr": round(net_new, 0),
        "nrr_pct": nrr,
        "grr_pct": grr,
        "expansion_rate_pct": expansion_rate,
        "churn_rate_pct": churn_rate,
        "contraction_rate_pct": contraction_rate,
        "nrr_benchmark": get_benchmark_label(nrr, NRR_BENCHMARKS),
        "grr_benchmark": get_benchmark_label(grr, GRR_BENCHMARKS),
    }


def detect_trends(period_results):
    """Detect trends across periods."""
    trends = []

    if len(period_results) < 2:
        return trends

    nrr_values = [p["nrr_pct"] for p in period_results]
    grr_values = [p["grr_pct"] for p in period_results]
    churn_values = [p["churn_rate_pct"] for p in period_results]

    # NRR trend
    if len(nrr_values) >= 2:
        recent_avg = sum(nrr_values[-2:]) / 2
        older_avg = sum(nrr_values[:max(1, len(nrr_values) - 2)]) / max(1, len(nrr_values) - 2)
        if recent_avg < older_avg - 2:
            trends.append({"metric": "NRR", "direction": "declining", "severity": "high" if recent_avg < 100 else "medium"})
        elif recent_avg > older_avg + 2:
            trends.append({"metric": "NRR", "direction": "improving", "severity": "positive"})

    # Churn trend
    if len(churn_values) >= 2:
        if churn_values[-1] > churn_values[-2] * 1.1:
            trends.append({"metric": "Churn Rate", "direction": "increasing", "severity": "high"})

    # Expansion vs churn balance
    last = period_results[-1]
    if last["expansion_arr"] < last["churned_arr"]:
        trends.append({"metric": "Expansion vs Churn", "direction": "churn exceeds expansion", "severity": "high"})

    return trends


def analyze_revenue(data):
    """Run full revenue waterfall analysis."""
    periods = data.get("periods", [])
    company = data.get("company", "Company")

    results = {
        "timestamp": datetime.now().isoformat(),
        "company": company,
        "periods_analyzed": len(periods),
        "period_results": [],
        "trends": [],
        "risk_signals": [],
        "recommendations": [],
        "summary": {},
    }

    for period in periods:
        results["period_results"].append(analyze_period(period))

    # Detect trends
    results["trends"] = detect_trends(results["period_results"])

    # Risk signals
    if results["period_results"]:
        latest = results["period_results"][-1]

        if latest["nrr_pct"] < 90:
            results["risk_signals"].append({
                "signal": f"NRR at {latest['nrr_pct']}% -- CRISIS level",
                "action": "Stop scaling sales. Fix retention first.",
                "severity": "critical",
            })
        elif latest["nrr_pct"] < 100:
            results["risk_signals"].append({
                "signal": f"NRR at {latest['nrr_pct']}% -- churn eating expansion",
                "action": "Diagnose: product gap, CS gap, or ICP problem?",
                "severity": "warning",
            })

        if latest["grr_pct"] < 80:
            results["risk_signals"].append({
                "signal": f"GRR at {latest['grr_pct']}% -- base is eroding",
                "action": "Immediate churn and contraction analysis needed",
                "severity": "critical",
            })

        if latest["expansion_rate_pct"] < 5:
            results["risk_signals"].append({
                "signal": "Expansion revenue below 5% -- upsell motion weak or missing",
                "action": "Design expansion triggers, usage-based upsell alerts, cross-sell bundles",
                "severity": "warning",
            })

    # Summary
    if results["period_results"]:
        latest = results["period_results"][-1]
        first = results["period_results"][0]
        results["summary"] = {
            "latest_arr": latest["closing_arr"],
            "arr_growth": round(latest["closing_arr"] - first["opening_arr"], 0),
            "avg_nrr": round(sum(p["nrr_pct"] for p in results["period_results"]) / len(results["period_results"]), 1),
            "avg_grr": round(sum(p["grr_pct"] for p in results["period_results"]) / len(results["period_results"]), 1),
            "total_new_logo": sum(p["new_logo_arr"] for p in results["period_results"]),
            "total_expansion": sum(p["expansion_arr"] for p in results["period_results"]),
            "total_churned": sum(p["churned_arr"] for p in results["period_results"]),
        }

    # Recommendations
    recs = results["recommendations"]
    for risk in results["risk_signals"]:
        recs.append(f"[{risk['severity'].upper()}] {risk['action']}")

    declining = [t for t in results["trends"] if t["direction"] in ("declining", "increasing") and t["severity"] == "high"]
    for t in declining:
        recs.append(f"Address {t['metric']} trend ({t['direction']}) -- investigate root cause")

    return results


def format_text(results):
    lines = [
        "=" * 60,
        "REVENUE WATERFALL ANALYSIS",
        "=" * 60,
        f"Company: {results['company']}",
        f"Periods Analyzed: {results['periods_analyzed']}",
        f"Analysis Date: {results['timestamp'][:10]}",
    ]

    if results["summary"]:
        s = results["summary"]
        lines.extend([
            "",
            "SUMMARY",
            f"  Latest ARR: ${s['latest_arr']:,.0f}",
            f"  ARR Growth: ${s['arr_growth']:,.0f}",
            f"  Avg NRR: {s['avg_nrr']}%",
            f"  Avg GRR: {s['avg_grr']}%",
            f"  Total New Logo: ${s['total_new_logo']:,.0f}",
            f"  Total Expansion: ${s['total_expansion']:,.0f}",
            f"  Total Churned: ${s['total_churned']:,.0f}",
        ])

    lines.append("")
    lines.append("PERIOD DETAIL")
    for p in results["period_results"]:
        lines.append(f"\n  {p['period']}")
        lines.append(f"    Opening: ${p['opening_arr']:,.0f} -> Closing: ${p['closing_arr']:,.0f}")
        lines.append(f"    + New Logo: ${p['new_logo_arr']:,.0f}")
        lines.append(f"    + Expansion: ${p['expansion_arr']:,.0f} ({p['expansion_rate_pct']}%)")
        lines.append(f"    - Contraction: ${p['contraction_arr']:,.0f} ({p['contraction_rate_pct']}%)")
        lines.append(f"    - Churn: ${p['churned_arr']:,.0f} ({p['churn_rate_pct']}%)")
        lines.append(f"    = Net New: ${p['net_new_arr']:,.0f}")
        lines.append(f"    NRR: {p['nrr_pct']}% ({p['nrr_benchmark']})")
        lines.append(f"    GRR: {p['grr_pct']}% ({p['grr_benchmark']})")

    if results["risk_signals"]:
        lines.append("")
        lines.append("RISK SIGNALS")
        for r in results["risk_signals"]:
            lines.append(f"  [{r['severity'].upper()}] {r['signal']}")
            lines.append(f"    Action: {r['action']}")

    if results["recommendations"]:
        lines.append("")
        lines.append("RECOMMENDATIONS")
        for rec in results["recommendations"]:
            lines.append(f"  * {rec}")

    return "\n".join(lines)


def main():
    parser = argparse.ArgumentParser(description="Analyze ARR waterfall with NRR/GRR and trend detection")
    parser.add_argument("--input", required=True, help="Path to JSON revenue data file")
    parser.add_argument("--json", action="store_true", help="Output in JSON format")
    args = parser.parse_args()

    try:
        data = load_data(args.input)
    except (FileNotFoundError, json.JSONDecodeError) as e:
        print(f"Error loading input file: {e}", file=sys.stderr)
        sys.exit(1)

    results = analyze_revenue(data)

    if args.json:
        print(json.dumps(results, indent=2))
    else:
        print(format_text(results))


if __name__ == "__main__":
    main()

#!/usr/bin/env python3
"""
Sales Efficiency Scorer - Score sales efficiency with SaaS benchmarking.

Calculates Magic Number, CAC Payback, quota attainment distribution, win rate,
and sales cycle metrics. Benchmarks against SaaS standards and generates
improvement recommendations.

Usage:
    python sales_efficiency_scorer.py --input sales_data.json
    python sales_efficiency_scorer.py --input sales_data.json --json
"""

import argparse
import json
import sys
from datetime import datetime
from statistics import median, stdev


def load_data(path):
    with open(path, "r") as f:
        return json.load(f)


def calculate_magic_number(net_new_arr, prior_q_sm_spend):
    """Magic Number = Net New ARR * 4 / Prior Quarter S&M Spend."""
    if prior_q_sm_spend <= 0:
        return 0
    return round((net_new_arr * 4) / prior_q_sm_spend, 2)


def benchmark_magic_number(value):
    if value >= 1.0:
        return {"label": "Excellent", "action": "Invest more aggressively in S&M"}
    elif value >= 0.75:
        return {"label": "Good", "action": "Healthy efficiency -- maintain or scale carefully"}
    elif value >= 0.5:
        return {"label": "Below Average", "action": "Review channel ROI and rep productivity"}
    else:
        return {"label": "Poor", "action": "S&M spend not converting -- diagnose before increasing"}


def calculate_cac_payback(sm_spend, new_customers, avg_arr, gross_margin_pct):
    """CAC Payback = CAC / (Avg ARR * Gross Margin %)."""
    if new_customers <= 0 or avg_arr <= 0 or gross_margin_pct <= 0:
        return 0, 0
    cac = sm_spend / new_customers
    payback_months = round((cac / (avg_arr * (gross_margin_pct / 100))) * 12, 1)
    return round(cac, 0), payback_months


def benchmark_cac_payback(months):
    if months <= 12:
        return {"label": "Excellent", "action": "Very efficient acquisition"}
    elif months <= 18:
        return {"label": "Good", "action": "Healthy payback period"}
    elif months <= 24:
        return {"label": "Acceptable", "action": "Monitor closely -- approaching threshold"}
    else:
        return {"label": "Poor", "action": "CAC payback too long -- reduce CAC or increase ACV"}


def analyze_quota_attainment(rep_attainments):
    """Analyze quota attainment distribution."""
    if not rep_attainments:
        return {}

    hitting = sum(1 for a in rep_attainments if a >= 100)
    over_80 = sum(1 for a in rep_attainments if a >= 80)
    under_50 = sum(1 for a in rep_attainments if a < 50)
    total = len(rep_attainments)

    pct_hitting = round((hitting / total) * 100, 1)
    avg_attainment = round(sum(rep_attainments) / total, 1)
    med_attainment = round(median(rep_attainments), 1)

    distribution_health = "Healthy"
    if pct_hitting < 50:
        distribution_health = "Quota too high or enablement issue"
    elif pct_hitting > 80:
        distribution_health = "Quota too low -- leaving revenue on table"

    result = {
        "total_reps": total,
        "hitting_quota_pct": pct_hitting,
        "above_80_pct": round((over_80 / total) * 100, 1),
        "below_50_pct": round((under_50 / total) * 100, 1),
        "avg_attainment": avg_attainment,
        "median_attainment": med_attainment,
        "distribution_health": distribution_health,
    }

    if total >= 3:
        result["stdev"] = round(stdev(rep_attainments), 1)

    return result


def calculate_win_rate(closed_won, closed_lost):
    """Win Rate = Closed-Won / (Closed-Won + Closed-Lost)."""
    total = closed_won + closed_lost
    if total == 0:
        return 0
    return round((closed_won / total) * 100, 1)


def benchmark_win_rate(rate):
    if rate >= 30:
        return {"label": "Strong", "action": "Maintain qualification rigor"}
    elif rate >= 20:
        return {"label": "Average", "action": "Review qualification criteria -- MEDDPICC"}
    elif rate >= 15:
        return {"label": "Below Average", "action": "Tighten qualification -- too many unqualified deals entering pipeline"}
    else:
        return {"label": "Poor", "action": "Fundamental sales process or ICP issue"}


def analyze_sales_efficiency(data):
    """Run full sales efficiency analysis."""
    company = data.get("company", "Company")
    revenue = data.get("revenue", {})
    costs = data.get("costs", {})
    pipeline = data.get("pipeline", {})
    reps = data.get("rep_attainments", [])

    # Magic Number
    net_new_arr = revenue.get("net_new_arr_quarter", 0)
    sm_spend = costs.get("prior_q_sm_spend", 0)
    magic = calculate_magic_number(net_new_arr, sm_spend)
    magic_bench = benchmark_magic_number(magic)

    # CAC Payback
    total_sm = costs.get("total_sm_spend_period", sm_spend)
    new_customers = revenue.get("new_customers", 0)
    avg_arr = revenue.get("avg_arr_per_customer", 0)
    gross_margin = revenue.get("gross_margin_pct", 75)
    cac, payback = calculate_cac_payback(total_sm, new_customers, avg_arr, gross_margin)
    payback_bench = benchmark_cac_payback(payback)

    # LTV:CAC
    avg_lifetime_months = revenue.get("avg_customer_lifetime_months", 36)
    ltv = round(avg_arr * (gross_margin / 100) * (avg_lifetime_months / 12), 0) if avg_arr > 0 else 0
    ltv_cac_ratio = round(ltv / max(cac, 1), 1) if cac > 0 else 0

    # Win Rate
    closed_won = pipeline.get("closed_won", 0)
    closed_lost = pipeline.get("closed_lost", 0)
    win_rate = calculate_win_rate(closed_won, closed_lost)
    win_bench = benchmark_win_rate(win_rate)

    # Sales Cycle
    avg_cycle_days = pipeline.get("avg_sales_cycle_days", 0)
    cycle_trend = pipeline.get("cycle_trend", "stable")

    # Quota attainment
    quota_analysis = analyze_quota_attainment(reps)

    # Composite efficiency score
    scores = []
    scores.append(min(100, magic * 100))  # Magic number scaled to 0-100
    scores.append(max(0, 100 - (payback * 4)))  # Payback: lower is better
    scores.append(win_rate * 3)  # Win rate scaled
    scores.append(quota_analysis.get("hitting_quota_pct", 50))
    efficiency_score = round(sum(scores) / max(len(scores), 1), 1)

    results = {
        "timestamp": datetime.now().isoformat(),
        "company": company,
        "efficiency_score": efficiency_score,
        "efficiency_label": "Efficient" if efficiency_score >= 60 else ("Moderate" if efficiency_score >= 40 else "Inefficient"),
        "magic_number": {"value": magic, "benchmark": magic_bench["label"], "action": magic_bench["action"]},
        "cac": {"value": cac, "payback_months": payback, "benchmark": payback_bench["label"], "action": payback_bench["action"]},
        "ltv_cac": {"ltv": ltv, "cac": cac, "ratio": ltv_cac_ratio, "healthy": ltv_cac_ratio >= 3},
        "win_rate": {"value": win_rate, "won": closed_won, "lost": closed_lost, "benchmark": win_bench["label"], "action": win_bench["action"]},
        "sales_cycle": {"avg_days": avg_cycle_days, "trend": cycle_trend},
        "quota_attainment": quota_analysis,
        "recommendations": [],
    }

    # Recommendations
    recs = results["recommendations"]
    if magic < 0.5:
        recs.append(f"Magic Number {magic} is poor -- review S&M spend efficiency before scaling")
    if payback > 24:
        recs.append(f"CAC Payback {payback} months exceeds 24-month threshold -- reduce CAC or increase ACV")
    if ltv_cac_ratio < 3 and ltv_cac_ratio > 0:
        recs.append(f"LTV:CAC ratio {ltv_cac_ratio}x below 3x target -- address churn or reduce acquisition cost")
    if win_rate < 20:
        recs.append(f"Win rate {win_rate}% is below average -- tighten qualification with MEDDPICC")
    if quota_analysis.get("below_50_pct", 0) > 25:
        recs.append(f"{quota_analysis['below_50_pct']}% of reps below 50% attainment -- review quota calibration and enablement")
    if cycle_trend == "increasing":
        recs.append("Sales cycle lengthening -- investigate competitive pressure or product alignment")

    return results


def format_text(results):
    lines = [
        "=" * 60,
        "SALES EFFICIENCY SCORECARD",
        "=" * 60,
        f"Company: {results['company']}",
        f"Analysis Date: {results['timestamp'][:10]}",
        "",
        f"EFFICIENCY SCORE: {results['efficiency_score']}/100 ({results['efficiency_label']})",
        "",
        "KEY METRICS",
        f"  Magic Number: {results['magic_number']['value']} ({results['magic_number']['benchmark']})",
        f"    {results['magic_number']['action']}",
        "",
        f"  CAC: ${results['cac']['value']:,.0f} | Payback: {results['cac']['payback_months']} months ({results['cac']['benchmark']})",
        f"    {results['cac']['action']}",
        "",
        f"  LTV:CAC: {results['ltv_cac']['ratio']}x (LTV=${results['ltv_cac']['ltv']:,.0f}, CAC=${results['ltv_cac']['cac']:,.0f})",
        f"    {'Healthy' if results['ltv_cac']['healthy'] else 'Below 3x target'}",
        "",
        f"  Win Rate: {results['win_rate']['value']}% ({results['win_rate']['won']}W / {results['win_rate']['lost']}L) ({results['win_rate']['benchmark']})",
        f"    {results['win_rate']['action']}",
        "",
        f"  Sales Cycle: {results['sales_cycle']['avg_days']} days (trend: {results['sales_cycle']['trend']})",
    ]

    qa = results["quota_attainment"]
    if qa:
        lines.extend([
            "",
            "QUOTA ATTAINMENT",
            f"  Reps: {qa['total_reps']}",
            f"  Hitting Quota: {qa['hitting_quota_pct']}%",
            f"  Avg Attainment: {qa['avg_attainment']}%",
            f"  Median Attainment: {qa['median_attainment']}%",
            f"  Below 50%: {qa['below_50_pct']}%",
            f"  Health: {qa['distribution_health']}",
        ])

    if results["recommendations"]:
        lines.append("")
        lines.append("RECOMMENDATIONS")
        for rec in results["recommendations"]:
            lines.append(f"  * {rec}")

    return "\n".join(lines)


def main():
    parser = argparse.ArgumentParser(description="Score sales efficiency with SaaS benchmarking")
    parser.add_argument("--input", required=True, help="Path to JSON sales data file")
    parser.add_argument("--json", action="store_true", help="Output in JSON format")
    args = parser.parse_args()

    try:
        data = load_data(args.input)
    except (FileNotFoundError, json.JSONDecodeError) as e:
        print(f"Error loading input file: {e}", file=sys.stderr)
        sys.exit(1)

    results = analyze_sales_efficiency(data)

    if args.json:
        print(json.dumps(results, indent=2))
    else:
        print(format_text(results))


if __name__ == "__main__":
    main()