Technical report¶

The stakeholder summary uses the WinRed-exclusion scenario. Q1 and Q2 below show the original reported-record results and explain how that scenario changes them. Both use the 2018 and 2020 cycles, covering January 2017 through December 2020.

Answers by question¶

Amounts combine both cycles. Medians are approximate; averages and amounts are rounded. The scenario reduces overlapping reporting but is not a verified count of unique donations.

Q1: sources · Q2: candidates · Q3: analysis plan · QA and model · Conduit comparison · Scope and next steps · Appendix

Q1. Contribution counts and amounts¶

SELECT
  cycle,
  COUNT(*) AS n_committee_contributions,
  COUNT(DISTINCT sub_id) AS n_distinct_ids,
  SUM(amount) AS gross_committee_amount,
  MIN(transaction_dt) AS earliest_included_date,
  MAX(transaction_dt) AS latest_included_date
FROM `fec-contributions-analysis.fec_analysis.stg_committee_q1`
WHERE is_q1_in_scope
GROUP BY cycle
ORDER BY cycle;

SELECT
  cycle,
  COUNT(*) AS n_individual_contributions,
  COUNT(DISTINCT sub_id) AS n_distinct_ids,
  SUM(amount) AS gross_individual_amount,
  MIN(transaction_dt) AS earliest_included_date,
  MAX(transaction_dt) AS latest_included_date
FROM `fec-contributions-analysis.fec_analysis.stg_individual_q1`
WHERE is_q1_in_scope
GROUP BY cycle
ORDER BY cycle;

Each included record ID is unique within its source and cycle. The counts describe transactions, not distinct donors. Committee activity includes payments to candidate committees and other registered committees.

The table uses the reported-record measure. Under the WinRed-exclusion scenario, individual contributions fall to 57,464,005 records totaling USD 12,667,245,189 and committee contributions fall to 652,869 records totaling USD 2,579,862,501. The stakeholder summary uses these figures. I explain the scenario and its limits in Intermediary reporting and overlap.

Peak month¶

October 2020 recorded USD 2,017,565,781 in combined activity, compared with USD 1,793,420,123 in September. The month-over-month increase is 12.50%.

WITH calendar AS (
  SELECT contribution_month
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      DATE '2017-01-01',
      DATE '2020-12-01',
      INTERVAL 1 MONTH
    )
  ) AS contribution_month
),
monthly AS (
  SELECT
    c.contribution_month,
    COALESCE(SUM(m.gross_amount), 0) AS total_amount
  FROM calendar AS c
  LEFT JOIN
    `fec-contributions-analysis.fec_analysis.agg_contributions_monthly` AS m
    USING (contribution_month)
  GROUP BY c.contribution_month
),
growth AS (
  SELECT
    contribution_month,
    total_amount,
    LAG(total_amount) OVER (
      ORDER BY contribution_month
    ) AS prior_month_amount
  FROM monthly
)
SELECT
  contribution_month,
  total_amount,
  prior_month_amount,
  100 * SAFE_DIVIDE(
    total_amount - prior_month_amount,
    prior_month_amount
  ) AS month_over_month_growth_pct
FROM growth
QUALIFY DENSE_RANK() OVER (ORDER BY total_amount DESC) = 1;

I aggregated the records by calendar month and used a complete monthly calendar before calculating the prior-month comparison. The monthly table reconciles exactly to the source counts and amounts, with 48 distinct months per source. It also keeps chart queries small.

The peak and growth rate apply to the reported-record definition. Excluding WinRed’s 15E conduit receipts and its outgoing committee contributions leaves October 2020 as the peak, at USD 1,636,196,210 versus USD 1,566,597,035 in September, an increase of 4.44%. This tests the effect of the reporting choice; it is not a fully deduplicated donation estimate.

Source queries: 16_model_committee_q1, 17_model_individual_q1, 18_model_monthly_contributions, 19_qa_monthly_reconciliation and 21_monthly_chart_data.

