Specific School Leads Import Process
Prospect Imports Must Have:
Original Start Date
New Start Date
Sic-Code
Lead Received Date (Only add this to new prospects, not on updates)
Person ID (email if non UTI school)
Default Phone mark “True” (whichever one has the most data)
Market
Non UTI Schools
- The Housing Consultant should put each school in a separate spreadsheet per school with headers and title the spreadsheets with the date.
- Once IT receives the file
- Save the file first as an xls format (NLC-MER 3-26-19.xls).
- Add column for Person ID after the “last name” column & copy email address into it.
- If the file does not contain a student number then copy the email address into the Person ID column
- If the file contains a student number then prefix the student number with the codes in the prefix file \\falcon\RentManager\Data Dumps\Live Data Dumps\Prospects\Imported\”Sic Code – Person ID Prefix Codes” This way they are always unique.
- Add column for Default Phone after the phone number columns. Fill each line with TRUE.
- Add column for New Start Date after Start Date & copy original start date into it.
- Cut and past the original start date and new start date columns close to the beginning of the file.
- Add column for Sic-Code at the end and put in the Sic-Code for the school.
- Add column for market at the end and put in the market of the school.
- Look at columns & format as necessary (proper case, phone numbers, & dates)
- Then save as a csv format to import from (NLC-MER 3-26-19.csv).
- Import into Rent Manager
- Do as an update first.
- Import without using template
- Update existing prospects by Person ID
- Add to Prospects
- If Adding … The default value is Collegiate Housing Services
- Next
- Select the csv file (click on the … button)
- Map the columns to Rent Manager:
- First Name-First Name
- Last Name-Last Name
- Person ID UDF Person ID
- Home Phone-Phone Number- Home
- Cell Phone-Phone Number -Cell
- Work Phone-Phone Number- Work
- Default Phone-pick whichever number has the most filled out
- Address-Street -Primary
- City-City- Primary
- State-State- Primary
- Zip-Postal- Primary
- Start Date (orig) – UDF Original State Date
- Start Date (new)- UDF New Start Date
- Admission Rep – UDF School Rep
- Email-Email
- Lead Received Date – UDF Lead Received Date (Do this only when adding new prospects).
- Military – UDF Military (T or F)
- Sic-Code – UDF App Sic-code
- Etc.
- Next
- Import
- Print and save to \\falcon\RentManager\Data Dumps\Live Data Dumps\Prospects\Imported
- This will be a file called Import Summary file.
- Edit the Import Summary file
- Select all fields and clear format
- Select all fields and order by the import results column
- This will basically put the leads that found no match at the top of the file. These are the ones that we will need to add via the next import.
- Now that we have the summary file in order with the “new” leads at the top we need to highlight and delete all the rows beneath the new leads.
- We now add a column “Leads Received Date” and enter today’s date. Copy this down for each row of these new leads.
- Save as csv. I don’t change the name.
- Import the new leads
- Rent Manager / Admin tab
- Import Prospects
- Add new prospects
- Next
- Select the file
- Map the following fields (Last Name, Person ID, Original Start Date, Lead Received Date).
- The reason we don’t need to map all of them is we will go back to the update tab and hit import again and it will populate all the information.
- Import
- Print and save the file
- Go back to the first update import tab that we left open and click the import button again.
- Print and save the file.
- Highlight all the files for this import, Cut and save them in the appropriate directory.
- I do this for documentation and to be able to look back at the process for any troubleshooting.
NOTE: When you are formatting field, you may need to clear formats first. For phone numbers, find & replace the following with blank/nothing: ( , ), -, space, ., & NA.
UTI All in One Import Instructions
- Open the file. Note number of rows.
- Add new folder to Q>RentManager>DataDumps>Live Data Dumps>Prospects> Imported\ . Name it UTI All in One Date (UTI All in One 3-25-19).
- Put file new folder.
- Save file in an xls format to work on (UTI All In One 8-12-22.xls).
- Save completed file in a csv format to import (UTI All In One 8-12-22.txt).
- Extract the data types of W
- Copy the data to new spreadsheet named UTI Withdrawals 3-25-19.
- Email Withdrawal spreadsheet to:
To: Traci Mason; (Collection & Assistant Supervisors?)
cc: Amanda Wiley; Shreeda; IT
Subject: UTI Withdrawals for – currentdate
Message: Here are the UTI withdrawals from this week’s import.
- Extract the data types of C and P
- Copy the data to new spreadsheet named UTI Cancels & Postpones 3-25-19.
- Email cancels & postpones spreadsheet to:
To: Ke’Shari, Teasia, Brionna
cc: Amanda Wiley; Shreeda; IT
Subject: UTI Cancels and Postponements – currentdate
Message: Here are the UTI cancels and postponements from this week’s import. NOTE: The Leads are uploaded.
6. Copy the data types of D and L
a. Copy the data to a new spreadsheet named UTI Deactivated & LOA’s 6-3-19.
b. Email Deactivated spreadsheet to:
i. Amanda; Shreeda; IT
ii. Subject: UTI Deactivated and LOA’s currentdate
iii. Here are the UTI Deactivated and LOA’s from this week’s import.
iv. Attach both files.
7. Prepare the file. See Notes section below.
8. Import Steps:
a. Update existing prospects.
b. Add new prospect that the update did not find (This time only add ‘E’ and ‘P’ and ‘A’) .
c. Update those with an actual new start date.
9. Import Prospects without a template. Check Update existing Prospects. Check Use the follow value from a UDF: Person ID. **This will update anyone who is already in Rent Manager and indicate who will need to be added. Only update original and new start 1 date. ** Leave the update screen open. You will reuse it. Don’t update Lead received Date.
10. Clean the results file from the update to get your list of prospects to add (only last name & person id, Lead Rec Date).
a. Print the summary file from step 7
b. Open in excel
c. Clear format for whole file
d. Delete first 3 lines
e. Sort by comment cell
f. Delete the following:
i. Success lines
ii. Extra lines
iii. Blank lines
iv. Blank columns
v. All Date Type other than E and P.
g. Save file as csv
h. Import file as Add to Prospects with default property as Collegiate Housing Services.
i. Map -> last name, person id, lead rec date
11. Import the original file again. This will update all the information for the students added in in step 8.
12. Import Person ID and New Start Date 2. This is only for students whose start date has changed (Postpones). Do for everyone but just map Person ID & New Start Date 2 for Rent Manager.
NOTES:
- Add Columns
- After Original Start Date for New Start Date 1 (Should have 3 start date columns; original, new start 1, & new start 2).
- After Campus Name for Sic-Code. Do a find & replace and replace the full name (UTI-Exton with short name (UTI-EX).
- Add a Market column and fill with the school market.
- After last phone number column for Default Phone. Fill with TRUE.
- At the end add Lead Received Date and fill with date of file.
- After a column for any that are not formatted correctly (ex: add address-proper after address column).
- Make the following formatting changes
- Gender-change M to Male & F to Female
- Marital Status-change U to Single. Will also need to change Married, Divorced, Widowed, & Separated
- Military-change true to T or false to F
- Check all names, address, city, & state for proper case
- Format all Dates to date format
- Phone numbers to phone format
- Make sure everyone has a Person ID
- Map the columns to Rent Manager:
- Data type – UDF Data Type
- Gender – UDF Gender
- Marital Status – UDF Marital Status
- Person ID – UDF Person ID
- First name-First Name
- Last name-Last Name
- Date of birth-Birthday
- Home Phone-Phone Number-Home
- Cell Phone-Phone Number-Cell
- Address-Street- Primary
- City-City- Primary
- State-State-Primary
- Zip Code-Postal-Primary
- Preferred method of contact – UDF Preferred method of contact
- Military – UDF Military
- Student enrollment date – UDF Student enrollment date
- Original start date – UDF Original Start Date
- New Start Date 1-UDF New Start Date
- New Start Date – UDF New Start Date (only as a update in step)
- Campus ID – UDF Campus ID
- Campus Name – UDF Campus Name
- Sic-Code – UDF App Sic-Code
- Rep Full Name – UDF School Rep
- FA Rep – UDF Financial Aid Rep
- Housing Status – UDF Housing Status
- Program Name – UDF App Major
- HS grad type – UDF HS Grad Type
- HS Graduation Date – UDF HS Grad Date
- Emergency Contact Name – UDF Emergency Contact Name
- Emergency Contact Phone – UDF Emergency Contact Phone
- Primary Email Address – UDF School Email
- Personal Email Address-Email
- Parents Email Address – UDF Prospect Parent Email
- Scholarship recipient – UDF UTI Scholarship recipient
- Save completed file in a csv format to import.
NLC All In One Import Procedure:
- Natural Gas Technician then the SIC–CODE has a suffix of XXX-XXX-GDP
- Telecommunications Line worker Program then the suffix is XXX-XXX-TEL
Default Phone cell
The 3 files are for the following sic codes correct?
Florida Campus = NLC-EDG – 780
Idaho Campus = NLC-MER – 320
Texas Campus = NLC-DTN – 300
Email:
To: ke’Shari Nowden; Teasia Allen, Brionna
cc: Amanda Wiley; Shreeda; IT
Subject: NLC All In One Leads Import 3-8-21
Message: NLC All In One leads have been imported.
– Existing leads are updated with the data in today’s file
– The leads that found no match on update are imported as new leads.
Rick
Sic code: CDI-PHX
Market: 350
Send Cancels as separate file.
To: ke’Shari Nowden; Weston
cc: Amanda Wiley; Shreeda; IT
Subject: CDI-PHX Leads Import 3-8-21
Message: CDI-PHX leads have been imported.
– Existing leads are updated with the data in today’s file
– The leads that found no match on update are imported as new leads.
Rick
Sic code: LCT-GP
Market: 300
Person ID: 015-
RELO GRANT IMPORT INSTRUCTION: One separate document for now
Sic code: DAVE-ORL
Market: 840
Person ID: 050-
OK, Rick and I discussed the import process. I created a UDF called Residency Start Date. The Block Start date will be imported into Start Date and Original Start Date, Residency Start will be imported into Residency Start Date. I have also added that field to the Prospect Tab under General Information where the other dates are. I also added “DO NOT MARK LOST UNTIL AFTER: pulls the date from Residency Start Date”. This made the most sense to Amanda and me.
To: ke’Shari Nowden; Weston
cc: Amanda Wiley; Shreeda; IT
Subject: DAVE-ORL Leads Import 3-8-21
Message: DAVE-ORL leads have been imported.
– Existing leads are updated with the data in today’s file
– The leads that found no match on update are imported as new leads.
Rick