Cumulative Sum of Unique Individual Donors Over Time

Overview

This example demonstrates how to get the cumulative sum of unique individual donors for a candidate for each day of an election cycle. This pre-calculated data is derived from the FEC individual contribution data. This is a very computationally expensive calculation (i.e. this query takes up substantial computing power and time), and is not suitable to be executed using a SQL query.

The pre-calculated data is available using the:

Querying the Cumulative Donors using the SQL Interface

Building an SQL expression on the pre-calculated data is easy since it is stored in a single table in CumulativeDonors (cf. DB Schema).

The query is simple and the user only needs to set the parameters according to their needs:

SELECT
    CYCLE AS 'Cycle',      ⟵ We rename the column headers with: AS 'name'
    CAND_ID AS 'Candidate Id',  ⟵ to appear prettier in the result file
    REF_DATE AS 'Date',
    CUMUL_DONORS AS 'Number of Cumulative Unique Donors'
FROM
    CumulativeDonors
WHERE 
    CAND_ID IN (
        SELECT CAND_ID   subquery to fetch the candidate Id for a partial name
        FROM CandidateMaster 
        WHERE CYCLE = 2020 AND (
            CAND_NAME LIKE '%Trump%' OR
               CAND_NAME LIKE '%Biden%'
        )
    ) AND
    CYCLE = 2020
ORDER BY CYCLE, CAND_ID, REF_DATE;

We make the query a bit more complex by adding a sub-query that fetches the candidate Ids for Trump and Biden from the Candidate Master table.

📘
Note: The constraint CYCLE = 2020 is needed both in the inner and outer query. If it is excluded in the outer query, the candidate Ids are fetched for the 2020 election cycle but the cumulative sum of unique donors is fetched for all queries with the candidates Ids. For example, this would also include the 2016 cycle for Trump.

With the AS statement, you can rename the headers of the result file. This is useful if the data is embedded using the /query endpoint. In the SQL Interface, the created /query URL for the above statement is:

https://elections.eventures.vc/query?token=<API_KEY>&sql=SELECT%0A%20%20%20%20CYCLE%20AS%20'Cycle'%2C%20%0A%20%20%20%20CAND_ID%20AS%20'Candidate%20Id'%2C%20%0A%20%20%20%20REF_DATE%20AS%20'Date'%2C%0A%20%20%20%20CUMUL_DONORS%20AS%20'Number%20of%20Cumulative%20Unique%20Donors'%0AFROM%0A%20%20%20%20CumulativeDonors%0AWHERE%20%0A%20%20%20%20CAND_ID%20IN%20(%0A%20%20%20%20%20%20%20%20SELECT%20CAND_ID%0A%20%20%20%20%20%20%20%20FROM%20CandidateMaster%20%0A%20%20%20%20%20%20%20%20WHERE%20CYCLE%20%3D%202020%20AND%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20CAND_NAME%20LIKE%20'%25Trump%25'%20OR%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CAND_NAME%20LIKE%20'%25Biden%25'%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%20AND%0A%20%20%20%20CYCLE%20%3D%202020%0AORDER%20BY%20CYCLE%2C%20CAND_ID%2C%20REF_DATE%3B%0A

📘
Note: The link also contains the <API_KEY> used for creating it. Sharing the link also shares the API key.

Querying the Cumulative Donors using the Data + Charts API

The Federal Election Commission (FEC) Data Data + Charts API provides the /cumulative_donors/candidate endpoint to get the pre-calculated cumulative sum of unique individual donors. This endpoint's inputs are one or more candidate Ids and cycles. This endpoint also returns the same data as the SQL Interface described above.

To retrieve the candidate Id from (partial) candidate name, you can run the /committee/candidate/search endpoint.

The SQL statement from the previous section can be run through the Data + Charts API by executing the HTTP GET request:

http://elections.eventures.vc/data-charts/v1/committee/candidate/search?name_part=Trump& name_part=Biden&cycle=2020&token=<API_KEY>

This gives the candidate Ids for Trump and Biden. This is followed by another HTTP GET request:

http://127.0.0.1:8080/data-charts/v1/cumulative_donors/candidate?candidate_id=P80000722&candidate_id=P80001571 &cycle=2020&token=<API_KEY>

with the candidate Ids from the first request as parameters.

Visualizing the Cumulative Donors in ObservableHQ

The cumulative sum of unique individual donors is located in the e.ventures ObservableHQ notebook collection. The ObservableHQ notebooks embed links to the SQL Interface and to the Data + Charts API as explained above. When opening or refreshing a notebook, the data is fetched from those endpoints. The endpoints always deliver the most up to date results, and reflected the new data due to new filings or amendments by candidates, etc.

The data in the ObservableHQ notebooks generated by users of FEC Data is always kept up to date with links to the SQL Interface or the Data + Charts API.

1908 — Self-Updating Chart generated with d3.js on ObservableHQ.com.

📘
Note: The ObservableHQ.com platform is built by one of the developers of D3.js, an extensive JavaScript library for visualizing data, and produces SVG images (Scalable Vector Graphics). These images are not pixel- but vector-based and, thus, are not pixelated if enlarged, which is optimal for all sorts of publication in online and offline media.