Clean and Format Your Data

Transfer Your Data to the Template

Transfer your source data to an NPSP Data Import template. Follow these guidelines when transferring your data to the template.

• Copy and paste the data from your existing data source, without altering your existing data source. Keeping your original data source intact is a best practice that allows you to go back and examine it should you experience problems or conflicts later on.
• Some fields are required. Each import template contains notes on which fields those are.
• If you have custom fields that have no corresponding field in the default template, add them in your template as well as in Advanced Mapping before you upload your data. For more information, see Customize Advanced Mapping.
• Review Clean and Format Your Data if you want to link records in the template based on their Salesforce ID.
• Additionally, review Configure NPSP Data Importer Options if your data source contains external IDs that reference another database

After you transfer your source data to the import template, clean up and format your data.

Important Remember to delete any columns you're not using and save your completed worksheet (tab) as a CSV or comma-separated values file.

Clean Up Your Data

Getting your data in great shape for import is the most important step. It's also the most labor-intensive.

To import into Salesforce, your data must be structured in the required format. Otherwise, Salesforce will reject it. Invest time to prepare your data properly. If you simply try to get your data into Salesforce as quickly as possible, without regard to accuracy or cleanliness, you’ll run into errors.

Salesforce offers a monthly webinar about data import. Sign up for the next webinar or watch one of the recordings to get more information. Learn more in Salesforce Fundamentals for NPSP.

Sample Data Quality Problems

Let's look together at some data quality problems and how to fix them.

Here's a sample set of 20 records in an import spreadsheet. Rows 7, 10, and 11 contain data formatting issues. Can you spot them?

• Row 7: The ZIP Code is missing a leading zero.
• Row 10: Several members of the Brontë family are grouped into the same row. Every row should represent a single person or organization. Also, the last name should be simply, "Brontë."
• Row 11: "Guest of Lewis Carroll" isn't a first name, and Salesforce will reject this row because there isn’t a Last Name.

Let's look at additional columns in the spreadsheet. See if you can spot other areas that are problematic. Want a hint? Rows 3, 4, 11, and 20.

• Row 3: Each donation should have a single check reference number. If there were two checks, the donation should be either two Opportunities or one Opportunity with multiple payments.
• Row 4: Date fields should be date values, which "no receipt" isn't.
• Row 11: The email address is incomplete.
• Row 20: Another date field, in the DOB 1 column, contains a non-date value.

Fix Data Quality Problems in Your Existing Data

It's common to find data quality problems in your existing data. In our sample data set, we found seven just within the first twenty rows. Fortunately, these problems are straightforward to fix.

Check for these common data problems in your source data.

• Does every field represent a single piece of information? For example, are first name and last name in separate columns? Are multiple phone numbers in separate columns?
• If you have multiple donations from the same person, is each donation on its own row with the associated donor's information?
• Is Contact information current and correctly formatted?
  • Addresses: Do you have complete address information in the correct format for mailing? Third-party tools are useful for address validation.
  • ZIP Codes: In spreadsheets and databases, make sure ZIP codes are formatted as text fields or else leading zeros may be removed.
  • Email Addresses: Make sure there is an @ symbol and a .com or other valid ending for every email address. Also make sure that there's only one email address in each email cell. Remove extraneous text like “mailto:”

  • Phone Numbers: If you plan to use the Phone Number as a matching field against existing Contacts, it's critical that it matches the correct format for your existing data. For example, if all of the phone numbers in your database are formatted as 123-345-4567, don't import phone numbers as 123.345.4567.

• Is data for required fields present in all your import rows? For example, all Contacts must have a Last Name and all Opportunities must have a Close Date.

You're done with data cleanup when every field represents a single piece of information, and every value in that field is in the correct format.

"But wait!" you say. "I have a spreadsheet with 20,000 rows of data! Do I really need to review every single row and make all of my data consistent?" The answer to this question is, unfortunately, yes. Manual data cleanup is tedious, but starting with clean data pays off in the long run.

There are ways to make the clean up process faster. For example, learn to use find-and-replace, formulas, deduplication, and other spreadsheet tools. Consider changing your data collection practices so that the data is closer to the format you need for import.

Prepare to Match Based on Salesforce ID

Learn the right way to match your imported records with existing records in Salesforce using Salesforce IDs.

The NPSP Data Import template includes special Imported fields that are useful if you want to match Contacts, Accounts, Campaigns, Opportunities, or Payments based on their existing 15 or 18 digit Salesforce ID. It's important to understand how these work when importing data.

Note To match Opportunities or Payments, you must set a matching rule during the data import process. For more information, see Configure NPSP Data Importer Options. Keep in mind that donation matching should only be used to update an open Opportunity when a Payment comes in. A successful match will change the Stage of an open Opportunity to Closed/Won and/or mark an open Payment as Paid.

Let's say for example that you want to update the Work Phone and Email for existing Contacts. You can easily achieve this as part of your data import. On the Contact detail page, simply copy the Salesforce ID from the browser URL.

Then, add the ID to the appropriate field in the data import template. To update existing Contacts, use either the Contact1 Imported or Contact2 Imported field. For Accounts, use the Account1 Imported or Account2 Imported field.

Note When uploading the file, indicate that you want to use the Salesforce.com ID to match against the Imported field. This ensures that the columns get mapped properly. See Upload Data from the Template for more information. Do a Trial Run to make sure you have everything set up correctly.

Now that your data is cleaned and formatted, Do a Trial Run to verify that your import works.