Как посчитать quota attainment по rep в SQL

Зачем quota attainment

Каждому sales rep назначен quarterly quota. Attainment = % достижения. 100% — норма. Свыше 110% — top performer. Меньше 70% — performance management. Распределение attainment по команде — это how well company hires + trains + sets quotas.

Формула

attainment = actual_revenue / quota

Часто normalized to quarter ARR.

Attainment в SQL

SELECT
    r.rep_id,
    r.rep_name,
    q.quarter,
    q.quota,
    SUM(d.deal_size) FILTER (WHERE d.status = 'won') AS actual,
    SUM(d.deal_size) FILTER (WHERE d.status = 'won')::NUMERIC * 100
    / NULLIF(q.quota, 0) AS attainment_pct
FROM reps r
JOIN rep_quarterly_quotas q USING (rep_id)
LEFT JOIN deals d
  ON d.rep_id = r.rep_id
 AND d.closed_at >= q.quarter_start
 AND d.closed_at <= q.quarter_end
WHERE q.quarter = '2026-Q1'
GROUP BY r.rep_id, r.rep_name, q.quarter, q.quota
ORDER BY attainment_pct DESC;

Ramp-up новичков

Новые reps первый квартал — 50% quota, второй — 75%, третий — 100%:

SELECT
    r.rep_id,
    r.start_date,
    EXTRACT(EPOCH FROM (CURRENT_DATE - r.start_date)) / 86400 / 90 AS quarters_since_start,
    q.quota,
    CASE
        WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - r.start_date)) / 86400 / 90 < 1 THEN q.quota * 0.5
        WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - r.start_date)) / 86400 / 90 < 2 THEN q.quota * 0.75
        ELSE q.quota
    END AS adjusted_quota
FROM reps r
JOIN rep_quarterly_quotas q USING (rep_id)
WHERE q.quarter = '2026-Q1';

Без ramp-up, новички почти всегда «underperform».

Distribution

Distribution attainment по команде:

SELECT
    CASE
        WHEN attainment_pct < 50 THEN '0-50%'
        WHEN attainment_pct < 70 THEN '50-70%'
        WHEN attainment_pct < 90 THEN '70-90%'
        WHEN attainment_pct < 110 THEN '90-110%'
        ELSE '110%+'
    END AS attainment_bucket,
    COUNT(*) AS reps
FROM rep_attainment
WHERE quarter = '2026-Q1'
GROUP BY 1
ORDER BY MIN(attainment_pct);

Healthy distribution: bell-shaped с peak 90-110%. Right-skewed (many top) — quota too low. Left-skewed (many low) — quota too high or product issue.

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

Ошибка 1. Считать ramp-up reps в average. Их 50% не отражает performance. Exclude или adjust.

Ошибка 2. Quota не нормирована по segment. SMB rep quota $400k. Enterprise rep $2M. Сравнивать absolute не имеет смысла.

Ошибка 3. Mid-quarter attainment. Q1 в марте 80% — не значит, что rep close Q1 100%+. Time-adjusted.

Ошибка 4. Включать commit deals. Commit ≠ closed-won. Только closed-won — actual attainment.

Ошибка 5. Distribution skew interpretation. 80% reps достигают 110%+ → comp plan стимулирует, но quota недо-aggressive. 50% reps делают меньше 70% → quota слишком высока, team demoralized.

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

• Как посчитать quota attainment в SQL
• Как посчитать sales forecast accuracy в SQL
• Как посчитать win rate в SQL
• Как посчитать pipeline velocity в SQL

FAQ

Какой attainment normal?

Median 90-110%. 70% reps make quota (rule of thumb).

Top 10% attainment?

130-180%. Top-reps significantly outperform.

Sandbag и attainment?

Sandbagger закрывает 100-110%. Crunch — 85-95%. Both compromised in different ways.

Comp at 100%?

Variable comp обычно kicks in 70-80%, hits 100% target на quota, accelerator после.

Multi-quarter attainment?

Annual attainment важнее single Q. Average over year.