You wouldn’t know it from reading this post on how Colorado gubernatorial candidates Bob Beauprez (R) and Bill Ritter (D) are raising the bulk of their campaign cash from large donors, but the analysis of state campaign contribution data took a long time to do.
This is worth noting not just so that I can complain-although I’m always happy to do that. It’s worth noting because if it takes me, somebody who has tracked campaign contributions, albeit at the federal level, for many years, that long to figure out, then woe to the ordinary interested voter. (I’m no computer whiz, believe me, but thanks to my work I’ve learned some basics that most people have no occasion to know.)
It’s not always enough to make campaign contribution data available. It’s also important to make it digestible. While simple totals of how much a candidate has raised are valuable, it is in the slicing and dicing of the data that patterns emerge that tell the stories behind the story. At the federal level, the U.S. Federal Election Commission posts some analyses of federal campaign contributions, such as this one on congressional fundraising. National advocacy and research groups, most notably the Center for Responsive Politics, as well as outfits such as the Campaign Finance Institute, Common Cause, Public Citizen, and Public Campaign (my employer) also add to the pile of sliced and diced data. This way the public, can learn how much money the oil and gas industry has been funneling toward members of Congress, or how much cash lobbyists gave to then-House majority leader Tom DeLay (R-TX).
But at the state level, there are fewer resources. The Colorado Secretary of State office makes the data available by download here, but doesn’t provide much analysis. The Institute on Money in State Politics does good analysis, but given that it has 50 states to cover, it takes awhile for each state’s data to be made up to date.
Here’s what was involved when I did the analysis of size of contributions to the governors’ race. Believe me, I won’t go into every nitty gritty step. You’d fall asleep. Indeed you may fall asleep even with this abridged version.
I should also say, before I plunge in, that Kris Reynolds, the official who handles questions about electronic download, was patient and helpful with me at each step of the way when I encountered problems, even when I had very annoying questions.
First step. I download seven files containing all the 2005 and 2006 campaign contribution data to my laptop. All together these files contain nearly 128,000 lines of information about who gave how much money to whom on what date.
Second step. I try to convert these files into Microsoft Excel sheets. I can’t read all the columns. I call Kris. She helps me figure out what I’m doing wrong.
Third step. One by one, I import each of the Excel sheets into Microsoft Access. I get some “type conversion” errors, which happens when there is a data type mismatch between two programs, for example, when a date is transferred to a non-date field. I try to fix these, can’t figure out how to do it, and then finally decide to give up for the moment, since the errors aren’t affecting the analysis I want to do of size of contributions.
Fourth step. I combine all the sheets into one table in my Microsoft Access database. Then I do a simple query to pull out the records for Bill Ritter and Bob Beauprez.
Fifth step. I total the amounts in the query on the gubernatorial candidates and double check them against the totals that are listed separately on the Colorado Secretary of State’s office here. But the totals in my Access file are far smaller than what were reported. I call Kris. Kris spends some time double checking the posted data files and says she comes up with the correct numbers. As we talk it through, I realize that my problem was that I based the query for pulling out the gubernatorial records on the wrong field. Rather than using the candidate name, I should have used the committee ID number. When I redo the query, the numbers match.
Sixth step. I pull in a query that I’ve used in the past in other Access databases that will help me automatically generate contribution amounts by range of contribution. I keep getting error messages. It takes a good hour before I figure out what’s wrong and fix it.
Seventh step. I finally have my analysis of how much money Ritter and Beauprez are raising from donors giving different amounts. I am now able to create some pie graphs based on the data and write my post.
All told, the data analysis took me at least six hours over several days. I’m sure next time I work with the Colorado data, the process will go more quickly, as I’ve learned more about the system.
It is a wonderful thing that the office of the Colorado Secretary of State makes campaign finance contribution data available to the public. But it takes more than that for people to make sense of it.
Comments are closed.