Project description:

The Better Government Association’s Illinois Public Pensions Database is the most comprehensive source of information about public pensions in Illinois, featuring more than seven years and 3 million lines of data.

A Chicago nonprofit newsroom, the BGA produces the database as part of its mission to make government throughout Illinois more transparent and accountable.
Shortfalls with funding Illinois’ public pension systems are perhaps the biggest emergency facing the state today. The BGA believes that by collecting this data and centralizing it into one place that is easy for citizens to access our organization brings greater clarity to this important topic.

Impact reached:

Illinois has become a state divided over the issue of public-employee pensions. Conservatives and some business leaders see them as a drain on resources that have brought Illinois to its fiscal knees while unions — threatened by such sentiments — have become fiercely protective of pensions as they defend their members and their own political power.

The Illinois Public Pensions Database aims to push aside such political banter and provide residents with unambiguous facts that will give them clear ideas about what is really behind the biggest issue affecting the state.

The project unearths details buried in complex fiscal reports and puts that information into an easy-to-use, first-of-its-kind tool that informs policy makers, media members and citizens whose lives are affected daily by this complex and intimidating issue.

The database provides an amount of detail on the critical topic of municipal and state finances that had never before been collected in one place. It added nuance to a discussion that could often devolve into empty talking points.

Though the project was unveiled only a few months ago, it drew early interest that has remained steady. Illinois policy experts and news organizations have regularly cited the database in articles, research projects and policy positions.

The database allows users to view basic information about the major pension funds including number and type of pension, annual payment, cost of debt and distribution of pension payments. It can be searched by fund and by year. Funds can be grouped by geography. It also revealed the cost of pension underfunding in a way that added great clarity to the issue.

Techniques/technologies used:

All records were obtained in a text-delimited format from Illinois’ 17 largest pension funds through public-records requests. The status of the request was monitored on a Google Sheet that tracked dates and responses by agency. 

Data cleaning and organization for this project was performed in MySQL. The database itself is managed with Django. PDF reports were obtained from a state agency that provides data to state legislators about the fiscal health of the pension funds. 

Agencies were categorized and uploaded using Python. Pension funding data was pulled from reports, entered into a CSV file, and entered into Django. 

The figure for back debt paid annually on pensions had to be calculated by using the available data from each report. The total normal cost — or the money spent by the fund to pay for annual pension benefits — was matched with the employer normal cost, or amount paid by the agency to make up the difference between the pension cost and the employee contribution, and operational costs, revealing the amount used to pay back debt created by years of underfunding pensions.  

What was the hardest part of this project?

Beyond normalizing the data reports from multiple agencies, finding the exact figures needed for the project from the state reports and making sure they accurately reflected the pension conditions was paramount to making the project work. While there was no accompanying story, this application was a journalistic project, meaning it was subject to the same standards of accuracy that an investigation would be. 

Making sure that all of the information presented was useful both to pension experts and everyday users was also important.  
There is also difficulty in sending 15 public-records requests. Agencies must be contacted regularly if they do not comply with the request. It took about six weeks to obtain all of the records for 2019, and data cleaning took about three weeks. The process will be repeated each year going forward.   

What can others learn from this project?

The project shows how packaging publicly available information can serve multiple audiences. The tool can serve pension experts as well as users looking to learn more about the topic. 

While the topic area might not be interesting for all audiences, this shows a way to make a complicated topic easy to understand for all users. It also includes enough data to make it useful for experts on the topic.

Government financial data is a cornerstone of the BGA approach. Projects like the Public Pension database show that a focus on these topics pay dividends to organizations willing to put the time and effort into building and maintaining these things.

Data like this are available in many U.S. states as well as other countries. Other organizations could use this model to build something that would be of interest to their local users. Pension problems are different in other places, and the Illinois Public Pension Database might be a starting place for other organizations to start to create something of interest to their audience. 

This project also serves as a good reference for the BGA team, as well as reporters at newsrooms throughout the state of Illinois. Anytime they are reporting on a figure collecting a pension, they can pull up years of data instantly to add context and character to their reporting project.

Project links:

pensions.bettergov.org

pensions.bettergov.org/user-guide/