Как посчитать Deal Velocity в SQL

Зачем Deal Velocity

Sales velocity — как быстро revenue течёт через pipeline. Если deals закрываются дольше — revenue stalls. Если SMB-cycle превратился в Enterprise (longer cycle) — adjust forecasts.

Формула

Sales Velocity = (Opportunities × Avg Deal Size × Win Rate) / Avg Sales Cycle (days)

Daily revenue rate, который flows through pipeline.

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

WITH stats AS (
    SELECT
        COUNT(*) FILTER (WHERE stage = 'won') AS won_deals,
        COUNT(*) FILTER (WHERE stage IN ('won', 'lost')) AS closed_deals,
        AVG(value) FILTER (WHERE stage = 'won') AS avg_deal_size,
        AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) FILTER (WHERE stage = 'won') AS avg_cycle_days,
        COUNT(*) FILTER (WHERE stage = 'won')::NUMERIC / NULLIF(COUNT(*) FILTER (WHERE stage IN ('won', 'lost')), 0) AS win_rate
    FROM deals
    WHERE closed_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    won_deals,
    avg_deal_size,
    win_rate,
    avg_cycle_days,
    (won_deals * avg_deal_size * win_rate) / NULLIF(avg_cycle_days, 0) AS sales_velocity_per_day
FROM stats;

По segments

SELECT
    segment,
    COUNT(*) FILTER (WHERE stage = 'won') AS won_deals,
    AVG(value) FILTER (WHERE stage = 'won') AS avg_deal,
    AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400) FILTER (WHERE stage = 'won') AS avg_cycle_days,
    COUNT(*) FILTER (WHERE stage = 'won')::NUMERIC * 100
        / NULLIF(COUNT(*) FILTER (WHERE stage IN ('won','lost')), 0) AS win_rate_pct
FROM deals
GROUP BY segment;

SMB обычно faster cycle. Enterprise — longer cycle + bigger deal.

Time-to-close

SELECT
    CASE
        WHEN cycle_days <= 30 THEN 'fast (<30d)'
        WHEN cycle_days <= 90 THEN 'normal (30-90d)'
        WHEN cycle_days <= 180 THEN 'slow (90-180d)'
        ELSE 'very slow (180+d)'
    END AS bucket,
    COUNT(*) AS deals,
    AVG(value) AS avg_value
FROM (
    SELECT
        deal_id,
        value,
        EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400 AS cycle_days
    FROM deals
    WHERE stage = 'won'
) x
GROUP BY 1
ORDER BY 1;

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

Ошибка 1. Включить open deals в cycle. Cycle days — only closed deals. Open — pending, длительность unknown.

Ошибка 2. Срaвнить SMB и Enterprise. Apples to oranges. Decompose by segment.

Ошибка 3. Avg vs Median cycle. Few extreme outliers (e.g. 2-year enterprise deal) skew avg. Use median.

Ошибка 4. Лost deals — не считать? Lost — также часть cycle. Include for accuracy.

Ошибка 5. Re-opened deals. Deal lost, потом reopened, won. Cycle = original creation или reopened?

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

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

FAQ

Какой Deal Velocity ok?

SMB: $5-50K daily rate per rep. Enterprise: $1-10K daily (higher value, slower cycle).

Sales Velocity vs Quota?

Velocity × business days = quarterly attainment estimate.

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

Yes. Faster revenue flow.

How to improve?

1. Reduce cycle (better qualification). 2) Higher win rate (training). 3) Bigger deals (upsell at close).

Sales Velocity для SaaS?

Same formula, но deal_size = ARR (annual). Sales velocity = «ARR signed per day».