Importing Leads into Microsoft CRM (and other CRM systems)
This post demonstrates importing leads, an important first step in the sales process for many (most?) organisations. Leads can be gathered from many sources, which can lead to multiple problems such as poor quality data and duplicates.
Inaport can be used to:
- Assign a quality score to each lead, allowing us to block low scoring leads, and rank the ones imported;
- Use better matching techniques to prevent duplicates being imported.
While this post uses Microsoft Dynamics CRM 2011 as the target CRM system, the principles discussed can be used with Sage SalesLogix, SageCRM, ACT! by Sage, and GoldMine.
Importing Leads using Microsoft CRM
Microsoft supplies a lead import template that can be populated in Excel. Once the template is populated with data, importing into Microsoft CRM is a matter of a few clicks; the process is straightforward, but there are caveats and shortcomings. Consider some sample data:
Sample data for import into Microsoft CRM Leads
This was imported into Microsoft CRM leads using the standard data import tool. The results were:
Sample lead data imported into Microsoft CRM
There are some clear problems with this data:
- “Fred Smith” is obviously duplicated, even though duplicate detection rules were in place.
- The names are not formatted well.
- Row 3 “asasa” is poor quality, and probably should not have been imported.
These problems are not easy to rectify using the standard Microsfot CRM data import capabilities.
Importing leads using Inaport
Use any data source
When importing data using Microsoft CRM, there are some important restrictions on the data source:
- The file type can only be .csv, .txt or .xml
- Maximum file size is 8Mb.
- Each file can only be mapped to a single entity in Microsoft CRM.
None of these restriction apply when using Inaport. For the purposes of this demonstration, the same data using the Leads template was used, but this is not a requirement. Before importing, the Excel spreadsheet was modified to add columns:
- RowID, a unique number for each row
- LeadScore, which will hold a lead quality score calculated by Inaport
- IsImported, which will hold the unique identifier of the lead in Microsoft CRM once imported.
The following screen shot shows the modifications:
Lead source data with added columns
Lead Quality Scoring
The first step in the process is to assess the quality of the leads. When using the standard Microsoft import, the only real option is to manually examine the data in the Excel spreadsheet and manually correct or delete records. As the size of the dataset grows, this process becomes more and more time consuming and error prone.
Inaport allows the introduction of automated lead scoring. The basic principle is:
- Assign a perfect score (50 in our example).
- use a set of rules to deduct points from the lead; each rule can be used to deduct one or more points
The following screen shot shows the rules built for the demonstration:
Lead scoring rules
- The first checks if the lead has a blank email address, Last Name, or Business Phone number. If so, 10 points are deducted.
- Rule 3 checks if the phone number has the same digit repeated 4 more times (e.g. “1111234” or “1234444”), while rule 4 checks if the phone number has “1234” or 2345″ or “7890”. In each case, points are deducted.
- Rules 5 and 6 check the email address, deducting points if it is from gmail or yahoo, and more points if the first part of the email is 1 to 4 characters long and it is from gmail or yahoo (e.g. abcd@gmail.com)
There are no restrictions on what the rules can be, and no restrictions on how many rules there can be. In addition, Inaport has a Preview Pane, that allows you to see the results of applying the rules to a set of the source records – you can preview 5 or 500 records, and tune your rules appropriately. See screen shot below for how the rules score the sample data.
Lead data score results
Inaport is then used to for two important actions:
- Update the source spreadsheet with the quality score for each record.
- Prevent importing records which score below some cut off level.
This gives us some important benefits. By scoring the leads and preventing poor scoring being imported, the quality of the data in CRM system is improved. By updating the source spreadsheet with the score, it is possible to go back and do a manual review of just the records with poor scores, greatly improving the manual review process.
Updating the source spredsheet with a score
Improved Matching
The duplicate detection rules in Microsoft CRM are restricted to exact match on a field or fields, or matching the first N characters of a field. This why “Fred Smith, InaPlex” and “Fred Smith, InaPlex Inc” were not detected as duplicates; the duplicate rules in Microsoft CRM included a check on the first 15 characters of the company name but that included the “Inc”.
Inaport allows matching on any field or combination of fields. More importantly, it allows the use of expressions to transform the fields for matching purposes. For example, it has a “normcomp()” function which can be used to normalise company names. normcomp() will:
- remove noise words such as “Inc”, “incorporated,”, “llc”, “Ltd”, “GmbH”, “sarl”, …
- remove punctuation and trim white space
- lower case everything.
The actual matching expression used in the demonstration is:
Inaport Match Criteria
This expression uses the normcomp() function to normalise the company name, then appends the normalised last name. The result is that “Fred Smith, InaPlex” and “Fred Smith, InaPlex Inc” will now match and be detected as duplicates.
In addition to this type of standard matching, Inaport supports “fuzzy matching”, where the source and target are compared using a string similarity algorithm, and a score assigned. Possible matches based on the criteria and score are shown to the user and the user can decide which (if any) is the correct match. Using fuzzy matching, “inaplex” and Ianplex” would be shown as possible matches.
Updating the Source with Import Results
If a lead source row is successfully imported, the source spreadsheet is updated with the primary key (unique identifier) of the lead in Microsoft CRM. There are two advantages to this:
- It is a positive indicator that the record has been imported, meaning if a manual review is done the record can be skipped.
- If the source record is updated for some reason, Inaport can be used to update the lead in Microsoft CRM.
The source update is done using this expression:
Update source with Microsoft CRM lead ID
Results of the Import
Here are the results after the Inaport import was run:
Inaport lead import into Microsoft CRM
Notice that:
- Fred Smith” has not been duplicated;
- The poor quality record “asasa” has not been imported;
- The names are formatted correctly;
- Most importantly, the lead quality score is available in Microsoft CRM.
Having the lead quality score in Microsoft CRM makes it possible for the internal sales team to focus on the highest quality leads first.
The source spreadsheet has also been updated:
- Source Data after Import
This demonstrates that the source data can now be sorted by whether it has been imported or not, and by quality score for manual checking and enhancement if appropriate.
If you would like more information on how Inaport can be applied to your particular requirements, please do not hesitate to contact us.
InaPlex Blog 
Trackbacks & Pingbacks