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.

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

To get totals by district:

    
        select district, candidate, sum(contribution_amount) as sum
        from house_cand_cont
        group by district, candidate
        order by sum 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 fish(at)coloradomediaproject.com