College Match Application Specs and Tasks

College Match Application Launch

This page covers specifications and tasks for the College Match App launch (Aug 15).

Milestones

College Match App Specifications

The College Match Application will extend the Pre-application feature set and schema with:

• transcript input formCollege Match Application Specs and Tasks - CRM - CiviCRM Wiki
• additional essay questions
• additional fields conditional on which colleCollege Match Application Specs and Tasks - CRM - CiviCRM Wikige(s) the student is applying to
• registering recommenders and tracking task status
• ability to print/view application/recommendation. ideally they should be able to print out the pdf version of their completed application/recommendation.

College Match Application (changes from the Preapp application) 

The College Match Application builds upon the Preapp application. It extends the data model by changing and adding to the model.* Personal Information

• • Added: Upload picture. Upload file into contact's Documents.
  • Removed: the 'Citizenship' options displayed in the Scholarship application
  • Added: 'Citizenship' options: US Citizen, US/Dual Citizen, Permenant Resident, Non-citizen. 
    • Added: If 'US/Dual Citizen', then show question regarding what other country the student is a citizen of.
  • Removed: 'Race/Ethnicity' options.
  • Added: 'Race/Ethinicity' options: 'African America, Black', 'Native American, Alaska Native', 'Asian American', 'Asian, including Indian subcontinent', 'Hispanic, Latino', 'Mexican American, Chicano', 'Native Hawaiian, Pacific Islander', 'Puerto Rican', 'White or Caucasian', 'Other', 'Decline to State'
  • Removed: 'Number of Siblings'
  • Added: 'Number of Siblings' drop-down option w/ max value of 10.
• Additional Information
  • Removed: Ranking scholarships
  • Removed: 'Do you plan to take the SAT or ACT?'
  • Added: 'Did you study for the SAT or ACT? '
  • Added: Question about displacement from Katrina or Rita.
  • Added: Question about how they heard about QB
  • Added: Question regarding 'dismissed from school' and 'ever been arrested'.
  • Note: The 3 "Referrals" will need to be flagged in some way to indicate whether the Student added them in the Preapplication or College Match application.
• Educational Interests
  • Removed: Question about partner colleges and other colleges
• Household Information
  • Added: Foster care question.
• Parent/Guardian Detail
  • Added: 'Do you have contact with this person', 'Is this guardian an US citizen', 'Place of birth', 'Country of birth'
  • Added: Permanent Address & Telephone fields, same fields as in 'Personal Information'
• Sibling Information
• Household Income
  • Added: Display total sum for income(s) entered on page.
• High School Information
  • Moved: 'Other school information' within 'High School Information' (Quest: might want to rename 'High School Info' to 'School Info' which then has 'High School Info' and 'Other School Info')
  • Added: School Search pop-up
  • Changed: Using School Type options from CEEB data (as values for custom_2): 'Public', 'Independent, Not Religious', 'Independent, Catholic', 'Other Independent, Religious', 'Home school association', 'Charter', 'Correspondence', 'Other'.
• Other School Information
• Academic Information
  • Moved: 'Academic Information', 'Testing', 'Transcript' into 'Academic Information'
  • Added: Unweighted GPA.
  • Added: Weighted GPA
  • Added: 'Does your school give ranking'
  • Added: Grading System and explain Grading system
• Transcript
  • Added: New section for entering transcript information
• Testing
  • Removed: PLAN, PSAT.
  • Added: for SAT and ACT, students can enter up to 3 scores.
  • New quest_student_summary table will store calculated highest value for each section and composite consisting of total of highest achieved for each section. We will store 2 composites for the SAT's: SAT_composite_all (reading, math, writing sections) and SAT_composite (math and reading only). All student_summary fields need to be searchable and exportable.
  • Added: If the applicant won the SAT prep scholarship, ask questions regarding test performance after studying for SATs using Princeton Review material. Whether an applicant won the SAT prep scholarship is stored as custom data. The custom data group 'College Prep Scholarship 2006' has a custom field called 'Test Prep Scholarship'. If the value of 'Test Prep Scholarship' is 'Winner' then the student won the SAT prep scholarship.
  • Added: Questions regarding if the applicant plans to take tests in the near future.
• Extracurricular Information
  • Added: New section for entering 'Extracurricular Information' and 'Work Experience'
• Essays
  • Added: New section for essays
• Recommendations
  • Added: New section for registering recommenders
• College Match Ranking
  • Added: New section for ranking colleges
• Forward Application
  • Added: New section regarding forwarding the applicant's information to colleges/scholarship opps.
• Partner Supplement
  • Added: New section w/ partner college specific questions.
• NCP Waiver
  • Added: New section w/ link to download NCP waiver form.

Transcript input form(s)

See mockups: http://www.questbridge.org/app_staging/cm/appl/transcripts.html

Transcript: Computing GPA

