Data questions (and some answers)

[evz]

Data questions (and some answers)

[evz]

• Do the older files change? So, for instance, will the candidate transactions file from 2010 ever change again?

[sandrafish]

Yes, some older files sometimes do change, but it's likely relatively rare. Here's one example. I guess she didn't go all the way back to 2010, but to 2012 url

[evz]

It does seem like there are unique identifiers for candidates, filings and offices that can be sought. There also seems to be a concept of something like election cycle which is how the reports are broken down.

So, if for instance you look at the query parameters for the URL that points to all of the reports for Sandra Martinez since the last election she ran in it looks like:

CandidateReportH.aspx?es=17&ot=1&o=39&c=1561&p=0&d=&ct=0&fn=Susana&ln=Martinez

• es refers to the Election Year. For some reason 17 is 2014, 20 is 2016, 13 is 2010, etc.

• ot refers to the Office Type. Those you can extract from the "Office Type" select on the main search page (exposed by clicking "Advanced Search Options"). They are:

  "6": County Offices,
  "3": Judicial,
  "2": Legislative,
  "5": Public Education Commission,
  "4": Public Regulation Commission,
  "7": School Board,
  "1": Statewide,

• o refers to the Office. These can also be derived from the select on the main search form:

  "43": "Attorney General",
  "48": Commissioner Of Public Lands,
  "28": County Assessor,
  "24": County Clerk,
  "21": County Commissioner,
  "19": County Council,
  "18": County Sheriff,
  "44": County Surveyor,
  "30": County Treasurer,
  "32": District Attorney,
  "46": District Judge,
  "36": District Judge - Retention,
  "42": District Magistrate,
  "50": Exploratory Campaign,
  "35": Fifth Judicial District,
  "16": Fourth Judicial District,
  "39": Governor Of New Mexico,
  "38": Judge Of The Court Of Appeals,
  "22": Judge Of The Court Of Appeals - Retention,
  "14": Justice Of The Supreme Court,
  "37": Justice Of The Supreme Court - Retention,
  "34": Lieutenant Governor,
  "49": Magistrate Judge,
  "15": Metropolitan Court Judge,
  "40": Municipal Judge,
  "25": Ninth Judicial District,
  "31": Probate Judge,
  "41": Public Education Commission,
  "13": Public Regulation Commissioner,
  "53": Public Regulation Commissioner - Voter Action Act,
  "54": School Board Member,
  "29": Second Judicial District,
  "17": Secretary Of State,
  "26": Sixth Judicial District,
  "47": State Auditor,
  "20": State Board Of Education,
  "27": State Representative,
  "23": State Senator,
  "33": State Treasurer,
  "12": Tenth Judicial District,

• c refers to the Candidate. I'm pretty sure this is the unique ID we'll want for candidates.

The rest of the query parameters seem to just be page state things.

[evz]

On the individual report detail pages, there are similar query parameters:

CandidateReportContribution.aspx?es=13&ot=1&o=39&c=1561&r=32415&p=0&d=ALL&ct=0&fn=&ln=

The one to make note of here is r which seems to be a unique ID for the report.

[evz]

Similarly with PACs, there are some things to be derived from the query parameters. On a main page for a PAC, you can get their ID from the p parameter:

PACReport.aspx?se=0&p=5627

On the Contributions & Expenditures detail pages for a given PAC, it seems like the rh might be the unique ID for the filing:

PACcontributions.aspx?se=0&p=7255&rh=49593

PACexpenditures.aspx?se=0&p=7255&rh=49593

The big difference is if you leave the rh parameter off of the Contributions URL, you seem to get all contributions ever given to the PAC. If you leave it off of the Expenditures URL, it says it can't find anything. So ¯\_(ツ)_/¯

[evz]

OK so here are some fun numbers that I got from just loading all the data into PostgreSQL and running some queries (no attempt at rooting out duplicate entries, etc so there are probably problems with these numbers). If @sandrafish & team want to take a look at these and see if they jive (in a general sense) with your understanding of what's going on in New Mexico, that would be a great sanity check.

Top 20 PACs by amount spent (2010-current):

                                 name                                  |      sum      
