Report from your own database
Measure response times, volume and ratings with SQL over the helpdesk's tables, and connect a dashboard tool with a read-only role.
Every conversation, message and rating lives in your own database, so a report is a SQL query. How it works explains why the tables are yours to read. The agent UI's Overview covers the everyday numbers: volume, median first response and resolution times, and ratings, over the last 7, 30 or 90 days. For anything else, query the tables.
Each query below covers the last 30 days and leaves out conversations that were merged into another, so a merged duplicate is not counted twice.
The queries are written for Postgres. On SQL Server the tables sit in the same helpdesk schema; on MySQL and SQLite they carry a prefix instead, so helpdesk.conversation is helpdesk_conversation. Medians, date arithmetic and JOIN LATERAL need each database's own SQL. On SQLite and SQL Server a boolean such as internal is stored as 1 or 0, and on SQLite a time is ISO 8601 text in UTC.
A minor release only adds columns and tables. Renaming or dropping one takes a major release, and its notes say so, so check them before you upgrade a helpdesk that reports depend on.
Median first response
The time from a conversation's start to the first reply the customer could see. Internal notes do not count.
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY first_reply - c.created_at)
AS median_first_response
FROM helpdesk.conversation c
JOIN LATERAL (
SELECT min(m.created_at) AS first_reply
FROM helpdesk.message m
WHERE m.conversation_id = c.id AND m.author_type = 'agent' AND NOT m.internal
) r ON first_reply IS NOT NULL
WHERE c.created_at > now() - interval '30 days' AND c.merged_into_id IS NULL;Median time to resolution
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY resolved_at - created_at)
AS median_resolution
FROM helpdesk.conversation
WHERE status = 'resolved' AND resolved_at > now() - interval '30 days'
AND merged_into_id IS NULL;These two count every hour on the clock. The Overview counts only open hours for an inbox with business hours, so its numbers for such an inbox are usually lower than these.
90th percentile first response
The same query with percentile_cont(0.9). On a conversation opened since the column arrived, first_responded_at already holds the first public reply, so the join can go:
SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY first_responded_at - created_at)
AS p90_first_response
FROM helpdesk.conversation
WHERE created_at > now() - interval '30 days' AND merged_into_id IS NULL
AND first_responded_at IS NOT NULL;SLA attainment per customer and month
Only conversations that fell under a target count. A response target is met when the first reply came before respond_by; a resolution target when the conversation was resolved before resolve_by, which already allows for the time the clock was paused. An open conversation whose deadline has passed counts as missed:
SELECT co.name AS company, date_trunc('month', c.created_at) AS month,
count(*) FILTER (WHERE c.respond_by IS NOT NULL) AS response_targets,
count(*) FILTER (WHERE c.respond_by IS NOT NULL
AND coalesce(c.first_responded_at, now()) <= c.respond_by) AS response_met,
count(*) FILTER (WHERE c.resolve_by IS NOT NULL) AS resolution_targets,
count(*) FILTER (WHERE c.resolve_by IS NOT NULL
AND coalesce(c.resolved_at, now()) <= c.resolve_by) AS resolution_met
FROM helpdesk.conversation c
LEFT JOIN helpdesk.company co ON co.id = c.company_id
WHERE c.created_at > now() - interval '12 months' AND c.merged_into_id IS NULL
GROUP BY 1, 2
ORDER BY 1, 2;Every breach the jobs run recorded is a row in conversation_event with the kind sla.breach and data->>'target', so a credit report can list them by conversation:
SELECT c.number, co.name AS company, c.tier, c.priority,
e.data->>'target' AS target, e.data->>'due' AS due, e.created_at AS recorded_at
FROM helpdesk.conversation_event e
JOIN helpdesk.conversation c ON c.id = e.conversation_id
LEFT JOIN helpdesk.company co ON co.id = c.company_id
WHERE e.kind = 'sla.breach' AND e.created_at > now() - interval '30 days'
ORDER BY e.created_at DESC;Volume per inbox and type
SELECT inbox, type, count(*) AS conversations
FROM helpdesk.conversation
WHERE created_at > now() - interval '30 days' AND merged_into_id IS NULL
GROUP BY inbox, type
ORDER BY conversations DESC;Ratings
A customer rates a resolved conversation as good or bad, from the widget or from the reply email:
SELECT rating, count(*) AS conversations
FROM helpdesk.conversation
WHERE rated_at > now() - interval '30 days' AND merged_into_id IS NULL
GROUP BY rating;A comment the customer added is in rating_comment.
Most-used tags
SELECT t.tag, count(*) AS conversations
FROM helpdesk.conversation c
JOIN helpdesk.conversation_tag t ON t.conversation_id = c.id
WHERE c.created_at > now() - interval '30 days' AND c.merged_into_id IS NULL
GROUP BY t.tag
ORDER BY conversations DESC
LIMIT 10;Resolved per agent
SELECT coalesce(a.name, a.email, 'Unassigned') AS agent, count(*) AS resolved
FROM helpdesk.conversation c
LEFT JOIN helpdesk.agent a ON a.id = c.assignee_id
WHERE c.status = 'resolved' AND c.resolved_at > now() - interval '30 days'
AND c.merged_into_id IS NULL
GROUP BY 1
ORDER BY resolved DESC;Every table and column is described in Database. With retentionDays set, deleted conversations leave the reports too, so keep any history you need in a table of your own.
Connect a dashboard
For Metabase, a PostHog data warehouse source or any other tool that reads your database, give it a role of its own that reads the helpdesk's tables. On Postgres, run this as the role that runs your migrations. The ALTER DEFAULT PRIVILEGES line grants every table a later migration adds as well, so when a release adds a table that holds secrets or personal data, add it to the REVOKE line:
CREATE ROLE helpdesk_reports LOGIN PASSWORD 'change-me';
GRANT USAGE ON SCHEMA helpdesk TO helpdesk_reports;
GRANT SELECT ON ALL TABLES IN SCHEMA helpdesk TO helpdesk_reports;
ALTER DEFAULT PRIVILEGES IN SCHEMA helpdesk GRANT SELECT ON TABLES TO helpdesk_reports;
REVOKE SELECT ON helpdesk.setting, helpdesk.identity, helpdesk.rate_limit FROM helpdesk_reports;The last line keeps three tables out of the dashboard that no report needs: setting holds the key that signs rating links, identity holds visitor token hashes and your users' ids, and rate_limit holds client IP addresses. audit_log holds who changed what, which a dashboard may read; add it to the REVOKE line if yours should not. On another database, grant read access in its own way and keep the same tables out.