Managing Police Report Data

If you are new here and don’t follow me on Twitter or Youtube, then you might not be aware that I report bad motorists to the Police. I’ve been doing so for the past 10 years, mostly with no luck but since 2018 a new portal has been provided and action is being taken.

This has resulted in 207 reports in just over 2 years. Quickly I realised I need to track my reports, see what action is being taken, how long it takes to respond etc…. Obviously this means a spreadsheet!

My basic columns are:

  • Report Reference (this is the reference given by the system as I report)
  • Reference (this is another reference given if action is being taken)
  • Numberplate
  • Vehicle type
  • Vehicle make
  • Vehicle model
  • Vehicle colour
  • Incident type
  • Lat, Long
  • Location
  • Time
  • Date happened
  • Date reported
  • Date Response
  • Response time in days (calculation based on the time between the two previous columns
  • Response (are they taking action or not)
  • Chase up (date I chased)
  • Reason (reason it was dropped or what is going on with the case)
  • Result (text result of what happened)
  • Points (How many points they were issued)
  • Fine (the fine they received)
  • Costs (court costs applied to the case)
  • Victim Surcharge (VS applied to the case, this doesn’t go to me, is used to help victims of crime)
  • Disqualification (how long for)
  • Result Date
  • Outcome (what happened with the report; nothing, court, fpn, driving course)
  • Uploaded (is it on youtube)
  • Notes

I colour code my rows to make it easy to spot where things are in the process and how are things going. Red means no action taken, orange means warning, blue means NIP in progress, Green means result, pink means not guilty in court and blank means no response yet.

I chase old reports every 6 months to try and get updates, as they don’t always inform me if they have sent them on a driving course or issued a FPN.

I store each year in a different sheet, so overtime it stays readable. I have my first sheet as a summary sheet where I have calculations to work out totals and state of each years reports.

Because I store this information in a spreedsheet I can then easily share the data via Google Data Studio. View the data from the past 2 years.
Some of these are ongoing reports, some of these don’t have all the information as I changed what I was recording as time went by and I’ve not gone back yet. Note this is only a small selection of the columns I have data on.

One thought on “Managing Police Report Data

  1. Gaz, sorry for only now getting to read this. I have been super busy with projects around the houses ie new roof, solar panels installed

    Thank you for sharing the spread sheet.

    I really like seeing the ones with the hefty fines. Just thinking that they are a bit low for what danger these drivers are for any possible victim/s them may endanger.

    I am walking zeds pushbike, just so you know.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.