gokubi.com

One of the big challenges with CRM is keeping it filled with high-quality data. This means taking the time to enter data for your consituents, making sure all members of your team are doing the same, and making time to periodically look at your data, cleaning out duplicates as you find them.

One of the most powerful tools for analyzing and working with data is Excel. You can export an Salesforce.com report to Excel, and you can use the power of Excel formulas to make data cleaning easier. Here are a few tips:

Put the Id fields on your report
Always bring over Contact Id and Account Id to your reports if your going to do data cleaning. These Id’s are very helpful in the process as you’ll see.

Use Excel’s sort
You can do more powerful sorting in Excel than you can in Salesforce.com. Deduping by matching first and last name is easier if you sort by those two fields.

Use a simple formula to highlight duplicates
To quickly find duplicates, start by sorting on the fields that you want to search for dupes. I’ll use email in this example.
In a new column in your exported report, add a formula that compares the email for this row with the emails on the row s above and below it.

=IF(OR(A3=A4,A3=A2),"Duplicate Email","")

This formula will print out the word Dupe if the value in cell A3 is the same as that is A4 or A2. You can now quickly scan this new column to see duplicates.

To look for duplicates that have the same first and last name, the formula is slightly more complicated:

=IF(OR(AND(C4=C5,D4=D5),AND(C4=C3,D4=D3)),"Duplicate Contact Name","")

C4 is first name, D4 is last name for the Contact in question. If the name exactly matches the row below or above, it will print the text.

Have fun with hyperlinks
Create hyperlinks back into Salesforce.com for acting on duplicates. To get to a Salesforce.com object like Contact or Account, use a formula like this:

=HYPERLINK("https://na3.salesforce.com/" & B2,"View Contact")

B2 is the Contact Id–a clickable hyperlink is generated that will take you directly to the contact’s record. This will work with any Salesforce.com Id–Contact, Opportunity, Account, etc.

Salesforce has a powerful Contact merge fuction that will combine up to 3 Contacts into one record, preserving any related items like Opportunities. It’s a great way to deal with duplicates. You can link to the wizard from Excel, just pass in the Account Id of the Contacts you want to merge:

=HYPERLINK("https://na3.salesforce.com/merge/conmergewizard.jsp?id=" & B2,"Merge Contacts")

You’ll get dropped in the Merge wizard saving a couple clicks. Just select the contact to merge and you’re off.

Excel formulas can really speed up your data duplication work. If you use these simple tips you can keep your Salesforce.com data as clean as possible, resulting in more successful CRM.

This entry was posted on Tuesday, June 27th, 2006 at 5:03 pm and is filed under CRM, Technology. You can leave a response, or trackback from your own site.