You are currently viewing Importing ERP data into Salesforce & keeping it clean

Importing ERP data into Salesforce & keeping it clean

Abstract: The Legacy ERP system (IBM/AS400) publishes API notifications when records are created or updated in the system. A middleware process(Boomi) subscribes to these notifications to Extract, Transform, and Load (ETL) the data into Salesforce in real-time. All the existing ERP data needs to be available in Salesforce for the real-time updates to work. In this kind of setup, what steps and procedures need to be incorporated to get all the relevant data into Salesforce, keep the data clean and ensure the ongoing updates work seamlessly.

Best Practices:

  1. Identify Customer Data Stewards: Bad data creeps into the system over time. Garbage in and garbage out, right? We don’t have to move all the bad data in ERP into Salesforce. In the initial stages of the project, it pays off to identify data stewards who have intimate knowledge of ERP data. As the project advances, the project plan should account for tasks to clean up bad data before migrating them to Salesforce.
  2. Clean before you import: De-duping the ERP data before migrating to Salesforce is super helpful. If duplicates are messy, insufficient data is ineffective at best. Contacts without phone or email cannot be used. The data should be cleaned before importing to Salesforce, so we don’t have bad data in the two systems.
  3. Import Relevant data: A thorough data cleanup is preferred but not always possible. A strategy to identify ERP data with the most recent activity e.g., accounts with activity in the last 2 years can be identified, cleaned, and migrated. 
  4. Data cleaning is an ongoing process: Proper procedures should be incorporated into Salesforce to keep the new data pure. Validation Rules could be set up to reject contact creation when all the five points of contact are not entered. Likewise, Duplicate rules (standard and custom) can be set up to alert and/or restrict the creation of duplicates. Reports can be created to track bad data. The sales managers can subscribe to these reports to track repeat offenders and perform root cause analysis to close the gaps in the process. 
  5. Data loading best practices: The video explains the data loading best practices. Below is a quick summary relevant to this article:
    • Learn MS Excel: Data loading doesn’t just involve familiarity with tools such as Data Loader & JitterBit. It also requires data manipulation in excel. Excel features such as Vlookups, formulas to transform data e.g., Upper case to camel case(INACTIVE to Inactive), concatenating multiple address fields into one address field are extensively used.
    • Salesforce User Creation should precede data load: The CreatedBy field on the record shows who created the record. If the original user needs to be preserved, the user must be created in Salesforce before loading the data. Change the CreatedBy ID in excel to either the original user or a system user based on the requirement. If you don’t populate this field, the CreatedBy is stamped with whoever uploads the data.
  6. Test for Integration: It’s essential to test the ongoing updates from ERP after the data load. Initially, some of the external Ids were not correctly populated as they were not displayed on the page layouts. For instance, the external id for a custom object was loaded as “C10234”. But the actual external id was “10234C”. As a result, the updates from ERP were not getting upserted.
  7. Post-production monitoring: After the go-live date, the customer data stewards have a key role to play. Salesforce reports can be created to track the data coming from ERP. For instance, the “Contacts created yesterday” report can be scheduled to be sent every day to the data stewards. The data can be cross-checked with ERP to test the ongoing updates.
  8. Develop a robust auditing system for data integration failures: What happens if there is bad data and the validation rule has stopped the creation of the record in the middleware flow. A Salesforce middleware audit log object(API_Notification__c) can be created to log any failures that can be inspected to troubleshoot the errors.



It pays off to identify customer data stewards early on in the project. It’s imperative to get clean data into Salesforce and keep data It pays off to identify customer data stewards early on in the project. It’s imperative to get clean data into Salesforce and keep data cleaning an ongoing process for any new data created in Salesforce. Always test for the integration of the new middleware process with the data loaded from ERP. Import relevant data into Salesforce, e.g.., accounts with activity in the last two years. Setup post-production monitoring to monitor the data quality ongoing basis. Middleware auditing mechanisms can help pinpoint data integration failures.