Migrating to LGL from your current system
In this article:
- Migrating from spreadsheets
- Migrating from a standardized, off-the-shelf donor management system
- Migrating from a custom system
Before you can move your data into Little Green Light, it will need to be exported from the system you have been using and normalized/formatted for import into Little Green Light. ( Normalized means each data type, such as Email, Phone, Constituent Type, etc., goes in its own column.)
Your system most likely fits into one of the three types described in this article; these include Excel or another spreadsheet application, a standard donor management system such as GiftWorks or eTapestry, or a custom system using a product such as FileMaker Pro or Salesforce. Once you are working with the data you have exported from your current system, please read the "Migrating from spreadsheets" section below for instructions on readying your data for import to LGL You may also want to download a sample of an import-ready spreadsheet for reference.
Migrating from spreadsheets
If you’ve been using spreadsheets to manage your data, the Flex Importer (the tool used to import data into LGL) will be able to bring your data into LGL as long as each column contains consistent information of the same type as the field you will map that column to in LGL. Chances are you'll end up with an import-ready spreadsheet, similar to this one, that contains both constituent and gift information (gifts are in multiple columns).
Data that is not formatted correctly will cause an error in the Flex Importer. See below for an example:
Inconsistent data—for example, phone numbers mixed with email addresses in one column
Data of the same type in the same column
Your first step is to compile basic constituent information in one spreadsheet. This includes name, contact information, any associated categorization type data, and groups or coding about mail/email/phone calls. Pulling all that data together into adjacent columns in one spreadsheet will allow you to review and normalize it all in one place.
With name data, it’s critical to be consistent with whatever structure you’re using. If your names are in one of three possible structures and the data is consistent from one cell to the next throughout the column, you will be able to import it.
Names are typically represented in full (i.e., both first and last names) in a single column or in multiple columns (i.e., first name in one column and last name in the other). What do your names look like now? Do you have a single name column with different types of names in it, or do you have name information separated into multiple name columns? See the images below for examples of name data and the possible structures it can have. Your names will need to match one of these structures, and must remain consistent throughout the column.
Full name in one column
Full name in one column, with the last name first
First name and last name, each in its own column
For the portion of your constituents that are organizations, there are conventions to follow regarding bringing in the organization as a constituent versus as an individual who is a contact at an organization. Add a column to your spreadsheet titled "Organization" and include the names in it as you want them to appear in LGL.
Constituent type (and LGL defaults)
If you don’t have one already, create a "Constituent type" column and add it to your spreadsheet.
If your spreadsheet contains any rows with only a last name and no organization name, whether in a single column or two separate columns, LGL will create the record as an individual-type record.
If your spreadsheet contains any rows with only an organization name, LGL will create an organization type record.
You won’t need to fill out any rows in your constituent type column, except when you have both a last name and an organization name and you want that record to be an organization record rather than an individual type record.
Recommended structure for name data
The recommended structure for name data that is ready for import is last name separated from first name and then organization name in three separate columns, as follows (we highly recommend adding "Constituent Type" as an additional column):
Items to check
- Is your name data in one column? Are organization names mixed with individual names? If the latter, you will need to separate them by adding a new column titled "Organization Name" and placing the organization name in it.
- Is the format of your name data consistent from the top row of your spreadsheet to the bottom row? Make sure names are consistently John Smith, or Smith, John throughout the column/s. Either format will work, but not both in the same column.
Addressee/label names and salutations
If you want to control what appears in your Addressee and Salutation fields for use in mailings and emails that you send from LGL, add a column for addressee/label name and one for salutation. Now is a good time to do this, rather than hunting and pecking through your data after it has been migrated.
Annual report names
If your spreadsheet does not provide an Annual Report column populated with the names you want to appear in the Annual Report field, LGL automatically imports the data from the calculated Addressee field into the Annual Report field for an Individual record and from the Name field for an Organization record.
LGL address fields include:
- Street (three lines are available)
- ZIP code
If your address data is divided into these fields, it will be easy to map and easy to import into LGL.
Splitting address information
Please note that splitting address information is not possible during import into LGL. This means you cannot import a full address from a single column into separate address fields in LGL. If your address information is combined into one cell and you import it, the entirety of the data in that cell will be brought into the street field in LGL.
If all of your address data is in one column, your best option will be to turn on Smarty Streets in your account, which will normalize your addresses. There is a chance that Smarty Streets won’t achieve perfect results, however, it is likely to clean up the vast majority of addresses, as long as the address was fully imported.
You can import as many phone numbers as you have a record of. If you have columns titled "home phone", "work phone", and "mobile" or "cell", you would import these as a single column for each phone number. The column header should indicate what type of phone number is contained within it.
A column for each phone number type
You wouldn’t necessarily need to normalize your phone numbers (by placing each type in its own column), but it’s worth noting that if you don’t do this you will need to pay careful attention to the mapping and add rules to each phone number column to tell LGL what type of phone number it is.
2 columns for each phone number
An alternative is two columns per phone number, one column with the phone number and the other describing what type of phone number it is. In this case, you wouldn’t need to change the phone number, but keep in mind that LGL will not create phone number types from your data. You will need to either normalize the phone number types and match them to what’s in LGL before loading, or you’ll need to go into LGL before import and add all your phone number types so the types match, either using what’s in the account or adding the types (go to Settings > Menu Items > Contact Info Menu Items, and add the type/s you want there) that are not there by default. Examples include an account in which any non-default phone number types have not yet been created, with phone numbers such as "wife’s cell" or "cell" instead of "mobile".
Note: The default phone number type for any phone number that doesn't have an identified type within LGL is "home". This means that if you use "cell" instead of "mobile" as your column heading, your number will become "home". In other words, if LGL can’t match the phone number type to something already in LGL, it will make it a "home phone".
Number formats in Excel
LGL allows a single field for each phone number, which allows you to store extensions and other similar information. This field can accept numbers and text, and also accommodate a variety of other formatting needs for extension. The field is set up this way to simplify and reduce data maintenance work.
What this means for your import is that LGL takes the plain-text version of the number in your spreadsheet. If you are using Excel and you have ten-digit numbers formatted with parentheses around your area codes, for example, LGL will import the plain text only. This will result in a block of numbers being imported into the phone number field.
For these reasons, it’s important to understand how your phone numbers are formatted. The value in the cell is what LGL will import. To check your formatting, save your file as a CSV (Choose File > Save As, and choose ".csv" from the dropdown menu), and view your numbers in the CSV file. You can also check this if you navigate to your file, right-click, open with Notepad in Windows or Text Editor on the Mac, and you’ll be able to confirm it. You can format the phone numbers in CSV, and that format will be preserved when the data imports into LGL.
It's important to place no more than one email address per cell in any given column. The Flex Importer looks for the pattern of an email address based on the @ and dot symbols. In cases where there are multiples of these characters, the email address would be classified as invalid.
Grouping-type information or coding
If you have grouping type information, it needs to be consistently laid out in your spreadsheet. For example, if you have a column or a number of columns where each one represents a certain kind of code the constituent should have, the best situation is that the value that you want represented in LGL has been added to that column. You may have a column for board member and "yes" or "x" in the spreadsheet. However, it would be better to have a column called "Group" and to have the value in the cell for that person be "board member".
Another possibility is a column containing multiple values, such as "board member" and "volunteer", in one cell. Make sure what you’re using to separate those values is the same for the entire column, and remember that you’ll need to set up that mapping to bring the import in properly.
After you have compiled your constituent data and contact information, you can move on to preparing your giving data.
Please note that LGL requires a full gift date for each gift amount that you import. The preferred date format is yyyy-mm-dd, resulting in a date that would look like this: 2015-10-22.
If a gift date is not provided, the import date will become the gift date. This means if you have 10 columns of gift data by year, each will need to be split into multiple columns, one for the gift date, one for the gift amount, and others for any other gift-related information such as category, payment type, and so on. This can amount to some added work, but conceptually there’s not much more to it.
Importing campaign, appeal, fund, or event information
If you have gifts associated with campaigns, appeals, funds, or events, you have two options for bringing that data into LGL:
- Create an additional column (in addition to gift date and gift amount) for each type of information (i.e., campaign, appeal, fund, or event) and name the column accordingly
- Set a rule during mapping so that every dollar amount in the column goes to a certain campaign, appeal, fund or event
If you set a default for campaign, appeal, fund, or event
If you are importing information about gifts by setting a default in the Flex Importer for a campaign, appeal, fund, or event, please note that you will need to create the campaign, appeal, fund, or event in your LGL account before importing. Also note that the coding will apply to all gifts in the import.
Gifts in their own spreadsheet by year or by appeal
If you have Excel spreadsheets for individual years of giving or for individual giving campaigns, funds, events, or appeals, you will need to go through the same steps described above, normalizing each spreadsheet and import one at a time. To minimize duplication, you can use LGL’s matching functionality.
Migrating from a standardized, off-the-shelf donor management system
If you are migrating from a donor management system that uses standardized data fields, your first step is to identify what that system uses as the unique ID for every constituent. The key here is for the unique constituent ID to be present in every one of the exports. From there you can put together a series of exports.
You would typically need two sets of exports at minimum:
- The first contains one constituent per row, and each row contains the unique constituent ID, name and contact information, and grouping or categorization type information
- The second typically contains a list of individual gifts where every gift is in a single row and the unique constituent ID, gift amount, gift date, and any information about the gift such as campaign, fund, appeal, or event name, and gift notes (if available) are also included
Other data such as notes, contact reports, or multiple categorizations can all be imported as long as you look at where you have that information in your current system and export like data together with the unique constituent ID.
If your system doesn’t follow the same protocols as LGL (in other words, first name separated from last name separated from organization name), you will be best served by manipulating that data and putting it into separate columns before importing. If you import that data as is, you will need to clean it up later by editing individual constituent records or through an export/import process where you clean it up in Excel.
If your system doesn’t allow you to export street separated from city, state, and ZIP code and you want them separated on import, you’ll need to manually work through every row to get the street, city, state, and ZIP into their own columns. The most efficient path here would be to import the entire address into the "Street" field and rely on Smarty Streets to clean it up after import.
Once you have your address information in Excel, you can go through each column to make sure the data is in the correct columns and is uniformly formatted from the top of each column to the bottom.
Gift type. Every gift assigned in LGL gets one of four gift types: Gift, Pledge, In Kind, or Other Income. Gift types in LGL are not flexible. If you try to import gift types that are anything other than those four, such as "fulfilled pledge", "unfulfilled pledge", or "cash", those will be ignored and assigned a gift type of "Gift".
Gift date. Make sure your gift dates are formatted as a full date with a four-digit year. The recommended date format is yyyy-mm-dd, and the four-digit format is required for the year.
Gift amount. In your Excel file, highlight the Gift Amount column and make sure it is formatted as a number without commas or parentheses and that it uses decimal points if there are any. Any dollar signs or other currency symbol should be stripped out of your import file.
Other data about gifts. Every unique campaign, fund, event, or appeal name that you include in your spreadsheet will be created as an individual campaign, fund, event, or appeal in your account. It’s important to keep this in mind as you’re preparing your data. To avoid creating campaigns, funds, events, or appeals that you don’t want to use, make sure you remove any such columns before import.
LGL doesn’t support split gifts, so every gift amount will be a gift record. If, for example, your data has multiple funds listed for a single gift, LGL will read the first fund name only.
When your export is complete
In addition to the above guidance, once you have your data exported from your previous system, use the instructions in the first section of this article related to migrating from spreadsheets to prepare it for import.
Migrating from a custom system
Migrating to LGL from a custom system such as FileMaker Pro, Microsoft Access, or Salesforce is similar at a high level to migrating to LGL from an off-the-shelf donor management system in that a key to success is finding and exporting a unique identifier for each of your donors or constituents. Once you have this identifier exported, you can then layer your imports effectively.
We can’t provide detailed steps for how to get your data out of a custom system, but we can give you some general tips. If your data structure is too complex to do the migration yourself, we recommend working with a consultant. Learn more about the LGL Consultant Network on our website.
Salesforce uses unique IDs, however, the IDs rely on differentiations of case (uppercase versus lowercase). An example is unique identifiers of A123c and a123c, in which the only difference is the case in the letter A. In Salesforce, these are two unique IDs.
LGL doesn’t care about case, so those two Salesforce IDs would be identical in LGL. To help with this issue, we can turn on a setting in your account for case-sensitive external ID match, which ensures that we do a case-sensitive match instead of a case-insensitive match in your account. If you are coming to LGL from Salesforce, please contact us at firstname.lastname@example.org and we will turn on the setting for you.
Contacts and entities
Because Salesforce is designed for business to business sales, data is stored at an "entity" level as well as at a "contact" level within the entity. For example, you may not be able to put in phone numbers/addresses at the entity level but only at the contact level. And you can’t have a contact without an entity to house it. However, monetary transactions can be assigned to either the entity or the contact, so you are likely going to need to export both and manage de-duplication in LGL.
The best possible setup we’ve seen in Salesforce is an entity called "Individuals", containing all of the individuals as contacts within it, and an entity called "Organizations", containing all of the organizations as contacts within it. Some users create a contact and an entity for one person in some cases and put data in both places. Unless your setup is as clear as the first one we described above, with only two entities, you’ll need to import both entity-level and contact-level data into LGL. This is why we recommend exporting both from Salesforce.
Since we don’t migrate data in-house and are not consultants, and because every Salesforce installation is customized, we will not be able to determine what your exports need to be, whether you need both entities and contacts or just one of them. You have to know your own data to make that determination. If your data is complicated, and you're having trouble figuring out how to export/import it on your own, we recommend working with a consultant who has experience with Salesforce. (Note: If a consultant has Salesforce experience, it will be listed on their website.)
When your export is complete
If you want to migrate your own data, once you have it in Excel spreadsheets, please follow the instructions in the first section of this article to get your data ready for import.
In Microsoft Access, data is stored in tables and each table has its own set of unique identifiers. You may have created different designs with Access, so you could have 20 tables with complex relationships and other complicated data structures represented. Or you could have two tables, only one of which is useful and the other you may not need to refer to. Our experience is that almost all Access databases have unique identifiers per table. This means that even if you haven’t seen unique IDs before, you should be able to get access to them at least for your main constituent information. Even in situations where there is a single table with a lot of fields, the unique constituent ID can help you split that work up into manageable layers for import and rely on the unique ID to help you.
In some instances, you can export tables into Excel files, but this doesn’t work for all Access databases. If your use of Access has been more like collecting several Excel spreadsheets, you may be able to convert each table into Excel and then follow the instructions in the first section of this article for preparing the data for migration.
The key in migrating from FileMaker Pro is to make sure you know what your unique IDs are and that they are, in fact, unique. With FileMaker Pro, we have found it critical to test what you believe to be unique IDs before relying on them for import. For example, it’s possible to type a number in FileMaker Pro that also contains text. Upon export of this identifier, only the number part of the string is produced. We are also aware of some exports in which the stated unique IDs turned out not to be the unique IDs.
If you believe you have a unique ID field, make sure to test it in Excel. When you export it, you should be able to run highlighting duplicate values or experiment with removing duplicate values.
Also, check to see if you have repeating fields. If you have repeating fields, there is a help document available from FileMaker Pro that provides instructions on how to export the data in these fields. Unless you follow these instructions, you won’t be able to export all of the values and may get only one value. It’s important to be aware of the issue of repeating fields so that you don’t fall prey to that problem.
Your next step is to get your data into Excel. Once in Excel you can follow the instructions in the first section of this article to prepare it for migration.