One of the more interesting aspects of working for a data quality company is the challenge associated with solving real world business issues through effective data management. In order to provide an end-to-end solution, several moving parts must be taken into consideration, data quality software being just one of them.
A few months back, helpIT was given the challenge of working with a multi-channel retail organization seeking a Single Customer View to improve the effectiveness of their marketing efforts. They received customer data from several channels including: Point of Sale, Website, and Call Center. Their hope was to link up every transaction over the past 5 years to a single instance of the right customer.
In a vacuum, this is a pretty straightforward job:
- Standardize and clean all of the data
- Identify the priority of transaction data sources and merge all sources together based on individual contact information. Develop a single list or table of all unique customers and assign a unique ID
- Take all transaction data sources and then match against the unique customers and assign the ID of the unique customer to the transaction.
With live business data it’s rarely if ever that simple. Here are some of the issues we had to circumvent:
- Very high rate of repeat customers across multiple channels
- Different data governance rules and standards at all points of capture
Point of Sale – Only name and email were required. When no address was provided – store address was used. “Not Provided” also acceptable causing other incomplete data Website – Entered as preferred by customer Call Center – Typed into CRM application, no customer lookup process
- No “My Account” section on the website, which means that all orders are treated as new customers
- Archaic Point-of-Sale application that was too expensive to replace for this project
- Newly created SQL Server environment that acts as a central data repository but had no starting point for unique customers
To come up with a solution that could enable the customer to develop and also maintain a Single Customer View we proposed the following workflow that could be used for both.
This was immediately identified as the best source of information because the customers are entering it themselves and have the genuine desire to receive delivery or be contacted if there is an issue with the orders. The process was started with an initial batch clean up of all historical web orders as follows:
- Run all orders through Address Validation and National Change of Address (NCOA) to get the most up to date information on the contacts
- Standardize all data points using the matchIT SQL casing and parsing engine
- Performe contact level deduplication with matchIT SQL using combination of exact and fuzzy matching routines and included confidence scores for all matches.
- Our client identified the confidence thresholds that were either confident matches to commit, matches that required manual review, and unique records. They completed the manual review and incorporated some further logic based on these matches to prevent future review for the same issues. The final step in their process was to identify a future score threshold to commit transactions from other sources to the customer record.
- The deduplication process was finalized and a new SQL Server table was created with standardized, accurate, and unique customer data that would act as the “golden record”.
- All transactions from the Web history file were updated with a new column containing the unique customer ID from the “golden record” table.
This was the next area to undertake and was essentially a repetition of the process used on the Website data with the exception of the final steps of matching the cleaned version of the Call Center data with the “golden record” table.
After performing the overlap, any unique customers from the call center were then added to the “golden record” table and assigned an ID. All the overlapping Call Center customers received the overlapped ID from the “golden record” table which was then appended to the related Call Center transactions.
This was the tricky part!
Some of the data contained full and accurate customer information, but nearly 30% of the customer transaction data captured at the store level contained the store address information.
So how did we overcome this?
- We created a suppression table that contained only their store addresses
- All store transactions with the minimum information for capture (at least a name and address) were standardized and then matched against the store suppressions file yielding a list of matches (customers with store info as their address) and non matches (customers that provided their own address information)
- For the customers that provided their address the process then went back to the same procedure run on the call center data
- For the customers with store information we had to use a different set of matching logic that ignored the address information and instead looked to the other data elements like name, email, phone, credit card number and date of birth. Several matchkeys were required because of the inconsistency in what matches would be found.
- The client then decided for the remaining portion of customers in the Store file (3%) to put those customers in a hold table until some other piece of information popped up that would allow for a bridging of the transaction to a new transaction.
A workflow diagram of the store process can be found to the left:
The key to the whole strategy was to identify a way to isolate records with alternative matching requirements on an automated basis. Once we separated the records with store addresses we were free to develop the ideal logic for each scenario, providing an end to end solution for a very complicated but frequently occurring data issue.
If you are attempting to establish the ever-elusive single customer view, remember that there are several moving parts to the process that go well beyond the implementation of a data quality software. The solution may well require a brand new workflow to reach the desired objective.
For more information about establishing a Single Customer View or to sign up for a Free CRM Data Analysis, click here.