How to prepare your incident data for analysis

The bar is lower than it looks, and in a different place. What a record needs before anyone can read patterns out of it is: a date, something that identifies the kind of incident, and a description of what happened written by a person.

That is genuinely most of it. Not a taxonomy, not a severity score, not a coded contributing-factor field, not a consistent facility naming scheme. Those are useful when present and none of them is the constraint.

The constraint is the description. A record with impeccable structured fields and an empty narrative column is close to unreadable, because every one of the conditions worth finding lives in the sentence somebody wrote rather than in the dropdown they picked. A record with messy fields and two real sentences per incident is analysable.

Volume matters less than people assume as well. A few hundred incidents with narratives will support more than several thousand rows of categorical data, because the questions worth asking are about what surrounded events rather than how many of each kind there were.

It is worth being concrete about why the free-text column carries the weight.

A dropdown can only offer options somebody anticipated. It encodes last year’s understanding of what matters, and it is precisely blind to whatever nobody had thought of — which is the category any useful finding belongs to. The narrative has no such limit. A crew member describing what happened will mention the handover, the unfamiliar vehicle, the fact that this is how it is usually done, without any of those being an option anywhere on the form.

This has a practical consequence for your export. If your system stores the narrative across several columns — an initial description, a supervisor’s addition, a closing note — export all of them, even the ones that look redundant. A supervisor’s addition can carry context the original report does not, and it is easy to leave out of an export because it reads as duplicative.

It also means you should worry more about narrative completeness than about field completeness. Ten per cent of rows missing a location code is a nuisance. Forty per cent of rows with a blank or one-word narrative is a finding about your reporting process, and it will bound anything the analysis can say.

Three things are worth checking in any export, and all three are cheap.

Dates break by inconsistency and by Excel. Different source systems write dates differently, and a spreadsheet in the middle of the chain will reinterpret some of them silently — the classic outcome being a set of rows that look plausible and are months wrong. Sort by date, look at the earliest and latest values, and check that both are real. Also decide which date you mean: where an export carries both an incident date and a report date, they are different columns and can be days apart.

Types break by vocabulary. The same kind of event appears as MVA, collision, vehicle accident and rig damage depending on who entered it and when. This is normal and largely recoverable, but it is worth knowing how many distinct values your type column actually contains before you assume it has five.

And check for a column that is present, well-named, and almost entirely blank. A column that is ninety per cent empty is not a data field; it is a decision somebody made not to collect something, and it should be treated as absent rather than sparse.

A common reason exports get delayed is somebody deciding the columns need cleaning up first. Before doing that, check whether it is needed at all — the cleanup can cost more than it saves.

Column mapping works on synonyms rather than exact names. A date arrives as date of injury, date of accident, date of incident, incident date, or just date, and all of them resolve to the same field. A narrative arrives as narrative, description, details, what happened, incident description, or a supervisor-specific column name, and all of them are treated as narrative. Incident types are matched the same way: employee, staff and worker all mean the same thing, as do vehicle, rig, apparatus, MVA and collision.

Which means your own vocabulary is fine, and renaming things introduces a risk you did not have before — a hand-edited export is a copy, and copies drift from the source they were supposed to represent. Send the export as your system produces it.

The two things genuinely worth doing before you send: include every narrative column rather than the one that looks primary, and say which date field is which if your system carries more than one. Everything else is better handled by whoever reads it, and if a column is genuinely ambiguous the honest resolution is a question rather than a guess.

It depends on your volume rather than on a fixed number of months. What you are trying to reach is enough incidents in each group you care about to say anything about that group.

A service running a few hundred incidents a year needs longer than one running several thousand. If you want to say something about post-move incidents specifically, what matters is how many post-move incidents you have — not how many incidents in total, and not how many months the file covers.

Twenty-four months is a common working range for a reason: it covers two of every season, which lets a seasonal pattern be distinguished from a change, and it gives the smaller categories more chance of holding enough events to be worth stratifying. Twelve months is workable. Six can describe the record, and is thin for comparing periods within it.

There is a trade-off going the other way. Older data describes an organization that may no longer exist — different staffing, different fleet, different protocols. Reaching back four years to gain volume can mean analyzing two different services and calling the result one trend. If something significant changed, that boundary is worth naming up front rather than discovering it in the findings.

Names of crew, patients and complainants come out. Direct identifiers — badge numbers, employee IDs, license plates, addresses, phone numbers, medical record numbers — come out. Anything that identifies a patient should not be in an operational safety export in the first place.

What should stay is the part people over-remove: the narrative. The instinct when de-identifying is to strip the description because it contains names, and the risk is that the whole sentence goes with them. Replace the name and keep the sentence. My partner was still inside finishing the handoff carries the entire finding; redacted to [REDACTED] was still inside it carries most of it; deleted, it carries none.

Keep the structural detail too. Which station, which shift, which vehicle type, which hour of the shift — these are what make stratification possible, and none of them identifies a person on their own in an organization of any size. If your service is small enough that a station and a shift together identify one individual, that is worth flagging explicitly rather than solving by deletion, because it changes how findings can responsibly be reported.

The general principle: remove identity, keep circumstance. Over-redaction produces a file that is safe and says nothing, and it is a more common failure than under-redaction.

A short pass in a spreadsheet catches most of what would otherwise come back as a question, and doing it yourself means you learn the answers rather than being told them.

Count the rows, and check that against what you believe your annual volume to be. A gap here is worth chasing before anything else — it points at a filter or a date range having done something you did not intend.

Count blank narratives as a percentage of the whole. Count distinct values in the type column. Sort by date and read the first and last row. Look at the widest column and the narrowest — either can be holding something unexpected. And open twenty rows at random and read them as a person would, which catches truncation, encoding damage and boilerplate that no summary statistic will.

Then check for the columns that are entirely empty and the ones that are entirely identical, because both mean the same thing: a field that exists in the schema and was never really collected.

If the export fails several of these, that is not something to tidy up before anyone sees it. It is the first finding, it can be more actionable than anything the analysis would have produced, and it is considerably cheaper to fix.