Definitions and caveats¶

I used gross positive amounts so the contribution measure has a consistent meaning across sources. Refunds, zero amounts, nonblank memo codes, and records with missing or out-of-cycle dates are excluded. Both memo codes X and Y are excluded by the same rule. Amounts are cast to NUMERIC for aggregation.

Individual receipts require entity type IND and transaction types 10, 15, 15E, 30, 30E, 31, 31E, 32, or 32E. This excludes candidate self-contributions, loans and intermediary disbursements. The source contains itemized records; it is not a complete measure of all donations.

Committee contributions use outgoing transaction types 24K and 24Z. For Q1, I used other_committee_tx_2018 and other_committee_tx_2020. For Q2, I used committee_contributions_2018 and committee_contributions_2020 because they contain candidate attribution. I did not add the two committee sources together.

The FEC transaction code definitions informed these filters. The unit of analysis is a qualifying record within a cycle; candidate statistics combine records by candidate ID across both cycles.

Individual receipts make up most of the reported amount at the October 2020 peak. The stacked areas show the contribution of each source to the combined monthly total. Source: 21_monthly_chart_data.csv.

-- 21_monthly_chart_data
-- I use the saved monthly summary to compare the two sources
-- over time without scanning the original contribution tables again.
WITH monthly AS (
  SELECT
    contribution_month,
    SUM(
      IF(source_type = 'individual', gross_amount, 0)
    ) AS individual_amount,
    SUM(
      IF(source_type = 'committee', gross_amount, 0)
    ) AS committee_amount,
    SUM(gross_amount) AS total_amount
  FROM
    `fec-contributions-analysis.fec_analysis.agg_contributions_monthly`
  GROUP BY contribution_month
)
SELECT
  contribution_month,
  individual_amount,
  committee_amount,
  total_amount,
  100 * SAFE_DIVIDE(
    total_amount - LAG(total_amount) OVER (
      ORDER BY contribution_month
    ),
    LAG(total_amount) OVER (
      ORDER BY contribution_month
    )
  ) AS month_over_month_growth_pct
FROM monthly
ORDER BY contribution_month;

Q2. Candidate coverage and distribution¶

Qualifying committee records identify 2,614 candidate IDs and USD 1,120,452,295 in attributed contributions across both cycles. The median total per candidate ID is approximately USD 15,800, compared with a mean of USD 428,635. The top 262 IDs receive 61.91% of the attributed amount.

-- 12_q2_candidate_summary
-- I total qualifying contributions for each reported candidate ID
-- across both cycles, then describe the distribution of those totals.
-- Missing candidate IDs remain separate; I cannot tell how many
-- candidates those unidentified records represent.
WITH eligible AS (
  SELECT cand_id, amount
  FROM
    `fec-contributions-analysis.fec_analysis.stg_committee_contributions`
  WHERE is_q2_in_scope
),
candidate_totals AS (
  SELECT
    cand_id,
    SUM(amount) AS total_received
  FROM eligible
  WHERE cand_id IS NOT NULL
  GROUP BY cand_id
),
ranked AS (
  SELECT
    cand_id,
    total_received,
    ROW_NUMBER() OVER (
      ORDER BY total_received DESC, cand_id
    ) AS candidate_rank,
    COUNT(*) OVER () AS n_candidates
  FROM candidate_totals
),
summary AS (
  SELECT
    COUNT(*) AS n_reported_candidate_ids,
    SUM(total_received) AS total_attributed_amount,
    MIN(total_received) AS minimum_received,
    AVG(total_received) AS mean_received,
    APPROX_QUANTILES(total_received, 100) AS percentiles,
    MAX(total_received) AS maximum_received,
    STDDEV_POP(total_received) AS population_stddev,
    100 * SAFE_DIVIDE(
      SUM(IF(
        candidate_rank <= CEIL(n_candidates * 0.10),
        total_received, 0
      )),
      SUM(total_received)
    ) AS top_10_percent_share_pct
  FROM ranked
)
SELECT
  n_reported_candidate_ids,
  total_attributed_amount,
  minimum_received,
  percentiles[OFFSET(25)] AS approximate_p25,
  percentiles[OFFSET(50)] AS approximate_median,
  mean_received,
  percentiles[OFFSET(75)] AS approximate_p75,
  percentiles[OFFSET(90)] AS approximate_p90,
  percentiles[OFFSET(99)] AS approximate_p99,
  maximum_received,
  population_stddev,
  top_10_percent_share_pct,
  (
    SELECT COUNTIF(cand_id IS NULL)
    FROM eligible
  ) AS n_contributions_without_candidate_id,
  (
    SELECT SUM(amount)
    FROM eligible
    WHERE cand_id IS NULL
  ) AS amount_without_candidate_id
