Using the Student Org Data Import Service

Contacts, Inquiries and/or Campaign Members can be automatically created in the Student Life Salesforce Org using the Data Import Service automation. Users create a comma-delimited (CSV) file which maps data to specific fields in Salesforce and duplicate data is removed/updated as appropriate. 

This document provides instructions and important information on using the Data Import Service:

Notes: 

  • Users require a specific Permission Set in order to access the functionality. If you need this Permission Set, submit a Salesforce Help Ticket.
  • All Contact records created using the Data Import Service are marked as Prospect records.

Understanding the Data Import Service

Duplicate Checking Criteria

The Data Import Service checks for duplicate data to prevent multiple records being created for one person in Salesforce. The records are matched on one of two data points, in the following order of priority:

  1. UMID - If a UMID column exists in the upload file and it contains data, it is used to match records.
  2. Contact Email - If there is no UMID in the upload file or no match is found based on the UMID, the process compares the Contact Email in the file to the University Email, Prospect Email, Alternate Email, and Work Email fields in Salesforce.

If no matches are found using either the UMID or Contact Email fields, a new Prospect record is created.

Data Updated on Duplicate Records

If a duplicate record is identified in the upload file, it is processed as follows: 

Contact Fields

Contact data is NOT overwritten in Salesforce, with the exception of the following checkbox fields:

  1. Is Prospect is set to TRUE for all matched records. 
  2. If the upload file contains a column for Engineering Interest, Nursing Interest, Public Policy Interest, Rackham Interest, SEAS Interest, SOE Interest, Stamps Interest and/or Taubman Interest, the matched Salesforce records are set to TRUE when the value in the applicable field(s) is TRUE.

Inquiry Fields 

A new Inquiry is always created, regardless of if a Contact match exists.

Campaign Member Fields

If a duplicate contact is found AND they are already a member of the campaign specified in the upload file, no Campaign Member data is overwritten.

Creating an Upload File

The upload file must: 

  • Be saved as a CSV file type.
  • Contain headers that match the formatting exactly as explained in this document. If any headers are incorrect, the process returns an error message.
  • Files must be smaller than 2 MB. If a file is larger that 2 MB (approximately 11,000 rows in a CSV file), it is recommended that the data be split into multiple CSV files.
  • Include, at minimum, columns labeled Contact Email, Contact First Name, Contact Last Name, and Function. Data must be present in all columns.
  • The last column of the CSV should be labeled Function and have a value of Prospect in every row.

The following tables explain the data fields that can be included in the upload file and the required header names. Not all fields need to be included with the exception of the four columns listed above (Contact Email, Contact First Name, Contact Last Name, and Function).

Important! Only the fields listed in the tables below can be included in the CSV file. If your file includes any columns other than the ones listed below, the Data Import Service will give an error and the file will not be processed.     

Contact Fields

Column Header Description
Contact First Name REQUIRED. Contact's first name. Maximum 40 characters.
Contact Middle Name The contact's middle name. Maximum 40 characters.
Contact Last Name REQUIRED. Contact's last name. Maximum 40 characters.
Contact Email

REQUIRED. Contact’s email address. Must be formatted as a full email address (i.e., [email protected]). Change any @med.umich.edu email addresses to @umich.edu before uploading the file. 

For new Contact records, the Contact Email is saved to both the Prospect Email field and Preferred Email field in Salesforce.
UMID

Contact’s eight-digit UMID. Must include any leading zeros.

Most CSV programs remove the leading zeros by default (ie, 00123456 becomes 123456). To ensure the UMID maintains the leading zeros, in Excel change the column format to 'Custom' with a value of '00000000’ (eight zeros).

If a UMID column is included, every row in the upload file must have a UMID value. If the file contains rows with and without a UMID, split it into two CSV files.
Sex Valid values are:
  • Female
  • Male
  • Unknown
