Как посчитать Pipeline Coverage в SQL

Зачем Pipeline Coverage

В B2B sales pipeline coverage показывает: «хватит ли open opportunities, чтобы выполнить quota». Если quota $1M, pipeline $1.5M, win rate 30% → реально закроет только $450K. Coverage 1.5x — undercover.

Формула

Pipeline Coverage = Open Pipeline Value / Quota

Healthy ratio: 3-5x quota (с учётом win rate).

Базовый расчёт

Данные: deals(deal_id, stage, value, sales_rep, owner_team).

SELECT
    sales_rep,
    SUM(value) FILTER (WHERE stage NOT IN ('won', 'lost')) AS open_pipeline,
    -- quota — manual config, например 1M per rep per quarter
    1000000 AS quarterly_quota,
    SUM(value) FILTER (WHERE stage NOT IN ('won', 'lost'))::NUMERIC / 1000000 AS coverage_ratio
FROM deals
GROUP BY sales_rep
ORDER BY coverage_ratio;

Coverage <3x — sales rep at risk. Coverage >5x — healthy.

По stages

SELECT
    stage,
    COUNT(*) AS deals,
    SUM(value) AS pipeline_value
FROM deals
WHERE stage NOT IN ('won', 'lost')
GROUP BY stage
ORDER BY
    CASE stage
        WHEN 'discovery' THEN 1
        WHEN 'qualification' THEN 2
        WHEN 'demo' THEN 3
        WHEN 'proposal' THEN 4
        WHEN 'negotiation' THEN 5
    END;

Weighted Pipeline

Weight by stage win-rate:

WITH stage_weights AS (
    SELECT * FROM (VALUES
        ('discovery', 0.10),
        ('qualification', 0.20),
        ('demo', 0.40),
        ('proposal', 0.60),
        ('negotiation', 0.80)
    ) AS w(stage, win_prob)
)
SELECT
    SUM(d.value * w.win_prob) AS weighted_pipeline,
    SUM(d.value) AS raw_pipeline,
    SUM(d.value * w.win_prob)::NUMERIC / NULLIF(SUM(d.value), 0) AS avg_weight
FROM deals d
JOIN stage_weights w ON w.stage = d.stage
WHERE d.stage NOT IN ('won', 'lost');

Weighted pipeline ≈ realistic forecast.

Частые ошибки

Ошибка 1. Including stale deals. Deal без activity 90+ days — likely dead. Exclude из coverage.

Ошибка 2. Wrong quota. Quota changes quarter-over-quarter. Use current.

Ошибка 3. One-time vs recurring revenue. В SaaS — annual contract value (ACV) vs one-time. Distinguish.

Ошибка 4. Forecast vs actual. Pipeline — forecast. Closed won — actual. Don't confuse.

Ошибка 5. Stage rot. Deals stuck в demo для 6 месяцев. Inflate pipeline без real chance.

Связанные темы

• Как посчитать Win Rate в SQL
• Как посчитать Magic Number в SQL
• Как посчитать ARR в SQL
• Как посчитать CAC в SQL

FAQ

Какой Pipeline Coverage ok?

3-5x quota — стандарт. Зависит от win rate (high win rate ⇒ smaller coverage needed).

Weighted или raw pipeline?

Weighted — realistic. Raw — total opportunity.

Coverage low — что делать?

1. More marketing leads. 2) Faster sales velocity. 3) Reduce quota (если cycle long).

Coverage растёт — хорошо?

Может означать deals не закрываются (stuck в stages). Watch deal age.

Pipeline Coverage и forecast accuracy?

Связаны. Better coverage = better forecast. Track historical accuracy.