SQL Interface

Overview

The Federal Election Commission (FEC) Database Model is kept deliberately simple to allow access to the data using the SQL interface without too complex queries. The SQL Interface gives the most powerful and complete access to the data that we collect. Using the Structured Query Language (SQL) users can directly run queries on the enriched contribution data as it is structured in our database Model.

A SQL query is a one or multiple line statement that is sent to the database. The result is returned as a CSV file. Here is an example query that selects the official FEC Id (candidate Id) of candidates with "Biden" in their name. It is useful to get the unique Id to query the data without using the full name as filed with the FEC:

SELECT CMTE_ID
FROM CommitteeMaster
WHERE CYCLE = 2020 AND CAND_ID IN (
    SELECT CAND_ID
    FROM CandidateMaster
    WHERE CYCLE = 2020 AND 
          CAND_NAME LIKE '%Biden%'
);

In this section, we'll detail how to use our online SQL Interface to get the data you want.

Getting Started

1. Go to https://data.eventures.vc/ui?tokens=<API_KEY>.

📘

Note: Please replace <API_KEY> with your API key provided by an e.ventures team member.

2. You will see the following sections:

• SQL Query (MySQL 5.7): In the field here, you can copy and paste our SQL queries depending on on your Use Cases. You can find out about the:
  • Donor-age distribution for candidates and committees
  • Donor-age distribution per age band
  • Cumulative sum of unique individual donors over time
  • Candidate-to-candidate comparison
• Token: This should be pre-filled with your <API_KEY>. If not, enter your API key provided by an e.ventures team member.
• Download CSV button: Clicking this button generates a CSV file with the results of your SQL Server query under SQL Query (MySQL 5.7).
• Create Link button: Clicking this button generates a link under the Encoded URL section. You can embed this link on your website or paste this link in a browser. This will generate a downloadable CSV file with the results of your SQL Server query under SQL Query (MySQL 5.7).
• Encoded URL: By default, this is blank if you did not click on the Create Link button. If the Create Link button is clicked, then there will be link here. You can click on the copy button to copy the link.

📘

Note:
The advantages of the URL encoded query are:

• The encoded URL can be embedded wherever a CSV file is required, e.g., in an Observable HQ notebook or a similar service.
• The results are cached in the Elections Platform and subsequent requests to the same URL take only seconds even for the most complex queries.
• The query always returns the latest data in our database. If new reports are filed with the FEC, they are automatically imported into our system and enriched with our external sources which takes hours or up to a day depending on the amount of data that is filed. After that, also earlier encoded URLs return the up-to-date data from the updated cache.

Next Steps

For more details on what you can do with the data, please refer to the Use Cases section. You can also learn how to access the data on the e.ventures Data Platform via our:

• Application Programming Interface (APIs)
• e.ventures Cohort Analysis Google Sheet