Birthdate Must be in YYYY-MM-DD format, (e.g., 2021-06-30).
Mailing Street The street in the mailing address. Maximum 255 characters.
Mailing City The city in the mailing address. Maximum 40 characters.
Mailing State

The state or province in the mailing address. Maximum 80 characters.

Use the two-letter state abbreviation when possible.
Mailing Zip Code

The zip or postal code in the mailing address. Maximum 20 characters.

Can include numbers and dashes, if needed.
Mailing County The county in the mailing address. Maximum 255 characters.
Mailing Country

The country in the mailing address. Maximum 80 characters.

Use the abbreviated country name when possible.
Mobile Phone

The contact's mobile phone number. Maximum 40 characters.

Can include numbers, dashes, and parenthesis, if needed. U.S. numbers should be formatted as (XXX) XXX-XXXX (e.g., (734) 555-1234).
Engineering Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Nursing Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Public Policy Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Rackham Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
SEAS Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
SOE Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Stamps Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Taubman Interest If Contact is interested in the corresponding School/College, field value must be TRUE.
Citizenship Status

Valid values are: 

  • U.S. Citizen or U.S. National
  • U.S. Dual Citizen
  • U.S. Permanent Resident
  • U.S. Refugee or Asylee
  • Other (Non-U.S.)
Ethnic Group

Valid Values are: 

  • 2 or More
  • Asian
  • Black
  • Hawaiian
  • Hispanic
  • Native Amr
  • Not Indic
  • White
American Indian or Alaska Native If Contact identifies with the ethnicity label, set the field value to be TRUE.
Asian If Contact identifies with the ethnicity label, set the field value to be TRUE.
Black or African American If Contact identifies with the ethnicity label, set the field value to be TRUE.
Hawaiian or Other Pacific Islander If Contact identifies with the ethnicity label, set the field value to be TRUE.
Hispanic If Contact identifies with the ethnicity label, set the field value to be TRUE.
Hispanic Latino If Contact identifies with the ethnicity label, set the field value to be TRUE.
Multi Ethnic If Contact identifies with the ethnicity label, set the field value to be TRUE.
White If Contact identifies with the ethnicity label, set the field value to be TRUE.

Inquiry Fields

Column Header Description
Program of Interest

REQUIRED TO CREATE AN INQUIRY. Must exactly match the value in the Recruitment Academic Index field. Values can be found by searching on the Recruitment Academic Indexes tab within Salesforce.

If there is no Recruitment Academic Index value for a program, submit a Salesforce Help ticket to have it added.

This value sets the Academic Program Code, Program Plan Code, Level, and other common fields on the Inquiry that define the Program of Interest.
Start Year

The year the Contact wants to begin their program of interest. Must be a four-digit number (e.g., 2022).

If no value is provided, the field defaults to either the current calendar year if the current date is in January-August, or the next calendar year if the current date is in September-December.
Start Term

The academic term the Contact wants to begin their program of interest.

Valid values are:
  • Fall
  • Winter
  • Spring
  • Summer
  • Spring/Summer
If no value is provided, the field defaults to either the program of interest’s default value, or Fall for programs of interest with no specified default.
Last School Attended

The Contact’s previous Educational Institution. Value must be the nine-digit External ID ending with -El from the Educational Institution record Salesforce, (e.g., 12345678-El, 22446688-El).

An Educational Institution can be located in Salesforce by searching for its name. For example, searching for “Lake Orion High School” returns an Account with an External ID of 000023324-EI.

If Last School Attended data was exported from Business Objects, the External ID is the same, however -EI needs to be manually added to the end of the ID (e.g., 98765432 is changed to 98765432-El).

If the Last School Attended is unknown or unneeded, the value can either be left blank, or set to UnknownSchool-EI to be recorded as Unknown in Salesforce.
Inquiry Created Date

The date the Inquiry request was made. Must be in YYYY-MM-DD format (e.g., 2021-06-30).

