Complaint data rarely arrive tidy. One agency logs a pothole report as a service request, another calls the same thing an incident, and a third never publishes the category at all. Comparing databases is less about finding one perfect source than learning each agency’s dialect—and reading the gaps between them. That is where repeat problems, slow responses, and quiet shifts in public concern tend to show up.

Map the sources before you merge them

Start with an inventory, not a spreadsheet. Local complaint records usually sit in several families of systems, each built for a different purpose. A 311 portal is designed to route service requests. A police complaint summary is designed to show accountability. A licensing board’s complaint log is designed to track regulated businesses. Those purposes shape what gets counted, what gets published, and what disappears before publication.

• Municipal 311 and service-request portals: strong for volume, location tags, and repeat-address patterns; often weak on informal complaints, duplicates, and internal referrals.
• Police complaint or internal-affairs summaries: useful for allegation types and discipline outcomes; publication schedules vary widely, and many records may be available only by request.
• Licensing and inspection complaint logs: good for complaints against regulated businesses, landlords, and premises; poor for activity outside the licensing system.
• Ombudsman, ethics, and civil-review bodies: helpful for complaint-handling delays and case outcomes; often publish summaries rather than raw records.
• Court dockets and administrative hearings: reliable for formal disputes and dispositions; slow, legalistic, and limited to cases that were actually filed.
• Consumer complaint databases: useful for cross-jurisdiction patterns; self-selected reports often lack verification of merit.

Pick at least two sources that touch the same subject—say, a city 311 system and a county licensing board. Compare what each one calls a complaint, what it publishes, and what it omits. Absence from one database is not proof that nothing happened. It may mean the complaint went to a different office, was screened out, or was never recorded in a public system.

Read the intake rules like a source document

Before counting anything, get the data dictionary and the intake policy. Ask what counts as a complaint, whether duplicates are merged, whether anonymous reports are included, and whether withdrawn complaints stay in the file. A database that publishes only screened-in cases will look smaller and more serious than one that publishes every call. Neither is necessarily dishonest; they are measuring different things.

Watch the difference between the date received and the date entered. Check whether the category is chosen by the complainant or coded by staff. Look for status codes such as “closed,” “no violation,” “unfounded,” “referred,” and “duplicate.” Those words are not interchangeable. A cluster of “no violation” closures may mean weak complaints—or weak investigations. You need the agency’s definitions before you decide.

Normalize without flattening

A comparison needs a common structure, but it should not erase the original record. Build a clean layer while preserving the raw data. A workable sequence looks like this:

1. Assign a stable source ID and keep the original row untouched.
2. Separate dates into received, acknowledged, assigned, and closed.
3. Split locations into address, parcel, license number, and district where available.
4. Map categories to a small crosswalk, but keep the agency’s original label.
5. Translate status and disposition into plain language.
6. Flag possible duplicates with a reason, not just a deletion.

Entity resolution is the hard part. “123 Main St., Apt. 4” may appear as “123 Main Street #4,” “123 Main Apt 4,” and “123 Mian St 4.” Business names change with new owners; a person’s name may be misspelled or incomplete. Use parcel numbers, license numbers, and addresses as anchors before relying on names. Common names produce false matches, and naming a person from a single complaint record is risky. Keep a confidence level for every match and be ready to explain it.

Measure delay, not just volume

Complaint counts tell you where people are looking. Response times tell you what happens after they look. Define the intervals you care about: receipt to acknowledgment, acknowledgment to first inspection, first action to closure. Compare medians and percentiles rather than averages, because one year-long case can distort a small agency’s average.

Distinguish agency delay from complainant nonresponse. A case can sit “open” because an inspector is waiting for access, a landlord is unavailable, or a resident has moved. Read the case notes where they exist. If the notes are thin, say so. Hidden trends often appear in status changes: a backlog of cases that are old but still “assigned,” a category that suddenly takes twice as long to close, or a spike in complaints that coincides with a new online form.

A rising complaint count can mean more problems, better intake, or a new category code. The comparison should tell the reader which explanation the evidence supports.

Triangulate repeat patterns

Repeat offenders are usually repeat addresses, licenses, or case types—not just repeat names. Look for a property that appears in 311 records and licensing complaints, or a business with several complaints across two agencies. Check the sequence: complaint, inspection, closure. A pattern is stronger when the same location produces the same kind of problem after an earlier inspection or closure.

Before calling something a pattern, run three tests:

• Cross-source confirmation: the subject appears in at least two independent records, or in one record plus a supporting document.
• Specificity: the match is tied to the same address, license, unit, or case type—not merely the same broad category.
• Outcome check: you have read the dispositions, not just counted the complaints.

Then ask the agency for comment. Public-records rules vary by jurisdiction, and publication can carry legal risk when a named person or business is identifiable. If findings could harm a named party, have a lawyer review them before publication. A pattern is a lead, not a verdict.

Keep a comparison log

Investigative work gets messy fast. Record the source, access date, request number, data-dictionary version, extraction query, exclusions, duplicate rules, and category crosswalk. Note what you could not get and why. Keep a clean copy and a raw copy. When a number changes, you want to know whether the agency updated its data, you changed a filter, or you corrected an error.

Documenting the boring parts makes the findings harder to dismiss. It also helps an editor see where uncertainty lives. If one agency publishes case-level records and another publishes only monthly totals, the comparison is still useful—but the limits belong in the story, not in a footnote.

A short field checklist

1. Choose two agencies with overlapping jurisdiction and a shared complaint type.
2. Request the data dictionary, status codes, and intake policy.
3. Pull the same date range and record the access date for each source.
4. Sample twenty records by hand to see how intake actually works.
5. Normalize dates and locations before counting or ranking.
6. Compute median time to first substantive action, not just total volume.
7. Test repeat subjects against a second source and read the dispositions.
8. Seek agency comment and legal review before naming individuals or businesses.

Good comparisons do not pretend the data are cleaner than they are. They show the reader what each source can support, where the gaps sit, and why a pattern deserves a closer look.

Photo: AlphaTradeZone / Pexels