Import student and parent data

Overview

If you are importing school data, you may have the following data sources:

A student information system (SIS) to track student and family information for admissions, grade reports, etc., or
Other "student"-centered data from a different department

NOTE: For the purposes of this article, we will refer to any data where the student is the primary focus as coming from a "SIS".

If you are jumping into Little Green Light (LGL) as your first donor management system, you can use the data from your SIS to populate LGL with a bit of work. If you use both LGL and your SIS already, you may have updated information in your SIS that you want to add to LGL. This article is intended to help you with either situation.

Key concepts

Most SIS systems organize data by student. The primary purpose and functionality of the SIS, logically, is centered on managing information about each student and what is going on for them at school. This is fundamentally different from a donor management system like LGL because the primary purpose and functionality of LGL is to help the organization (in this case, the school) manage information about their donors and prospects. Since current students are rarely prospects or donors, it can take a bit of elbow grease to turn the rich data of your SIS into data you can load effectively into LGL.

The following concepts, which pertain to the functionality of the Flex Importer, are important to understand:

The Flex Importer can create multiple constituent records from one row of data. In the context of an SIS system, if the data for the student and both parents is contained in one row of data, the Flex Importer can add three separate constituents (one for each person) from that one row of data.
The Flex Importer can create relationships between constituents only when they are in the same row of data. If the student and both parents are in the same row of data, the Flex Importer can create three records, as mentioned above, and also establish relationships between the three records. So in one row of an import, the Flex Importer can establish that the student is the child of both parents and that the parents are in a spousal relationship. However, no relationship can be created between someone in row #4 of your data and someone in row #5 of your data.
The Flex Importer can use data in one column for multiple constituents. One example might be that the row has one column for the student's graduation year; that one column can be used to populate class affiliation with graduation years for the student and both parents (it can be used three times, once per constituent). Another example might be that the home address is present only once in the row; the Flex Importer can be set up to use that address information for all three records created from that one row.
The Flex Importer can look for existing constituents in LGL and add information about them during an import, and duplicate records can be minimized. The Flex Importer saves each row of data before proceeding to the next row; this means LGL can check data in row #3 against data in row #2 only after row #2 has been saved. It also means that in the Preview stage of the import one might see "new" constituents that will end up being "updates" of existing constituents because they are matches for other constituents that haven't been saved yet.

In referring to the model below, keep in mind that your data may differ from what is shown in the models. The key is to try to understand the operational principles at work and adapt them to your situation.

Model: Data with one student per row

This is the most common situation we've seen with data from an SIS, and it means you will have data where each student is in one row and their parents are listed in columns to the right of that student, in the same row. When there are siblings, the parent names are repeated for each sibling.

One important item to work through in your data itself before loading it into the Flex Importer is to focus on whether or not the two parents in the row are married and living together or divorced and living at different addresses. If the student in row #4 has divorced parents, whereas the two students in rows #2 and #3 have married parents, you may have to adjust the data to work with divorced parents differently from the married parents. If the data is already in separate rows, the import should be easier than if the divorced parents are "Parent 1" and "Parent 2" in the same row.

We'll start by working through the model data shown below ( NOTE: Click on the screenshot to see a larger version):

According to this model, we would work through the following setup in the Flex Importer to create three separate records from one row of data where one student and two parents are in the same row. In row #4, because only one parent is listed two records will be created. The setup will be the same for all rows.

Here is how to set up the matching preferences for the import:

It is important to note the matching preferences in the image above. Two important issues to keep in mind here:

If there are instances in your data where two people share the same email address, do not use the first option ("Match on email address and name"). Instead, use only the third option ("Match on name only").
If you have a unique ID for both the student and the parents in your data, uncheck all matching options and use the unique IDs only.

Next set up the first round of mapping as follows:

Here's a basic way to think about this: You will map the student data to constituent #1, the first parent data to constituent #2, and the second parent data to constituent #3.

Pay careful attention to these "Constituent" numbers (boxed in red in the above screenshot) as you are mapping the fields. For example, "Spouse Name" is attached to Constituent #2 (Parent 1). Also note where the contact information points and how you'll need to map the Parent 2 email to Constituent #3 (Parent 2 is Constituent #3).

Here are the key rules/defaults for the "Student". Please note that all rules are set from the "Last Name" mapping:

You'll see that contact type has been set to "Dependent", which is appropriate for young students. If you will be contacting your students directly, you may not want to alter the contact type. Also, we've highlighted how to copy the "Phone number" and "Street" from the columns related to "Parent 1". Not highlighted or shown are the setups to use the city, state, and Zip code data from "Parent 1" for the "Student".

Next we set the rule needed to ensure proper creation of the class affiliation graduation year:

Class affiliations remain consistent throughout time. No matter whether the person is a current or past student, they will always have the class affiliation of "Student" for their class year. This is why it is vital to use the defaults LGL provides of "Student" for all students, no matter whether they are currently alumni or students. In addition, it is not possible to change class affiliations in bulk, so please be sure you set anyone who graduated from or will graduate in a class to be a "Student" of that class. Once this is set up, you won't ever need to change it in the future.

These are the defaults/rules for the Parent 1 record:

As before, these defaults should be set from the "Last Name" mapping:

Please note that the class affiliation has been set to "Parent" and the year has been copied from the single column of "Graduation Year". Also, the relationship between "Parent 1" and "Student" has been set up. This setup will create both directions of the relationship, from the parent to the student and from the student to the parent.

These are the defaults for the Parent 2 record:

The first sets the "Contact type" to "Spouse/Partner" and sets the class affiliation and graduation year.

The next one sets the relationships between "Parent 2" and "Student" as well as establishing the spousal relationship between "Parent 2" and "Parent 1".

The next one creates the contact information (phone and address) from the data.

(This is the same idea as using the data for the student. Not pictured here is the data for State and Zip code.)

The final default sets the spouse name for "Parent 2" to use the "Parent 1 First Name" and "Parent 1 Last Name" data.

The total number of default rules set for the "Parent 2 Last Name" field is 14.

After all of these mappings and rules have been set up, your preview should look like this:

If you recall, the model contained a row with only Parent 1 data. That preview should look like this, since the "Constituent #3" data is empty (you can ignore the error listed for Constituent #3):

Once your import is ready for review after this mapping, you should see something like this:

At this point in the process, it is important to click "Show full records" for some of the constituents to get a good sense of whether all the mapping you set up is working correctly.