Как посчитать self-service deflection в SQL

Зачем deflection

Юзер ищет ответ в help center, читает FAQ, watches видео — и не создаёт тикет. Это deflection. Экономит время агентов и юзера. Метрика deflection rate показывает, насколько self-service инвестиция окупается.

Метрика

Сложно мерить напрямую: «юзер не создал тикет» — невидим. Proxy:

• search в knowledge base без последующего тикета
• viewer help article без последующего тикета
• user vote «yes, this helped»

Deflection в SQL

WITH help_visitors AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('day', visit_date)::DATE AS visit_day
    FROM help_center_visits
    WHERE visit_date >= CURRENT_DATE - INTERVAL '30 days'
),
ticket_creators AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('day', created_at)::DATE AS ticket_day
    FROM support_tickets
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
deflection AS (
    SELECT
        h.user_id,
        h.visit_day,
        EXISTS (
            SELECT 1 FROM ticket_creators t
            WHERE t.user_id = h.user_id
              AND t.ticket_day BETWEEN h.visit_day AND h.visit_day + INTERVAL '1 day'
        ) AS created_ticket
    FROM help_visitors h
)
SELECT
    COUNT(*) AS help_visits,
    COUNT(*) FILTER (WHERE NOT created_ticket) AS deflected,
    COUNT(*) FILTER (WHERE NOT created_ticket)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS deflection_rate_pct
FROM deflection;

Deflection 70-80% — здоровый help center.

По топикам

WITH article_visits AS (
    SELECT
        article_id,
        user_id,
        visit_date
    FROM help_center_visits
    WHERE visit_date >= CURRENT_DATE - INTERVAL '30 days'
),
post_visit_tickets AS (
    SELECT DISTINCT
        a.article_id,
        a.user_id
    FROM article_visits a
    JOIN support_tickets t
      ON t.user_id = a.user_id
     AND t.created_at BETWEEN a.visit_date AND a.visit_date + INTERVAL '1 day'
)
SELECT
    a.article_id,
    COUNT(DISTINCT a.user_id) AS visitors,
    COUNT(DISTINCT t.user_id) AS still_created_ticket,
    COUNT(DISTINCT a.user_id) - COUNT(DISTINCT t.user_id) AS deflected,
    (COUNT(DISTINCT a.user_id) - COUNT(DISTINCT t.user_id))::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT a.user_id), 0) AS deflection_pct
FROM article_visits a
LEFT JOIN post_visit_tickets t USING (article_id, user_id)
GROUP BY a.article_id
HAVING COUNT(DISTINCT a.user_id) >= 50
ORDER BY deflection_pct ASC
LIMIT 20;

Articles с low deflection — переписать (плохо объясняет).

Экономия

WITH counts AS (
    SELECT
        COUNT(*) AS help_visits,
        COUNT(*) FILTER (WHERE NOT created_ticket) AS deflected
    FROM deflection
),
cost AS (
    SELECT 12.0 AS cost_per_ticket  -- agent time × wage
)
SELECT
    deflected,
    deflected * cost_per_ticket AS estimated_savings_usd
FROM counts, cost;

100k deflections × $12 = $1.2M savings ежемесячно. Это бюджет на help center team.

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

Ошибка 1. Считать каждый visit = deflection. Юзер мог просто browse. Не каждый visit = «I had a problem».

Ошибка 2. Не учитывать time window. Тикет через 7 дней после visit — не related. Окно 24-48 часов.

Ошибка 3. Игнорировать negative feedback. Article voted «not helpful» — не deflection, даже если тикета нет.

Ошибка 4. Сравнивать deflection без context. 50% deflection в новом help center — норма. После 2 лет improvement — 80%.

Ошибка 5. Cost savings overstate. Customer мог не создать ticket даже без help center. Только incremental savings.

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

• Как посчитать CSAT в SQL
• Как посчитать ticket volume anomaly в SQL
• Как посчитать agent productivity в SQL
• Как посчитать time-to-resolution в SQL

FAQ

Какой deflection норма?

70-80% для зрелого help center. 50-60% для нового.

Окно после visit?

24-48 часов. Дальше — слабая связь.

Article quality vs deflection?

Бывает, полезная статья всё равно ведёт к тикету (юзер хочет double-check). Article quality — не единственная метрика.

Deflection через chatbot?

Chatbot, разрешающий вопрос до создания тикета — это deflection. Считайте отдельно.

Deflection и CSAT?

Не всегда позитивно: иногда юзер «решил сам» с frustration. Survey deflection users.