Deduping with Excel

Last Updated on Tuesday, 27 June 2006 05:03 Written by Steve Tuesday, 27 June 2006 05:03

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.

dotproject and Salesforce hookin’ up

Last Updated on Thursday, 22 June 2006 05:13 Written by Steve Thursday, 22 June 2006 05:13

I have continued to work on the dotproject and Salesforce.com integration I talked about earlier. Here’s what I have now:

• Salesforce is the truth for Accounts and Contacts
• dotproject is the truth for project information
• Accounts are created in Salesforce.com and pushed to dotproject
• Projects (and Tasks and Tasklogs) are created in dotproject and pushed to Salesforce.com

Main benefits of doing this:

• We want one source of truth for CRM data, and Salesforce.com is where we want that data. Now we have that.
• We have two years of project and time tracking data in dotproject, and we want to continue using it for project tracking
• My power users can now do cool reports and dashboards on our project and tasklog data

The nuts and bolts

When an Account is created in Salesforce.com, it has a link for creating a dotproject Company:

If you follow that, you land on the Company add form, with basic data filled in for you, including the Salesforce.com Account id.

You save the Company and it’s linked to the Salesforce.com Account and vice versa.

I then have a nightly batch that pulls Projects, Tasks, and Task logs into Salesforce.com via the webservices API and the PHP toolkit. All projects and task logs are available as related lists on the Account. Here is a list of projects for a Salesforce.com Account, with a lot of summary numbers I calculate when I pull the projects over:

We’re now in a position to use Salesforce.com as the UI for navigating to all Accounts and Projects. We are essentially deep linking to dotproject projects, and not really using the dotproject UI for a lot of our navigation. This is great because the Salesforce.com search functionality and recent items list is a much better UI for digging through CRM data.

Oh, and it took me 2 mintues to add a Web Tab for dotproject’s Timecard, so we can add time without “leaving” Salesforce.com.

I’m pretty excited about this integration. It’s not super complicated but it’s going to radically simplify our lives, and hopefully allow us to be more efficient. We’re utilizing the individual strengths of two tools with different core competencies. Thats what we shoot for in our consulting work, so it feels good to implement that strategy in our back office work.

Thanks again to the folks who made dotproject what it is, and to the Salesforce.com Foundation for the donation!

Staying up on Appexchange listings

Last Updated on Wednesday, 21 June 2006 08:37 Written by Steve Wednesday, 21 June 2006 08:37

There are a couple hundred packages on the Salesforce.com Appexchange right now. How could you possibly know all that is there and stay up on new things? Well, the only way to know what is there is to take some time and check them out. But staying up on what is new is really easy. Point your RSS reader (Bloglines is the easiest if you’re new to RSS) to the Appexchange’s new listing feed. You’ll get pinged each time a new listing goes up.

This is where RSS really shines–it lets you know what’s just been published. It’s the best way to stay on top of what’s new, and has dropped a few Appexchange gems in my lap lately.