Data Profiling – Investigate Before You Implement – Customer Communications Management by Pitney Bowes

Customer Communication Management White Paper Data Profiling – Investigate Before You Implement Written by MAS Strategies Reston, VA www.mas-strategies.com May 2005 Customer Communication Management White Paper Data Profiling - Investigate Before You Implement Introduction: Some Scenarios: While carpenters apply the “measure twice, cut once” rule in • An analyst is using her company’s data warehouse to practicing their craft, the IT industry often ignores this warning determine the top 25 best-selling products for the prior year. when building operational and analytical systems. Few of us While the list of product numbers and dollar values were would have difficulty providing examples of systems that were relatively easy to obtain from the data in the data warehouse, the analyst could not determine what several of the product delayed, or even abandoned, due to quality issues associated numbers represented because no corresponding product with data upon which they depended. Many times these issues number values were contained in the product file. could have been avoided or implementation schedules more realistically set if an effort had first been made to audit the source data files. An audit would reveal what was really contained • Seeking to maximize its profits, a company decides to offer in the files as opposed to what was expected based on the file special service to its 100 top accounts while offering minimal documentation — assuming of course, that any documentation service to those generating minimal revenues. Unfortunately, actually existed. When setting up their databases, IT practitioners due to variances in name and address information, one of the would be wise to customize another carpentry rule: “verify, company’s top customers is represented in the customer then load.” master file as several different smaller accounts and thus not included on the “top 100” list. This customer is dissatisfied “Garbage in, garbage out” is a concise way of expressing the with the service it is receiving and is about make another concept that the quality of any system implementation is directly vendor its preferred supplier. dependent on the quality of the data it contains. Data profiling is a vital component of any data quality program; it is the component that helps identify any garbage and then monitors the data to • An organization grows large enough to deploy a sales force prevent new garbage from accumulating. It should be deployed automation (SFA) application in order to make certain it can on an ongoing basis to monitor data quality and identify any effectively track and monitor customer and prospect contacts problems so that steps can be taken to fix them before they and associated sales activities. The organization loads the become more serious. SFA application with the contact lists contained in each salesperson’s e-mail system. As a result, contacts who had Data profiling tools, although sometimes only used after-the-fact worked for several of the organization’s customers appear in to identify the causes of problem, should be deployed up-front more than one account, with multiple entries in each if, say, so that organizations can examine their source data and verify two different sales representatives used a different first name their assumptions at the beginning of the development life cycle. (e.g., Mike/Michael) and/or different spellings of a last As most systems professionals will agree, the earlier in the name for the same individual. Due to such inaccuracies, development cycle that an erroneous assumption is caught, many salespeople consider the SFA application more of a the less expensive it will be to take the necessary steps to nuisance than a productive tool and continue to maintain their own contact lists in the e-mail system or sometimes correct the problem. To use one more cliché: an ounce of even in a spreadsheet. prevention is worth a pound of cure. Page 1 Customer Communication Management White Paper Data Profiling - Investigate Before You Implement (continued) • A multidivisional company feeds vendor data from each of its divisions to a centralized data warehouse. A concerted effort Success Story: had been made by each division to ensure that its vendor file was accurate, clean and did not contain redundant entries. • A health care organization discovered that a newly Implementation of the data warehouse is delayed when, at the acquired company had been quite careless in its data last minute, it is discovered that although each division had supplied the required data content, the record layouts and value entry procedures. Social Security numbers, used as lists (e.g., region codes of 1,2,3,4 in one file and A,B,C,D in the patient identifier, were routinely entered inside another) for each field from each division’s files were not uniform; additional, unplanned efforts were required to correct this. of a free-form patient name field. The placement was seemingly random, as the number sometimes appeared at the beginning of the name field; sometimes between • A company wishes to conduct a direct marketing campaign first, middle or last names; and sometimes even within targeting consumers age 50 or older. When generating its prospect list it discovers that, at least according to the birth one of these names. After the company deployed dates in its files, several of these prospects have yet to be born software with data profiling functionality capable of while others are several hundred years old. Another campaign that targets female prospects using the same file indicates the recognizing the pattern for a Social Security number, somewhat extraordinary “fact” that prospects fall into one of it was able to search the name field and place the six distinct gender codes. number in a new field. Only then was the organization As these scenarios clearly indicate, data profiling is not just applicable to data warehouse environments; it is applicable finally able to merge the patient records of its newly to any operational or analytical system that requires quality acquired company with its existing patient records. data. This includes data warehouses, new operational applications and migrations from internally developed legacy systems to packaged applications. Many organizations have made significant investments in new systems only to discover, post-implementation, that the systems were full of incorrect data because the input sources did not contain the data that was expected. An early investment in data profiling could have identified these problems by verifying the source content and discovering any anomalies. The up-front use of data profiling will quickly pay off the very first time it discovers the fact that the contents of an important field include data values that have no relation to what was expected. Page 2 Customer Communication Management White Paper The Many Faces of Data Profiling: Data profiling is not limited to examining individual record fields, Data profiling is also used to analyze intra-record dependencies it also examines intra-record dependencies within one file or table such as highlighting a record with a code indicating a female and inter-record dependencies across two or more files or tables. that also contains a salutation code of “Mr.