If no date is entered, it defaults to the current date.
Inquiry Source The original source of the Contact’s inquiry. See the Inquiry Status list for valid values.
Inquiry Source Other Free text field. Maximum 255 characters. Used to describe the source if the Inquiry Source is set to Other.
Inquiry Source Detail Free text field. Maximum 255 characters. Any additional information used to track the Inquiry Source.
Inquiry Type The Academic Career the Inquiry is associated with. Do not map unless the Inquiry list was provided by Business Objects or the Data Warehouse. In other systems this field is labeled “Acad Career”.
UTM Campaign Free text field. Maximum 255 characters. The marketing campaign name - product, promo code or slogan (e.g., LawSchool-On-Campus-2023-Lead-Gen-Form). This is used when tracking Marketing Campaigns via URL.
UTM Content Free text field. Maximum 255 characters. Used to differentiate ads that point to the same URL. This is used when tracking Marketing Campaigns via URL.  
UTM Medium Free text field. Maximum 255 characters.  The marketing medium (e.g., cpc, banner, email). This is used when tracking Marketing Campaigns via URL.  
UTM Source Free text field. Maximum 255 characters. The referrer (e.g., Google, newsletter, LinkedIn). This is used when tracking Marketing Campaigns via URL.  
UTM Term Free text field. Maximum 255 characters.  The paid keywords. This is used when tracking Marketing Campaigns via URL.  

Campaign Member Fields

Column Header Description
Campaign REQUIRED TO ADD A CONTACT TO A CAMPAIGN. The Campaign’s Salesforce ID as shown in the Campaign Id Full field. If the Campaign is not in Salesforce, it must be created before members can be added.
Attendance

The Contact’s attendance status.

Valid Values are:
  • Attended
  • Registered
  • Canceled
  • Walk-in
  • No-show
  • Cannot Attend
Use only if attendance tracking is important for the specific Campaign event.

Running the Data Import Service in Salesforce

  1. Select Data Import Service from the Salesforce menu.

Salesforce menu showing the Data Import Service option

  1. Click Next
  2. Drag and drop the CSV file onto the file upload page or click Upload Files to navigate to and select the CSV file. 
  3. Click Done on the file upload confirmation message.
  4. Click Next

Once the process is complete, the Data Import Results page displays.

Reviewing Data Import Results

The Data Import Results page provides an overview of what data was processed by the data loader. 

Results Overviews

The Contact Results, Inquiry Results and Campaign Results boxes provide an overview of the number of records created, records matched, and errors recorded. This can be used as a guide to verify the correct number of records were processed, and identify any data that may require investigation.

Example of the Contact Results, Inquiry Results and Campaign Member Results showing the number of records created, contact matches, pre-existing campaign members, and data errors.

Data Import

The Data Import table displays a list of all records in the upload file and the process results for each. This allows you to easily see which records were Created and/or Matched, and which records have errors. 

In the example below: 

  • Lindsay Prospect’s row in the upload file was matched to an existing contact record, and Inquiry and Campaign Membership records were successfully created for her.  
  • Alana Student’s row in the upload file created a new Contact record and Campaign Member record. However, there was an error in the Inquiry data and an Inquiry record was not created.

Data Import Result list

Result Details & Error Resolution

More information about each individual record is available by clicking the link in the STG Index field. This page shows each possible data field that could have been included in the upload file, with the exception of the School/College Interest checkboxes. If any data for the individual was added/updated in a field, it displays on this page. 

Records with error messages should be investigated to determine the error and resolve it.

In the example below, an error occurred in the Inquiry record. This error was caused because the external ID in the upload file was not present in Salesforce.

Errors require data to be corrected on the upload file and reprocessed because they prevent records from being created. The entire file does not need to be re-uploaded. Rows without errors can be deleted from the file. Correct any rows with errors, and upload the revised file.

CSV Templates

The spreadsheet templates listed below can be used to create an input file. Click on a file to open it, then download a copy to your computer for your use.

Tags: 
Last Updated: 
Tuesday, June 28, 2022