Research how we can update labeling or categorizing committees in the database

[JonellaCulmer]

What we're after: As a data researcher, I want to be able to know all the pertinent specific information for the committee type so that I know what kind of committee I am looking at.

Background: Rethink how we are labeling or categorizing committees on the website. Would also like the committee descriptions seen at the top of profile pages to be clearer and match what we provide on other parts of the site. PAC with non-contribution account is accurate, but most people call it hybrid or Carey PAC. We should try (when possible) to use common names for things.

Example from recent snapshot work: Organization type (see rows 12-17) determines "category" when only committee types N, Q and C "care about" organization type. If organization type is included with any other committee type it is a data or filing error.

Previous slack conversations for additional context:

• https://fecgov.slack.com/archives/C3X3K6EVA/p1626350870247300
• https://fecgov.slack.com/archives/C3X3K6EVA/p1626284898240700?thread_ts=1626120442.218100&cid=C3X3K6EVA

Completion criteria:

• [ ] Use the committee labels in the comments below to create an underlying MV database change
• [ ] Create API output fields for the newcommittee_label parameter
• [ ] Put in follow-up issue to CMS to use API logic instead of complicated view logic for committee profile pages

[PaulClark2]

🔒 spreadsheet 🔒 version of this and the next comment

Committee label	Committee type	Committee designation	Organization type
House - principal campaign committee	H	P	n/a
House - other authorized campaign committee	H	A	n/a
House	H	U, B, D, NULL	n/a
Senate - principal campaign committee	S	P	n/a
Senate - other authorized campaign committee	S	A	n/a
Senate	S	U, B, D, NULL	n/a
President - principal campaign committee	P	P	n/a
President - other authorized campaign committee	P	A	n/a
President	P	U, B, D, NULL	n/a
Delegate	D	A, B, D, P, U, NULL	n/a
Corporation PAC - nonqualifed	N	A, B, P, U, NULL	C
Labor PAC - nonqualifed	N	A, B, P, U, NULL	L
Trade PAC - nonqualifed	N	A, B, P, U, NULL	T
Membership PAC - nonqualifed	N	A, B, P, U, NULL	M
Cooperative PAC - nonqualifed	N	A, B, P, U, NULL	V
Corporation without stock PAC - nonqualifed	N	A, B, P, U, NULL	W
PAC - nonqualifed	N	A, B, P, U, NULL	n/a
Corporation PAC - qualified	Q	A, B, P, U, NULL	C
Labor PAC - qualified	Q	A, B, P, U, NULL	L
Trade PAC - qualified	Q	A, B, P, U, NULL	T
Membership PAC - qualified	Q	A, B, P, U, NULL	M
Cooperative PAC - qualified	Q	A, B, P, U, NULL	V
Corporation without stock PAC - qualified	Q	A, B, P, U, NULL	W
PAC - qualified	Q	A, B, P, U, NULL	n/a
Super PAC (Independent Expenditure-Only)	O	A, B, D, P, U, NULL	n/a
Single candidate independent expenditure	U	A, B, D, P, U, NULL	n/a
Hybrid PAC (with non-Contribution Account) - nonqualified	V	A, B, D, P, U, NULL	n/a
Hybrid PAC (with non-Contribution Account) - qualified	W	A, B, D, P, U, NULL	n/a
Leadership PAC - nonqualifed	N	D	n/a
Leadership PAC - qualified	Q	D	n/a
Party - nonqualifed	X	A, B, D, P, U, NULL	n/a
Party - qualified	Y	A, B, D, P, U, NULL	n/a
Party nonfederal account	Z	A, B, D, P, U, NULL	n/a
Joint fundraising	C, D, E, H, I, N, O, P, Q, S, U, V, W, X, Y, Z	J	n/a
Independent expenditures reported by persons other than a political committee	I	A, B, D, P, U, NULL	n/a
Communication cost - Corporation	C	A, B, D, P, U, NULL	C
Communication cost - Labor Organization	C	A, B, D, P, U, NULL	L
Communication cost - Trade Association	C	A, B, D, P, U, NULL	T
Communication cost - Membership Organization	C	A, B, D, P, U, NULL	M
Communication cost - Cooperative	C	A, B, D, P, U, NULL	V
Communication cost - Corporation without Capital Stock	C	A, B, D, P, U, NULL	W
Communication cost	C	A, B, D, P, U, NULL	n/a
Electioneering Communications	E	A, B, D, P, U, NULL	n/a
Unknown	NULL	n/a	n/a

[PaulClark2]

