Как посчитать win/loss rate в SQL

Зачем win/loss rate

Sales-команда нужно знать: процент сделок, которые они выигрывают. Win rate 25% — стандарт B2B SaaS. Высокий — strong product/team. Низкий — poor qualification (qualify weak leads) или not winning против конкурентов. Win/loss analysis decomposes причины.

Формула

win_rate = won / (won + lost)
loss_rate = lost / (won + lost)

Open deals не входят, только закрытые.

Rate в SQL

SELECT
    COUNT(*) FILTER (WHERE status IN ('won', 'lost')) AS total_closed,
    COUNT(*) FILTER (WHERE status = 'won') AS won,
    COUNT(*) FILTER (WHERE status = 'lost') AS lost,
    COUNT(*) FILTER (WHERE status = 'won')::NUMERIC * 100
    / NULLIF(COUNT(*) FILTER (WHERE status IN ('won', 'lost')), 0) AS win_rate_pct
FROM deals
WHERE closed_at >= CURRENT_DATE - INTERVAL '6 months';

25-35% — standard. 50%+ — strong. Меньше 15% — investigate qualification.

По причинам loss

SELECT
    loss_reason,
    COUNT(*) AS deals,
    SUM(deal_size) AS lost_revenue,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct_of_losses
FROM deals
WHERE status = 'lost'
  AND closed_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY loss_reason
ORDER BY deals DESC;

Top reasons:

• «pricing» → competitive pricing
• «features missing» → product roadmap
• «timing» → re-engage cadence
• «competitor X» → competitive playbook

Против competitor

SELECT
    competitor_name,
    COUNT(*) AS deals_lost_to,
    SUM(deal_size) AS revenue_lost,
    AVG(deal_size) AS avg_deal_size
FROM deals
WHERE status = 'lost'
  AND loss_reason = 'lost_to_competitor'
  AND closed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY competitor_name
ORDER BY deals_lost_to DESC;

Если 60% losses в одного конкурента — competitive battle plan needed.

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

Ошибка 1. Включать open deals. Win rate by definition на closed. Открытые — отдельная metric (pipeline coverage).

Ошибка 2. Не различать reasons. «Lost» без причины бесполезен. Mandatory loss_reason при closing.

Ошибка 3. Loss reasons self-reported. Sales rep могут писать softer reasons. Win-loss interview более accurate.

Ошибка 4. Не сегментировать. Enterprise win rate 15%, SMB 35% — average 25% маскирует разницу.

Ошибка 5. Time period mix. Старые closed (3 years ago) + recent — competitive landscape изменился. Use 6-12 months max.

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

• Как посчитать pipeline velocity в SQL
• Как посчитать win rate в SQL
• Как посчитать sales cycle length в SQL
• Как посчитать deal velocity в SQL

FAQ

Какой win rate хороший?

B2B SaaS 25-35%. Enterprise 15-25%. SMB 35-50%.

Win rate по rep?

Yes — варьируется 10-50% между reps. Coaching opportunity.

Loss interview обязательно?

Для enterprise — yes. Для SMB — sample каждый 10-й.

Won by ARR vs by count?

ARR-weighted честнее. 1 large won deal может равно 10 small losts.

Trend важнее точки?

Yes — падающий win rate за 3 quarters — flag, даже если рейт «нормален».