% Baranger Awards Application Automation Guide % A&S-GSO Elizabeth Baranger Teaching Award Committee and Communications Committee

About

This guide covers how to do the Baranger awards, including nominations, applications, and reviewing.

The programming and/or code information in this guide is typed from memory and/or with a casual glance at code. It may not be exactly right, please revise it if it isn't.

For the privacy of nominees and applicants, this project is split amongst two repositories using Git submodules. Please ensure you have access to both repositories:

• Scripts and documentation: https://github.com/pittasgso/baranger
• Private nomination/application data: https://bitbucket.org/pittasgso/baranger-data

Timeline

Fall

• Decide timeline and dates:

  • When will nominations open?
    • Generally right at the beginning of January
  • When will nominations close and be due?
    • Generally due about February 1
  • When will applications be due?
    • Generally due about March 1
  • When will you announce winners?
    • Generally April 1 or 2
  • When will the awards luncheon be?
    • Generally about 2-3 weeks after the winners are announced

• Make a reservation with Lucca or University Club so the date is saved.

• Email all the higher-ups (Elizabeth Baranger, Dean Carr, A&S Dean) with a save-the-date for the luncheon date once it is set.

• Get in touch with prospective faculty reviewers. Joel Brady (CIDDE/Teaching Services) has in the past helped out. Others may be contacted. Having 2-3 faculty reviewers is good.

• Get in touch with the Communications Committee and make any necessary changes to the (now online) application. (The application has been pretty standard for a number of years, but the way the questions are asked or what personal information is obtained can always be tweaked. In particular, the instructions for the teaching challenge and what is expected in the teaching material reflection need to be clearer, if I remember correctly from the issues we had last year.)

• Make sure the Communications Committee has the nominations page ready to go as soon as winter break is over.

• Last year, we asked people to tell us where they had heard about the award when they nominated someone. That data still needs to be analyzed to help target advertising for the nominations more effectively.

• Work with Administrative Assistant to apply for any additional funding (i.e., from the Provost) for advertising the award. (We got this three years ago, but not the last two .)

• Clarify budget with Administrative Assistant. Generally there are 6 awards of $250 each (two per academic division, plus floating 7th award for any of the three divisions) plus the luncheon budget, which needs to be about $700-$800 for us to be able to pull it off (Lucca’s/Monica is great – if you tell her the budget and approximately how many people are coming, which tends to be about 18-23, she “makes it work” at that price point).

• Design/update advertisements for the nominations.

January

• Advertise for nominations. Stories in the Pitt News (if possible), Teaching Times, University Times, and Pitt Chronicle; advertisement in the Pitt News (if funding allows); posters around campus; have GSO delegates post advertisements in their departments this is vital; on the tvs around campus (in the dorms, Cathedral, etc.); emails to the graduate secretaries (this is the most important!).

• Open nominations; answer questions as they come in.

• When nominations close, remove duplicates (Communications Committee can help) and check manually to make sure everyone is a student. If information is missing or a person seems suspicious, contact the graduate secretary for the appropriate department to check the status of the person. This needs to be done in 1-3 days after the nominations close.

• Make sure the online application is edited and ready to go. Make sure individual log-in codes get generated (by the Communications Committee) for each nominee.

• Ask for reviewer help from the GSO and follow-up with faculty reviewers.

February

• Email all the people who were nominated and invite them to apply. (There is a Gmail account set up for the Teaching Awards, and I can share the log-in info.) Answer questions as they come in.

• Send a reminder email to nominees to submit their applications.

• Finalize reviewers (student and faculty). Finalize the rubric (if you want to make any changes).

March

• Work with A&S GSO Administrative Assistant to get payment to Lucca’s sorted out – whether we’re paying them ahead of time or the day of or a bit of both. (This has been a hot mess in the past.)

• Assign applications to reviewers, avoiding same departments and personal friends as much as possible. Get reviews, by mid-March if not sooner, for all applicants.

  • Generally, we’ve had each application reviewed by at least two people. If the scores are very discrepant (variously defined in the past, but 3+ points different in the total score last year, and about 5+ points different in the total score the year before), get a third review.

  • Generally, non-committee member volunteer reviewers have done about 8-12 applications, and committee member reviewers have done closer to 15-20.

• Average the total scores for each applicant. Cut out the bottom half or two-thirds from each division (depending on what kind of numbers you’re dealing with). Assign who remains to committee members so that at least one committee member reviews each semi-finalist application.

• Have the committee meet multiple times. Discuss each applicant in the top half to third of each division and whittle it down to 3-4 finalists in each division – enough that you have flexibility, but not so many that you overwhelm the faculty reviewers. Don’t include more finalists than really fit in that category.

