Using SQL

FollowtheMoneyCO is a joint project of the media collaborative COLab and the Colorado Sun, funded by the Colorado Media Project and the Colorado Sun.

The project aims to make Colorado campaign finance data more accessible to journalists and the public using downloadable data from the Colorado Secretary of State’s TRACER.

Datasette, software designed by Stanford University Knight fellow Simon Willison, is the foundation for this site. It uses SQLite, database software that uses structured query language to interviewthe data.

Here's how it works. First select a column such as city.

Then select an operator. The = will match exactly, the != will get everything that doesn't match exactly. Contains, ends with or starts with may be the most valuable operaters, especially with names. Use the more than, less than, etc. on contribution amounts.

Select a search term in the third column. This column is case sensitive, so searching for denver will return nothing. While Denver will return results.

You may search by multiple columns, such as committee names and amounts over 10,000.

You may also create custom queries. Here's one that selects contributors from the education candidate contribution table, the sum of contributions for that donor, ordered by the sum descending. Though keep in mind names aren't standardized here

    
        select contributor, sum(contribution_amount) as total
        from house_cont
        group by contributor
        order by total desc

To get totals by district:

    
        select District, candidate_name, Party,sum(contribution_amount) as total
        from house_cont
        group by District, candidate_name, Party
        order by total desc
    

This is a good tutorial on SQL.

Search results may be downloaded as a CSV (best for using a spreadsheet) or json file. If you're saving as a CSV, it's best to go to the "advanced" export and click on "download file."

Questions? Suggestions? email fishnette(at)gmail.com