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

Зачем FTFR

First Time Fix Rate — % tickets resolved при первом contact, без callbacks / re-opens. High FTFR = customers happy + support cost down.

Формула

FTFR = tickets_resolved_first_time / total_tickets_closed × 100%

«First time» = no re-opens, no transfers, no callbacks within window.

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

Данные: tickets(ticket_id, user_id, opened_at, closed_at, reopened_at).

SELECT
    DATE_TRUNC('week', closed_at) AS week,
    COUNT(*) AS total_closed,
    COUNT(*) FILTER (WHERE reopened_at IS NULL) AS first_time_fix,
    COUNT(*) FILTER (WHERE reopened_at IS NULL)::NUMERIC * 100 / COUNT(*) AS ftfr_pct
FROM tickets
WHERE closed_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

По агентам

SELECT
    agent_id,
    COUNT(*) AS handled,
    COUNT(*) FILTER (WHERE reopened_at IS NULL) AS ftf,
    COUNT(*) FILTER (WHERE reopened_at IS NULL)::NUMERIC * 100 / COUNT(*) AS ftfr_pct
FROM tickets
WHERE closed_at >= CURRENT_DATE - INTERVAL '30 days'
  AND agent_id IS NOT NULL
GROUP BY agent_id
HAVING COUNT(*) >= 20  -- only meaningful sample
ORDER BY ftfr_pct DESC;

По типам тикетов

SELECT
    ticket_type,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE reopened_at IS NULL)::NUMERIC * 100 / COUNT(*) AS ftfr_pct,
    AVG(EXTRACT(EPOCH FROM (closed_at - opened_at)) / 3600) AS avg_resolution_hours
FROM tickets
WHERE closed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY ticket_type
ORDER BY ftfr_pct DESC;

Low FTFR types → improve documentation, training, or escalation rules.

Re-open window

Considered «first time fix» = not reopened within X days:

SELECT
    ticket_id,
    closed_at,
    reopened_at,
    EXTRACT(EPOCH FROM (reopened_at - closed_at)) / 86400 AS days_to_reopen,
    -- 7-day window
    CASE
        WHEN reopened_at IS NULL THEN TRUE
        WHEN EXTRACT(EPOCH FROM (reopened_at - closed_at)) / 86400 > 7 THEN TRUE
        ELSE FALSE
    END AS is_first_time_fix
FROM tickets
WHERE closed_at >= CURRENT_DATE - INTERVAL '30 days';

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

Ошибка 1. Self-reopen vs system-reopen. User reopens manually vs system auto-reopens (e.g., follow-up). Treat differently.

Ошибка 2. Resolution definition. «Closed» vs «Resolved» vs «Customer accepted». Different statuses.

Ошибка 3. Re-open window. 3 days vs 30 days — different FTFR. Pick один.

Ошибка 4. New tickets for same issue. User opens new ticket вместо reopening. Tracked separately, but related.

Ошибка 5. Selection bias. Easy tickets → high FTFR. Filter by complexity.

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

• Incident response на собесе SA
• Как посчитать CSAT в SQL
• Как посчитать NPS в SQL
• SLA / SLO / SLI на собесе SA

FAQ

Какой FTFR ok?

70-80% — good. 85%+ — excellent. <60% — process / training problems.

FTFR vs FCR?

First Contact Resolution. Synonyms in support. «Fix» — field/technical. «Contact» — call center.

Re-open window?

7 days — стандарт. Pharma / critical: 30 days. Phone support: 24-48 hours.

FTFR падает — причины?

1. Complexity growing. 2) Training gap. 3) Documentation outdated. 4) Product bugs.

Self-service FTFR?

Track: did user return для same issue after KB article? % no return = self-service FTFR.