Building Research Sheets
No research sheet is built off of the work of only one person. It takes multiple people to help ensure that all of the research available is collected and is accurate. Google Sheets are used so that it's possible for a number of people to work on a sheet at the same time.
Google Sheets also make it possible for remote interns and supervisors to work on the same project. Since Vote Smart interns are not always in the same building, or even in the same state, it is important to have a way that supervisors can study the work of their interns in real time while also being able to highlight things that need to be addressed.
Building A Google Sheet
Step 1: Before beginning your research and filling up the Google Sheet it is important to make sure that certain boxes on the sheet are formatted correctly:
- In Row 1, columns A-F should be left blank.
- Starting in column G of Row 1, the questions on the PCT should be listed in the order they appear on our test.
- In Row 2, column A should be left blank, while columns B-F should be listed in order with: State, Candidate ID, Last Name, First Name, and Office.
- Columns G onward should have Q1, Q2, etc. to help make it clear for researchers to determine where they should begin their research.
- If done correctly, it should look like this:
| Question 1 | Question 2 | Question 3 | ||||||
| State | Candidate ID | Last Name | First Name | Office | Q1 | Q2 | Q3 |
Step 2: Now that the sheet has been formatted to indicate where certain information goes, you are able to start adding in candidate information. This information will be unique to each candidate and it is important to ensure that this information is accurate. This information will be started in Row 3, below the information added from step 1.
- Column A should be labeled Determination. This row is where you will be listing the determination for the candidate’s position on each individual question - this will be on every 5th row, with row numbers ending in 3 and 8.
- Column B should have the candidate’s state listed (if the candidate is running for President then the state should be NA for National).
- Column C should have the Candidate ID listed. This Candidate ID is individual to each candidate and it is incredibly important that the number is correct and attached to the correct candidate.
- Column D should have the candidate’s last name.
- Column E should have the candidate’s first name.
- Column F should have the office that the candidate is running for (ex. U.S. House, U.S. Senate, Governor, President, etc.).
- Column G and onward will have included the determinations made for each of the respective questions for that column listed in row 1 (Pro-Choice, Pro-Life, Yes, No, Unknown Position).
- In Column A, below the row including candidate information, place 4 rows labeled as 'citation' - this is where evidence pieces will be placed for each respective determination.
- If done correctly, it should look like this:
| Question 1 | Question 2 | Question 3 | ||||||
| State | Candidate ID | Last Name | First Name | Office | Q1 | Q2 | Q3 | |
| Determination | IA | 54321 | Example | Candidate | President | Unknown Position | Unknown Position | Unknown Position |
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Citation |
The above process will be repeated for every candidate for that research sheet. To get the full list of candidates to enter for each Research Sheet, you can use the following query:
SELECT x.state_id, x.candidate_id, x.lastname, x.firstname, x.office
FROM
(SELECT DISTINCT ec.state_id, ec.candidate_id, c.lastname, c.firstname, dn.name, ec.districtname_id, o.name AS office
FROM election e
JOIN election_electionstage ee ON e.election_id = ee.election_id
JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id
JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id
JOIN candidate c ON ec.candidate_id = c.candidate_id
JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id
JOIN party p ON ecp.party_id = p.party_id
JOIN districtname dn ON ec.districtname_id = dn.districtname_id
JOIN office o ON ec.office_id = o.office_id
WHERE e.electionyear = 2020 AND ec.state_id IN ('IA') AND ee.electionstage_id = 'G' AND ec.office_id IN (5,6) AND electioncandidatestatus_id = 8
Order BY ec.districtname_id, c.lastname) x
The variables to change in this query are electionyear, state_id, and electionstage_id (either 'P' for primary research, or 'G' for general) - the query currently has IA 2020 General candidates as an example. The list from this query can also be cross-referenced with the admin candidate list for the full list of candidates running for that particular office.
A final sheet would have a structure that would look similar to this:
| Question 1 | Question 2 | Question 3 | ||||||
| State | Candidate ID | Last Name | First Name | Office | Q1 | Q2 | Q3 | |
| Determination | IA | 98765 | Example | Candidate | President | Unknown Position | Unknown Position | Unknown Position |
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Determination | IA | 87654 | Example 2 | Candidate | President | Unknown Position | Unknown Position | Unknown Position |
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Determination | IA | 76543 | Example 3 | Candidate | President | Unknown Position | Unknown Position | Unknown Position |
| Citation | ||||||||
| Citation | ||||||||
| Citation | ||||||||
| Citation |
Step 3: Now that a research sheet has been built it is best to begin a query in order to help keep track of your determination rate. The determination rate represents the number of possible determinations that are labeled as either “Yes”, “No”, “Pro-Life”, or “Pro-Choice.” - The higher our determination rate is the less “Unknown Positions” we have. This can be a useful reference through research to focus research efforts to improve this determination rate across research sheets. To set-up this up, follow the below steps:
- Below the final candidate in the spreadsheet, insert a formula that counts each of the possible determinations give in each row for each answer (Pro-Life, Pro-Choice, Yes, and No, and Unknown Position) - each formula would use the following structure:
- =countifs(G3:final candidate row, “Answer text”)
- This will create 5 separate formulas, and look like below:
| Pro-Life | 2 |
| Pro-Choice | 2 |
| Yes | 30 |
| No | 16 |
| Unknown Position | 50 |
- Once this table has been created, use the following formula to calculate the determination rate, based on the results above. To do this, use the following formula:
- =(Pro-Life+Pro-Choice+Yes+No)/(Pro-Life+Pro-Choice+Yes+No+Unknown Position)*100
- The final table should look like below:
| Pro-Life | 2 |
| Pro-Choice | 2 |
| Yes | 30 |
| No | 16 |
| Unknown Position | 50 |
| Determination Rate | 50% |
| File | Last modified | Size |
|---|---|---|
| New_Research_Sheet_Example.xls | 2020-11-23 09:33 | 26Kb |
