How to calculate Ticket Reopen Rate

Formula

Reopen rate = tickets reopened after resolution ÷ tickets resolved × 100

SQL query

This example assumes: tickets(id, category, resolved_at, reopened_at). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT category,
       COUNT(*) FILTER (WHERE resolved_at IS NOT NULL)                         AS resolved,
       COUNT(*) FILTER (WHERE reopened_at IS NOT NULL) * 100.0
         / NULLIF(COUNT(*) FILTER (WHERE resolved_at IS NOT NULL), 0)          AS reopen_pct
FROM tickets
GROUP BY category
ORDER BY reopen_pct DESC;

Common mistakes that make Ticket Reopen Rate wrong

• Recently resolved tickets have had little time to be reopened; use a fixed window such as 14 days.
• A customer replying 'thanks' that reopens the ticket is noise; exclude automatic reopens.
• A new ticket on the same issue is not counted unless you link them.

Ask for Ticket Reopen Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• Which categories have the highest reopen rate?
• Does reopen rate differ by agent?