Carragee Consulting

Sometimes the most basic definitions represent a huge amount of complexity in business analytics.

I worked for an industrial laundry company that had +60 AS400 systems, one at each of our production facilities whose data compiled into a central data warehouse for reporting.

The customer master data was setup by the office managers at each location when new accounts were sold. They had different ways of assigning Customer IDs to locally serviced accounts and those with national contracts across locations.

For pricing purposes we considered a “Customer” the concatenation of Production Company (CO) & Customer (CUST). Sub-locations for the same customer were set up by Location (LOC).

Our pricing was a matrix based on some activity based costing analysis which gave lower unit prices for the total weekly customer purchase amount across locations and the amount paid at each truck stop.

Customer ID was not unique across locations because there was no enforcement at account setup time in the separate AS-400s.

Sometimes multiple locations at the same address were set up to divide out facility services products from uniform rentals, or to cue to the driver to use different entrances for the same building.

The finance group, the national accounts team and the pricing team each had different definitions of “Customer” which aligned with different data groupings and attributes. We were all trying to leverage the same few fields for different analytical and operational purposes so its no wonder our definitions lacked consistency.

Additionally, the concatenated keys we used for analysis were not stored in the reporting systems, they were always calculated in queries (or excel), sometimes with delimiters, sometimes as int, sometimes as varchar.

To report on something as simple as average customer weekly revenue, or percentage growth YoY you always ran up against the same question of “What is a customer?”,

I didn’t really know how common these issues were until years later as I’d seen many more companies data. There is always incongruous data across departments and many versions of the “truth”. It only gets more complicated with acquisitions, system conversions, reorgs and time.

This sort of semantic confusion and basic data modeling challenge is one of the reasons I don’t see AI replacing data engineers, business analysts and thinking people any time soon.