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 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