Migrating to LGL from your current system

In this article:

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., has its own column.)

The system you have been using most likely fits into one of the three types described in this article:

  • Excel or another spreadsheet application
  • A standard donor management system such as FrontStream (formerly GiftWorks) or eTapestry
  • A custom system using a product such as Salesforce or FileMaker Pro

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 examples of incorrectly and correctly formatted data.

Inconsistent data—for example, phone numbers mixed with email addresses in one column

Data of the same type in the same column

Constituent data

Your first step is to compile basic constituent information into 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.

Individual names

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

Organization names

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 (NOTE: We highly recommend adding "Constituent Type" as an additional column for name data):

Items to check

  1. Is your name data in one column? Are organization names mixed with individual names?
    2. If constituent names and organization names are included in the same column, you will need to separate them by adding a new column titled "Organization Name" and placing the organization names in it.
  2. Is the format of your name data consistent from the top row of your spreadsheet to the bottom row?
    3. Make sure names are consistently first name, last name (e.g.,  John Smith) or last name, first name (e.g.,  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.

Addresses

LGL address fields include:

  • Street (three lines are available)
  • City
  • State
  • 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 Address Verification in your account, which will normalize your addresses. There is a chance that Address Verification 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.

Phone numbers

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
  • The other column 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) 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 "Spouse'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 10-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.

Email addresses

It's imperative 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 will 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, that data will be much easier to import if you instead use column named "Group" and include the value in the cell for that person to have a classification of "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.

Gift information

After you have compiled your constituent data and contact information, you can move on to preparing your giving data.

Gift date

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: 

2021-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, as follows:

  • A column for the gift date
  • A column for the gift amount
  • An additional column for all 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 (e,g., 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 defaultin the Flex Importer for a campaign, appeal, fund, or event, please note that the campaign, appeal, fund, or event will need to be added in your LGL account before importing if it doesn't already exist. 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 them 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 will typically need two sets of exports, at minimum:

  • The first contains one constituent per row, where 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.

Name normalization

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.

Address normalization

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 Address Verification 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.

Gifts normalization

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 LGL account. If your spreadsheet includes instances of any of these that are not formatted identically to other instances will create the item by the name provided (letter casing included). 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 or data before import.

Split gifts

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 offer some general tips below. 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

Case-sensitive IDs

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 casing of the letter "a". In Salesforce, these are two unique IDs.

LGL doesn’t care about letter casing within ID numbers, 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 LGL does a case-sensitive match in your account instead of a case-insensitive match. If you are coming to LGL from Salesforce, please contact us at support@littlegreenlight.com and we will turn on that setting for you.

CAVEAT: If you are working with 15-character Salesforce IDs, we encourage you to generate 18-character IDs instead of turning on and using the case-sensitive setting in LGL. If you are using VLOOKUP somewhere in your file prep, be aware that VLOOKUP doesn't pay attention to case. If you are using 15-character IDs, you run the risk of matching to the wrong records.

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 instead 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 described, 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 the data ready for import.

Microsoft Access

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.

FileMaker Pro

The key to migrating from FileMaker Pro is making 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 (search on "export repeating fields") 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.

Related Articles