FROM summary;

Definitions and caveats¶

I aggregated by candidate ID before calculating the distribution. The large difference between the mean and median reflects concentration at the upper end.

Populated IDs remain in the analysis even where the same-cycle candidate reference has no match: 138 IDs in 2018 and 125 in 2020. The result is therefore a count of reported candidate IDs, not a verified count of unique people. Another 2,938 qualifying records, totaling USD 3,098,774, have no candidate ID and are excluded from the distribution.

Largest candidate total¶

The USD 49,608,977 maximum belongs to Donald Trump's candidate ID, P80001571. Of that amount, USD 48,407,633 was reported by WinRed, C00694323, in 2020: 257,625 records, all coded 24K, with a largest record of USD 5,600. WinRed accounts for 97.58% of this candidate's total. The full breakdown reconciles to the original maximum.

The WinRed records identify recipient committee C00580100, use entity type CCM, and have no memo text. They establish the reported amount and recipient attribution, but do not identify the original funding source of each payment. I have retained them in the stated committee-record measure. I would not describe the result as USD 49.6 million of independently funded PAC support.

Excluding WinRed from the committee-source measure reduces coverage from 2,614 to 2,603 candidate IDs and the attributed amount to USD 1,053,851,132. The approximate median is USD 15,750 and the mean is USD 404,860. The largest total becomes USD 6,283,681, for candidate ID S4GA11285; Trump’s remaining total is USD 1,201,344. The top 261 IDs still account for 59.61% of the amount. Concentration therefore persists, but the original maximum is heavily affected by conduit reporting. The stakeholder summary uses these figures. Other intermediaries may still be included.

Candidate and committee identities were checked against the FEC candidate record and FEC committee record. Source queries: 11_model_committee_staging, 12_q2_candidate_summary, 22_qa_largest_candidate, 24_qa_winred_candidate_reporting and 27_qa_winred_sensitivity.

WinRed-exclusion scenario, both cycles. The top group contains 261 of 2,603 candidate IDs, rounding 10% up to a whole candidate. Source: query 27. This shows concentration; the table above describes the original distribution.

Q3. Analysis plan: top 10 business payees by state¶

A preliminary ranking is feasible within four hours. A validated ranking is not: I estimate about 26 hours, or three to four working days. The aggregation itself is straightforward. Business identification, name standardization and payments through intermediaries take most of the time, and all three depend on manual review.

The query below produced the source assessment. Q3 asks for an analysis plan; I have not produced a business-payee ranking.

