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 cleanupDepending 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 sourcesRather than using an intermediate staging database, it might be easier to import all data into Salesforce and clean it there.
Required timeData cleanup may take significant time and going live quickly is more important than having accurate data.
Different data modelsSource 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:

ObjectSource countSalesforce countDifference
Accounts100991
Contacts789260529
   

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:

TypeExamples
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:

  • Heroku Connect maps to a PostgreSQL database. It is a native, bulletproof Salesforce product, although it may be considered expensive
  • Skyvia is a good alternative to Heroku Connect, supporting many database types
  • CDATA products can map Salesforce to Microsoft SQL Server database and use Salesforce via ODBC

Using these tools, data loading can be easily implemented in SQL.

ETL connectorsETL (Extract, Transform, Load) tools such as Microsoft SSIS have native or third-party connectors to access Salesforce data.
Custom toolsCustom developed tools are used for complex migration logic or dedicated software.

Implementing data migration

It is important to build the migration process so that:

  1. The process is automated and can be run multiple times.
  2. Data is loaded incrementally.
  3. Records are updated rather than removed and re-created.
  4. Data can be uploaded to multiple Salesforce instances.
  5. A full sandbox is used for development and testing.
  6. 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!

    Thank you for contacting Nextian. Your request was successfully submitted, we will get back to you within two working days.

    BY INDUSTRY

    Cloud Infrastructure Providers

    Cloud Software Companies

    Managed Service Providers

    Communications Service Providers

    BY ROLE

    CEO / Owner

    CRO / VP Sales

    CFO / VP Finance

    COO / VP Operations

    CPO / VP Product

    CIO / VP IT

    Product Management

    Plan, launch and manage your product offerings throughout their entire lifecycle.

    CPQ & Sales

    Quickly create accurate quotes for complex products, subscriptions and add-ons

    Order Management

    Ensure faster, consistent order delivery with tasks, workflows and automation

    Service Management, Support & Monitoring

    Retain and upsell customers with comprehensive account intelligence, support, monitoring, analytics

    Customer Portal

    Empower your customers with 24/7 self-service, support and on-line ordering

    NEXTIAN PLATFORM

    Platform Overview

    Billing Integration

    Network Monitoring Integration

    Reporting & Analytics