Handled With Care

In reality, people who deal with customers, manage their eccentricities and the occasional exceptions of daily routines are critical to the ongoing existence of most businesses. When a customer calls, it’s his business you want, not his compliance.

Pitt Ohio Express is a Pittsburgh-based, less than truckload (LTL) freight carrier that provides odd lot truck shipping through the Midwest and Mid-Atlantic states. From 22 terminals, Pitt Ohio accepts between 9,000 and 11,000 invoices daily. Less than one-third of orders come through electronic data interchange; the rest show up in paper handoffs between drivers and customers. About 98 percent of the time, orders are placed and picked up on one day and delivered the next. Necessarily, the emphasis is on operational efficiency rather than data quality.

It’s a poor foundation for a business intelligence (BI) program, but few in the LTL business would be surprised to hear that. “On any given day we’re not entirely sure who’s going to pick up or call us for a pickup,” says Justine Russo, manager, Business Intelligence Competency Center (BICC) at Pitt Ohio Express. “So we have our drivers out in various regions servicing their areas with a set of deliveries and calling into the dispatcher and finding out where freight needs to be picked up.”

It’s something like controlled chaos, and it doesn’t answer well to a clock. Drivers might return to terminals at 5:00, 7:00 or even 9:00 p.m., but the business requires that all invoices be entered into the transactional system by 11:00 p.m. to set up processes for next-day delivery. It’s efficient because Pitt Ohio knows its deadlines and employs data entry clerks that work very quickly.

“It’s a business process problem we don’t necessarily want to solve, because we don’t want to change our operational system and slow down that freight bill entry process,” says Christine Kowalecki, data quality analyst at Pitt Ohio Express. “So we really needed an efficient process on the back end that would clean up the data” in order to support sales, market and trend analysis.

From Paper to Portal

Before Pitt Ohio built its data warehouse, there was no shortage of valuable operational data, but it came delivered in green-bar paper phonebooks not suited for any serious analysis. It became Kowalecki’s problem to manipulate transactional data so it could go into the data warehouse in a consolidated manner and be cleansed for reporting, various cubes and BI systems.

The initial solution was to create a customer master record with both shippers’ and receivers’ names entered. It took time and much manual labor to find the data entry errors behind multiple identities and addresses, but once standardized, it allowed historical information to be consolidated. “We took those addresses, determined which was the correct one and kind of ‘smushed,’ which is our technical term, the other bad addresses into it,” Kowalecki explains. “Now we could call it one address, see all the revenue, see the correct revenue, but you can also see what’s behind it, see the bad data as historical. If a freight bill entry clerk went to create another shortcut with a bad address, it would actually go to the correct one.”

Along with the pain of manual reconciliation, a remaining problem was legacy technology that had run out of flexibility. “As we were consolidating locations, there were business rules built into the old application that could not be changed as our business changed,” states Russo. “We were running into problems with recreating shortcuts, so Christine [Kowalecki] wound up doing a lot of manual work either in Excel or with this slow piece of software.” Pitt Ohio shopped vendors for a new solution and, in the end, chose DataFlux dfPower Studio.

According to Russo, fuzzy matching in dfPower Studio ended most of the manual reconciliation and was able to match 650,000 records to some 10,000 actual, active customers at 30,000 locations. The initial goal was 95 percent consolidation, but Russo says the actual figure was closer to 99 percent. “Sales gets really excited to see a consistent name because then they get associated data on industry, pricing and things like that,” Russo says. If there are occasional disagreements over what sales thinks a customer actually is, Kowalecki is the gatekeeper who determines what ends up in the data warehouse.

Just as important, consolidated information is placed in the hands of those who directly need it through a portal interface called SIM, for sales information management system. “It is a single report view of any customer showing monthly totals, year-over-year of revenues, tonnage, shipments, servicing as well as service levels for that customer’s on-time percentage,” explains Russo. The report can be printed or emailed to the customer as a measure of Pitt Ohio’s service performance.

SIM carves information regionally or by other dimensions but mainly provides a useful and simple view for the nonpower user or analyst. “You type the customer name and get a 12-month and year-to-date (YTD) view,” says Russo. Separately, there are 30-plus online analytical processing (OLAP) cubes for sales managers or analysts to slice and dice to better understand the book of business.

The BICC

It might seem extravagant for a company with $261 million in revenue in 2007 to invest in a BI competency center. As it turns out, Pitt Ohio’s CFO had attended Gartner, Inc. conferences, took up the challenge and made it his business to support the work being done by Russo, Kowalecki and the IT team.