Data Migration to Salesforce — Challenges, Tools, Cleanup, Quality Measurements and Best Practices
Data migrations are an intrinsic part of Quote-to-Cash (QTC or Q2C) transformation projects.
As a popular CRM, Salesforce is often involved at the front-end of QTC for sales and CPQ.
As CRMs expand beyond their original role with modules such as order management, customer support and billing, a need for data migrations, beyond accounts, leads and contacts arises.
Compared to relational databases, data migrations to Salesforce add a few technical challenges and require additional tools.
Data cleanup
Usually, it is best to import data that has already been cleaned. However, sometimes it may be easier to clean the data after importing. This may be due to:
| Ease of cleanup | Depending on data sources, it may be easier or quicker to perform the cleanup in Salesforce with tools like DemandTools or others. |
| Consolidating data from multiple sources | Rather than using an intermediate staging database, it might be easier to import all data into Salesforce and clean it there. |
| Required time | Data cleanup may take significant time and going live quickly is more important than having accurate data. |
| Different data models | Source data model does not provide all data required by the (target) Salesforce model, and records need to be updated after import anyway. |
A mixed approach may also be applicable: clean data up to a certain point at the source, migrate, and finish the cleanup in Salesforce.
Progress and data quality monitoring
Data migrations are iterative and are best monitored with migration scorecards. A scorecard provides a comparison of rows and counts of source vs. destination data:
| Object | Source count | Salesforce count | Difference |
|---|---|---|---|
| Accounts | 100 | 99 | 1 |
| Contacts | 789 | 260 | 529 |
| … |
For large projects, scorecards usually include:
- Per-load snapshots for progress and data quality monitoring,
- Breakdowns per fields (columns) within objects (rows),
- Summaries (e.g., billed monthly revenue, sales pipeline),
- Error rates and others.
Establishing success criteria One of the most important questions while building a scorecard is what constitutes a data migration success? Is it 100% accuracy, or maybe 99%? Do identical rules apply to all objects? What is the cost of having perfect data vs. just very good data? |
Salesforce data mapping challenges
Regardless of what kind of data is migrated, the following Salesforce specifics must be considered:
| Object Ids/Primary Keys | Each row in Salesforce is identified by a unique object id. The ids are always auto-generated and cannot be set by users. As a result, the ids change between subsequent data loads and cannot be used as primary key identifiers for migration. Possible solutions to the problem include intermediate tables mapping source data to object ids or using source primary keys as external ids in the Salesforce model. |
| Relations/Foreign Keys | Salesforce object relationships are based on object ids. As a result, migrating relationships from source data will require mapping involving intermediate tables or external ids. |
| Record Types | Salesforce record types enable creation of sub-classes of objects with different layouts, action menus, workflows, etc. For example, Customer, Partner and Vendor types can be defined for the account object. As a result, separate source tables may need to be consolidated into a single Salesforce object during migration. |
Salesforce data upload tools
Salesforce does not provide a direct database access and data must be uploaded using specialized tools:
| Type | Examples |
|---|---|
| Data loaders | Data loaders are import/export tools and can source data from CSV files as well as databases:
|
| Salesforce as a database | These tools map Salesforce internal storage to SQL databases:
Using these tools, data loading can be easily implemented in SQL. |
| ETL connectors | ETL (Extract, Transform, Load) tools such as Microsoft SSIS have native or third-party connectors to access Salesforce data. |
| Custom tools | Custom developed tools are used for complex migration logic or dedicated software. |
Implementing data migration
It is important to build the migration process so that:
- The process is automated and can be run multiple times
- Data is loaded incrementally
- Records are updated rather than removed and re-created
- Data can be uploaded to multiple Salesforce instances
- A full sandbox is used for development and testing
- A staging database is used for cleanup and data transformation:
Source system(s) → Staging database → Transform & Cleanup → Upload to Salesforce
Conclusions
Data migrations to Salesforce can range from simple uploads to large, iterative projects with automation, staging databases, performance scorecards and others.
The latter is especially true when Salesforce is used beyond plain CRM with modules such as order management, customer support and others.
Nextian is a vendor of Quote-to-Cash (QTC) software for cloud and communications helping providers accelerate growth and increase customer lifetime value.
Contact us today to find out how we can help you!
