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

Зачем Deal Size

Average Deal Size — base метрика B2B sales. CAC needs to be < Deal Size × Margin × Years (LTV). Drives sales mix decisions: focus SMB volume or enterprise high-ticket.

ACV vs TCV

3-year contract $120k — TCV $120k, ACV $40k.

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

SELECT
    DATE_TRUNC('quarter', closed_at) AS quarter,
    COUNT(*) AS deals,
    AVG(acv) AS avg_acv,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY acv) AS median_acv,
    SUM(acv) AS bookings
FROM closed_won_deals
WHERE closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Distribution

WITH ranges AS (
    SELECT
        CASE
            WHEN acv < 1000 THEN '<$1k'
            WHEN acv < 10000 THEN '$1k-$10k'
            WHEN acv < 50000 THEN '$10k-$50k'
            WHEN acv < 100000 THEN '$50k-$100k'
            ELSE '$100k+'
        END AS bucket,
        acv
    FROM closed_won_deals
    WHERE closed_at >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    bucket,
    COUNT(*) AS deals,
    SUM(acv) AS revenue,
    SUM(acv)::NUMERIC * 100 / SUM(SUM(acv)) OVER () AS revenue_share_pct
FROM ranges
GROUP BY bucket
ORDER BY MIN(acv);

Pareto: top 20% deals → 80% revenue typical.

По segments

SELECT
    customer_segment,  -- SMB / Mid-Market / Enterprise
    COUNT(*) AS deals,
    AVG(acv) AS avg_acv,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY acv) AS median_acv,
    SUM(acv) AS revenue,
    AVG(sales_cycle_days) AS avg_cycle_days
FROM closed_won_deals
WHERE closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY customer_segment
ORDER BY avg_acv DESC;

Enterprise deals 10-50x SMB. Sales cycle proportional.

ACV trend

SELECT
    DATE_TRUNC('quarter', closed_at) AS quarter,
    AVG(acv) AS avg_acv,
    LAG(AVG(acv)) OVER (ORDER BY DATE_TRUNC('quarter', closed_at)) AS prev_avg_acv,
    (AVG(acv) - LAG(AVG(acv)) OVER (ORDER BY DATE_TRUNC('quarter', closed_at)))
    / NULLIF(LAG(AVG(acv)) OVER (ORDER BY DATE_TRUNC('quarter', closed_at)), 0) * 100 AS qoq_growth_pct
FROM closed_won_deals
WHERE closed_at >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

ACV расти — moving upmarket. Падает — downmarket / discount pressure.

Win rate × Deal size

SELECT
    customer_segment,
    COUNT(*) FILTER (WHERE outcome = 'won') AS wins,
    COUNT(*) AS total_opportunities,
    COUNT(*) FILTER (WHERE outcome = 'won')::NUMERIC * 100 / COUNT(*) AS win_rate_pct,
    AVG(acv) FILTER (WHERE outcome = 'won') AS avg_won_acv,
    COUNT(*) FILTER (WHERE outcome = 'won')::NUMERIC * 100 / COUNT(*) * AVG(acv) FILTER (WHERE outcome = 'won') AS expected_revenue_per_opp
FROM opportunities
WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
  AND outcome IS NOT NULL
GROUP BY customer_segment;

Expected revenue per opp = win_rate × avg ACV. Drives prioritization.

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

Ошибка 1. ACV vs TCV mixed. Reporting TCV as «deal size» inflates. Use ACV для consistent.

Ошибка 2. Discounted vs list price. Track both — discount frequency индикатор pricing power.

Ошибка 3. Outliers (whales). Один $1M deal pulls average. Median + segments.

Ошибка 4. Add-ons / expansion. Initial deal vs expansion within contract. Track separately.

Ошибка 5. Currency. Multi-currency pipeline. Convert at consistent rate.

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

• Как посчитать win rate в SQL
• Как посчитать deal velocity в SQL
• Как посчитать pipeline coverage в SQL
• Как посчитать quota attainment в SQL

FAQ

ACV vs TCV?

ACV — annual. TCV — total contract value (multi-year). Different metrics.

Какой Deal Size ok?

SMB: $1-10k ACV. Mid-Market: $10-100k. Enterprise: $100k-1M+.

Avg vs Median?

Avg inflated by big deals. Median typical. Report both.

Deal Size падает?

1. Discount pressure. 2) Smaller customers shifting. 3) Product mix. Investigate.

Multi-year discounting?

Typical 5-15% discount для multi-year commit. ACV recalculated.