Как посчитать Quota Attainment в SQL

Зачем Quota Attainment

% rep, что достигают quota — главная health metric sales team. Если 30% reps hit quota — sales process сломан или quota нереалистичная. 60-70% — норма.

Формула

Quota Attainment = closed_won / quota × 100%

% reps hitting quota = что больше всего важно.

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

Данные: deals(sales_rep, value, stage, closed_at), quotas(sales_rep, quarter, quota).

WITH q_actual AS (
    SELECT
        sales_rep,
        DATE_TRUNC('quarter', closed_at) AS quarter,
        SUM(value) AS actual_revenue
    FROM deals
    WHERE stage = 'won'
    GROUP BY 1, 2
)
SELECT
    q.sales_rep,
    q.quarter,
    a.actual_revenue,
    q.quota,
    a.actual_revenue::NUMERIC * 100 / NULLIF(q.quota, 0) AS attainment_pct
FROM quotas q
LEFT JOIN q_actual a ON a.sales_rep = q.sales_rep AND a.quarter = q.quarter
WHERE q.quarter = '2026-01-01'  -- Q1 2026
ORDER BY attainment_pct DESC;

By rep

Add ranking:

SELECT
    sales_rep,
    actual_revenue,
    quota,
    attainment_pct,
    CASE
        WHEN attainment_pct >= 100 THEN 'Hit quota'
        WHEN attainment_pct >= 80 THEN 'Near quota'
        WHEN attainment_pct >= 50 THEN 'Below'
        ELSE 'Way below'
    END AS performance
FROM (
    -- previous query
) x
ORDER BY attainment_pct DESC;

Distribution

SELECT
    CASE
        WHEN attainment_pct >= 150 THEN '150%+ (way OVER)'
        WHEN attainment_pct >= 100 THEN '100-150% (OVER)'
        WHEN attainment_pct >= 80 THEN '80-100% (close)'
        WHEN attainment_pct >= 50 THEN '50-80%'
        ELSE '< 50%'
    END AS bucket,
    COUNT(*) AS reps_in_bucket
FROM (
    -- previous query
) x
GROUP BY 1
ORDER BY 1;

Healthy team: 60-70% hit quota. <40% hit — quota too aggressive or sales broken.

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

Ошибка 1. Ramp-up reps. New rep first quarter — quota lower (ramping). Adjust.

Ошибка 2. Partial quarter. Если rep started mid-quarter, fair quota = pro-rated.

Ошибка 3. Different quota types. Some reps — new logo quota. Others — expansion. Don't average.

Ошибка 4. Including pending. «Will close this quarter» pipeline counts? Standard: только closed_won.

Ошибка 5. Bonus structure inflates focus. Reps push deals at quarter-end (sandbagging / blue-sheeting). Watch end-of-quarter pattern.

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

• Как посчитать Win Rate в SQL
• Как посчитать pipeline coverage в SQL
• Как посчитать Deal Velocity в SQL
• Как посчитать Magic Number в SQL

FAQ

Какой % hitting quota considered ok?

60-70% — healthy. 80%+ — quota too low. <40% — broken sales process / quota too aggressive.

Quota attainment vs Pipeline Coverage?

Attainment — backward (was). Coverage — forward (will be).

Bonus / commission tied to attainment?

Yes. Most reps paid based на % hit.

Quota по acquired vs renewed?

Зависит от sales team. New logo quota — net new ARR. Account exec — renewals + expansion.

Quota change mid-year?

Bad practice — кurts morale + payouts. Sometimes inevitable (M&A, restructure).