Civic complaint resolution: reopen rates and SLA by ward

The question a commissioner actually asks

A municipal commissioner or zonal officer rarely asks "what is our average resolution time". The question is closer to: which wards are leaving citizens with unresolved problems, and is the dashboard hiding it? Many grievance systems (city apps, helpline CRMs, state public grievance portals) show a green "closed within SLA" figure computed from the first time a field officer marked the ticket done. That number is easy to game and easy to misread, so the analysis has to be built from the event history, not the ticket's current status.

What the export usually looks like

You will typically get three extracts. Column names vary by vendor; the meaning is what matters.

If you only receive the complaint table with a status and closed_at column, ask for the event log. Without it you cannot see reopens, and closed_at is usually overwritten by the latest closure.

Define the four measures before touching data

• First-closure SLA % = complaints whose first closed event is within sla_hours of registered_at ÷ complaints registered.
• Resolved-within-SLA % = complaints whose final closure is within sla_hours, and which were never closed by an automatic rule, ÷ complaints registered.
• Reopen rate = complaints with at least one reopened event ÷ complaints closed at least once.
• Auto-closure count = complaints closed at any point by a system rule such as "citizen not reachable".

The first measure is what the portal shows. The second is what a citizen experiences. Reporting both, side by side, is the whole point.

A worked example on synthetic data

The data below is synthetic: ten complaints across two wards, with hypothetical SLAs of 24 hours for garbage, 48 for water leaks and 72 for streetlights.

CREATE TABLE complaint (complaint_id TEXT PRIMARY KEY, ward TEXT, category TEXT, registered_at TEXT);
CREATE TABLE complaint_event (complaint_id TEXT, event_type TEXT, event_at TEXT, closed_by TEXT);
CREATE TABLE category_sla (category TEXT PRIMARY KEY, sla_hours INTEGER);
INSERT INTO category_sla VALUES ('streetlight',72),('garbage',24),('water_leak',48);
INSERT INTO complaint VALUES
('C01','Ward 12','garbage','2026-08-01 08:00'),('C02','Ward 12','garbage','2026-08-01 09:30'),
('C03','Ward 12','streetlight','2026-08-02 19:00'),('C04','Ward 12','water_leak','2026-08-03 07:15'),
('C05','Ward 12','garbage','2026-08-04 10:00'),('C06','Ward 31','garbage','2026-08-01 11:00'),
('C07','Ward 31','streetlight','2026-08-02 20:00'),('C08','Ward 31','water_leak','2026-08-03 06:00'),
('C09','Ward 31','garbage','2026-08-05 12:00'),('C10','Ward 31','water_leak','2026-08-06 09:00');
INSERT INTO complaint_event VALUES
('C01','closed','2026-08-01 20:00','field'),
('C02','closed','2026-08-01 18:00','field'),('C02','reopened','2026-08-02 08:00','citizen'),
('C02','closed','2026-08-03 12:00','field'),
('C03','closed','2026-08-04 10:00','field'),
('C04','closed','2026-08-04 09:00','field'),
('C05','closed','2026-08-04 16:00','auto_not_reachable'),
('C06','closed','2026-08-01 22:00','field'),
('C07','closed','2026-08-03 09:00','field'),('C07','reopened','2026-08-04 10:00','citizen'),
('C07','closed','2026-08-07 10:00','field'),
('C08','closed','2026-08-05 18:00','field'),
('C09','closed','2026-08-06 08:00','field'),
('C10','closed','2026-08-06 15:00','auto_not_reachable'),('C10','reopened','2026-08-07 09:00','citizen'),
('C10','closed','2026-08-09 11:00','field');

WITH ev AS (
  SELECT complaint_id,
         MIN(CASE WHEN event_type = 'closed' THEN event_at END) AS first_close,
         MAX(CASE WHEN event_type = 'closed' THEN event_at END) AS final_close,
         SUM(event_type = 'reopened') AS reopens,
         MAX(event_type = 'closed' AND closed_by = 'auto_not_reachable') AS any_auto_close
  FROM complaint_event
  GROUP BY complaint_id
),
m AS (
  SELECT c.ward, c.complaint_id, s.sla_hours, ev.reopens, ev.any_auto_close,
         (julianday(ev.first_close) - julianday(c.registered_at)) * 24 AS hrs_first,
         (julianday(ev.final_close) - julianday(c.registered_at)) * 24 AS hrs_final
  FROM complaint c
  JOIN category_sla s USING (category)
  JOIN ev USING (complaint_id)
)
SELECT ward,
       COUNT(*) AS complaints,
       ROUND(100.0 * SUM(hrs_first <= sla_hours) / COUNT(*), 0) AS sla_pct_first_close,
       ROUND(100.0 * SUM(hrs_final <= sla_hours AND any_auto_close = 0) / COUNT(*), 0) AS sla_pct_resolved,
       ROUND(100.0 * SUM(reopens > 0) / COUNT(*), 0) AS reopen_pct,
       SUM(any_auto_close) AS auto_closed
FROM m
GROUP BY ward;

Output:

Ward 12 looks perfect on the portal's measure. Once you follow C02 (closed in 8.5 hours, reopened, finally fixed after 50.5 hours) and C05 (auto-closed because the citizen did not answer a call), its resolved-within-SLA figure is 60%. Every complaint in this sample was closed at least once, so the reopen denominator equals the complaint count; in real data, restrict it to complaints with a closure.

Where civic data produces wrong numbers

Reopens that arrive as new complaints. Many citizens never find the reopen button and simply file again. Match new complaints to recently closed ones by category, ward and location, using a radius and a time window you agree with the department, and treat matches as reopens. Without this step the ward that fails most can look efficient.

Auto-closure rules. "Citizen not reachable" closures stop the clock. Report them as a separate count; never fold them into resolved.

Clock start. Some systems start the SLA at assignment to a field officer, not at registration. The hours between registration and assignment are real waiting time for the citizen. State which clock you used.

Category re-tagging. A complaint moved from "water leak" to a category with a longer SLA mid-way can pass on paper. Keep the original category alongside the final one and report compliance on both.

Working hours versus calendar hours. If the civic body defines its SLA in working days, the calendar-hour calculation above overstates breaches for complaints registered on a Saturday evening. Confirm the definition in the citizen charter before publishing.

Volume differences between wards. A ward with 40 complaints a month and one with 4,000 should not share a league table without counts beside each percentage.

How to put this in front of a commissioner

Lead with one table per zone showing, for each ward: complaints registered, first-closure SLA %, resolved-within-SLA %, reopen rate and auto-closures. Sort by the gap between the first two percentages; the wards with the largest gap are where the dashboard and citizens disagree. Then give two or three anonymised complaint histories that show the pattern in plain words: "closed in 8 hours, reopened next morning, fixed two days later".

Avoid ranking individual field officers from this data. The gap often comes from material shortages, contractor response or inter-department handoffs, and naming staff invites gaming of the close button rather than fixing the pipe.

A good closing recommendation is procedural, not a target: publish resolved-within-SLA next to the existing figure, and require a photo or citizen confirmation before a closure counts.

Turning this into a portfolio project

If your city publishes grievance data on an open-data portal, this becomes a strong project: the question is public, the stakes are clear, and event-log SQL of this type is exactly what interviewers probe. The Data Analyst programme covers it in depth. To see which of these skills your current resume already shows, run the free skill check. For a regulated cousin of the same "which date starts the clock" problem, read bank NPA ageing.