• Provide the applications for the finalists to the faculty reviewers for additional opinions. Schedule yourself so that you can give them at least 4-5 days to look at them and provide feedback, and the committee still has a few days for a final meeting to discuss the faculty input and make the final decisions.

• Make the decisions on winners. 6 winners, 2 per division, plus a 7th floating award not assigned to a particular division (if it is warranted by stiff competition, and often given to the division either with the most applicants or where the decision is the hardest). One honorable mention has been given before – if given, they do not get the money, but they get a certificate and get invited to the luncheon.

April

• Email all applicants – winners and not.

• Get RSVPs from all luncheon attendees – faculty reviewers, deans, GSO executive committee, Teaching Awards committee, winners (no family – often asked about this, but we don’t have the budget for it), honorable mention if applicable.

• Get winner info to the A&S GSO Administrative Assistant so she can print certificates (and purchase sleeves for them, usually out of the ``general'' GSO budget) and start the process to give them their award money.

• Share winner info with faculty reviewers, graduate secretaries for the appropriate departments, Teaching Times, CIDDE, and anyone else who expressed interest in the information during the advertisement process.

• Luncheon – and celebrate!

Accept Nominations

Use Google Drive and the barangeraward account to create a nomination form. Forms from past years are on there as examples. Make the form open, so that it can accept submissions. Link to the nomination form in emails, advertisements, and the general A&S GSO site. After the deadline, close the form so it will not accept submissions.

Optional: Use a service, like that provided by tinyurl.com, to shorten the URL needed to access the application form. E.g., to http://tinyurl.com/gradexpo2013

Condense Nominations and Remove Duplicates

The Google Drive form will create a spreadsheet with nomination information. Someone should go through it and remove duplicates by, for example:

1. In the ``data'' directory, create a new directory for the current year. All data files should be saved there.

2. Using Google Drive, export the information to an .xlsx file (or equivalent).

   • Avoid modifying the original Drive document. That's why we export immediately.
   • Call this something like ``01-2015-nominations.xlsx''.

3. Open up the exported file in, for example, Microsoft Excel.

4. Sort by nominees' family name, then by given name.

5. Delete rows and remove duplicates manually.

   • Be sure to consider nicknames or that nominators may misspell things.
   • Using the email address field can be another way to identify duplicates.
   • Sometimes it is necessary to call up department's and verify information.
   • Committee members can later examine the original document to see what nominators say about the nominee. This feedback is useful to break ties.

6. The application generation scripts do not require all the information seen in the nomination form and they do need some extra information added.

   • Therefore, we will delete some unnecessary columns and add a few others.

7. Save the document to a new file (e.g., ``01-2015-nominations-dedup.csv'') then do the following:

   1. Create a new column before all the others, called ``Ready to send''

      • This will mark whether we want to send an email to that person or not.

   2. For all nominees, set their ready to send value to 1.

   3. Rearrange the spreadsheet to make sure that:

      1. Ready-to-send is column 0 (i.e., ``A'' in Excel).
      2. First name is column 1 (i.e., ``B'').
      3. Last name is column 2.
      4. Email address is column 3.
      5. Department is column 4.

      Regardless of the above, make sure that these column definitions match those at the top of gmail_imap/deduplicatedCSVToApplicationSQL.py, as this will be used in the next step.

Create Applications

This step requires Python 2.7ish. This is typically available on Linux machines. It also requires some basic Python coding knowledge.

Generating Application IDs

Open gmail_imap/deduplicatedCSVToApplicationSQL.py and update the line near the top that says something like:

YEAR = '2015'

Make sure this is a string.

Also, check/update the base URL for the application pages as necessary. Just look for the line that starts with:

url = 'http://...'

Once everything is set, run the following command (changing the filename for the data to match your filename).

# Generate application ID information and write out the SQL commands. The SQL
# commands will go to a file called injections.sql
./gmail_imap/deduplicatedCSVToApplicationSQL.py data/2015/01-2015-nominations-dedup.csv

That command will create two files in data/2015, with names like:

• data/2015/02-2015-nomination-listing.csv
• data/2015/02-2015-injections.sql

Examine the first file in Excel (using File, Open, View all Files) to make sure it is 100% correct. You will notice that there is now an appID column and a full Personal Application URL column.

Populating the Database

If you can run the above command and the "nomination listing" file is correct, you should be able to populate the database now.

You will need to install MySQL Workbench on your machine.