-- 20_qa_operating_expenditures
-- Before proposing a business-payee ranking, I need to check whether
-- the expenditure sources cover the intended period and contain
-- usable payee names and states.
-- The two descriptive tables had identical inventory counts,
-- so I am comparing their actual date and reporting-year coverage.
WITH records AS (
  SELECT
    'opex_2018' AS source_table,
    2018 AS cycle,
    sub_id, rpt_yr, transaction_dt,
    transaction_amt, name, state, entity_tp
  FROM `bigquery-public-data.fec.opex_2018`
  UNION ALL
  SELECT
    'opex_2020', 2020,
    sub_id, rpt_yr, transaction_dt,
    transaction_amt, name, state, entity_tp
  FROM `bigquery-public-data.fec.opex_2020`
  UNION ALL
  SELECT
    'oppexp18', 2018,
    sub_id, rpt_yr, transaction_dt,
    transaction_amt, name, state, entity_tp
  FROM `bigquery-public-data.fec.oppexp18`
  UNION ALL
  SELECT
    'oppexp20', 2020,
    sub_id, rpt_yr, transaction_dt,
    transaction_amt, name, state, entity_tp
  FROM `bigquery-public-data.fec.oppexp20`
)
SELECT
  source_table,
  COUNT(*) AS n_rows,
  COUNT(DISTINCT sub_id) AS n_distinct_ids,
  COUNTIF(sub_id IS NULL) AS n_missing_ids,
  MIN(rpt_yr) AS earliest_report_year,
  MAX(rpt_yr) AS latest_report_year,
  MIN(transaction_dt) AS earliest_date,
  MAX(transaction_dt) AS latest_date,
  COUNTIF(transaction_dt IS NULL) AS n_missing_dates,
  COUNTIF(
    transaction_dt BETWEEN
      DATE(cycle - 1, 1, 1) AND DATE(cycle, 12, 31)
  ) AS n_dates_in_cycle,
  COUNTIF(NULLIF(TRIM(name), '') IS NULL) AS n_missing_payee_names,
  COUNTIF(NULLIF(TRIM(state), '') IS NULL) AS n_missing_states,
  COUNTIF(NULLIF(TRIM(entity_tp), '') IS NULL) AS n_missing_entity_types,
  COUNTIF(transaction_amt < 0) AS n_negative_amounts,
  SUM(CAST(transaction_amt AS NUMERIC)) AS raw_total_amount
FROM records
GROUP BY source_table
ORDER BY source_table;

Approach and limitations¶

I would start with oppexp18 and oppexp20. Their reporting years match the requested cycles, and most dates fall within those periods. I ruled out opex_2020 for the 2020 analysis: its reporting years are 2017–2018 and every transaction date is missing. Its summary statistics also match opex_2018.

The alternative sources have missing names and states, negative amounts and implausible dates. oppexp18 contains exactly 1,800,000 records. That is a reason to verify coverage, although that count alone is not proof of truncation. opex_2018 has 80 more records and USD 377,626 more than oppexp18, so the two 2018 versions differ even apart from the missing dates.

I would first check record IDs, dates, memo entries and adjustments. Records without usable names or states would be accounted for separately. I would then standardize payee names and review likely matches among the largest recipients, using address details where available. Similar names alone would not justify merging businesses.

Intermediary payments need particular care. A processor or payroll provider may be the reported payee without being the final recipient. Supporting itemization must not be counted again as an additional payment. After applying those rules, I would aggregate accepted amounts by business and state, rank within each state, and review the leading results.

Questions for the stakeholder¶

I would confirm these before building the ranking:

• Should "state" mean the payee’s reported address or the paying committee’s state?
• Should the ranking use gross payments, or payments net of refunds and adjustments?
• Should a business be combined with its subsidiaries or parent company?
• Does a payment processor, payroll provider or conduit count as the business receiving the money?
• Should the two cycles be combined or ranked separately?

Until those are settled, my starting assumption would be total gross payments across both cycles, grouped by the payee’s reported state.

Time estimate¶

The four-hour version would contain the preliminary ranking, the SQL, the definitions and the value of excluded or unresolved records. I would spend the review time on cases large enough to change a top 10, and I would label the result as a ranking of identified business payees in the available records, not a complete ranking of all businesses.

Source query: 20_qa_operating_expenditures.

Source validation and modeling decisions¶

I compared table coverage before choosing sources. Similar names and matching totals were not sufficient evidence that two versions contained the same records.

