Understanding the structure of related group companies and reflecting these in your CRM is essential to help you plan territories, reduce duplicates and implement an effective account-based sales and marketing approach.
This is the second post in our series on data hygiene, building on the previous post about cleansing your CRM with DueDil. In this post, we will demonstrate how to use the Salesforce Account Hierarchy feature, and how DueDil data can be used to link related group companies.
Other CRM systems such as Microsoft Dynamics and NetSuite have similar company or account hierarchy functionalities, so this post should still be useful even if you’re not using Salesforce.
Below is an example of what we will end up with – a nicely formatted account hierarchy in Salesforce, showing all related companies and their owners:
As you can see from the above screenshot, companies often have a range of names that may be very different from their actual trading names, making even fuzzy name matching unreliable.
Using DueDil’s company hierarchy solves these problems by allowing you to group all related companies in your CRM, align internal owners, and position your sales and marketing messages appropriately based on the account segment.
We’ll keep this as simple as possible by focusing on the steps to import the Ultimate Domestic Parent from DueDil into your internal Salesforce account. The same steps may be repeated to import the global Ultimate Parent.
- Import your existing Salesforce accounts into DueDil
- Export the Ultimate Parent and/or Ultimate Domestic Parent from DueDil
- Identify missing parents and create these in your CRM
- Import parent relationships from DueDil into your CRM
1. Import your existing accounts to DueDil
As per Step 2 of our previous post on CRM Data Cleansing, you should first create a new list in DueDil that contains all your Salesforce accounts.
2. Export the Ultimate Parent and/or Ultimate Domestic Parent from DueDil
Export this list from DueDil and select the ‘Ultimate Domestic Parent’ and/or ‘Ultimate Parent’ options from the export screen:
3. Identify missing parents and create these in your CRM
When we completed this exercise internally, we found that a number of parent companies were missing from Salesforce, despite multiple sibling subsidiaries existing in our CRM.
At DueDil, we wanted to ensure that all related group companies would roll up consistently to the correct Ultimate Domestic Parent record in Salesforce. As such, we decided to create new account records for any that were missing.
We identified the missing parents by using the Ultimate Domestic Parent IDs from my exported DueDil list, which uses company number in the format: /company/gb/06999618. You will need to reformat these IDs so they match the Company Number field, by replacing the text ‘/company/ie/’ with just ‘IE’, and removing all instances of ‘/company/gb/’.
This will then let you perform a VLOOKUP in Excel (see section below), matching the Ultimate Domestic Parent number with each account’s individual Company Number, and therefore identifying any companies that are missing.
These missing companies can be added to a new DueDil list by mapping the reformatted Ultimate Domestic Parent field to Company Number, and then exporting the companies back from DueDil to create the new accounts in Salesforce.
4. Import parent relationships from DueDil to your CRM
From your CRM, create a report or export containing the ‘Account ID’ and ‘Company Number’ fields, being sure to include your newly-created parent companies.
Then, using your original list of accounts exported from DueDil, perform a VLOOKUP against your CRM file. The formula below shows how to match the reformatted Ultimate Domestic Parent IDs against the Company Numbers from your CRM file:
This will result in a spreadsheet containing the two fields required to re-import parent IDs into your CRM:
- Company Number (external ID reference to your CRM’s account records)
- Parent ID (Salesforce ID to match each record against the relevant parent account)
Import this file into your CRM using the normal methods, mapping the Parent ID to the field ‘Parent Account ID’ in Salesforce or your CRM’s equivalent.
We’ve mentioned these a few times, but VLOOKUP formulas needn’t be complicated. Essentially they are looking for a given value from Spreadsheet A, finding that same value on Spreadsheet B, and then returning another value from the same row on spreadsheet B.
The key is to ensure that on Spreadsheet B, the first column is the one that contains the lookup value. This column must be positioned to the left of the one you want to be returned!
Here are the elements of the above formula explained:
- B7 is the cell from Spreadsheet A that is being looked up (e.g. Company Number).
- [SalesforceExport]Sheet1! is the name of the Spreadsheet B, which is being checked.
- $A:$B are the columns being looked up, the first column containing the lookup value (eg Company Number), the second column being the one needed in return (eg Account ID).
- 2 is the number of columns to the right of the one that gets found. In this case, we want the second column (Account ID) to the right of the one we matched against.
- FALSE tells Excel to look only for exact matches, instead of the nearest match.