Research: some contributions not being marked as 'is_individual'

Summary

Our is_individual function does not correctly identify partnership lump-sums reported without partnership memo items as individual contributions.

Expected Behavior

We should correctly identify all individual contributions.

Actual Behavior

For example, only 2 of 16 contributions from this entity, American Ethane Company LLC, are correctly identified as individual contributions. All 16 contributions are individual contributions.

Compare results from individual contributors search https://www.fec.gov/data/receipts/individual-contributions/?contributor_name=American+Ethane&two_year_transaction_period=2018 and all receipts search https://www.fec.gov/data/receipts/?data_type=processed&contributor_name=American+Ethane&two_year_transaction_period=2018

select is_individual, cmte_id, contbr_nm,  contb_receipt_amt, contb_receipt_dt::timestamp::date, receipt_tp, receipt_desc, image_num, filing_form, line_num
from disclosure.fec_fitem_sched_a 
where rpt_yr in (2017, 2018) 
and cmte_id in ('C00543983', 'C00568162', 'C00558486', 'C00608398', 'C00632679', 'C00634774', 'C00608695')
and contbr_nm like '%AMERICAN ETHANE%';

is_individual	cmte_id	contbr_nm	contb_receipt_amt	contb_receipt_dt	receipt_tp	receipt_desc	image_num	filing_form	line_num
TRUE	C00632679	AMERICAN ETHANE COMPANY LLC	15000	4/16/2018	10		201807139115433865	F3X	11AI
TRUE	C00543983	AMERICAN ETHANE COMPANY LLC	-600	3/31/2018			201804200200346244	F3	11AI
FALSE	C00543983	AMERICAN ETHANE COMPANY LLC	1000	3/31/2018			201804200200346244	F3	11AI
FALSE	C00543983	AMERICAN ETHANE COMPANY LLC	600	3/31/2018			201804200200346245	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	2700	4/25/2018		PARTNERSHIP	201807109115289534	F3	11AI
FALSE	C00634774	AMERICAN ETHANE COMPANY LLC	5000	4/30/2018		PARTNERSHIP	201807139115402370	F3X	11AI
FALSE	C00608398	AMERICAN ETHANE COMPANY, LLC	2700	4/30/2018			201807180200573399	F3	11AI
FALSE	C00608398	AMERICAN ETHANE COMPANY, LLC	2700	4/30/2018			201807180200573398	F3	11AI
FALSE	C00608398	AMERICAN ETHANE COMPANY , LLC	2700	4/30/2018			201807180200573400	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	700	4/25/2018		PARTNERSHIP	201807099115254765	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	700	4/25/2018		PARTNERSHIP	201807109115289534	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	2700	4/25/2018		PARTNERSHIP	201807099115254765	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	2700	4/25/2018		PARTNERSHIP	201807099115254765	F3	11AI
FALSE	C00608695	AMERICAN ETHANE COMPANY, LLC	2700	4/25/2018		PARTNERSHIP	201807109115289534	F3	11AI
FALSE	C00558486	AMERICAN ETHANE COMPANY LLC	5400	5/10/2018		REFUNDED ON 6/1	201808309121545905	F3	11AI
FALSE	C00568162	AMERICAN ETHANE COMPANY LLC	10000	7/19/2018		CONTRIBUTION	201810159124938824	F3X	11AI

Current coding procedures do not categorize the partnership contribution even when there are no partner memo items. Our is_individual function looks for transaction types 10, 15, 15E, 15J, 30, 30T, 31, 31T, 32, 10J, 11, 11J, 30J, 31J, 32T and 32J. In addition it looks for transactions less than $200 with null memo code on Forms 3 and 3X Lines 11AI and 12 and Form 3P Lines 17, 17A and 18.

One suggestion is looking for LLC or partnership in the contributor name. This strategy would capture both partnership lump-sums reported with partnership memo items and partnership lump-sums reported without partnership memo items. I'm pretty sure this approach would inflate our individual contributions aggregations (by state and by size).

is_individual function:

IF
        (
            -- is_coded_individual(TRANSACTION_TP)
            coalesce(TRANSACTION_TP, ' ') in ('10', '15', '15E', '15J', '30', '30T', '31', '31T', '32', '10J', '11', '11J', '30J', '31J', '32T', '32J')
            OR
            -- is_inferred_individual(TRANSACTION_AMT, LINE_NUM, MEMO_CD, MEMO_TEXT, OTHER_ID, CMTE_ID)
            (
                TRANSACTION_AMT < 200
                and coalesce(LINE_NUM, ' ') in ('11AI', '12', '17', '17A', '18')
                and MEMO_CD is NULL
            )

        ) THEN

            v_IS_IND := 1;

 END IF;

To do

[ ] Make sure we're not doublecounting contributions from partnerships that are also attributed to individuals via memo entries (or similar type transactions).
[ ] Fix logic in both Oracle and Post Gres
[ ] Recalculate aggregates
[ ] Repopulate All Schedules A (time consuming!)
[ ] Consider if we need to split this into other tickets.

fecgov / openFEC