Preparing for migration

In this article:

• Introduction
• Plan your migration project
• Tools you'll need
• Key concepts
• Layering data
• Getting organized
• Documenting your steps

Introduction

No matter what form your data exists in, whether in a system you are migrating away from or in Excel, Access, or another application, it will need to be migrated into Little Green Light using one or more CSV files (or Excel spreadsheets) that are import-ready. Making your spreadsheets import-ready can often require careful preparation and manipulation. This article is intended to help you with this process.

Plan your migration project

1. Gather/extract your data and review it carefully. Expect to spend some time making sure you have all your current data available. Then spend some time reviewing it. Are there surprises? Gaps? Do the numbers add up to what you'd expect (number of constituents, number of gifts, dollar amounts)? Project time/energy estimate: 20%
2. Data manipulation - getting your data ready for import. This involves cleaning up inconsistencies, deleting unnecessary columns, adding needed columns, etc. Project time/energy estimate: 45%
3. Import the data. Presuming your manipulation work was 100% complete, this step will only need to be done once. You may discover you need to redo some part of the data preparation and then redo part of your import. Project time/energy estimate: 20%
4. Review the data in LGL. Look for agreement in key areas:
   • Do the number of constituents match? 
   • Do dollars add up during certain time periods (the work you do in step 1 makes this step easier)
   • Project time/energy estimate: 10%
5. Get yourself familiar with how LGL works in practice. A great way to accomplish this is to create a test mailing and produce a set of address labels (in PDF for testing). This will help you ensure names and addresses all came in correctly. Project time/energy estimate: 5%

Tools you'll need

Microsoft Excel

Excel is the most common tool used to prepare data for import. Our help documentation and videos around importing data focus on using Excel (or CSV) files. You can also use Microsoft Access or another tool to do this, as long as what you end up importing into LGL is a CSV file. (Note: A CSV is a text file containing information separated by commas that allows data to be saved in a consistently structured format, readable in many computer programs. A CSV file can be opened and saved as an Excel file and vice versa.)

LGL has published a “top 10” list of Excel tips that we strongly recommend reviewing as they’ll be important to your migration (Note: If you use Google Docs or another spreadsheet program, some of these 10 items will still be important because they are concepts and commands shared by other programs.)

The "Save as" command

Understand how to “Save As” from Excel to CSV, and know how to open the resulting CSV file with a plain text editor such as Notepad (Windows) or TextEdit (Mac). The reason for this is that while Excel will properly interpret a CSV by putting the plain text into columns, it will eliminate the formatting of the CSV text with regard to dates or ZIP codes (leading zeros will be dropped).

Other tools

Many people regularly use Microsoft Access as a data cleanup/management tool. Here at LGL, we find OpenRefine (formerly Google Refine) to be a powerful tool.

Key concepts

Unique identifiers - when migrating from a donor management system

All donor management systems, including LGL, use the standard database concept of a unique identifier (most often a number but sometimes a combination of letters and numbers) for various pieces of key data. Being vigilant about unique identifiers is vital to a successful migration. 

The two main identifiers you will need from your current system are:

• Constituent ID. The identifier that is unique to every constituent record. You will map this to the LGL field “External Constituent ID”, which functions as a unique identifier in LGL (note that LGL generates its own unique “LGL ID” for each constituent as well). Ensuring that every export you have from your previous system contains the Constituent ID is critical—without it there will be no way to connect the various pieces of data across the multiple import files.
• Gift ID. Similar to a donor ID, a Gift ID is used as a unique identifier. However, in some systems different types of gifts are housed in different tables and the identifier is unique only within a particular table. If this is the case, you will need to add text to the end of certain types of gifts pre-import. For example, pledge payments could have an overlapping number with pledges, so before importing we suggest adding “-pldg” to the end of each pledge ID and “-pmt” to the end of each payment ID, as shown below.

Pledges with their IDs (import first)

Pledge payments with overlapping IDs (to be imported only after pledges have been imported)

Layering data

If you're migrating from another donor management system. Using the Constituent ID to connect data to constituents during multiple imports, as described above, is part of a process that helps keep migrations moving along efficiently and with minimal errors. When working with a unique Constituent ID, we recommend dividing your files into manageable parts. So you might import just names first and then later import contact information (possibly even separating address from phone/email). This layering approach helps keep mapping and review simpler and less error-prone.

If you're migrating from a large spreadsheet. Sometimes people have many columns in one spreadsheet, making it challenging to manage all the information. In this the case, you can create your own unique ID for each row of your spreadsheet. Simply pick a number to start (it's often best to start large, for example, at 10001) and "fill down" with a series in Excel so that each row has a unique number. Once this is in place, you can "Save As" multiple times making each spreadsheet a layer of data. Be sure to always keep the first column with the ID and use it as the External Constituent ID when importing.

Getting organized

While not required, the following tips can help keep your migration project organized and documented.

File and folder structure

Create a dedicated folder/directory on your computer (or network drive) where you can easily access the files you generate. Inside that folder, create three sub-folders:

1. Import files. Use this folder to keep only the final CSV files you plan to load into LGL
2. Working files. Use this folder (and any needed sub-folders) to collect the files you work on (sort, edit, cleanse, etc.)
3. Original files. Use this folder to store your original exports. It is key to keep an untouched copy of your files/exports in case something goes awry during your preparation work. This way you can always have a reference of your raw data.

Additional considerations. If you keep the above folders with a service that offers you versioning, such as Google Drive, Dropbox, or Microsoft OneDrive, you will have less of a need to go all the way back to raw/original files if you take a misstep during preparation. In addition, you won’t have to worry about backups and will be able to access the files from multiple computers.

File-naming conventions

Be sure to name your files in simple but descriptive ways; some ideas are listed below.

• File names. Avoid using overlapping names (in other words, avoid using the same name for any of your files), even when keeping them in separate folders as described earlier.
• Files/Exports. When you create a file that has all the phone numbers, consider naming it something similar to “Phones-orig”.
• Working files. You will save the above file using the "Save as" command, which gives you an opportunity to name it something like “phones-wkg”. If there are multiple versions, you can give them names like “phones-wkg1”; “phones-wkg2”.
• Import files. When you are ready to import a file containing your phone numbers, for example, you will save it as a CSV file and can name it something like “phones-import” or “phones-final”.

Documenting your steps

If you will not be working on the migration in a single, concentrated time period, it can be a huge time saver to document your steps along the way. There are many tools available, so use whatever works best for you. If you don’t have any tool in mind, we recommend creating an Excel/spreadsheet file in your Working Files directory and keeping a simple to-do style list based on your original files:

If you have a lot of columns and want to track what you do with each, consider using the wonderful Excel “Paste Special” tool, “transpose”, to make a simple punch list of your column headers. Open the file you’re working on, select all values in the first row, and then copy and “paste special > transpose” into your tracking Excel file. Voila, those horizontal values are now a vertical set of values you can comment on or manage the status for.