1. Make sure your Pitt account has access to the A&S-GSO MySQL database. CSSD can give permissions. Normal committee members do not need access, only those populating the database. If you don't have access, the Administrative Assistant should send a request to [email protected] to add your account to:

   • The AS-ASGSOWebsite Access CDS group.
   • The Firewall-CSSD-SSLVPN-EWI-Departmental-MySql-Server-DB-Access-NetworkConnect firewall zone.

2. Disconnect any VPN clients already running.

3. Connect to Pitt's VPN using your preferred VPN client, or go to sremote.pitt.edu, and enter your Pitt username and password, with connection method as "Network Connect" (the default). Log in.

4. If using sremote, install any .jar files you are prompted to install.

5. Once connected, you should see a list all of the roles you have access to. Select Firewall-CSSD-SSLVPN-EWI-Departmental-MySql-Server-DB-Access-NetworkConnect

6. Click on the start button for "Network Connect" on the webpage.

7. If prompted to install ncLinuxApp.jar, do so. Eventually, an applet window will pop up.

8. Now you are ready to try to login.

9. Once you are connected to the proper VPN role, use MySQL Workbench to connect to:

   • Host: ewi-mysql-02.cssd.pitt.edu
   • Username: asgsouser
   • Password:
     • Only users with database access need to know this
     • It is stored in the private data repository on Bitbucket

10. Copy all information from last year applicants to a new database table, and empty the main table. In the Query Window, enter and execute the following queries:

USE asgso;
-- create new table to archive last year's records
CREATE TABLE 2014teachingawardstudent LIKE teachingawardstudent;
-- copy last year's records into the archive table
INSERT 2014teachingawardstudent SELECT * FROM teachingawardstudent;
-- empty the main table for the new year
TRUNCATE teachingawardstudent;

11. Copy and paste the contents of the injections.sql file generated earlier and execute them. You should now have a fully populated database table to begin accepting applications for the new year.

Updating the Application Form

Next, make sure the application form is ready to go for the new year. Modify the teachingawards/application.php file in the A&S-GSO website as necessary. The current year should automatically be brought into the script and certain elements of the form (like the cutoff date for OMETs) are automatically computed accordingly, but you may need to make other modifications if, for example, the requirements or any contact information have changed.

Make sure to activate the form by setting the following variable at the top:

$applicationProcessClosed = false;

Since Pitt's Unix server does not support Git (as of 2015), you will need to submit your changes via FTP:

• Host: ewi-prod.cssd.pitt.edu
• Username: [Your Pitt username]
• Password: [Your Pitt password] You will need to be connected to the Firewall-CSSD-SSLVPN-EWI-Departmental-MySql-Server-DB-Access-NetworkConnect firewall zone as before, but your access is handled by your membership in the AS-ASGSOWebsite Access CDS group. Once logged in, you can find the A&S-GSO website located in the /asgso directory, alongside other sites you may administer.

Once you've uploaded the new teachingawards/application.php file, the form is ready to go.

Sending Emails

There is a command to send applicants information (e.g., that they were nominated, their application link). Emails will be sent to every person who has a 1 in their ready-to-send column. Therefore, you may edit the .csv to have 0s in most people's ready-to-send columns (i.e., if you need to resend the information to a particular person and not resend to all the others).

Double-check that the columns in nomination-listing.csv match up with the definitions in gmail_imap/sendNominationEmails.py under the line which reads:

if __name__ == '__main__':

Be sure to proofread the email text in gmail_imap/sendNominationEmails.py to make sure that it will send out the appropriate dates, etc. I recommend creating your own little dummy nomination-listing-DUMMY.csv, putting only one dummy nominee's name in it (i.e., yourself) and seeing if you receive the email, that all dates are correct, etc.:

# Run a dummy test to see if everything is working
./gmail_imap/sendNominationEmails.py data/2015/02-2015-nomination-listing-DUMMY.csv

This script will login to Gmail, generate emails from the dummy list you provided, and send them. You can retrieve the password from the private data repository on Bitbucket.

When you run this test, there's a fairly good chance Gmail will raise an authentication error of some sort. Gmail try to implement various security measures and these change every year, making it difficult to use "simple authentication" methods such as the basic password entry we use in our scripts. Follow the instructions in the error message you receive at the command prompt to resolve this issue. As of February 2015, this required navigating to https://www.google.com/settings/security/lesssecureapps while logged in and turning on "access for less secure apps".

Once everything is working, run the script on the real nomination-listing.csv file:

# Use the filename from the immediately previous substep.
./gmail_imap/sendNominationEmails.py data/2015/02-2015-nomination-listing.csv

