Many 311 tickets arrive with null category codes but usable free-text descriptions. How should cleansing resolve those nulls?

Many 311 tickets arrive with null category codes but usable free-text descriptions. Cleansing should map that free text through a reference table (keywords, phrases, or classified labels) to resolve category before aggregation. Restoring categories from description text recovers routing and SLA metrics without inventing random departments or discarding the narrative that citizens provided.

Mapping free text through a reference table after aggregation uses the right source but the wrong timing: the tickets have already rolled up under null, so counts and SLA metrics stay broken even after the later mapping. Mapping channel code through a reference table before aggregation uses the wrong source: channel tells how the ticket arrived, not what the issue is about, so it cannot recover the category that the free-text description actually contains. Mapping free text through a reference table during reporting also comes too late: it leaves the null unresolved in the cleansed data every other report still reads from, forcing each report to re-derive category instead of reusing a single value resolved before aggregation.

Exam caveat: Reference-driven mapping from free text is preferred over silent null aggregates or random defaults. Keep an unmapped quarantine for phrases the reference table cannot yet classify, and expand the map as 311 language evolves.

Operational check: Take tickets with null category and known phrases, apply the reference map, and confirm categories resolve correctly before gold aggregation. Measure residual null rate and review unmapped phrases for dictionary updates. Ensure free-text columns remain available in silver even after category is filled.