-----------------------------------------------------------------------+---------------
 Patriot Majority New Mexico                                           | $3,600,890.60
 SUSANA PAC                                                            | $2,731,587.53
 Reform New Mexico Now                                                 | $2,427,250.00
 ActBlue New Mexico                                                    | $1,834,823.74
 Advance New Mexico Now                                                | $1,787,429.51
 Republican Campaign Committee of New Mexico                           | $1,206,891.49
 Democratic Party of New Mexico                                        |   $982,571.87
 Committee on Individual Responsibility                                |   $875,620.91
 CVNM Verde Voters Fund                                                |   $813,693.77
 REALTORS ASSOC OF NM PAC                                              |   $739,214.79
 RGA New Mexico PAC                                                    |   $700,000.00
 Ken Martinez Leadership Fund                                          |   $662,313.69
 Go Bonds For Education                                                |   $628,649.76
 Property Casualty Insurers Association of America Political Account I |   $510,032.69
 House Democratic Campaign Committee                                   |   $466,109.08
 Plumbers & Steamfitters, UA Local Union No. 412                       |   $423,404.48
 New Mexico Defense Fund                                               |   $365,295.02
 Southwest Regional Council of Carpenters NM Pac                       |   $358,097.50
 New Mexico Democratic Legislative Campaign Committee                  |   $352,171.83
 AFT New Mexico Solidarity Fund                                        |   $346,592.41

Top 20 PACs by amount received (2010-current):

                                 name                                  |      sum      
-----------------------------------------------------------------------+---------------
 Patriot Majority New Mexico                                           | $3,617,025.72
 SUSANA PAC                                                            | $3,091,639.77
 Reform New Mexico Now                                                 | $2,431,293.40
 Advance New Mexico Now                                                | $2,274,250.00
 ActBlue New Mexico                                                    | $1,871,975.86
 Republican Campaign Committee of New Mexico                           | $1,234,084.10
 Committee on Individual Responsibility                                | $1,057,298.92
 Democratic Party of New Mexico                                        |   $994,063.90
 CVNM Verde Voters Fund                                                |   $874,845.42
 Go Bonds For Education                                                |   $778,207.00
 REALTORS ASSOC OF NM PAC                                              |   $766,964.46
 RGA New Mexico PAC                                                    |   $700,000.00
 Ken Martinez Leadership Fund                                          |   $673,336.31
 Property Casualty Insurers Association of America Political Account I |   $585,924.77
 House Democratic Campaign Committee                                   |   $520,909.46
 Plumbers & Steamfitters, UA Local Union No. 412                       |   $506,932.41
 Don Tripp Speaker PAC                                                 |   $393,275.97
 New Mexico Defense Fund                                               |   $389,819.03
 PNM Responsible Citizens Group                                        |   $389,431.61
 DLCC New Mexico Committee                                             |   $351,820.39

Top 20 individual contributors by amount given (2010-current):

     first_name     |      last_name       |     sum     
--------------------+----------------------+-------------
 Robert             | Perry                | $356,465.00
 Lisa               | Curtis               | $347,209.40
 Alan               | Webber               | $325,673.00
 Sheldon            | Adelson              | $250,000.00
 Foster             | Friess               | $245,000.00
 Stanley            | Harper               | $218,400.00
 Mark               | Murphy               | $186,600.00
 Marcus             | Hiles                | $135,400.00
 John               | Richardson           | $134,930.00
 George             | Soros                | $112,200.00
 Anne               | Marion               | $110,000.00
 Doylene            | Perry                | $110,000.00
 Sallie             | Bingham              | $105,950.00
 Paul               | Foster               | $104,211.00
 B.                 | Hughes               | $100,000.00
 Barrett  & Polly   | Toan & O'Brien       | $100,000.00
 James              | Collie               |  $94,319.44
 Paul               | Schmidt              |  $94,100.00
 Turner             | Branch               |  $90,775.00
 Secretary of State | Public Election Fund |  $85,748.00

Top 20 "business" contributors by amount given (2010-current):

                company_name                 |      sum      
---------------------------------------------+---------------
 AFSCME                                      | $1,833,552.45
 Republican State Leadership Committee       | $1,413,750.00
 RGA New Mexico 2010 PAC                     | $1,311,963.78
 NEA Advocacy Fund                           |   $986,000.00
 Public Election Fund                        |   $796,512.00
 Republican Governors Association            |   $712,264.50
 Union Members                               |   $464,489.15
 Devon Energy                                |   $400,500.00
 State of New Mexico                         |   $399,445.00
 Democratic Party of New Mexico              |   $348,163.42
 COIR Committee on Individual Responsibility |   $337,000.00
 AFT Solidarity 527                          |   $332,111.91
 American Federation of Teachers             |   $323,193.06
 Democratic Legislative Campaign Committee   |   $318,230.85
 Ken Martinez Leadership Fund                |   $311,295.60
 New Mexico RPAC                             |   $272,030.00
 Republican Attorneys General Association    |   $265,200.00
 Democratic Governors Association/New Mexico |   $250,000.00
 Service Employees International Union       |   $236,000.00
 NEA-NM Members                              |   $234,533.88