Deduplicating record IDs does not identify every repeated underlying donation. Likewise, retaining a populated candidate ID does not validate every attribution. These are separate questions from whether the SQL preserves the selected records.

Staging rows retain eligibility flags. Query 29 saves the deduplicated individual rows in a table clustered by cycle and committee; the staging view now reads that snapshot. The monthly aggregate remains the reporting layer for charts.

The repeated-ID comparison processed 14.73 GB in 13.821 seconds. I used job information to monitor execution cost and runtime; the reconciliation checks provide the evidence for aggregation accuracy.

Source queries: 01_source_inventory through 10_qa_committee_profile; 13_qa_other_committee_profile through 19_qa_monthly_reconciliation.

The views preserve the selected source fields, not every original column. The monthly table supports repeated reporting without rescanning the individual source. Adding cycle to the record key keeps election-cycle extracts separate.

The dimensions have one row per ID and cycle. Multiple source labels are retained together rather than silently choosing one. Candidate attribution is attached only to matching committee records; individual receipts are not assigned to candidates through an assumed committee relationship. The platform flag identifies the reporting committee, not the original donor. Query 29 checks the richer fact view against the existing monthly counts and amounts before it is used for reporting.

The snapshot is a fixed extract, not an automatic refresh pipeline. Query 29 rebuilds it directly from the original sources. Query 18 can rebuild the monthly table; query 29 should run last to restore the enriched fact view. This keeps the original analysis reproducible while supporting repeated investigation without repeating the large deduplication.

For conduit-reported individual receipts, recipient_committee_id is the reporting conduit’s ID, not necessarily the final recipient committee.

Intermediary reporting and overlap¶

The committee-source comparison found 1,010,120 shared qualifying record IDs. Using one source for Q1 avoids adding those records twice. I also checked a different issue: the same donation appearing under separate IDs in a conduit’s report and a recipient’s report.

ActBlue and WinRed report their side of an earmarked donation with different transaction codes, so my filters treat them differently.

My transaction filter excludes 24T, so ActBlue donations are counted through the recipient’s report. WinRed codes its own receipts 15E, the same as the recipient, so both sides pass the transaction and memo filters. ActBlue follows the same pattern in 2018, and WinRed has no records in the 2018 data. A blank memo code alone does not resolve conduit reporting.

WinRed’s other_id field identifies an intended recipient on all but 83 of its included records. I compared that ID with the receiving committee’s ID, together with the donor’s name, five-digit ZIP code, date and amount. Names were trimmed and converted to uppercase. The comparison returned 1,916,040 groups with exactly one record on each side, totaling USD 341,092,327 per side. Another 177,252 groups contained multiple records on one or both sides. Many records had no match under these fields.

These matches are strong evidence of repeated reporting, but the matching fields are not a unique donation identifier. I did not automatically delete matching records or treat unmatched records as proof of separate donations. The FEC’s reporting guidance explains that recipients disclose the original contributor and the date the conduit received the contribution. WinRed’s conduit guidance also describes reporting by both parties.

To measure the effect on the answers, I excluded WinRed-reported 15E receipts and WinRed’s outgoing committee contributions while retaining recipient-reported individual receipts. All other filters stayed the same.

The timing of the peak and the concentration of candidate receipts hold under this test. Their size is sensitive to how conduit activity is counted.

The summary uses the WinRed-exclusion scenario. It reduces overlapping reporting but also removes unmatched conduit records. A missing match can reflect differences in fields, reporting coverage or itemization; it is not proof that a recipient never reported the donation. The scenario is therefore not a fully deduplicated measure of original donor funding.

Committee-side follow-up¶

Query 28 checked reporting committees whose names contain ActBlue or WinRed in both committee sources. ActBlue has six included Q1 records totaling USD 237,000: five records in 2018 worth USD 37,000 and one in 2020 worth USD 200,000. It has no included Q2 records. WinRed has 1,097,635 included Q1 records worth USD 140,617,466 and 472,869 included Q2 records worth USD 66,674,728. The Q1 excluded amounts match query 27 exactly. The Q2 amount is USD 73,565 higher than the USD 66,601,163 removed in query 27, which restricts candidate totals to records with a populated candidate ID.

