Set up Lead and Scores Upload from Spreadsheet

About Lead and Scores Upload from Spreadsheet

This Campus Cafe function is used for two purposes.

First, the Campus Cafe system can accept a spreadsheet containing information on admissions leads. The system will create new inquiry records for individuals on the spreadsheet without existing records in Campus Cafe. Existing records will not be updated. The system may be configured to map each column in the spreadsheet to a data element in Campus Cafe. This provides institutions the flexibility to upload spreadsheets of different formats/layouts.

Second and separately, the Campus Cafe system can accept a spreadsheet containing scores from tests (e.g. ACT or SAT) for new or existing leads, applicants or students. The system may be configured to map each column in the spreadsheet to a data element in Campus Cafe. This provides institutions the flexibility to upload spreadsheets of different formats/layouts such as from the College Board's SAT file or the ACT score file. For any individual on the spreadsheet the system cannot match to an existing person record in Campus Cafe the system will create a new record for the individual.

Prerequisites for Uploading Leads

• Recommended that the semester the inquiries plan to attend are established in Campus Cafe under STParms.

Prerequisites for Uploading Scores

• Required that score headers (test types) be configured

Matching Records to Existing Leads/Applicants/Students

Upon upload, Campus Cafe will attempt to match individuals in the spreadsheet to existing person records within Campus Cafe. 

Upon a match with a lead, applicant or student, biographical information will not be updated or added.

Upon a match with a lead, applicant or student, scores will be added to the person's record. Scores for individuals who do not exist in Campus Cafe will have a person record created and scores added. For records the system identifies as possible matches, the system will email a list to the email addresses entered into the field at the top of the Scores Upload page. Conflicting data can be changed in Campus Cafe or on the spreadsheet to match and the entire spreadsheet, or just the possible matches, reuploaded. 

Score records that match on the below will automatically be added to existing people in Campus Cafe

• Match on Social Security Number
• Match on first name, last name and date of birth
• Match on first name, first 8 positions of street address and city
• Match on email address and date of birth
• Match on first name, last name, email address, and home or mobile phone

The holding table is not used in this process.

File Format

The file should contain a header (first) row with information describing the data in each column. The column headers (descriptions) may be anything.

The file must be saved in .xlsx or .xls (Excel). A .csv (comma separated value) format is not supported.

Configure Column Mapping for Leads and/or Scores Upload

Campus Café must be configured to identify what data element appears in what column of the spreadsheet. Saved mappings are available to all users with access to this page and to upload scores. 

Permission #1254 is required to access this function.

