open_secrets.md

I have imported all the 2012 data individual contributions into a database and noticed that there is a microfilm number for all 2012 data except for 15J donations.

Query:

select count(*), transactiontype from donations where microfilm is null group by transactiontype;

| count(*) | transactiontype |
+----------+-----------------+
|       16 | 10J             |
|      571 | 11J             |
|   488034 | 15J             |
|        1 | 19J             |
+----------+-----------------+

So I started doing some digging. In the data dictionary it states that the individual contribution are sourced from the FEC table itcont, although I after downloading the FEC data for 2012, I noticed that 15J donations are not found in the itcont table but in the itoth table. Moreover the FECTransID in the OpenSecrets bulk data doesn't seem to match with the codes in the FEC data.

Here's an example of a result I'm trying to understand (the person was chosen at random, btw):

+-------+---------------------+-------------+--------------------+-----------+------------+--------+-----------------+-----------+-------------+
| cycle | fectransid          | contribid   | contrib            | recipid   | date       | amount | transactiontype | cmteid    | microfilm   |
+-------+---------------------+-------------+--------------------+-----------+------------+--------+-----------------+-----------+-------------+
| 2012  | 4020720131180975863 | k0001607366 | KRAFT, DANIEL A MR | C00003418 | 2012-09-29 |  30800 | 15J             | C00003418 | NULL        |
| 2012  | 4021520131183584151 | k0001607366 | KRAFT, DANIEL A MR | N00000286 | 2012-09-29 |   2500 | 15J             | C00431171 | NULL        |
| 2012  | 4021520131183584152 | k0001607366 | KRAFT, DANIEL A MR | N00000286 | 2012-09-29 |   2500 | 15J             | C00431171 | NULL        |
| 2012  | 4112120121172679068 | k0001607366 | KRAFT, DANIEL A MR | C00518282 | 2012-09-29 |  75000 | 15              | C00518282 | 12961053672 |
| 2012  | 4030920131185160017 | k0001607366 | KRAFT, DANIEL A MR | C00075820 | 2012-10-12 |  19600 | 15J             | C00075820 | NULL        |
| 2012  | 1010420130011322430 | k0001607366 | KRAFT, DANIEL A MR | C00027466 | 2012-10-15 |  19600 | 15J             | C00027466 | NULL        |
| 2012  | 4010920131177570193 | j1002384017 | KRAFT, DANIEL A MR | N00000286 | 2012-11-05 |    250 | 15              | C00431171 | 12963795217 |
| 2012  | 1011820130011546296 | j1002384017 | KRAFT, DANIEL A MR | C00027466 | 2012-11-06 |    250 | 15              | C00027466 | 12021111600 |
+-------+---------------------+-------------+--------------------+-----------+------------+--------+-----------------+-----------+-------------+

I can find those same donations in the FEC data in the itoth and icont tables.

grep -ri 'KRAFT, DANIEL'

itoth.txt:C00003418|A|12G||13940597445|15J|IND|KRAFT, DANIEL A. MR.|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|09292012|30800||201212GL11JFCCT01090|867755|X|INDIVIDUAL CONTRIBUTION MEMO OF ROMNEY VICTORY INC JFC TRANSFER|4041920131187633721
itoth.txt:C00431171|A|12G|P|14941685334|15J|IND|KRAFT, DANIEL A. MR.|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|09292012|2500||SA18.3019990.33.V010|933475|X|TRANSFER FROM ROMNEY VICTORY INC.|4071120141216926772
itoth.txt:C00431171|A|12G|G|14941685335|15J|IND|KRAFT, DANIEL A. MR.|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|09292012|2500||SA18.3019990.34.V010|933475|X|TRANSFER FROM ROMNEY VICTORY INC.|4071120141216926773
itoth.txt:C00027466|A|12G||12021032225|15J|IND|KRAFT, DANIEL A MR|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|10152012|19600||SA01021205126644|843446|X|TRANSFER [MEMO ITEM]|1060320130014136511
itoth.txt:C00075820|A|12G|P|201509239002776935|15J|IND|KRAFT, DANIEL A. MR.|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|10122012|19600||SA12.14832724|1026068|X|JFC ATTRIB: ROMNEY VICTORY INC.|4092420151254846327
itcont.txt:C00431171|A|30G|G|14961420605|15|IND|KRAFT, DANIEL A. MR.|BOTHELL|WA|98012|||11052012|250||SA17.4330323|933479|||4063020141216024598
itcont.txt:C00027466|A|30G||12021111600|15|IND|KRAFT, DANIEL A MR|BOTHELL|WA|98012|RETIRED|RETIRED|11062012|250||SA01161114124785|847224||CONTRIBUTION|1060320130014119142
itcont.txt:C00518282|A|Q3|P|13941706968|15|IND|KRAFT, DANIEL A. MR.|NEWTON|MA|02458|INTERNATIONAL FOREST PRODUCTS|PRESIDENT AND C.E.O.|09292012|75000||SA11.3019990|889722|||4102520131197969314

However, if you compare the fectransid, you noticed the are not the same in all cases -- why not?

Those 15J records in the itoth file have valid image numbers that point to the correct FEC document: http://docquery.fec.gov/cgi-bin/fecimg/?13940597445 -- why are they empty in the OpenSecrets data?

Am I missing something? I don't quite fully understand what a Memo/Transfer donation is (that's my own homework for the weekend), but any advice or help you have is very welcome!

thanks again for all the hard work you put into OpenSecrets!