As the script is running, you can login to Gmail manually and examine its Sent folder to see the sent emails as they go through.

If there are a large number of notifications to be sent (more than about 75), you may encounter a "too many login attempts" error from Gmail. If this happens, determine which email was the last to be successfully sent, and create a "continuation" file from the original nomination list by removing the lines which were successfully sent. Wait 10 to 15 minutes before rerunning the notification script on this "continuation" file.

Optional: Midway through the application process, maybe resend notifications to everyone. Edit the file to let them know that there's X weeks left or whatever.

Close Applications

When the deadline for applications has passed, close the application form by editing teachingawards/application.php in the A&S-GSO website, setting the following variable at the top:

$applicationProcessClosed = true;

Prepare Review Material

Once the applications are closed, you will want to compile all of the documents for the reviewers to look at.

Organize Recommendation Emails

Letters of recommendation that have been emailed to the barangeraward email account need to be sorted using Gmail's labels. You must be consistent. It is recommended you use the format:

year/lastName

That is, create a label for the year of the award and a sublabel for the student's last name. For example, if it is the 2014-2015 academic year and you received a letter of recommendation for Bruce Wayne, label it:

2015/Wayne

Once you decide on a format to label the recommendation letters, you must change the file create_reviews/main.py, around line 15, where it starts with GMAIL_LABEL_FORMAT = . Even if you stick with the year/lastName format, you will need to modify the file here to change the current year.

What if two people have the same last name, e.g. ''Bruce Wayne'' and ''Pauline Wayne''? Give them different labels, such as 2015/WayneB and 2015/WayneP. You let everyone else have the standard label (e.g. 2015/Kent for ''Clark Kent'').

Install Dependencies

Before running these scripts, you must install pip, a Python package manager. Then, from the create_reviews directory, install the necessary dependencies on your system by running:

pip install --user -r requirements.txt

Check Settings

Before creating the reviews, we need to check a few settings. In the create_reviews directory, run:

python main.py -h

You should see output like:

usage: main.py [-h] [--email EMAIL] [--password PASSWORD]
               [--dbhostname DBHOSTNAME] [--dbusername DBUSERNAME]
               [--dbpassword DBPASSWORD] [--dbdatabase DBDATABASE]

optional arguments:
  -h, --help            show this help message and exit
  --email EMAIL, -e EMAIL
                        Username of the email address the letters of
                        recommendation are sent to. (default: barangeraward)
  --password PASSWORD, -p PASSWORD
                        Password for the email address the letters of
                        recommendation are sent to. (default: None)
  --dbhostname DBHOSTNAME
                        Hostname of the database (default: ewi-
                        mysql-02.cssd.pitt.edu)
  --dbusername DBUSERNAME
                        Username for the database (default: asgsouser)
  --dbpassword DBPASSWORD
                        Password for the database (default: None)
  --dbdatabase DBDATABASE
                        Database to use (default: asgso)

What is of interest to us are the defaults. Are those values still the correct email and database values? If they need permanent changing, modify the default values in create_reviews/main.py under the line which reads:

if __name__ == '__main__':

Otherwise, we can simply override any of these values with an extra flag at runtime. For example, if the letters of recommendation were sent to otherusername, append --email otherusername when running the main program below.

Create Application Reviews

To create the application reviews, you will need to connect to the VPN with the Firewall-CSSD-SSLVPN-EWI-Departmental-MySql-Server-DB-Access-NetworkConnect role as described above.

Since generating the review files is resource-intensive, the following line near the bottom of create_reviews/main.py may be commented out:

# UNCOMMENT THE LINE BELOW WHEN READY TO GENERATE THE REVIEW FILES
main(args.dbhostname, args.dbusername, args.dbpassword, args.dbdatabase, args.email, args.password)

While it's probably a good idea to try running this script once first without actually generating the files (to ensure all of the dependencies are properly installed), make sure this line is uncommented when you're ready to go for real.

Once everything's set, in the create_reviews directory, run:

python main.py --password "EMAIL_PASSWORD" --dbpassword "DB_PASSWORD"

where EMAIL_PASSWORD and DB_PASSWORD are the email and database passwords, respectively, available from the private data repository on Bitbucket. Be sure to keep the surrounding quotation marks, especially if either password contains special characters. If any additional flags were deemed necessary above, be sure to pass those in as well.

At this point, the create_reviews/main.py script will log into the Gmail account and download the letters of recommendation. It'll also go to the teaching award database and download the saved information. It'll then try to match up the letters to the information from the database. As it does this, it'll print out its progress (telling you which applicant it's on).

If you see the following, that means it was unable to find the Gmail label for the particular applicant automatically:

