Как посчитать agent productivity в SQL

Зачем productivity

Productivity для CS — это не только «сколько тикетов закрыл». Включает CSAT, FCR, AHT. Топ-agent может делать 20 тикетов/день при CSAT 4.8. Middle — 15 при 4.5. Балансируйте на quality + speed, иначе агенты гонятся за volume и портят experience.

Tickets per agent

SELECT
    agent_id,
    DATE(closed_at) AS day,
    COUNT(*) AS tickets_closed
FROM support_tickets
WHERE status = 'resolved'
  AND closed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY agent_id, DATE(closed_at)
ORDER BY day DESC, tickets_closed DESC;

Average — обычно 20-40 simple, 5-10 complex per shift.

Average handle time

SELECT
    agent_id,
    AVG(EXTRACT(EPOCH FROM (closed_at - assigned_at)) / 60) AS avg_handle_minutes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (closed_at - assigned_at)) / 60) AS median_minutes
FROM support_tickets
WHERE status = 'resolved'
  AND closed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY agent_id
ORDER BY avg_handle_minutes;

AHT 5 мин для simple → норма. 60+ мин на средний ticket = agent ineffective или поднимает hard cases.

First contact resolution

WITH ticket_summary AS (
    SELECT
        ticket_id,
        agent_id,
        COUNT(*) FILTER (WHERE message_type = 'reply' AND author = 'customer') AS customer_replies,
        BOOL_OR(status = 'resolved') AS resolved
    FROM ticket_messages
    GROUP BY ticket_id, agent_id
)
SELECT
    agent_id,
    COUNT(*) AS total_tickets,
    SUM(CASE WHEN customer_replies <= 1 AND resolved THEN 1 ELSE 0 END) AS fcr_count,
    SUM(CASE WHEN customer_replies <= 1 AND resolved THEN 1 ELSE 0 END)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS fcr_pct
FROM ticket_summary
GROUP BY agent_id
ORDER BY fcr_pct DESC;

FCR > 70% — отлично. < 50% — много back-and-forth.

Balanced scorecard

WITH metrics AS (
    SELECT
        agent_id,
        COUNT(*) AS tickets,
        AVG(EXTRACT(EPOCH FROM (closed_at - assigned_at)) / 60) AS avg_handle,
        AVG(csat_score) AS avg_csat,
        SUM(CASE WHEN fcr THEN 1 ELSE 0 END)::NUMERIC / COUNT(*) AS fcr_rate
    FROM agent_kpi
    WHERE period = '2026-04-01'
    GROUP BY agent_id
)
SELECT
    agent_id,
    NTILE(10) OVER (ORDER BY tickets)            AS volume_decile,
    NTILE(10) OVER (ORDER BY -avg_handle)        AS speed_decile,
    NTILE(10) OVER (ORDER BY avg_csat)           AS quality_decile,
    NTILE(10) OVER (ORDER BY fcr_rate)           AS fcr_decile,
    (NTILE(10) OVER (ORDER BY tickets)
     + NTILE(10) OVER (ORDER BY -avg_handle)
     + NTILE(10) OVER (ORDER BY avg_csat)
     + NTILE(10) OVER (ORDER BY fcr_rate)) AS composite_score
FROM metrics
ORDER BY composite_score DESC;

Composite score из 4 metrics — balanced.

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

Ошибка 1. Только volume = productivity. Agent с 40 closed но CSAT 3 — это плохо, не хорошо.

Ошибка 2. Сравнивать на разных категориях. Tier-1 vs Tier-2 разные tickets. Сравнивайте within tier.

Ошибка 3. Игнорировать ticket complexity. Простой password reset = 2 мин. Bug investigation = 40 мин. AHT need weighted.

Ошибка 4. Public ranking. Public leaderboard демотивирует bottom. Используйте для coaching privately.

Ошибка 5. Не учитывать shift overlap. Agent работает 4 часа из 8 — productivity per hour, not per day.

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

• Как посчитать average handle time в SQL
• Как посчитать first response time в SQL
• Как посчитать CSAT в SQL
• Как посчитать first-time-fix rate в SQL

FAQ

Сколько тикетов в день норма?

Tier-1: 20-40. Tier-2: 8-15. Enterprise: 4-8.

AHT норма?

Chat: 5-10 мин. Email: 15-30 мин. Phone: 6-12 мин.

FCR vs reopens?

Reopens — обратная картина (плохое resolution). FCR — first attempt success.

Quality vs quantity?

Quality важнее. Speed без quality = бесконечные reopens.

Coaching по metrics?

Yes — но privately, с context (категории, complexity).