One of the strengths of spreadsheet programs like MS Excel or Google Sheets is that they provide an easy way to view, analyze, edit and organize data. Even if you’re not comfortable with “data”, it’s easy to review and edit a column of names. And, you can organize attributes related to a single record by adding columns of data. However, the things that make spreadsheets easy to use also represent limitations and problems in the context of a data migration to Salesforce.
The best way to illustrate these potential issues is to show you how they arise in two different parts of a Salesforce data migration.
CLEANING YOUR DATA
Excel makes it easy to visually inspect data for obvious errors (blanks, bad data, misspellings), but there’s a limit to how much you can do manually. How many records can you review before your eyes get blurry? 100? 1,000? The technical limit of rows in Excel is over 1,000,000! Even a small data migration project can involve tens of thousands of records. While technically possible, using Excel alone to inspect and validate data invites problems.
To make data cleansing easier, KELL Partners uses SQL database tools to simplify the data cleansing process for our clients as much as possible. We’ve compiled the lessons from over 1,500 Salesforce data migrations into a library of SQL-based utilities to simplify the process of finding and fixing common data problems. The data cleansing process can never be fully automated, but we like to try to limit any manual data review to a few dozen records.
MAPPING DATA RELATIONSHIPS
Many legacy systems have a “flat” data structure. A single contact record may have dozens of associated attributes – address, city, state, spouse, company, donor flags, event participation flags, etc. All these attributes can be easily represented in a spreadsheet. Each row will be one contact and each column will be an attribute of that contact.
Salesforce uses a relational database that is much more difficult to represent in a simple row and column format. A single contact will have an entry in multiple database tables, and each entry will be related to entries in other tables. You’ll have separate tables for organizations, contacts, donations, events/participants, etc. It’s much more difficult to represent this kind of data in a spreadsheet. You could use multiple spreadsheets and advanced functions like LOOKUP tables, but the complexity of that approach can quickly overwhelm even the most skilled Excel master.
Excel is great for reviewing specific pieces of data or small data sets. For actually managing, transforming and validating data during a data migration, we recommend using a SQL-based relational database.
If your organization is ready to move from a legacy system to Salesforce, contact KELL Partners to help. We bring the experience from over 1,000 Salesforce implementations and 1,500 data migrations to all our projects and are constantly improving our proprietary implementation tools to make each project as efficient and successful as possible.