Gmail label not found for Wayne, Bruce (expected '2015/Wayne').
Please enter true Gmail label (or blank if none): 

In the example above, it is asking you to enter the Gmail label for Bruce Wayne because it couldn't find the label automatically. This could happen because Bruce Wayne may have used a different last name in the application than you used in the Gmail label. It may also have happened because two people had the same last name (e.g. "Bruce Wayne" and "Pauline Wayne") and so you gave both of them different labels.

Log in to the Gmail account, find the appropriate label, and enter the full label name into the program. For the example above, you may have decided to add the first initial (i.e. ''WayneB''), so enter 2015/WayneB.

When the program completes, the reviews will be a folder like baranger-applications-2015 within the create_reviews folder. Open the index.html file and go through each review to make sure things look right. In particular, check that the letters of recommendation are correct! If something's wrong, you can manually make changes to the html file and/or delete things by going into the student's subfolder (the folder names are based on the applicants' names).

Once you've checked everything, zip up the entire folder. Be sure to use the .zip format since most reviewers will be familiar with it. Since the file will be quite large, upload the zip file to Box and set the sharing permissions such that anyone with the link can access the file. Be sure to delete the zip file once the winners have been selected. You can easily do this by setting the "link expiration" date to April 30.

Review Applications

Obtain a dedicated group of volunteer reviewers to review the applications. Each application should be reviewed by 2 or 3 student reviewers and at least 1 faculty reviewer.

Upon sharing the zipped applications with reviewers, each reviewer should be semi-randomly assigned a particular set of applications to review, such that enough reviewers are assigned to each application. While reviewers should be assigned applications from similar fields within their own academic division (humanities, social sciences, natural sciences), they should never review applications from their own department. (TODO: Automate this process, and hook it into the database so people can review directly from the web instead of using the zip file.)

Reviewers should make use of a rubric to score each of their assigned applications and submit their scores to the Committee. The Committee should then compile these scores from all reviewers and run statistics against them. (TODO: This could also be automated.)

Afterwards, it is the responsibility of the Committee to make the final selection of the best two candidates from each academic division, assisted by (but not bound by) the compiled reviewer scores. In addition to these six awards, the Committee may opt to issue a seventh award to the third-place candidate in any one division, subject to budgetary constraints. Honorable mentions may also be given to any other close contenders; these carry no cash prize, but typically include an invitation to the luncheon.

Notify Applicants of Results

The review material generator above will have created a CSV file named something like baranger-applicants-2015.csv in the root directory of the zip folder which contains a list of all nominees who submitted applications. (We don't really want to notify non-applicants, for instance, that they didn't win.) Copy this file into data/2015, and manually break it apart into three files: One each for winners, honorable mentions, and nonwinners. Be sure to keep the header line in each file.

• data/2015/03-2015-post-notification-1-winners.csv
• data/2015/03-2015-post-notification-2-honorable.csv
• data/2015/03-2015-post-notification-3-nonwinners.csv

Like when sending the initial emails, double-check that the columns in these files match up with the definitions in the Python scripts under the line which reads:

if __name__ == '__main__':

Update the email text in each script to be sure to send out the right details for the luncheon, etc. Take particular note of the amount of the cash prize, as well as the total number of awards given. Don't forget to change the years in both the text and the subject.

Since we really don't want to send false congratulations to nonwinners, for example, consider running a script or two against a dummy list with just yourself, as before. Once you're confident with everything, VERY CAREFULLY run each script once on its corresponding data file:

./gmail_imap/sendPostEmail1winners.py data/2015/03-2015-post-notification-1-winners.csv
./gmail_imap/sendPostEmail2honorable.py data/2015/03-2015-post-notification-2-honorable.csv
./gmail_imap/sendPostEmail3nonwinners.py data/2015/03-2015-post-notification-3-nonwinners.csv

Again, BE VERY CAREFUL to map each script to its data file correctly. All of the other caveats pertaining to dealing with Gmail also apply here, just as before. (TODO: Combine these scripts and files to have one script send all three types of notification.)

After these notifications are sent, post the winners and honorable mentions to the teaching awards page of the A&S-GSO website. Continue checking the Gmail account for further correspondence until about a week after the awards luncheon. In particular, luncheon invitees will typically respond to this address.

About This Document

This guide is written in markdown and generated using Pandoc. Pandoc is available for many operating systems. A command like the following will build the PDF guide from the original .md source.

pandoc --table-of-contents -o README.pdf README.md

The doc-gen.sh shell script is provided as a wrapper for this command.

Pandoc also supports HTML output, among others.