Self Service DI Fuzzy lookup

It’s an unfortunate fact of life that dealing with data from third-parties is not always straightforward.

Take the example where you have a list of customers and a third-party is selling you a list of potential sales leads. Before you pass these leads into the CRM system you want to ensure that non of these leads are customers already.

This would be straightforward if the way customer details were stored was standard.  As we know, it isn’t standard.

For example, in the customer record we have a customer :

Bill’s Builders Limited, Arlington Ind. Park, Chester, Cheshire, CH2 3RT

This customer is also present in the lead data :

Bill’s Builders Ltd, Arlington Industrial Park, Chester, CH23RT

Without eyeballing each sales lead it’s difficult match these records.  Thanks to our resident SQL Dev, I was introduced to SQL Server Integration Services (SSIS) a while ago.

SSIS is a data integration tool bundled with SQL Server. Within SSIS we have a tool called Fuzzy Lookup,   Fuzzy Lookup is like Excel’s VLOOKUP on steroids.  With Fuzzy Lookup we can specifiy similarity levels and other advanced attributes to match across many columns. I’ve been using SSIS quite a bit lately to clean data, however, it’s more of a developer tool hence many of you may find it unwieldy.

Fear not, the folks at Microsoft Research have developed an Excel add-in that gives you the features of SSIS Fuzzy lookup right on the Excel worksheeet.

You can download the add-in here :

http://www.microsoft.com/en-us/download/details.aspx?id=15011

If you want to read about the direction Microsoft is going with data cleaning in general you can find a wealth of material here :

http://research.microsoft.com/en-us/projects/datacleaning/default.aspx

There’s good documentation with the download so I won’t repeat the instructions here.

This really works.  A few months ago I heard that our Biz Dev people had access to 10K leads but many of these leads were customers already. I offered to de-dup the records to help them out.  Within 1 hour the job was done including training up the marketing manager to use the Excel Add-in in the future.

Is this a sign of Self Service DI arrriving?  or is it a defensive strategy to protect the Office cashcow from Google Docs?

About Lee Hawthorn

Data Professional
This entry was posted in Excel and tagged , . Bookmark the permalink.

Leave a Reply