Top 20 Candidates by amount received (2010-current):

 first_name |    last_name    |      sum       
------------+-----------------+----------------
 Susana     | Martinez        | $17,007,194.26
 Diane      | Denish          |  $5,207,743.31
 Gary       | King            |  $2,289,864.32
 Hector     | Balderas        |  $1,487,034.08
 Alan       | Webber          |  $1,057,662.19
 John       | Sanchez         |    $965,475.42
 Maggie     | Toulouse Oliver |    $933,570.46
 Nate       | Gentry          |    $900,141.50
 Tim        | Keller          |    $690,782.98
 Matthew    | Chandler        |    $628,222.38
 Dianna     | Duran           |    $586,493.94
 Stephanie  | Garcia Richard  |    $526,218.29
 Ray        | Powell          |    $524,189.27
 Michael    | Sanchez         |    $522,705.46
 Brian      | Colon           |    $507,759.28
 Doug       | Turner          |    $500,883.07
 Lawrence   | Rael            |    $473,707.43
 Aubrey     | Dunn            |    $456,480.35
 Pete       | Domenici        |    $436,738.82
 J. Miles   | Hanisee         |    $435,938.00

Top 20 candidates by amount spent (2010-current):

 first_name |    last_name    |      sum       
------------+-----------------+----------------
 Susana     | Martinez        | $19,664,480.90
 Diane      | Denish          |  $8,832,242.35
 Gary       | King            |  $3,122,517.15
 Allen      | Weh             |  $2,089,388.32
 Hector     | Balderas        |  $1,207,092.79
 Alan       | Webber          |  $1,093,434.55
 Lawrence   | Rael            |    $818,757.89
 Maggie     | Toulouse Oliver |    $787,586.33
 Brian      | Colon           |    $734,336.09
 Tim        | Keller          |    $727,843.36
 John       | Sanchez         |    $725,432.37
 Doug       | Turner          |    $651,395.43
 Nate       | Gentry          |    $649,829.78
 Pete       | Domenici        |    $594,647.86
 Dianna     | Duran           |    $557,086.56
 Al         | Park            |    $519,984.10
 Ray        | Powell          |    $518,517.33
 Matthew    | Chandler        |    $517,343.01
 Timothy    | Jennings        |    $485,356.13
 Michael    | Sanchez         |    $473,631.63

[evz]

A few more random thoughts:

• One thing that we never really figured out in the Illinois data (but it would be cool to get for New Mexico if we can) is a way to track an "expenditure" from one committee that is, in reality, a "contribution" to another committee. They did denote when something was a "Transfer-in" or a "Transfer-out" but there were no identifiers for the committee on the other side of the equation so it was impossible to reliably recreate the double-entry bookkeeping you'd expect to see. I see no evidence of this kind of accountability latent within the system that the website is built upon, but it'd be cool to ask just in case there is something there.
• Getting information besides name for candidates and committees is I think going to be crucial. The kinds of things we'll want to know about committees include: supported candidates, contact info (address, phone, etc) and officers (even if it's just names; in Illinois we found that many candidates/office holders were also officers of powerful committees which, in effect, gives them that much more power). For candidates it would be good to know what office they are seeking, whether they are incumbent or a challenger, when they sought that office, and then all the regular contact info.
• To get any kind of picture of the current amount of money controlled by any committee or candidate, we'll need to get some kind of balance summary for a given filing. It looks like this info exists someplace because those PDFs that you download seem to be dynamically generated.
• Bonus Points Section Any kind of disambiguation of people that we can get will be useful. They already seem to have a concept of a unique ID for a person no matter which office they are running for. So, for instance Betty Gonzales-Martinez for County Sheriff seems to have the same ID as Betty Gonzales-Martinez for Magistrate Judge. I'm not sure if that's a mistake or if that's the same person but it'd be cool if it was the same person. Also, if they do store info about committee officers, having IDs that link together candidates who are also hold those positions would be nifty.

[sandrafish]

So i'm took a quick look at these PACs in a couple of different ways.

If you go here and download their results as a spreadsheet, i get this for top 20 PACS contributions:

And this for top 20 PACs spending:

When i download all the data for PACs from here, i get vastly different results raised:

And spent:

