What is deflection rate, and how do you measure it in Metabase?

Explicit success signals, minus anyone who opened a ticket within 72 hours.

-- Monthly deflection rate from an explicit-signal self-service model.
-- A self-service event counts as a resolution only if (a) it carries an
-- explicit success signal and (b) the same user opened no ticket within
-- 72 hours. Assumes user identity is joinable across both tables.
WITH deflected AS (
  SELECT
    date_trunc('month', s.occurred_at) AS month,
    COUNT(*)                           AS self_service_resolutions
  FROM self_service_events s
  WHERE s.event IN ('article_marked_helpful', 'bot_resolution_confirmed')
    -- Only count events whose 72-hour no-ticket window has fully closed.
    -- A brand-new event has no follow-up ticket YET, so counting it now
    -- would inflate today's deflection and quietly reverse tomorrow.
    AND s.occurred_at < NOW() - INTERVAL '72 hours'
    AND NOT EXISTS (
      SELECT 1
      FROM tickets t
      WHERE t.requester_id = s.user_id
        AND t.created_at BETWEEN s.occurred_at
                             AND s.occurred_at + INTERVAL '72 hours'
    )
  GROUP BY 1
),
created AS (
  SELECT
    date_trunc('month', t.created_at) AS month,
    COUNT(*)                          AS tickets_created
  FROM tickets t
  GROUP BY 1
)
SELECT
  d.month,
  d.self_service_resolutions,
  c.tickets_created,
  ROUND(100.0 * d.self_service_resolutions
        / NULLIF(d.self_service_resolutions + c.tickets_created, 0), 1)
    AS deflection_rate_pct
FROM deflected d
JOIN created c USING (month)
ORDER BY d.month;

Ranks articles by confirmed deflections and shows how often users ticketed anyway.

-- Top deflecting articles, last 90 days: confirmed self-service
-- resolutions with no follow-up ticket within 72 hours, ranked.
SELECT
  s.article_id,
  s.article_title,
  COUNT(*) AS confirmed_resolutions,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM tickets t
      WHERE t.requester_id = s.user_id
        AND t.created_at BETWEEN s.occurred_at
                             AND s.occurred_at + INTERVAL '72 hours'
    )
  )        AS ticket_anyway,
  COUNT(*) FILTER (
    WHERE NOT EXISTS (
      SELECT 1
      FROM tickets t
      WHERE t.requester_id = s.user_id
        AND t.created_at BETWEEN s.occurred_at
                             AND s.occurred_at + INTERVAL '72 hours'
    )
  )        AS deflections
FROM self_service_events s
WHERE s.event IN ('article_marked_helpful', 'bot_resolution_confirmed')
  AND s.occurred_at >= current_date - INTERVAL '90 days'
  -- Same 72-hour maturity guard as the monthly query: an event too recent
  -- to have accrued its follow-up ticket can't be scored as a deflection yet.
  AND s.occurred_at < NOW() - INTERVAL '72 hours'
GROUP BY s.article_id, s.article_title
ORDER BY deflections DESC
LIMIT 25;