• For applicants that receive 'A-B-C-D' grades
  • Compute both weighted and unweighted GPA.
  • Do not compute GPA for '12th grade'
  • Computing weighted GPA
    • For each course entered, compute the grade points. Grade points are computed by multiplying the units/credits with the grade.
      • If the course has a honor status, then the values for letter grades is: A+/A= 5, A-=4.7, B+= 4.3, B=4, B-=3.7, C+=3.3, C=3, C-=2.7, D+=2.3, D=2, D-=1.7, F=0

• • • • If the course DOES NOT have a honor status, then the values for letter grades is: A+/A= 4, A-=3.7, B+= 3.3, B=3, B-=2.7, C+=2.3, C=2, C-=1.7, D+=1.3, D=1, D-=0.7, F=0
    • To compute the GPA, take the sum of grade points and divide it by the total credit. Round to nearest 10th - rounding up if hundreds value is 5 or higher.
  • Computing unweighted GPA
    • For each course entered, compute the grade points. Grade points are computed by multiplying the units/credits with the grade.
    • The value for letter grades is: A+/A= 4, A-=3.7, B+= 3.3, B=3, B-=2.7, C+=2.3, C=2, C-=1.7, D+=1.3, D=1, D-=.7, F=0
    • To compute the GPA, take the sum of grade points and divide it by the total credit. Round to nearest 10th - rounding up if hundreds value is 5 or higher.
• For application that are on an 'alternative' grading system (non 'A-B-C-D')
  • Do not compute weighted and unweighted GPA.
  • Student data export should have a field that 'says' the student is on an 'alternative' grading system

Additional app page(s) and changes from Preapp model

(e.g. essays, etc.)

Student tasks (requirements)

Recommender Module Specifications

• Applicant (student) enters two teachers and one counselor in the Recommendations section and submits form.
• For each of the 3 entities, application code checks for duplicate contacts on Email address. (NOTE: We do NOT use School field from this form for duplicate match - as some counselors (and possibly teachers) may work at multiple schools.)
• If no match:
  • Create a new contact record. (Make sure a hash (civicrm_contact.hash) is populated for each inserted contact.)
  • Assign contact record to "Recommenders" (CiviCRM) group.
  • Create relationship "Teacher / Student" OR "Counselor / Student" between new contact and student.
  • Create relationship "Teacher / School" between new contact and related high school org contact (identified by drop-down on recommender form).
  • Create Drupal user account. Use contact email as Username (users.name) and Email (users.mail). Set verified status to FALSE (Paras is researching the correct way to set NOT VERIFIED status). Assign to Recommender role.
  • Create uf_match record (links user to contact).
• If match:
  • Create relationship "Teacher / Student" OR "Counselor / Student" between matching contact and student.
  • Check for existing relationship "Teacher / School" between contact and related high school org contact and create if it doesn't exist.
• Create Task Status record for task Submit Recommendation (child task of College Match Application)
  • Responsible entity is teacher or counselor. Target entity is student. Initial status is Not Started.
• Send templated email to each teacher/counselor contact with text requesting them to complete the Recommendation form for the student.
  • If this teacher/counselor is a new contact, OR if it's an existing contact AND the associated Drupal User is NOT VERIFIED... email contains a link to a custom Verify Registration" form (see next bullet). This link includes contact hash value (h=) and an MD5 encrypted copy of their email address (m=).
  • If the teacher/counselor contact record already exists AND the associated Drupal User is VERIFIED...email contains a link to Drupal account login page.
• Custom Verify Registration function:
  • Passes hash (h=) and email MD5 (m=) from GET params to a new API - crm_get_user_by_hash.
  • This API decrypts MD5 of email address and verifies the found contact's primary email address. If hash and email are valid, API returns the Drupal Users.id value. Else it returns 0.
  • If we have a valid user - prompt them to set Password for this account (entered twice)
  • On submit, set user password and set user status to VERIFIED.
  • On submit, add user to appropriate group (teacher, counselor, and recommender).
  • Redirect to Recommender Locker.
• Subsequent successful logins also redirects Recommenders to their version of the Locker (based on assigned Role).
  • Locker includes links to Recommendation forms for each related student (see locker mockup).
  • Locker retrieves listing of students whom this Recommender is responsible for, and the current status of each Recommendation by calling a new version of the task status API. API takes Submit Recommendation task id and teacher/counselor contact id - and returns an array of Student Name + Last Edit Date (task_status.modified_date) + Current Status.
• Task status for a given Recommendation is updated to In Progress when the first form section is submitted successfully. Task status modified date is updated for each successful section submit. Status set to Completed when all sections of Recommendation are complete.
• A student may decide to change Recommenders as long as the recommendation status from that person is Not Started or In Progress.
  • When a recommender is "replaced", we follow the steps above for creating/updating recommender tables AND set the task_status for the "removed" recommender to Cancelled (NOTE: we need a new status option value for this). We also need to email the 'replaced' recommender regarding the status change.
• Batch emails (if possible, otherwise Quest can run a script to get the DB dump and write a script to email the recommenders)
  • Send out batch emails to recommenders who are registered but haven't verified in over 3 weeks.
  • Send out batch emails to recommenders a week before the recommendations are due. The email should be sent to 'Not verified' and 'Verified' recommenders, limiting 'Verified' recommenders to individuals who have recommendations in 'Not Started' and 'In Progress' status.

