fecjjeng
commented
4 years ago This is from a summary table (dsc_sched_b_aggregate_purpose_new). The purpose is a calculated field. Currently the "purpose" are categorized as followed:
IF TRANSACTION_TP in ('24G') THEN
v_PURPOSE := 'TRANSFERS';
ELSIF TRANSACTION_TP in ('24K') THEN
v_PURPOSE := 'CONTRIBUTIONS';
ELSIF TRANSACTION_TP in ('20C', '20F', '20G', '20R', '22J', '22K', '22L', '22U') THEN
v_PURPOSE := 'LOAN-REPAYMENTS';
ELSIF TRANSACTION_TP in ('17R', '20Y', '21Y', '22R', '22Y', '22Z', '23Y', '28L', '40T', '40Y', '40Z', '41T', '41Y', '41Z', '42T', '42Y', '42Z') THEN
v_PURPOSE := 'REFUNDS';
ELSE
cleaned := regexp_replace(PURPOSE, '[^a-zA-Z0-9]+', ' ');
IF REGEXP_LIKE (cleaned, 'salary|overhead|rent|postage|office supplies|office equipment|furniture|ballot access fees|petition drive|party fee|legal fee|accounting fee', 'i') then
v_PURPOSE := 'ADMINISTRATIVE';
ELSIF REGEXP_LIKE (cleaned, 'travel reimbursement|commercial carrier ticket|reimbursement for use of private vehicle|advance payments? for corporate aircraft|lodging|meal', 'i') then
v_PURPOSE := 'TRAVEL';
ELSIF REGEXP_LIKE (cleaned, 'direct mail|fundraising event|mailing list|consultant fee|call list|invitations including printing|catering|event space rental', 'i') then
v_PURPOSE := 'FUNDRAISING';
ELSIF REGEXP_LIKE (cleaned, 'general public advertising|radio|television|print|related production costs|media', 'i') then
v_PURPOSE := 'ADVERTISING';
ELSIF REGEXP_LIKE (cleaned, 'opinion poll', 'i') then
v_PURPOSE := 'POLLING';
ELSIF REGEXP_LIKE (cleaned, 'button|bumper sticker|brochure|mass mailing|pen|poster|balloon', 'i') then
v_PURPOSE := 'MATERIALS';
ELSIF REGEXP_LIKE (cleaned, 'candidate appearance|campaign rall(y|ies)|town meeting|phone bank|catering|get out the vote|canvassing|driving voters to polls', 'i') then
v_PURPOSE := 'EVENTS';
ELSIF REGEXP_LIKE (cleaned, 'contributions? to federal candidate|contributions? to federal political committee|donations? to nonfederal candidate|donations? to nonfederal committee', 'i') then
v_PURPOSE := 'CONTRIBUTIONS';
ELSE
v_PURPOSE := 'OTHER';
END IF;
END IF;
From @PaulClark2 This committee only has eight purposes. When I run a query against the DB I get over 800.
https://api.open.fec.gov/v1/schedules/schedule_b/by_purpose/?committee_id=C00658567&sort_nulls_last=false&page=1&cycle=2018&sort_null_only=false&api_key=DEMO_KEY&per_page=20&sort_hide_null=false
select purpose, sum(transaction_amt) from DISCLOSURE.F_ITEM_RECEIPT_OR_EXP where rpt_yr in (2017, 2018) and cmte_id = 'C00658567' and sched_tp_cd = 'SB' and memo_cd is null group by purpose;