Как посчитать hiring funnel в SQL

Зачем hiring funnel

Сколько резюме до одного нанятого? 100 → 1 — стандарт для tech. Если 50 — рекрутер плохо фильтрует. Если 500 — баг в screening. Funnel показывает, где теряем candidates: cold screening, technical interview, behavioral, offer-stage rejection.

Стандартные шаги

• applied — резюме поступило
• screened — рекрутер просмотрел
• phone_screen — first call
• onsite_interview — second-stage
• offer — предложение
• accepted — принято
• started — first day

Funnel в SQL

WITH cohort AS (
    SELECT candidate_id, applied_at, position_id, source
    FROM candidates
    WHERE applied_at >= '2026-01-01' AND applied_at < '2026-04-01'
),
stages AS (
    SELECT
        c.candidate_id,
        MAX(CASE WHEN s.stage_name = 'screened'          THEN 1 ELSE 0 END) AS s_screened,
        MAX(CASE WHEN s.stage_name = 'phone_screen'      THEN 1 ELSE 0 END) AS s_phone,
        MAX(CASE WHEN s.stage_name = 'onsite_interview'  THEN 1 ELSE 0 END) AS s_onsite,
        MAX(CASE WHEN s.stage_name = 'offer'             THEN 1 ELSE 0 END) AS s_offer,
        MAX(CASE WHEN s.stage_name = 'accepted'          THEN 1 ELSE 0 END) AS s_accepted
    FROM cohort c
    LEFT JOIN candidate_stages s USING (candidate_id)
    GROUP BY c.candidate_id
)
SELECT
    COUNT(*) AS applied,
    SUM(s_screened) AS screened,
    SUM(s_phone) AS phone,
    SUM(s_onsite) AS onsite,
    SUM(s_offer) AS offers,
    SUM(s_accepted) AS accepted,
    SUM(s_accepted)::NUMERIC * 100 / COUNT(*) AS overall_conversion_pct
FROM stages;

Overall conversion 1% — typical для tech. 0.3% — strict filter; 3% — loose.

Bottleneck step

WITH counts AS (
    SELECT
        100000 AS applied,
        25000 AS screened,
        12000 AS phone,
        5000 AS onsite,
        1200 AS offers,
        900 AS accepted
)
SELECT
    'applied → screened' AS step,
    25000.0 / 100000 AS conversion_pct UNION ALL
SELECT 'screened → phone',  12000.0 / 25000  UNION ALL
SELECT 'phone → onsite',     5000.0 / 12000  UNION ALL
SELECT 'onsite → offer',     1200.0 / 5000   UNION ALL
SELECT 'offer → accepted',    900.0 / 1200;

Каждый шаг — separate conversion rate. Самая низкая — bottleneck.

По каналу

SELECT
    source,
    COUNT(*) AS applied,
    SUM(CASE WHEN final_stage = 'accepted' THEN 1 ELSE 0 END) AS accepted,
    SUM(CASE WHEN final_stage = 'accepted' THEN 1 ELSE 0 END)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS conversion_pct
FROM candidates_with_outcome
WHERE applied_at >= '2026-01-01'
GROUP BY source
HAVING COUNT(*) >= 50
ORDER BY conversion_pct DESC;

Referral source обычно 5-10× выше conversion vs LinkedIn ads. ROI должен учитывать.

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

Ошибка 1. Считать на open positions. Open positions могут «висеть» 3 месяца. Cohort по applied_at, не по position.

Ошибка 2. Включать internal applications с external. Internal candidates сильно отличаются (already screened). Сегментируйте.

Ошибка 3. Не учитывать дисквалификации. Withdrawn by candidate vs rejected by company — разные причины. Track separately.

Ошибка 4. Time-to-fill путать с conversion. Time-to-fill = days from open to hire. Conversion = candidate progression.

Ошибка 5. Один funnel для всех ролей. Senior engineer и QA имеют разные funnels. Сегментируйте.

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

• Как посчитать time-to-hire в SQL
• Как посчитать funnel в SQL
• Как посчитать funnel drop-off в SQL
• Как посчитать employee turnover в SQL

FAQ

Какой overall conversion?

Tech: 0.5-2%. Hard-to-fill: 0.1-0.3%. Easy roles: 3-5%.

Самый узкий шаг?

Обычно onsite → offer (25%) или screened → phone (40-60%).

Decline reasons track?

Yes — top 3 reasons почему отказал candidate (комп, локация, продукт).

Per source ROI?

Conversion × volume × cost per source. Recruiter agency: high cost / high CR. Free job boards: low cost / low CR.

Hire-to-start lag?

Обычно 2-6 weeks. Включайте в time-to-hire для accuracy.