Data Migration

• All pre-app data will be migrated to new schema, with existing data retained.
• Quest (Paras) will update High School records (contact_organization) with the matching CEEB code using a script he's developed. CiviCRM will then merge High Schools and corresponding school<-->student relationships based on CEEB code matches.
  • Mapping High School 'school types'
    • For schools that have a CEEB code, use the CEEB data values for 'school type'. In other words, override what the student entered.
    • For schools that don't have a CEEB code, map the student entered values to CEEB 'school type' list using the following mapping scheme:
      • 'Public' (310) maps to 'public' (A)
      • 'Private' (311) maps to 'independent, not religious' (B)
      • 'Parochial' (312) maps to 'other independent, religious' (D)
      • The new label to code mappings are (not needed for migration, but documented here):
        '' (blank) = no response
        'A' = public
        'B' = independent, not religious
        'C' = independent, catholic
        'D' = other independent, religious
        'E' = home school association
        'F' = charter
        'G' = correspondence
        'H' = other
        'I' = education provider
• Essay records need to be migrated to new DB schema. Essay type_id will be 1. Contact_id field is migrated to both source_contact_id and target_contact_id in new Essay schema.
• quest_referral records need the application_id value populated to 348 (FK to civicrm_option_value - indicates that they came from the Preapplication). Also need to parse old 'name' column into first and last name used in new schema. New referral_type enum s/b set to 'Student' for all migrated records.

Student table (quest_student)

• quest_student.gpa_id => quest_student.gpa_unweighted_id
• The following columns have been moved from quest_student to quest_student_summary (if column name has changed, this is shown). Migrate the data to new table columns for each:
  • quest_student.score_SAT => quest_student_summary.SAT_composite_all
  • quest_student.score_ACT => quest_student_summary.ACT_composite
  • quest_student.score_PLAN => quest_student_summary.PLAN_composite
  • quest_student.score_PSAT => quest_student_summary.PSAT_composite
  • quest_student.household_income_total => quest_student_summary.household_income_total

• Copy quest_household.member_count WHERE household_type='Current' to the new quest_student_summary.household_member_count column

• convert ethnicity_1_id option_values to the new ones using mapping below
  • - 'American Indian/Alaska Native' (ethnicity_1_id) maps to 'Native American, Alaska Native'
    • 'Arabic/Middle Eastern' maps to 'Asian American'
    • 'Black/African-American' maps to 'African American, Black'
    • 'Chinese American' maps to 'Asian American'
    • 'Ethnic Hawaiian' maps to 'Native Hawaiian, Pacific Islander'
    • 'Filipino American' maps to 'Native Hawaiian, Pacific Islander'
    • 'Japanese American' maps to 'Asian American'
    • 'Korean American' maps to 'Asian American'
    • 'Latin American/Latino' maps to 'Hispanic, Latino'
    • 'Mexican American/Chicano' maps to 'Mexican American, Chicano'
    • 'Other Asian American' maps to 'Asian American'
    • 'Other Hispanic American' maps to 'Hispanic, Latino'
    • 'Puerto Rican American' maps to 'Puerto Rican'
    • 'South Asian American' maps to 'Asian, including Indian Subcontinent'
    • 'South Asian/Indian' maps to 'Asian, including Indian Subcontinent'
    • 'Vietnamese American' maps to 'Asian American'
    • 'White/Non-minority American' maps to 'White or Caucasian'
    • 'Other' maps to 'Other'
    • 'Decline to state' maps to 'Decline to state'

• map citizenship_status_id to new ones using mapping below:
  • - 'US/Dual Citizen' (citizenship_status_id) maps to 'US/Dual Citizen' (new values)
    • 'Permanent Resident' maps to 'Permanent Resident'
    • 'Non Citizen' maps to 'Non Citizen'

College Match Processing

Summary of requirements (spec TBD):

• Locker allows student to upload files
• "Reader" accounts created (self serve or by admin) and group created for each to segment permissions
• Admins can assign pools of students to "readers"
• Reader module ready (admin assigns x students to a "reader", reader assigns status - finalist, reject)
• Admin then needs to be able to segment students based on reader assigned status (vetting the decisions)

Locker APIs

Student locker 

• Get application and application section's status.
  • getTaskStatus( $sourceID, $targetID, $taskID ); 
    // returns Not Started/In Progress/Completed, last updated date, URL to edit task, for task
• Get status on recommendations.
  • getRecommendationStatus( $sourceID );  
    // given a sourceID, returns an array of - display_name, contact_id, status for all recommendations that the student has requested 
• Get URL for print/view of completed application

Recommender locker 

• Get list of recommendations requested and status on each recommendation
  • getRecommenderStudentInfo( $sourceID )
    // returns a array of
    //   - display_name, school_name, contact_id, recommendar_status, recommender_url
    // for all students that he/she needs to write a recommendation for 

• Get URL for print/view of completed recommendation

Additional issues

• Establish game plan for segmenting (archiving/retaining?) prior years applicant data.