What is ticket backlog, and how do you measure it in Metabase?

Ticket backlog is the number of open tickets at a point in time, trended daily. It's a stock where ticket volume is a flow: volume tells you what arrived, backlog tells you what's still waiting — and how long it's been waiting, which is the part customers feel. Measure it in Metabase from help desk data synced into a database (Zendesk, Intercom, Kustomer, or Front).

What does a ticket backlog chart look like in Metabase?

Chart tickets still open at the end of each day as a line and read the slope — a falling line means solved is outrunning created. A one-day jump like Jul 20's is a surge doing what surges do; what matters is that the line resumes falling within days, and that the oldest age bucket isn't quietly growing while the total shrinks.

What ticket backlog measures

It measures the queue's standing debt, and it's best read three ways at once:

• The level — open tickets at end of day, trended. Good for spotting step changes after launches, outages, or staffing gaps.
• The flow — created minus solved per day. Backlog is just the running sum of this delta, so a persistently positive gap is the earliest warning that the level is about to climb.
• The age distribution — open tickets bucketed 0-2d, 3-7d, 8-30d, 30d+. This is the view that separates "busy" from "drowning," and the 30d+ bucket deserves its own card and its own target.

Backlog also feeds capacity planning: excess backlog divided by spare daily solve capacity is your burn-down time, and if solved-per-day doesn't exceed created-per-day, the honest answer is "never" — which is the number that justifies hiring or a deflection investment.

What data does it need?

• A tickets table with created_at, solved_at, status, and queue — enough to reconstruct "open on day X" as created on or before X and not yet solved.
• History that survives the sync. A pipeline that mirrors current state only — purging closed tickets or overwriting statuses without timestamps — cannot reconstruct past backlog. In that case, write a daily snapshot of the open count to its own table and trend that.
• Optionally a status-history table, so "pending on customer" tickets can be excluded from the backlog you hold agents accountable for.

SQL patterns

-- Daily backlog: tickets open at the end of each day, reconstructed
-- from created/solved timestamps.
-- Caveat: this only works if solved_at survives in your sync. A sync that
-- mirrors current state (and hard-deletes or overwrites history) cannot
-- reconstruct past backlog -- in that case, snapshot the open count daily
-- into its own table and trend that instead.
SELECT
  d.day,
  COUNT(t.id) AS open_tickets
FROM generate_series(
  current_date - INTERVAL '90 days',
  current_date,
  INTERVAL '1 day'
) AS d(day)
-- d.day is midnight (the START of the day), so compare against the next
-- midnight to count what was still open at the END of the day -- otherwise
-- a ticket created and solved the same day never shows up in the backlog.
LEFT JOIN tickets t
  ON t.created_at < d.day + INTERVAL '1 day'
 AND (t.solved_at IS NULL OR t.solved_at >= d.day + INTERVAL '1 day')
GROUP BY d.day
ORDER BY d.day;

-- Current backlog by age bucket and queue. The 30d+ column is the one
-- that should alarm you.
SELECT
  t.queue,
  COUNT(*)                                                     AS open_tickets,
  COUNT(*) FILTER (WHERE now() - t.created_at <= INTERVAL '2 days')  AS age_0_2d,
  COUNT(*) FILTER (WHERE now() - t.created_at >  INTERVAL '2 days'
                     AND now() - t.created_at <= INTERVAL '7 days')  AS age_3_7d,
  COUNT(*) FILTER (WHERE now() - t.created_at >  INTERVAL '7 days'
                     AND now() - t.created_at <= INTERVAL '30 days') AS age_8_30d,
  COUNT(*) FILTER (WHERE now() - t.created_at >  INTERVAL '30 days') AS age_over_30d
FROM tickets t
WHERE t.status IN ('new', 'open', 'pending')
GROUP BY t.queue
ORDER BY age_over_30d DESC, open_tickets DESC;

Pitfalls

Where this metric applies

• Zendesk + Metabase — created/solved timestamps and status for backlog reconstruction
• Intercom + Metabase — open conversation counts and state changes
• Kustomer + Metabase — conversation status and queue assignment
• Front + Metabase — open vs archived conversations by inbox

Related

Metrics

Analytics & dashboards

FAQ