Business Problem

Our customer for this chapter is a bricks-and-mortar retailer who has recently acquired an online competitor. The companies have many customers in common, and both companies sell similar products. The bricks-and-mortar stores have a loyalty program that they use to profile their customers' buying habits, and the online company tracks their active members using customer account numbers.

Problem Statement

The company wants to have a unified view of sales from both sources. They are also experiencing problems with analytic reports being inaccurate and incomplete. Sales results are always lower than expected. The problems have been traced to poor quality source data.

It isn't possible to get a combined view of a customer's activities in the store and on the Web. The Internet customers do not have a loyalty card that they can use at a store, and the loyalty card is not recognized by the online checkout. The two customer lists have not been successfully combined into a common list. An attempt was made to merge the customer lists, but customers who were patrons of both stores were never resolved to one customer. Minor differences in the customer records caused a significant number of matches to be missed.

Detailed customer data is sometimes missing because a new customer can obtain and use a bar-coded loyalty card by filling out a form at the cashier, but the data on the form may not be entered into the system for several weeks afterward.

Some product sales data is missing from regular reports, and the totals from the data warehouse do not always agree with what the stores report. When the two product catalogs were merged, they tried to match every product stock-keeping unit (SKU) between them and assign a new SKU to every product. However, this was a semi-manual process, and it seems some products were missed. Cashiers simply key in the SKU and the price marked on the packaging. Customers are returning some goods with the old SKUs marked on the packaging. None of these SKUs show up in the analysis because the SKUs aren't in the catalog.

Finally, the two sales projection systems haven't been integrated yet, and some budgets are being entered via spreadsheets. Through misspellings, the product categories can't always be determined.