Preparing Research Sheets for Import
Once Issue Positions research has completed for a state/NPAT Group, preparation of research sheets for IT import is an important step to ensure research accuracy and smoothness to the import process. The steps below are important to follow prior to sending import sheets to IT.
Step 1: In order to be added to a IT ticket, the research sheets will need to be downloaded from the Google Drive into the network drive. These sheets should be downloaded as Excel Spreadsheets (.xlsx file) and saved in the network drive in the appropriate folder for the current election cycle, which will be located in: /research/active/research programs/pct/
You can download the file from Google Sheets by selecting File>Download>Microsoft Excel.
Step 2: Before importing the sheets it is important to go through every piece of evidence, if possible, and make sure that everything is formatted correctly. This check also gives you the opportunity to make sure that evidence is where it should be and that determinations are as strong as you would like them to be. Have a detailed look through the sheet as a final check to ensure accuracy and relevancy of the collected research. This process can be done by numerous people to avoid burnout, and improve research standards with multiple eyes on each research sheet.
Step 3: In order to ensure a clean worksheet it is also important to get rid of things that could make links messy for users. Below are multiple QC processes to complete prior for knowing sheets are ready for import:
- keyword searches on the VS website. Keyword searches may have been utilized in research via the VS website to find appropriate statements for a question - these keyword searches can appear in the URL of a particular citation. In order to get rid of keyword searches you should hit Ctrl. and F in order to open a search box. You should then type in “search=”. This should give you a list of every keyword search in the worksheet. You should then go through and delete all of the keyword searches. It may be laborious but it does make a difference in the finished product.
- 'justfacts.votesmart.org' links. (This may only be a temporary issue through website redesign process) - some of the website URLs were adjusted during the process of website redesign in 2019-2020. In order to fix this you should go into the “Find and Select” folder, clicking on “Replace”:
- Place this link in the “Find what” box: https://justfacts.
- Place this link in the “Replace with” box: https:
- This means that a link originally looking like this: https://justfacts.votesmart.org/public-statement/1478290/tweet should look like this at the end: https://votesmart.org/public-statement/1478290/tweet
- Quality Control. The final step before importing any sheet should be to do a quality control check to ensure that all of the determinations are formatted correctly. When first filling out a research sheet it is very easy to overlook errors that could have been put in accidentally (ex. Putting a determination as “pro-Life” instead of “Pro-Life”). These may not seem like big deals, but any errors prevent a successful import process.
- This query will help to search the entire sheet to determine if there are any errors in determinations: (the blank (after the C and Z) will be the row number immediately below the last candidate on the spreadsheet - for example, if the last candidate ID falls in row 63, then enter number 64 in the blanks of the formula.)
- =(COUNTA(C3:C_,)*(Number of PCT Questions)) - (SUMPRODUCT((--EXACT("Unknown Position",G3:Z_))))- (SUMPRODUCT((--EXACT("Yes",G3:Z_))))- (SUMPRODUCT((--EXACT("No", G3:Z_))))- (SUMPRODUCT((--EXACT("Pro-Choice",G3:Z_))))- (SUMPRODUCT((--EXACT("Pro-Life",G3:Z_))))
- If the above formula returns a result other than zero, you can use this supporting formula to locate the issue. For each row of determinations, the following formula should equal the number of questions on the test. If the number is less, than you can locate the issue on the respective row.
- =(Number of PCT Questions) - (SUMPRODUCT((--EXACT("Unknown Position", G3:Z3))))- (SUMPRODUCT((--EXACT("Yes", G3:Z3))))- (SUMPRODUCT((--EXACT("No", G3:Z3))))- (SUMPRODUCT((--EXACT("Pro-Choice", G3:Z3))))- (SUMPRODUCT((--EXACT("Pro-Life", G3:Z3))))
(If there are no errors in the sheet then the query below should produce a 0)
| File | Last modified | Size |
|---|---|---|
| Completed_Research_Sheet_Example.xls | 2020-11-23 09:34 | 108Kb |