This closes the platform-coding check for the two sources. It does not trace the original funding behind ActBlue’s six records or payments routed through other committees. I retained those records under the stated filters and made no further adjustment to the totals.

Source queries: 23_qa_intermediary_reporting through 28_qa_committee_platform_reporting. Results reviewed September 14, 2026.

-- 27_qa_winred_sensitivity
-- Measure the effect of excluding WinRed conduit receipts and outgoing payments.
-- This is a reporting-side sensitivity test, not a complete donation deduplication.
WITH excluded AS (
  SELECT
    'individual' AS source_type,
    transaction_dt,
    amount
  FROM `fec-contributions-analysis.fec_analysis.stg_individual_q1`
  WHERE is_q1_in_scope
    AND cmte_id = 'C00694323'
    AND transaction_tp = '15E'
  UNION ALL
  SELECT
    'committee',
    transaction_dt,
    amount
  FROM `fec-contributions-analysis.fec_analysis.stg_committee_q1`
  WHERE is_q1_in_scope
    AND cmte_id = 'C00694323'
),
monthly AS (
  SELECT
    source_type,
    DATE_TRUNC(transaction_dt, MONTH) AS contribution_month,
    COUNT(*) AS n_records,
    SUM(amount) AS amount
  FROM excluded
  GROUP BY 1, 2
),
candidate_totals AS (
  SELECT
    cand_id,
    SUM(amount) AS amount
  FROM `fec-contributions-analysis.fec_analysis.stg_committee_contributions`
  WHERE is_q2_in_scope
    AND cand_id IS NOT NULL
    AND cmte_id != 'C00694323'
  GROUP BY 1
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY amount DESC, cand_id) AS ranking,
    COUNT(*) OVER () AS n_candidates
  FROM candidate_totals
),
candidate_summary AS (
  SELECT
    COUNT(*) AS n_candidates,
    SUM(amount) AS amount,
    MIN(amount) AS minimum,
    AVG(amount) AS mean,
    APPROX_QUANTILES(amount, 100)[OFFSET(50)] AS approximate_median,
    MAX(amount) AS maximum,
    STDDEV_POP(amount) AS population_stddev,
    100 * SAFE_DIVIDE(
      SUM(IF(ranking <= CEIL(n_candidates * .1), amount, 0)),
      SUM(amount)
    ) AS top_ten_percent_share_pct,
    ARRAY_AGG(STRUCT(cand_id, amount) ORDER BY ranking LIMIT 1) AS largest_candidate
  FROM ranked
)
SELECT
  'excluded_q1_monthly' AS result_type,
  TO_JSON_STRING(m) AS details
FROM monthly AS m
UNION ALL
SELECT
  'q2_without_winred',
  TO_JSON_STRING(c)
FROM candidate_summary AS c;

Scope and next steps¶

Scope¶

I focused the analysis on the three questions, source quality and checks that could change the answers. The report separates the findings from the detailed SQL and QA appendix so a reviewer can read the conclusions first and inspect the supporting work where needed.

Completed refinements¶

I materialized the deduplicated individual records so repeated analysis no longer reruns that step. I also added recipient and reporting-committee fields, a reporting-platform flag, candidate attribution where available, and committee and candidate dimensions. The updated model reconciles to the original totals. Q2 includes a concentration chart showing the share received by the top 10% of candidate IDs.

With more time¶

I would add a full Q2 distribution chart using candidate-level totals, with a log scale to make the smaller amounts visible alongside the largest recipients. The current chart shows concentration, not the shape of the full distribution. I would also automate refreshes and reconciliation for the saved individual table, and extend the conduit fields beyond the named reporting platforms to trace more of the path from the original donor to the recipient. Those additions would support recurring reporting and further analysis beyond this submission.