CleanImport Help: salesforce.com
CleanImport is a CSV-file deduping and import tool embedded in salesforce.com. CleanImport dedupes the records in CSV files against your CRM records prior to import. CleanImport can be launched from your salesforce.com ActivePrime Admin tab.
Click on Import Records to launch CleanImport.
To use CleanImport, first select the type of records you wish to import (Accounts, Contacts, or Leads) from the menu in the upper left corner. It is best to import Accounts first, followed by Contacts. Leads can be imported at any time since they exist independently in salesforce.com.
After selecting which type of records to import, you must choose your Settings. CleanImport will find duplicates in your CRM based on these Settings.
To define your settings, click Settings in the upper left corner. In this menu, you can select the CSV to import, the CRM records to compare for de-duping, the merge rule for the master record, your custom match settings, the displayed fields, and concatenated fields. The menu can be collapsed by clicking Settings again or by clicking Close in the corner of the menu.
Select the CSV to Import
Select the CSV to import by clicking the Browse for CSV button. Choose a CSV file of records and click Open to upload the list to CleanImport.
After the file is uploaded, map your CSV fields to the CRM fields. For each field, select the CRM field that corresponds with the CSV field. These are the fields that your CSV data will be imported into. CleanImport will suggest Most Likely Matches if possible. To skip a field, select Do Not Import.
Select the CRM Records to Compare
Select the CRM records to compare your import list against for de-duping. You can choose to compare against a report in the CRM, a list of CRM record IDs in a CSV, or no records at all.
To compare your imported records against a report in salesforce.com, select the option Compare my CSV against the report in my CRM. Click the + button and enter a Report Name and Report Description. Next to Report URL, paste a link to salesforce.com report you have created and saved. Click Save to use this report in CleanImport. For best performance, we recommend using a list with 250,000 records or less.
To compare your imported records against a list of record IDs, select the option Use external CSV list of IDs to dedupe. Click the Browse for CSV... button, select the file containing the record IDs to dedupe against, and click Open to upload the list to CleanImport. After the file is uploaded, use the dropdown menu to select the column containing the record ID values.
To import your list without de-duping against any records, select the option Do not compare my CSV to any CRM records.
For more information, see this instructional video.
Reports that you upload will be saved for future use in CleanImport. You may edit or delete a report at any time. If you have multiple reports uploaded in CleanImport, simply select the one you would like to use for the current session by clicking on it.
You can create a report via the salesforce.com Reports tab. The report can include any number of filters to target a desired segment of records. If you make any changes to an existing report in salesforce.com, be sure to save the report again before using it in CleanImport.
**Important note: You must include the ID field (AccountID, ContactID, or LeadID) in the first column of your report. Once you have added the appropriate ID field and filters, save the report in order to select it in CleanImport. No other fields are required, but you may add any fields you wish to better visualize your data.
**Important note: CleanImport reads reports as if logged in to the CleanImport admin user of salesforce.com. If another user, besides the CleanImport admin user, creates the report, the Report Options need to be set to Show All Accounts (not just My Accounts) in order for CleanImport to read the records in the report.
Choose a merge rule to define how master records in duplicate sets are chosen.
There are two standard merge rules available, plus any custom merge rules your company may have ordered. The standard choices are:
|CRM Most Complete Address:||The master record in a duplicate set will be the CRM record with the most complete address.|
|CSV Most Complete Address:||The master record in a duplicate set will be the CSV record with the most complete address.|
To select a merge rule, simply choose it from the dropdown menu.
The Master Record is utilized to determine how the final row of field values will be created. The master record will be used to populate the final field values; any missing values will be populated by the other records in the duplicate set.
After CleanImport identifies duplicate sets, you may wish to re-run a different merge rule. To do this, simply choose a different merge rule from the menu and click Run Merge Rule.
Choose match settings to identify duplicates. The fields available for matching are the mapped CRM fields defined from your imported CSV. Each field has a sliding scale to select the logic that CleanImport will use to match your records. The options are:
|Don't Match||This field will be ignored for matching.|
|Exact Match||Data in these fields will be matched only if values are perfectly identical (case sensitive).|
|Starts With||Data in these fields will be matched if values begin with the same first few characters. You can define the number of characters to match via the Advanced... button.|
|Fuzzy Match||Similar data in these fields will be matched to each other. CleanImport applies matching algorithms and domain knowledge to identify misspellings, abbreviations, and phonetic matches. This is the most used option.|
More fields selected for matching will result in a narrower search for duplicates. Fewer fields selected will result in a broader search. A few trial runs will quickly reveal the best combination of fields for matching your data. It is usually recommended to use the powerful Fuzzy matching capability that ActivePrime's tools are known for, which identifies several types of similar matches. Exact matching is typically used on fields that are distinct character-wise, such as email, website, or customer ID. Starts With matching may be valuable for specific data de-duping needs.
**Please note that when matching on phone number fields, your results will be most accurate using Exact or Starts With matching. Fuzzy matching on phone number fields may yield inadequate results.
If you are new to CleanImport, try these basic match settings for Accounts:
If you are new to CleanImport, try these basic match settings for Contacts and Leads:
Click Advanced... next to any field to choose settings for blank field matching and case sensitivity.
- Blank field matching defines how CleanImport will match empty fields. Blank field matching is set to No by default, meaning that empty fields from two records will not match with each other ("___ Smith" does not match with "___ Smith" with blank field matching for First Name off). You can turn on blank field matching by selecting Yes, so that an empty field will match with another empty field ("___ Smith" will match with "___ Smith" with blank field matching for First Name on). Note that neither option allows you to match an empty field to a non-empty field ("___ Smith" does not match with "John Smith" if you match by First Name). This functionality is important in preventing large duplicate sets resulting from fields with missing data. For example, this happens when matching only on Account Name and several records are missing an Account Name (in this case, it would be preferable to select No for blank field matching).
- Case sensitivity for Exact matching defines whether the match will be sensitive to casing (upper case vs. lowercase).
- Characters for Starts With matching defines the number of characters that CleanImport will use to perform Starts With matching. The default value is 5 characters. Change this number by clicking More or Less. The minimum number of characters required is 3. Note that this option only appears when you have selected Starts With matching for the field.
- Words to ignore defines which words CleanImport will ignore when matching your fields. Enter one word per line. These entries are case-insensitive. Note that these words will not be ignored if you use Exact matching.
Choose the fields to display onscreen. You can add, remove, and rearrange any fields that exist in salesforce.com including custom fields. All available salesforce.com fields are listed in the Available fields column. Fields that will be displayed in your CleanImport results are listed in the Show these fields in this order column.
To change the displayed fields, click on a field name and use the Add and Remove buttons. To reorder displayed fields, select a field from the Show these fields in this order column and use the Move up and Move down buttons. To discard any changes you have made, click Cancel.
After CleanImport identifies duplicate sets, you may wish to change the fields displayed onscreen. To do this, simply make your changes in the Displayed Fields menu and click Apply.
Remember to display all fields that will help you to review your duplicates. For instance, you may want to include custom fields, record owners, or lead sources. Please note that CleanImport will not overwrite any field that is not shown onscreen.
There may be some fields for which you want to retain the values from each record in your duplicate set, for example, descriptions or sales notes. CleanImport lets you keep all this data by concatenating the values from each record into the final record field.
To concatenate the data for a field, check off the desired field and click in the dropdown to select the separator of your choice.
After CleanImport identifies duplicate sets, you may wish to change field concatenation settings. To do this, simply make your changes in the Concatenate Fields menu and click Apply.
Saving Your Settings
You can save your settings by clicking the Save As... button at the top of the Settings menu. Enter a name for your customized settings and click Save.
Your saved settings are listed in the Current Settings menu in the upper left side of the CleanImport window. To load your saved settings at a later time, click on the menu next to Current Settings and select the saved settings.
Once your settings have been chosen, click Find Duplicates. CleanImport will find duplicate records within the CSV records for import and any CRM records you have chosen to compare. The results will be displayed in the main body of the CleanImport window.
CleanImport's progress will be displayed at the top of the screen. Segmenting your data and finding matches may take a few moments, especially with large lists of records.
Customizing Merge Rules
Before you start creating new merge rules, please make sure to add to CleanImport all the display fields that will be used in Merge Rules. (Setting -> Displayed Fields).
After you have done that you can launch Merge Rules manager
Merge Rules Manager
You can launch Merge Rules Manager from Settings and also from Options:
You can activate or deactivate any Merge Rules. If a rule is not active it will not be visible for CleanImport users.
Creating and updating Merge Rules
When you create a new rule you will first need to select an action “Select Master Record” - Select Master Record will set the master record based on meeting your selected criteria.
The record that meets all Criteria Conditions will be set as the Master Record.
“Block Duplicate Record” - Block Duplicate Record will block a duplicate record to not be merged, based on meeting your selected criteria. The record that meets all Criteria Conditions will be removed from the Duplicate Set and will not be merged. It is the equivalent of using “Remove” link on the left of the record in the gridview.
“Block Duplicate Set” - Block Duplicate Set will block the entire duplicate set from being processed and the records will not be merged. If any record meets all the Criteria Conditions, the whole duplicate set will be removed and will not be merged. This is the equivalent of using the “Remove” link in the gridview on all records in Duplicate Set (note that you cannot remove Master, so if it’s done manually, you will have 1 record in that Duplicate Set).
“Set Field Value” - Set Field Value will set the desired field value based on meeting your selected criteria. If all the Criteria Conditions are met, the specified value will be set to “Final” Master Record. Note: It will be set to Final field value directly and will not affect other conflicting values checkboxes. Sample: We need to set the value of “Required evaluation” to “Yes” if we don’t have data for “Phone”, “Fax”, and “Website”. The Merge Rule can be set up with “Set Field Value”, Action: Set Value to “Required evaluation” = “Yes”, Conditions: “Phone”, “Fax”, and “Website” = “Column Is Empty”. In this case, CleanImport will put “Yes” to Final value of field “Required evaluation”, but only if whole columns for all 3 fields “Phone”, “Fax”, “Website” is empty for this Duplicate Set.
“Add the desired Set Value actions:”
Note: this will be applied to each Duplicate Set individually.
“Set Value” - Set a value to a specific field. You have to fill in the value to set. “Set Value To Largest” - If a field value is a number, it will be set to the larges number. If the value is text, the largest value will be defined by ASCII ordering (Z is larger than A). All values that contain a mix of numbers and letter will be treated as text.
“Set Value To Longest” - The longest value will be used. Sample: “555-555-5555” vs. “555-555-5555 ext 555”. “555-555-5555 ext 555” will be used.
“Set Value To Most Frequent” - The most frequent value will be selected.
“Set Value To Shortest” - This is the opposite to “Set Value To Longest”. The shortest value will be used. Sample: “555-555-5555” vs. “555-555-5555 ext 555”, “555-555-5555” will be used.
“Set Value To Smallest” - This is the opposite of “Set Value To Largest”. If a field value is a number, it will be set to the smallest number. If the value is text, the smallest values will be defined by ASCII ordering (A is smaller than Z). All values that contain a mix of numbers and letter will be treated as text.
The last part is selecting the criteria for this rule
First, you need to define if this will be “And” or “Or” logic.
Select “All” if you need “And” logic, select “Any” for “Or” logic.
You can add multiple conditions and Sub-Conditions. For each Sub-Condition you have to define the logic as well (All/Any).
For each Condition, you will need to define the field and the criteria.
The first set of fields (before the break) is a pre-defined set of multiple fields.
“Address” - Any Address that is presented in CleanImport displayed fields. This field can be set to “Is Most Complete” or “Is Least Complete”
“Billing Address” - Only the Billing Address fields that are presented in CleanImport displayed fields will be processed. This field can be set to “Is Most Complete” or “Is Least Complete”.
“Shipping Address” - Only the Shipping Address fields that are presented in CleanImport displayed fields will be processed. This field can be set to “Is Most Complete” or “Is Least Complete”.
“Create Date” - The record create Date. This field can be set to “Is Newest” or “Is Oldest”.
“Duplicate Type” - This will evaluate whole Duplicate Sets (groups of records in CleanImport with common number, 1.1, 1.2, 1.3, 1.4) and the defined duplicate type. This can be set to “Is Conflicting Duplicate” - duplicates that conflicts each other, marked with red background color in CleanImport, “Is Exact Duplicate” or “Is Non Duplicate”.
“Multiple Fields” - This allows you to evaluate multiple fields at once. It can be very useful if your Master Record preference is not based on Date or Address. You will select the criteria for the fields, and then select multiple fields in any order (Use Ctrl+Mouse Click to select/deselect multiple items).
“Are All Empty” - For fields with no data.
“All Contain Data” - For fields with data.
“Least Filled In” - For fields with less data.
“Most Filled In” - For fields with the most populated data. Most useful for helping select the Master Record.
“All other fields” - You can select a field and its criteria. Criteria can be based on the Duplicate set (“Column…”, “Largest Number”, ”Smallest Number”), or an individual value (“Value..”).
Understanding Your Results
After CleanImport has found duplicates, the results will be displayed in the main body of the CleanImport window. Your results are organized into three categories:
- Duplicates Requiring Review
- Duplicates Not Requiring Review
- Unique CSV Records
You can manipulate the data to fine-tune the duplicate sets before merging duplicate records.
Your matching results are organized into three categories:
|Duplicates Requiring Review:||Duplicate records with conflicting data in the displayed fields. These records should be reviewed before merging.|
|Duplicates Not Requiring Review:||Duplicate records without conflicting data in the displayed fields. These duplicates are exact matches and require little to no review before merging.|
|Unique CSV Records:||Unmatched or non-duplicate records in your CSV file as defined by your settings.|
Click the arrow to expand the grids and review your duplicate sets.
Reviewing Your Data
Click the arrow to expand the grids and review your duplicate sets. Duplicate sets are grouped and numbered in the grids. The Source identifies whether the records are from the CSV file or your CRM. The Final record represents the final field level values for the surviving merged record for each set.
You can easily review your duplicate sets before importing:
- Remove a record from a duplicate set by using the Remove link.
- Change the master record of the duplicate set by reselecting the Master check box (CRM records only). The master record is the surviving record that will be retained and updated after merging the duplicate set.
- Choose final record data by selecting check boxes in highlighted fields representing conflicting data in a duplicate set. You can also manually edit the final record by clicking in the final record data fields and typing your changes.
These duplicates grids are densely packed with information. The Source identifies whether the records are from the CSV file or your CRM. Underlined text in CRM records are hyperlinks to the existing record. Additionally, final record fields that appear in green can be edited by clicking into the field and typing your changes.
Duplicate sets highlighted in yellow indicate a display warning or error. You can view the warning by clicking view warnings in the Master field of the final record, or by clicking View Errors in the category grid title bar.
Your duplicate sets can be sorted by the data in any of your displayed fields. Simply click on the column header to enable ascending or descending sorting. Your duplicate sets will be sorted amongst each other by the final record data in the selected column.
If you are not satisfied with the match results, you can fine-tune your settings and re-run CleanImport until it finds duplicate just the way you want.
Exporting Your Data
Each category of grids can be exported to a spreadsheet. Exporting may be helpful if you require external review of duplicate sets. Click the Export button in the category grid title bar to choose a file name and type. You may filter by owners if desired. Click Save to generate the file.
You can see the statistics of your current CleanImport run by clicking Statistics in the upper-left corner of the window. You will be shown the number of total duplicates, duplicate sets by category, and unique records (non-duplicates). These values help you do a "health check" of your data to see how much duplication currently exists against your CSV import list.
Importing Your Records
When you are satisfied with duplicate identification and have reviewed your data, you can import your data from the three grids in the following ways:
- One-by-one by hovering over the Final record arrow and selecting Import Set.
- Page-by-page by clicking Import Current Page in the category grid title bar.
- All Records by clicking Import All in the category grid title bar.
When you import records from a duplicate set with only CSV records, one new record will be created in salesforce.com per set with final record field values. When you import records from the Unique CSV Records, one new record will be created per listing in this grid.
When you import records from a duplicate set that contain CRM records, the master CRM record will be updated with the final record field values and a new record will not be created. If more than one CRM record exists in the duplicate set, only the master record will be updated; CRM duplicates will not be removed from salesforce.com.
**It is important to note that each Import process is final and cannot be undone.
Successfully imported records will be highlighted in green . Unsuccessful imports will be highlighted in red with errors. You can view error details by clicking View Errors in the Master field of the final record or in the category grid title bar. If you can correct the cause of error, you can re-attempt to import your records.
**Please note that CleanImport will not overwrite the master record for any field that is not displayed onscreen. Therefore, the master record will retain its original value for any field that is not displayed. You may change the displayed fields while reviewing your data by reselecting your Settings.
When reviewing large numbers of records, it is advised that you import duplicate sets one-by-one or page-by-page to keep your data manageable.
Your salesforce.com account administrator is responsible for maintaining your CleanImport account options. Your account options can be accessed by clicking Options in the upper right corner of the CleanImport window. Here, you can update your CleanImport account, manage users, manage saved settings, manage email notifications, update CRM fields, and make feature suggestions.
Update CleanImport Account
This page is used to update your salesforce.com administrator information. This is the account that CleanImport accesses to perform searches. If the admin username or password for the account changes, please update it here so that CleanImport can continue accessing your salesforce.com data.
This page allows you to manage the salesforce.com users who are able to use CleanImport. To enable a user, check the AP Enabled box next to that user's name. To disable a user, uncheck this box. You can add additional Admin users by checking the AP Admin box. You can only enable as many CleanImport users as the number of licenses you have in your subscription. If you need more licenses, click Inquire About Adding More Licenses.
Any new users of your salesforce.com account will appear on this list and can be enabled at any time, provided that you have enough user licenses.
For long lists of users, you may need to search for users. You can use the column filters to search for users. Enter your filter criteria and click Apply Filter to refine the list of users on display. You can also press Ctrl+F to find users.
- Above CRM Status, you can select for active, inactive, or all users.
- After updating users, you must click Save to save your changes. Clicking Apply Filter to search for users will discard any unsaved changes on screen.
- Click Export CSV to export the CleanImport user list on screen.
This page allows you to save your CleanImport settings for future use. Click Save Current Settings to save your custom settings that are currently being used in CleanImport.
All prior saved settings will also appear on this page. You can load any saved setting by clicking Load. You can change the default setting which CleanImport will automatically load when it starts. You can also delete and rename settings here.
This page allows you to manage the email addresses that will receive notifications about CleanImport. Enter one email address per line in the text box, and click Save Emails to save your changes.
Update CRM Fields
Click Update CRM Fields to update the fields and labels from your CRM into CleanImport. If a user changes any labels, adds new custom labels, or removes labels, clicking this button will refresh their appearance in CleanImport instantly.
Is there anything new you would like to see in CleanImport? Anything that ActivePrime could do differently?
Click Feature Suggestion to send a feature suggestion to ActivePrime. This link will lead you to our support portal, where you can submit your suggestion to the ActivePrime team.
Frequently Asked Questions
For further assistance, visit our Support Portal at www.activeprime.com/support/.
Interested in more data quality solutions?
ActivePrime has developed an array of complementary tools including ActivePrime Search, CleanEnter, CleanCRM, CleanCSV, and CleanImport that are embedded in salesforce.com. ActivePrime Search is a real-time, fuzzy, CRM search solution. CleanEnter is a real-time duplicate detection service. CleanCRM is a batch data de-duping tool. CleanCSV dedupes CSV files of records. CleanImport dedupes CSV files against your CRM records and imports them. To learn more about these products, visit www.activeprime.com/products/cleanimport/. Sign up for a free trial through CleanImport Free Trial.
Last Revision: 4/22/2014