Как посчитать First Response Time в SQL

Зачем First Response Time

В support FRT — ключевая метрика customer perception. Юзер пишет в chat — ждёт ответ 10 секунд (chat) или 4 часа (email). Если FRT превышен — frustration.

Формула

FRT = (first_agent_response_time - ticket_created_time)

В minutes / hours.

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

Данные: tickets(ticket_id, user_id, created_at, channel, ...), ticket_messages(ticket_id, sender_type, sent_at).

WITH first_response AS (
    SELECT
        t.ticket_id,
        t.created_at AS ticket_created,
        MIN(m.sent_at) FILTER (WHERE m.sender_type = 'agent') AS first_agent_response
    FROM tickets t
    LEFT JOIN ticket_messages m ON m.ticket_id = t.ticket_id
    WHERE t.created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY t.ticket_id, t.created_at
)
SELECT
    AVG(EXTRACT(EPOCH FROM (first_agent_response - ticket_created)) / 60) AS avg_frt_minutes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (first_agent_response - ticket_created)) / 60
    ) AS median_frt_minutes,
    PERCENTILE_CONT(0.9) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (first_agent_response - ticket_created)) / 60
    ) AS p90_frt_minutes
FROM first_response
WHERE first_agent_response IS NOT NULL;

SLA compliance

Допустим, SLA: chat 5 min, email 4h.

WITH frt AS (
    SELECT
        t.ticket_id,
        t.channel,
        EXTRACT(EPOCH FROM (MIN(m.sent_at) FILTER (WHERE m.sender_type = 'agent') - t.created_at)) / 60 AS frt_min
    FROM tickets t
    LEFT JOIN ticket_messages m ON m.ticket_id = t.ticket_id
    GROUP BY t.ticket_id, t.channel, t.created_at
)
SELECT
    channel,
    COUNT(*) AS tickets,
    COUNT(*) FILTER (WHERE
        (channel = 'chat' AND frt_min <= 5)
        OR (channel = 'email' AND frt_min <= 240)
    )::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS sla_compliance_pct
FROM frt
WHERE frt_min IS NOT NULL
GROUP BY channel;

По каналам

SELECT
    channel,
    COUNT(*) AS tickets,
    AVG(frt_min) AS avg_frt_min,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY frt_min) AS median_frt_min
FROM frt
WHERE frt_min IS NOT NULL
GROUP BY channel
ORDER BY avg_frt_min;

Chat обычно 1-5 min. Email 1-8 hours. Phone — instant (или missed).

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

Ошибка 1. Outside business hours. Ticket в 22:00 (вне рабочего времени) → FRT следующего дня 9:00 = 11 hours. Это не fair vs business hours metric.

Ошибка 2. Auto-replies as response. «We received your ticket» — это auto-reply, не human response. Filter by sender_type = 'agent' (real).

Ошибка 3. Ticket re-opens. Юзер reopened ticket. FRT для re-open — отдельная метрика.

Ошибка 4. Average на skewed. Few extreme outliers (long FRT) dominate avg. Use median + percentiles.

Ошибка 5. Time-zone. Tickets in UTC, support в local time. Reconcile.

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

• Как посчитать CSAT в SQL
• Как посчитать CES в SQL
• Как посчитать NPS в SQL
• Как посчитать time to resolution в SQL

FAQ

Какой FRT ok?

Chat: 1-5 min. Email: 1-4h. Phone: instant. Зависит от audience expectations.

Business hours adjusted?

Yes, для fair comparison. Calculate FRT only during business hours.

Auto-reply считать?

No. Только human responses.

FRT vs Time to Resolution?

FRT — first response. Resolution — full close.

High FRT — что делать?

1. Increase staffing. 2) Better self-service. 3) Ticket routing optimization. 4) Knowledge base improvements.