Legislators, auditors and business groups have tried for years to answer the question: Where does the state of New Mexico spend its money when it pays independent contractors for goods and services?
Does the bulk of the money stay inside New Mexico? Or do most of the dollars leave the state?
Searchlight New Mexico found out. It took seven months, and it wasn’t easy.
The state makes purchasing records available on the New Mexico Sunshine Portal. Those records do not contain detailed information on the vendors who supply goods and services under various kinds of contracts and purchase agreements — the kind of information that can answer the questions we wanted to ask.
Yet that information is a matter of public interest. Because it does not exist — despite legislative efforts — we decided to create it and share it with the public.
Beginning in June 2017, we filed public records requests with four different state departments and agencies, seeking access to records from the databases that feed into the Sunshine Portal and that contain vendor information from purchase orders. Our goal was to receive data that allowed us to connect the state’s vendors with the goods and services they sold to New Mexico over a five-year period, with enough detail to analyze the state’s purchasing habits.
In response to our public records requests, in early December 2017, New Mexico publicly released a table of vendors who were registered with the state at the time. According to the state, that list is to be updated quarterly. As of this article’s publication, it has not once been updated.
The table contained over 38,000 rows, each representing one active vendor. For each one, the table included a unique ID number, and the vendor’s name and full address, as we had requested.
That was a start, but we wanted to find out a good deal more about the vendors with whom New Mexico is doing business. Our questions included:
- Who are the state’s largest vendors and where are they located?
- Which state departments and agencies make the most purchases, and for what?
- Which arms of the state do the most business within New Mexico, and which tend to look outside?
- Statewide, how much purchasing happens inside our border and how much elsewhere?
- Where is New Mexico’s vendor spending concentrated?
To answer those questions, we had to tie the state’s list of active vendors to its purchasing records. Due in part to our series of open records requests, the vendors data release contained the unique ID for each one — a number that matches the vendor’s ID in the purchasing tables on the Sunshine Portal. This allowed us to connect rows in the two groups of tables — to say that Company X in the purchases table matches Company X in the active vendors table, so the disparate data about Company X from both tables could then be combined for analysis.
We first downloaded the complete purchasing records tables for each fiscal year from 2013 through 2017, which together totaled more than 41 million data points. We then joined them into a single table and merged that with the vendors list, performed additional data tidying and formatting, and loaded the results into our data portal, which is free and open to the public.
There were more problems. First, the state’s purchasing tables identified the department or agency that made a purchase using numeric codes. This is standard practice in database administration, but it makes things difficult for humans to understand. So we separately requested a data dictionary — a file that translates a coded column in a data table into plain English.
Second, we wanted to eliminate cases in which a vendor was actually another state department or agency. For example, when the state distributes money to schools or local governments, or one agency charges another for services like IT work, the table recorded the party that supplied the service or the money as a vendor. For our reporting, we were not interested in payments that represented New Mexico essentially shifting money internally.
Moreover, deciding whether a national company with a local office counts as a resident vendor can be fraught. For this analysis, we counted as “in-state” dollars those paid by New Mexico to a person or company with a New Mexico address.
Finally, we filtered out instances in which the data provided by New Mexico were incomplete — the main problem was empty address fields for vendors.
That means the data in our portal are as close to comprehensive as possible, while the data underlying our reporting on New Mexico’s vendors are a subset focused on payments to vendors outside state government — those that have addresses.
You can review our code here and explore the raw data here.
John R. Roby can be reached at johnroby@searchlightnm.com.