select 
cmte_id, 
  (CASE 
      WHEN cm.cmte_tp = 'H' and cmte_dsgn = 'P' THEN 'House - principal campaign committee'
      WHEN cm.cmte_tp = 'H' and cmte_dsgn = 'A' THEN 'House - other authorized campaign committee'                       
      WHEN cm.cmte_tp = 'H' and NVL(cmte_dsgn, 'F') IN ('B', 'D', 'U', 'F') THEN 'House'

      WHEN cm.cmte_tp = 'S' and cmte_dsgn = 'P' THEN 'Senate - principal campaign committee'
      WHEN cm.cmte_tp = 'S' and cmte_dsgn = 'A' THEN 'Senate - other authorized campaign committee'                       
      WHEN cm.cmte_tp = 'S' and NVL(cmte_dsgn, 'F') IN ('B', 'D', 'U', 'F') THEN 'Senate'

      WHEN cm.cmte_tp = 'P' and cmte_dsgn = 'P' THEN 'President - principal campaign committee'
      WHEN cm.cmte_tp = 'P' and cmte_dsgn = 'A' THEN 'President - other authorized campaign committee'                       
      WHEN cm.cmte_tp = 'P' and NVL(cmte_dsgn, 'F') IN ('B', 'D', 'U', 'F') THEN 'President'

      WHEN cm.cmte_tp = 'D' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Delegate'

      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'C' THEN 'Corporation PAC - nonqualifed'                        
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'L' THEN 'Labor PAC - nonqualifed'                        
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'T' THEN 'Trade PAC - nonqualifed'                                         
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'M' THEN 'Membership PAC - nonqualifed'                        
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'V' THEN 'Cooperative PAC - nonqualifed'                        
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'W' THEN 'Corporation without stock PAC - nonqualifed'
      WHEN cm.cmte_tp = 'N' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') THEN 'PAC - nonqualifed'

      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'C' THEN 'Corporation PAC - qualified'                        
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'L' THEN 'Labor PAC - qualified'                        
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'T' THEN 'Trade PAC - qualified'                                         
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'M' THEN 'Membership PAC - qualified'                        
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'V' THEN 'Cooperative PAC - qualified'                        
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') and cm.org_tp = 'W' THEN 'Corporation without stock PAC - qualified'
      WHEN cm.cmte_tp = 'Q' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'P', 'U', 'F') THEN 'PAC - qualified'

      WHEN cm.cmte_tp = 'O' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Super PAC (Independent Expenditure-Only)'

      WHEN cm.cmte_tp = 'U' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Single candidate independent expenditure'

      WHEN cm.cmte_tp = 'V' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Hybrid PAC (with non-Contribution Account) - nonqualified'     
      WHEN cm.cmte_tp = 'W' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Hybrid PAC (with non-Contribution Account) - qualified'

      WHEN cm.cmte_tp = 'N' and cm.cmte_dsgn = 'D' THEN 'Leadership PAC - nonqualifed'
      WHEN cm.cmte_tp = 'Q' and cm.cmte_dsgn = 'D' THEN 'Leadership PAC - qualified'

      ---Do not label lobbyist-registrant PACs on committee profile page. Use committee search to find them, https://www.fec.gov/data/committees/?designation=B  
      ---WHEN cm.cmte_tp = 'N' and cm.cmte_dsgn = 'B' THEN 'Lobbyist-registrant PAC - nonqualified'
      ---WHEN cm.cmte_tp = 'Q' and cm.cmte_dsgn = 'B' THEN 'Lobbyist-registrant PAC - qualified'

      WHEN cm.cmte_tp = 'X' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Party - nonqualifed'
      WHEN cm.cmte_tp = 'Y' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Party - qualified'
      WHEN cm.cmte_tp = 'Z' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Party nonfederal account'

      WHEN cm.cmte_dsgn = 'J' THEN 'Joint fundraising'

      WHEN cm.cmte_tp = 'I' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Independent expenditures reported by persons other than a political committee'

      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'C' THEN 'Communication cost - Corporation'                        
      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'L' THEN 'Communication cost - Labor Organization'                        
      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'T' THEN 'Communication cost - Trade Association'                                         
      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'M' THEN 'Communication cost - Membership Organization'                        
      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'V' THEN 'Communication cost - Cooperative'                        
      WHEN cm.cmte_tp = 'C' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') and cm.org_tp = 'W' THEN 'Communication cost - Corporation without Capital Stock'
      WHEN cm.cmte_tp = 'C' and cm.org_tp IS NULL THEN 'Communication cost'

      WHEN cm.cmte_tp = 'E' and NVL(cmte_dsgn, 'F') IN ('A', 'B', 'D', 'P', 'U', 'F') THEN 'Electioneering Communications' 

      ELSE 'UNKNOWN'
  END) display_committee_type, 
cm.cmte_tp,
cm.cmte_dsgn, 
cm.org_tp,
cm.fec_election_yr, 
cm.cmte_nm
from disclosure.cmte_valid_fec_yr cm
---where fec_election_yr = 2022
---where cmte_tp = 'H' 
--order by fec_election_yr desc, cmte_id asc
; 

[hcaofec]

Based on the discussion with developers, we can add a column to ofec_committee_history_mv, and the value of this column is calculated by a database function which uses the logic above.