Data migration best practices

Salesforce data migration projects can be a hassle if you don’t have any best practices to follow. I recently had to split an object into 2 objects as we were storing information of 2 separate “elements” into 1 object. This kind of architectural mistake causes many issues. Namely, fields are getting hard to get, as we don’t understand which field stands for which “element”, business logic such as workflow rules, validation rules, lightning flows… are getting needlessly complex and more generally, the Salesforce administration team struggles to maintain the current architecture.

Moreover, if you make this kind of mistake in a standard object, you’ll need to fill required fields that don’t make sense for some records (for example, you must fill a stage name on the Opportunity Object). This typically ends up by adding many record types, which leads to various complications.

Therefore, I had to migrate some records to a dedicated custom object. Even though it seems straightforward, without the right sequence and best practices, this kind of migration can be a disaster. In this post, I’ll share some best practices to follow for this kind of data migration projects.  I’ll take an example of a failed data migration in a fictitious company named ACME.

 

The story of ACME

Company ACME is a startup that simplifies the way people order food in fast foods. They sell on-premise devices, and bigger stores order several devices. A CFO recently joined the company and is changing ACME’s revenue model, shifting from selling devices to selling subscriptions.

Sales representatives at ACME currently create one opportunity per device, and when they implemented Salesforce.com, the first Salesforce Administrator intern created some device specific fields on the Opportunity Object. Sales reps now complain because for bigger customers, having to create 3 or 4 opportunities for one deal is tedious.

Due to revenue model changes and the sales team organization, sales reps currently create many kinds of opportunities record types. They have 3 sales directors with different sales processes, and thus there are 12 opportunity record types. Some record types store device information and some don’t.

 

The New Data Architecture

The CFO suggests to create a new custom object to store devices sold. In this way, sales reps will be able to manage their pipeline more efficiently, and associate 1 or more devices to one opportunity. Due to the new revenue model, he’ll use another object called “Subscription”, and the Contract Management team will be able to associate devices to these subscriptions.

Robert, the new Junior Salesforce Administrator, reads a bit about Salesforce custom objects. He creates a new custom object, called “Device”, in ACME’s Salesforce production organization. As he struggles to understand some custom fields purposes, he decides to spend some time with a few sales reps to understand which fields need to be migrated from the Opportunity Object to the Device Object. He comes up with a file like the one below, and creates some fields the Device Object (where Object A is the Opportunity Object, and Object B is the Device Object).

Robert just adds one lookup field on the Device Object to associate a Device to an opportunity.

Data Loads

Robert now wants to identify which records need to be migrated to the Device Object. He knows that the previous administrator used the Opportunity Object record types to differentiate records that should remain in the Opportunity Object from records that should be stored in the Device Object. Robert has a list of 12 record types, and 5 of them should be stored in the Device Object.

He extracts all opportunities with the Data Loader into a CSV file and removes the 7 record types he knows shouldn’t be migrated to the Device Object. Then, Robert removes the fields that only concern the Opportunity Object, and imports the CSV file to the Device Object with the Data Loader.

 

Deletion of obsolete records and data model

The next day, Robert has 600 records on the Device Object, and deletes the 600 duplicates on the Opportunity Object. Then, he removes obsolete page layouts and record types on the Opportunity Object. He spends some time updating business logic such as lightning flows, validation rules and workflow rules.

Next, he deletes all obsolete fields from the Opportunity Object. Robert renames one field to “Obsolete field #3” as he couldn’t delete it. There were already 2 fields named “Obsolete field #1” and “Obsolete field #2” that had the same issue : each time he tries to delete these fields, he received this error message: “This custom field is referenced elsewhere is Salesforce.com”.

 

Go Live…

At the end of the 2^nd day, Robert shoots an email to Salesforce users to let them know that the deployment is now live. During the night, he receives some compliments from many ACME’s employees. Robert doesn’t know it yet, but the coming weeks are going to be painful…

 

Child relationships losses

The Sales Operations team complains about report & dashboards accuracy. The VP of Sales Operations has a report in his homepage to check if there is any device that hasn’t been updated for more than a year. Updates are stored within Salesforce in the Update Object, and are related to Opportunities. The VP of Sales Operations knew he had over 100 devices in this report that needed to be updated, and know these devices hasn’t been updated yet.

Robert didn’t really think about the consequences of deleting obsolete records. When he deleted obsolete the Opportunity Object records, he broke the lookup relationship between update records and opportunity records.

Here, Robert should have updated some Opportunity child relationships. The easiest way to do so is to open the Workbench and to go to Info > Standard & Custom Objects > Select your SObject > Check Child Relationships, here : https://workbench.developerforce.com/describe.php. For each relationship, you have to ask yourself if your new object needs it.

In our example, he should have done the following:

• Ensure that the opportunity records has a unique field beside the record Id
• Create this unique field as text on the device object, and then change the field type to autonumber
• Insert device records
• Create a lookup field to Devices on the “Update” object named “Device”
• Fill the “device” field on “Update” records by using the autonumber field (unique key between the opportunities & devices)
• Delete obsolete opportunity records

 

Sharing & Visibility Issues

The Channel team complains about issues with the partner portal. Their partners still have access to opportunities, but they can’t see any device records. They need this information for big customer, to check which store has some devices and which don’t. They used to have a report on the partner portal to check these up sell opportunities.

Robert didn’t think about the sharing model of the new custom object. He created a lookup field on the device object, which causes several issues. Sales reps now have to choose a device owner, which don’t really make sense to them, and as Robert set the Organization Wide Default to “Public Read Only” for Internal Users and to “Private” for External Users, partners now can’t see any device records.

Robert should have asked the following questions when creating a new object:

• Who needs edit access, and to which records?
• Who needs read only access, and to which records ?
• For those who can edit records, is there any fields they shouldn’t edit or see ?
• Etc…

The sharing solution that needs to be built depends on these answers. Maybe a Master-Detail relationship with the Opportunity Object was a better option, as device owners didn’t really make sense. For partners, he could have created a “Sharing set”, to share the right device records with partners.

 

Poor fields definition

Data migration projects are always a good opportunity to check all fields definitions. In our example, Robert struggled to understand which field concerned Opportunity and those which concerned Devices. Filling descriptions and help texts, using global value sets (global picklists), defining field naming convention… helps everyone to understand the purpose of each custom field. Spending time doing so is definitely worth it as your company grows.

 

Data Loads issues

The production team has some issues concerning device IDs. They used a unique field called “Device Identification Number” in Salesforce that matches ACME’s software device records. They had usage data concerning these devices, that allowed the Customer Success department to minimize the churn rate, which defines their  quarterly bonuses.

When Robert inserted new records on the Device Object, he didn’t think the fields transformations:

• Auto-numbers
• Record Id
• Audit fields
  • createdDate
  • lastModifiedDate
  • createdBy
  • lastModifiedBy
  • systemModStamp
• Etc…

 

In our example, it seems that the Production used an autonumber custom field to track device IDs. Robert should have followed this process:

1. Create the “Device Identifinition Number” as text on the Device Object
2. Insert new device records
3. Change the “Device Identifinition Number” field type to autonumber

 

The takeaway

Data migration projects proceed smoothly if you have the right checklist. Thinking about child relationships on Workbench, sharing & visibility settings, field definitions and the data load sequence will prevent most of the troubles.