In support of our long-overdue public conversation about police and policing in the US, I’ve been digging through city data, making plots, and posting them on Twitter. Here’s one that got more attention than my stuff usually does (generally I live tweet city council meetings for a very small audience). It shows some of our police officers making wildly more money than other city employees.
One of my favorite quotes comes from Hyman Rickover, who led the engineering teams who put nuclear reactors on US submarines. He said, “Nothing so sharpens the thought process as writing down one’s arguments. Weaknesses overlooked in oral discussion become painfully obvious on the written page.”
Because of the interest, and because this is a really important conversation, I’ve stitched a few of those tweets into this post. I’ve included source material, as well as a bit of a “how-to” on looking at this stuff. I hope that this will inspire other people to take a look under the hood at government data of all sorts. A diversity of perspectives and approaches is always the best way to figure things out, whether we’re talking about civics or science.
In creating this document, I discovered several errors and omissions in my early thinking. I expect that to continue. The plot above, for example, contains a certain nuance that has adjusted my interpretation. Things are not as clear as they appear at first glance.
In that spirit, let’s look at some data.
The data I used to create that first plot is pretty straightforward. Somerville has a data portal. On that portal, there are reports titled “weekly payroll gross wages over 50k” for each of the last several years.
You can fight with the user interface they’ve provided (it’s actually pretty good, I’m just a snob about these things), but I tend to err on the side of exporting the raw data. One big advantage of this is reproducibility in case the city decides to modify the data or take the portal down.
You don’t have to go far in data like this before you find lots of personally identifying information (names). I’ve decided to omit those from my plots. I do this because I don’t think that people’s names are relevant to the points I’m trying to make. I would also like to avoid triggering any retaliation either against or by anybody who shows up here.
A quick eyeball of the data shows the police absolutely dominating in terms of gross take-home pay. Here’s a plot showing the highest vs. the lowest paid city employees in 2018. The report cuts off at $50k, so we don’t even see the lowest earners (which feels sadly typical). There’s also no accounting for the fact that some people worked only a portion of the year.
The orange is base salary. The other colors are overtime, detail, and “other.”
What is “Other?”
For that, we need to dive into some other documents, starting with Somerville’s budget.
It’s almost impossible to make heads or tails of anything from the budget proposals on the city website. The 2020 edition was a 255 page PDF filled with flowery language, colors, pictures, and charts. It’s an interesting read, but it’s completely unusable from a data perspective. Take this example from page 169:
Since budget season happens during the prior fiscal year, we don’t yet have complete data about how much of the money will be spent. The analytically inclined reader is left to squint and work sums, subtracting $8.9M from $11.4M and spreading the remaining money out over the two months remaining in the fiscal year to figure out whether that proposed budget increase of $400k seems appropriate.
This is no way to live.
Fortunately, the numbers in that PDF come from a database at city hall. The finance department’s website includes lots of reports that look a lot more like data. Unfortunately, most of those reports are similarly opaque PDFs like the one below.
When I started digging into this stuff, I tried to use these files for a while. I wrote some horrible computer programs to extract numbers from the PDFs. I was able to get pieces of the picture, but it was always more work than it was worth and my results were always brittle and incomplete.
My city councilor, JT Scott grappled with this frustrating situation for years, applying both public and private pressure. Early this year he had a breakthrough and received an export of the city’s budget vs. actuals for the last 8 years. Most important, he got it as data.
This thing is a gold mine. You can download it here.
The data is sorted by department and then by category. For each row, we see four numbers (three, really): The original budgeted amount comes first, followed by the mid-year transfers in or out from that line item (more about those below). The “revised” budget is the sum of the original and any transfers. The actual amount expended comes last.
Those transfers and adjustments are the mechanism by which the administration shuffles small and large amounts of money post-budget. Many of the transfers come before the city council (though some do not). They don’t usually get a lot of attention. I suspect that’s because they’re generally so soul-crushingly boring.
Here’s a very ordinary example where the city moved money from the salaries line item to a “professional and technical services” line so that they could hire a consultant while a particular job-slot was vacant.
By law, the city cannot exceed the adjusted budget for any line item. Any money that remains un-allocated at the end of the year goes away. The way that we hang on to some of that money is to transfer it from a budget line item into a fund. These funds are accounts that can hold money year over year. Here’s a snippet of the end-of-year shuffle from last June, stashing a bunch of money before it goes away.
There’s no magic or loophole here — the money in those funds is just one more source of cash for the city in the next year. In order to get spent, it needs to be assigned a budget line item which will then be approved by the city council.
Thrilling, right? Stay with me, I’m going somewhere with this.
Consistently over, Consistently Under
Some budget items are consistently underspent, year over year. The police budget has a line item named CARE OF PRISONERS. It starts out at $7k, though we never ever spend that much. Each year we pull a bit of money out of that line and do something else with it.
Other items are consistently overspent. The police budget for OFFICE FURNITURE gets budgeted at a steady $4k, year over year. That has been sufficient only two years out of the past eight. The rest of the time we’ve shifted money in from somewhere else.
The details of where the money came from and where it went are available. Unfortunately, they’re mostly trapped in the city council minutes, another PDF hell.
It’s important to note that there’s nothing necessarily sketchy in any of this. Any competent middle manager in a big, bureaucratic organization knows how this game is played. You pad some items a little bit to make slack for the small adjustments you need to make throughout the year.
If you’re good at this sort of stuff, you can be a hero to a colleague or a boss by having money laying around for an urgent need.
Speaking of “good at this stuff,” here’s an example from our Department of Public Works in the CARE OF TREES line item (I find it poignant that the word “care” only occurs two places in the budget data, prisoners and trees). Anyway, most years, tree care provides a bit of a cushion to support other line items in DPW. 2018 was an exception — we went over because of a citywide campaign against dutch elm disease.
Despite our protestations of poverty, there’s usually money laying around in the city budget, if you know where to look.
Let’s talk about something more timely: The WEAPONS line in the police budget. This one is -consistent-. Every year since 2016, we’ve budgeted around $76k and spent about 90% of it.
The WEAPONS line appears as an empty placeholder in 2015. It’s not there in 2014 or prior. This is another thing that makes retrospective analysis hard — from time to time we change our accounting. That means I need to go digging around to find the row or rows that drop by about the right amount at the right time.
Cutting to the chase, WEAPONS used to be part of UNIFORM ALLOWANCE.
You might think, as I did, that $69k per year of weapons feels really damn high for a suburban city 4.4 square miles in size and with a population of 88,000 people. However, this money is not for buying guns and billy-clubs. This WEAPONS line falls in the “POLICE PERSONAL SERVICES,” category. We pay our officers when they complete the (required) weapons safety training. It works out to about $500 per cop per year.
This is part of that “other” category from above along with SHIFT DIFF, LONGEVITY, COURT TIME, HOLIDAYS, ED INCENTIVE, UNIF ALLOW, 5/2 BUYBACK, and my personal favorite MASS DESTRUCTION.
I still don’t know where the gun money is.
My guess is that guns (and bullets, and pepper spray, and zip ties) are buried in one of the POLICE ORDINARY MAINTENANCE budget lines. There are 44 of those, covering everything from OFF FURN to FLOWR&FLAG.
It’s also possible that guns, being durable machines that maintain value year over year, might fall into the capital budget. In that case we would need to go to a completely different set of documents. The information is out there, it’s just hard to find.
One question that comes up frequently is “how much are we paying out annually to settle lawsuits?” The answer is that I can tell you for the whole city, but it’s super hard to find out for a single department.
The JDG/SETTL category shows up 3 times in the 2018 spreadsheet. The law department has a placeholder that never gets used. The other two are workers comp and damages to persons and property. The former pays our employees when they get hurt on the job. The latter settles up with the people that we harm, either actively or through negligence.
2019 was a bit of an outlier.
I don’t know exactly what that $5.4M settlement is for. There were two things of about the right size that happened in 2019: We finally signed a contract with some (but not all) of our city unions and we settled up with the owners of the properties that we seized by eminent domain in Union Square. If the bulk of the “other” line in the police gross income was back pay from a hard fought contract negotiation, then I have more sympathy.
I’m not going to sleep right until I understand how that $5.4M moved out of the settlements line, and whether some of it was the $2.2M that shows up in the evocatively named OTHR LUMP line in the police budget.
But first, we really need to talk about the overtime.
In 2018, there were 123 police officers of all ranks in Somerville. Forty five of them (a third) took home at least 50% more money above and beyond their salary in overtime and detail pay. For the fire department, with 134 people, that number was three. The numbers were similar in 2017, with 49 officers (and one firefighter) were above the 50% mark.
On the face of it, that’s insane and we need to get it under control. Whether we look from a business perspective (we could get a lot more person-time for the same money!), a quality of life perspective (that -has- to work out to a lot of time away from family and home!), or an equity lens (many critical categories of employee don’t appear to qualify for overtime at all) — we need to have the conversation.
We also need clarity on those lump sums and “other.” That’s a lot of cash moving around.
I am cautiously optimistic that the energy I see in the streets these last two weeks might translate into long needed change. As we tear down pieces of this country’s painful legacy of racism, violence, and hate — I hope that we can be thoughtful and open about the facts and the figures.
This stuff doesn’t fit in a tweet. Long form thoughts don’t survive the mode of public “debate” that we’ve got right now. It will take diligence, courage, transparency, and a fair amount of spreadsheet work to come to the right conclusions.
Or, as I put it online:
Here’s to sharpening our thought process, finding and owning up to those errors and omissions, and getting it right this time.