And i took a specific look at the Republican Campaign Committee, coming up with 2,750,985.66 raised and 2,694,747.16 spent...

so something is off for them and a couple of others there... but the full download was way off - and appears to include duplicates of some reports potentially.

Then there's Act Blue, which i despise, because they are a conduit for individual donations and have the potential for tons of double-reporting.

One thing i'm wondering about is whether we could filter these lists by cycle? That would be super helpful - as would filtering individual candidates by cycles.

On your second list of things....

Yes, we should ask on our call tomorrow if they identify transfers, but i don't think that they do...

When they file to create a committee it's a paper thing - i've got a few PDF examples... And yes, you're right that some of these PACs are created by lawmakers... but i fear this info is all on paper...

You are correct that the cash on hand is entered electronically - and hoping we can get them to include this somehow...

i don't think they store info about committee offices - we should ask... but it also appears to me that they keep the same id for candidates when folks move on to run for higher office.

Let me know what other questions you have.

[evz]

I suspect a lot of these inconsistencies will be solved once we have unique IDs and figure out how to identify and handle corrected filings.

[derekeder]

Ok, so it looks like these are the main questions to ask the New Mexico Secretary of State:

1. In the data download, can you provide unique IDs for candidates, committees, reports and donations?
2. Can you provide a full list of offices, office types and election years as well as their corresponding IDs in the system?
3. Can you confirm how corrected filings are handled?
4. Can additional data be provided about committees, including supported candidates, contact info (address, phone, etc) and officers?
5. Can we get digital versions of committee quarterly reports?
6. How are transfers between committees handled?

[derekeder]

Additionally, we will check on the status of these previous requests:

1. Add the candidate offices to the data download
2. Make the data download available in a place that we can access programmatically (like an FTP folder)

[sandrafish]

sounds good…
give a call…

sandra fish
data journalist, New Mexico In Depth http://nmindepth.com/
president, Journalism & Women Symposium http://www.jaws.org/
http://www.linkedin.com/in/sandrafish http://www.linkedin.com/in/sandrafish
twitter.com/fishnette http://twitter.com/fishnette
Boulder, CO
303-589-0584

On Jul 7, 2016, at 9:58 AM, Derek Eder [email protected] wrote:

Ok, so it looks like these are the main questions to ask the New Mexico Secretary of State:

In the data download, can you provide unique IDs for candidates, committees, reports and donations?
Can you provide a full list of offices, office types and election years as well as their corresponding IDs in the system?
Can you confirm how corrected filings are handled?
Can additional data be provided about committees, including supported candidates, contact info (address, phone, etc) and officers?
Can we get digital versions of committee quarterly reports?
How are transfers between committees handled?

[sandrafish]

Here's a recent example of No. 6, i think. in really tiny font.

[derekeder]

That was a good call. Sounds like Kari is agreeable to our requests. Here's the revised list. @sandrafish let me know if this looks good and I'll send it over to Kari.

---

1. Make the data download available in a place that we can access programmatically (like an FTP folder)
2. In the data download, can you provide unique IDs for candidates, PACs, reports, donations and any other tables?
3. In the data download, can you provide separately, a full list of offices, office types, election seasons, contribution expenditure types, as well as their corresponding IDs in the system?
4. In the data download, can you add the active vs inactive status for candidates and PACs?
5. In the data download, can additional info be provided about committees including contact info (address, phone, etc) and officers?

Alternatively, what we'd like ideally is a full export of every table in your system pertaining to campaign finance. This would mean a separate Excel or CSV file for each table. Pending information that has not been published yet could be filtered out. Between this and the list of requests above, it would likely be much cheaper and faster for the vendor to implement, leaving you with more budget to spend on other enhancements.

[sandrafish]

Yes, that was a good call - one of the factors here is that i think they'll push to get this done soon, given that a new SoS will be elected in November.

i'd move No. 1 to No. 4 and move everything else up. Tho have you tried accessing the download programmatically? (i have not, but i'm usually just looking for specific portions too)

In No. 3, 4 and 5, i wouldn't begin with saying in the data download - i think these are static tables that we'd want to receive periodically - there won't be much change here on out for candidates, but there could be additional PACs hit the scene.

You might also suggest that we'd be willing to talk with her and the vendor at some point if that would be helpful.

[derekeder]

Ok will update this and send it over.

Keeping No 1 at the top is important, since it will allow us to update our site automatically. The alternative is a scraper, which is more fragile and would require additional code.

[derekeder]

Closing this - we've gotten what we need.