I Thought He Came With You (ITHCWY) is Robert Ellison's blog.

San Francisco Budget - The Missing Manual

Overview
Organization Hierarchy

Organization Group and Department
Police Department Programs

Object Hierarchy

Character, Object, Sub-object for Revenue
Character, Object, Sub-object for Spending
Sub-objects for the Police Department

Fund Hierarchy

Fund Type and Fund
Fund Type to Organization Group

I recently released my San Francisco Budget GPT. This is based on open data from DataSF, and while it can answer many questions it helps to have enough context to know what to ask. The published dataset has a data dictionary but it can be frustratingly vague. As well as the downloadable data there is a helpful site where you can generate some reports. This post should help fill in some of the gaps.

Overview

Budget data is available from the 2009-2010 to the 2024 to 2025 fiscal years (which run from July 1 to June 30). In the file the fiscal year is at the end of the period. Every dollar has a source and a use, there is a 'Revenue or Spending' column that is Revenue for sources and Spending for uses. The actual dollar values are in a 'Budget' column. There are three hierarchies that allow you to analyze the data - organization, object and fund. Each hierarchy has three levels, and each level has a code and a description. For the rest of this post I'm using data from the 2023-2024 fiscal year.

The total budget for 2023-2024 is $14.6 billion dollars. That's a little hard to put into context. On the one hand it's about two dimes for every week the universe has existed. On the other, it's only a quarter of Elon Musk's Tesla bonus.

Organization Hierarchy

This is the organizational structure of the city - Organization Group, Department and Program. One example is Public Safety, Police, Operating. Here's the full Organization Group and Department structure:

Organization Group and Department

(A quick note on charts - there is a lot of information to present here. Each chart has a full screen button at the top right and I recommend using this to see the most detail. Charts also have tooltips and so you'll have a much better time with this post if you use a big screen and a mouse. On sunburst (nested pie) charts click any segment to filter to that parent only.)

I haven't included Program here. It sounds like it should be useful, the data dictionary says: "For example, the Police Department has programs for Patrol, Investigations, and Administration." But it doesn't:

Police Department Programs

Maybe this worked at some point, but it's not a lot of help for the current year (It's a bit of a mystery, but not like that time I found out someone redistricted the Farallon Islands.)

It's also helpful to know that the department code has been prefixed to every department. So in the data the Police Department is 'POL Police'. This doesn't seem to happen elsewhere in the dataset. It probably helps to find monstrosities like 'MTA Municipal Transprtn Agncy' because we presumably can't find the spare change for longer column names.

Object Hierarchy

My favorite hierarchy is what we're spending money on at a reasonably granular level, broken down into Character, Object and Sub-object. This works both ways and you can look up sources as well as uses. An example on the revenue side is 'Fines, Forfeitures & Penalties', 'Traffic Fines', 'Traffic Fines - Parking' ($98 million).

Character, Object, Sub-object for Revenue

Character, Object, Sub-object for Spending

Sub-objects for the Police Department

While Program doesn't work, Sub-Object allows us to break down Police department spending in more detail:

Fund Hierarchy

Last but not least the fund hierarchy is where the money comes from. The three levels are Fund Type, Fund and Fund Category. As with objects you can also use this on the Spending side to see where funds are being spent.

Fund Type is critical to understanding the budget. There are three main buckets - General Fund, Enterprise Funds and Special Revenue Funds. Enterprise Funds are for self funding departments, so while $1.2 billion of the budget is for San Francisco International Airport that's not coming out of my pocket. The General Fund is discretionary spending and Special Revenue Funds are dollars that have been restricted to specific uses, for example by the endless ballot measures that San Francisco and California are so fond of.

Fund Type and Fund

This treemap shows the first two levels of the fund hierarchy:

Fund Type to Organization Group

Lastly this chart shows the flow of Fund Types to Organization Groups for Spending, excluding Enterprise Funds:

Add your comment...

(All Politics Posts)

(Published to the Fediverse as: San Francisco Budget - The Missing Manual #politics #sanfrancisco #budget Detailed guide to the three hierarchies of the San Francisco Budget from DataSF. )

politics, san francisco, budget

Add Comment

All comments are moderated. Your email address is used to display a Gravatar and optionally for notification of new comments and to sign up for the newsletter.