1. Navigate to Admissions > Lead and Scores Upload Mappings
2. Click Add
   Mapping Name: Descriptive text to identify the spreadsheet layout. Required.
   Description: Additional text to identify the spreadsheet layout. Required.
   
   Unless otherwise specified, each box represents the column number where the data element appears in the spreadsheet. For example, if the individual’s first name appears in the second column, enter a 2 in the first name box.
   First Name: Individual’s first name, typically legal name. Required.
   Last Name: Individual’s last name, typically legal name. Required.
   Middle Name: Individual’s middle name, typically legal name. Optional.
   Birth Date: Individual’s birthday. Set the format in Birth Date Format. Optional but recommend to assist with matching existing records.
   Birth Date Format: How the date of birth is stored in the spreadsheet. For example, if the birthday appears as 10091985 with 10 (October) being the month, 09 being the day and 1985 being the year, then choose MMDDYYYY. Required if uploading birthday, otherwise optional.
   Social Security Number: Individual’s Social Security Number. Optional.
   Gender: Individual’s gender. In the spreadsheet Male or M indicates male; a Female or F indicates female. Only male and female genders are supported. Optional.
   Address 1: Individual’s home street address. Optional.
   Address 2: Individual’s home street address second line. Optional.
   City: Individual’s home address city. Optional.
   State: Individual’s home address state. Must be valid two-character state abbreviation (e.g. RI). Optional.
   Zip: Individual’s Zip code. Optional.
   Zip Ext: Individual’s Zip extension. Optional.
   Country: The individual’s address country. The country must appear in the file as the Campus Café country code as set using STPARM XXXXCN. Note Campus Café codes as delivered are not standard ISO codes. Optional.
   Area Code: If the spreadsheet stores the individual’s phone area code in its own field, the column number where the area code appears. If the spreadsheet stores the entire phone number in one field leave this as 0. Optional.
   Exchange: If the spreadsheet stores the individual’s phone exchange in its own field, the column number where the exchange appears. If the spreadsheet stores the entire phone number in one field leave this at 0. Optional.
   Phone Line Number or Full Number: If the spreadsheet stores the individual’s line/subscriber number as its own field, the column number where that appears. If the spreadsheet stores the entire phone number in one field, the column number where the entire phone number appears. The phone number may appear in the spreadsheet in any of the following formats: 401-555-1234 or 4015551234 or (401) 555-1234. Optional.
   Phone Type: Individual’s phone number type. In the spreadsheet HOME indicates a residential phone number that will be stored in Campus Cafe with the home address. Anything else in the spreadsheet will associate the number with the individual's mobile (cell) phone in Campus Cafe. If mapped to 0, meaning not mapped to a column in the spreadsheet, the phone number will be associated with the individual's mobile (cell) phone. Optional.
   Email: The individual’s email. Optional.
   Admissions Site: The individual’s Campus Café admissions site code. Optional.
   High School Code: The Campus Café School ID number of the individual’s high school. Note, the school ID codes as delivered are proprietary. Optional.
   High School Grad Year: The individual’s high school year of graduation. Must be exactly four digits. (e.g. 2008). Optional.
   Inquiry Current Grade Level: The individual's current grade, typically high school class (sophomore, junior or senior). Only supported values are "10th grade", "11th grade" and "12th grade". Note the process supports uploading the expected high school graduation year using the High School Grad Year Field, which allows you to determine the student's high school grade level. Optional.
   International Student: In the spreadsheet, a Y indicates an international student as shown on the individual’s Bios screen. Otherwise leave blank. Optional.
   Inquiry Major: The Campus Café major code for which the individual has expressed interest. Optional.
   Transfer Code: In the spreadsheet, a H indicates a high school graduate; a G a GED completer; a C an individual with prior college experience (transfer student). This is shown on the individual’s inquiry detail screen and applicant detail screen and used in delivered IPEDS reports. Optional.
   Is Hispanic: In the spreadsheet, a Y indicates an individual of Hispanic origins. A N indicates not of Hispanic origin. Shown on the individual’s Bios screen and used in delivered IPEDS reports. Optional.
   Race: In the spreadsheet, a number corresponding to the IPEDS ethnic category. 1 indicates White; 2 indicates Black; 3 indicates Asian; 4 indicates American Indian/Alaska Native; 5 indicates Hawaiian. Use in lieu of White, Black, Asian, American Indian/Alaska Native and Hawaiian fields. Optional.
   White: In the spreadsheet, a Y indicates White. Use in lieu of Race field. Optional.
   Black: In the spreadsheet, a Y indicates Black. Use in lieu of Race field. Optional.
   Asian: In the spreadsheet, a Y indicates Asian. Use in lieu of Race field. Optional.
   American Indian/Alaska: In the spreadsheet, a Y indicates White. Use in lieu of Race field. Optional.
   Hawaiian: In the spreadsheet, a Y indicates White. Use in lieu of Race field. Optional.
   
   Score Sets 1-8
   A file may contain eight sets of scores provided they all belong to the same test type. For example, you may bring in the SAT (test type) with individual scores for reading, writing and math.
   Score 1 Name: The specific score (e.g. reading)
   Score 1: The column number where the score 1 resides.
   Score Perc 1: The column number where the score 1 percentile resides.
   Official Date: The column where the date of the score for the score set resides. This is typically the date the test was administered. Note: If a separate date is needed for each score being uploaded, use a new Score Set for each one (1, 2, 3, etc.). If the Official Date is the same for all scores, use a Single Score Set.
   Official Date Format: How the score date is stored in the spreadsheet. For example, if score appears as 10091985 with 10 (October) being the month, 09 being the day and 1985 being the year, then choose MMDDYYYY. For a file that contains only a month and year choose MMYYYY. The system will assign the score the date of the first of that month and year. The official date format is used for all scores and score sets.
3. Click Save