Business Information

Technology insights for the data-driven enterprise

d1revolver - Fotolia

Thread the needle between applications and data with enterprise analytics

Several users and experts share advice on linking ERP transactional data with external data to speed up processes, improve profitability and uncover new business opportunities.

Two old, low-tech professions are under the microscope in Bruce Bedford's enterprise analytics project at Oberweis Dairy Inc.: the milkman delivering bottles of fresh milk to porch boxes at dawn and the mail carrier working methodically through her daily route.

The fourth-generation family-owned business, based in North Aurora, Ill., delivers premium ice cream and milk to homes and distributes them with other dairy products to grocery chains such as Jewel and Whole Foods. The company also owns 41 stores -- each one a combination ice cream shop and convenience store -- that sell packaged farm products like bacon and cheese.

Bedford, who is vice president of marketing analytics and consumer insights at Oberweis, has been using SAS enterprise analytics software to improve the profitability of the home-delivery routes, which cover several Midwestern and mid-Atlantic states. Now he's tapping the analytical models in the software to pinpoint markets for an expansion into North Carolina.

Bruce BedfordBruce Bedford

"Subscription-based" is a term used for cloud software and other technology services, but it also describes home delivery. Customers can set up standing orders for Oberweis deliveries and modify those orders online. "With a subscription-based business model like that, there are really two primary drivers of business success for us," Bedford said, pointing to customer acquisition and retention.

"We're constantly trying to grow the business by adding new customers," he said. The problem, however, is that growth won't materialize if Oberweis loses customers as fast as it can add them. "It turns out that you can actually address both problems in a single, analytically driven activity," he explained.

Targeting customer loyalty

In the past, the objective of direct mail was to get a good response rate, but that didn't address the issue of customer retention. The company discovered it could target prospects better by identifying the characteristics of those who remain customers beyond a six-month introductory period. Bedford's task was to build a model in SAS enterprise analytics to predict which of the new customers would stay and then target them in the direct mailings.

The analytics pinpointed a problem when Oberweis signed up new customers. The standard $2.99 delivery charge was often waived for new customers during the six-month promotional period. When the promotion ended, many of those customers were unhappy with having to pay a delivery charge and stopped ordering. Bedford said the company then decided that instead of offering free delivery for a specified period, new customers should start off with a lower-than-standard delivery charge for the entire first year.

Oberweis also learned that large direct-mail campaigns can be profitable as long as the company monitors response rates from customers that the analytical model identifies as good prospects. As a result, the campaigns are now the organization's primary method of adding home-delivery customers.

Most data analysts, Bedford said, try to model at the household level, but it's expensive to obtain the data from information services providers like Acxiom and Experian that have it. That's where the mail carrier comes into play. "You can actually do a very, very good job if you focus not at the household level but focus on a carrier-route level," Bedford explained. The U.S. Postal Service sorts ZIP codes according to the routes that individual mail carriers follow -- perhaps 400 to 500 homes each -- and gives postage discounts of around 50% to companies whose mailings cover an entire route.

"The analytics challenge is trying to identify those carrier routes where you're very likely to find your target customer in high proportion," Bedford said, so you can focus on them and avoid routes with low percentages of customers. With the SAS tools, he added, "We are able to find our best customer and reach them at a very, very attractive postage rate."

For Oberweis home deliveries in North Carolina, Bedford has been applying the customer-acquisition model to score carrier routes in territories where the company has no experience with customers. Dashboard heat maps show the probabilities of consumers accepting the service and sticking with it long enough for Oberweis to break even financially.

Delivering essential data

At Oberweis, an aging Ross ERP system (a technology now owned by software vendor Aptean) runs on an IBM AS/400 minicomputer. Specialized for the dairy industry, it handles delivery routes and the complexities of federal price controls. "It is difficult to navigate, quite honestly," Bedford said, citing the two-digit variables for dates. "We have integrated it with SAS tables to make it a lot easier to deal with." Batch jobs run nightly to bring information into SAS data sets. "We've got some ODBC [Open Database Connectivity] connections straight into that ERP system as well," he said.

The ERP software lacks master data constraints, and Bedford said he regrets not pushing early on for a person devoted to master data management. "It's just tables that accept numbers and characters, and the data is ugly because of that," he said. "You can get into the system and literally put in just about any number you want, in any field you want, with really no limitation. So it's really dependent on people knowing what they're doing when they're entering data. Bringing it over to SAS allows me to do a lot of processing of the data that cleans it up."

For enterprise analytics on the home-delivery and grocery businesses, data from the ERP delivery-routing module is combined with demographic data from Pitney Bowes. For the company-owned stores, ERP data combines with point-of-sale data from Micros (now owned by Oracle). "That allows us to have a full view of data across the company for all three channels of business," Bedford said.

Over the years, he has learned that simply throwing statistics at people who lack the necessary technical training turns them off. "It took me a little while to figure out that in order to get them to understand the value of analytics, a picture was worth so much more than a discussion of standard errors. So I started to give presentations that were much more graphically oriented," Bedford explained. "The challenge has been getting people to see that those numbers sitting in tables have an extraordinary amount of value that extends far beyond just a graph or a report."

As if lives depended on it 

"Fast" is the operative word for Jonathan Greenberg, IT director of the Fast Analytics team at the University of Michigan Health System, based in Ann Arbor, Mich. "There's an opportunity cost that's lost when you don't engage your customers when they're energetic," Greenberg said. "It's not perfect analytics, but it's fast, and it gets you 80% down the road in a couple of weeks instead of 100% in a couple of months."

Jonathan GreenbergJonathan Greenberg

Although the organization's main enterprise system isn't a huge ERP platform, it is "epic" nonetheless: the electronic medical record (EMR) system sold by Epic Systems Corp. Medical practices use EMRs to manage patient records, coordinate care and account for finances. A key component is the revenue-cycle module that manages reimbursement from government agencies such as Medicare.

Greenberg was working in the revenue-cycle department in 2010 when the university moved to Epic from an older EMR system. "I recognized that Epic wasn't going to have the kind of reporting we needed," he said. He considered 14 analytics vendors and chose Tableau Software. "We were looking for a way to accomplish something that we hadn't been able to accomplish before, namely providing people with the means to answer their own questions," Greenberg said. The initial goal was to simplify the revenue-cycle reporting process. Employees could only reconcile 13% of cash accounts and had to do it manually.

Greenberg's team combined Epic EMR data with "a bunch of bank files" in a new data mart. "We built analytic tools to understand the data better, and then … we built these reconciliation tools to do the work." The system automatically reconciled 97% of the cash, and the additional tools let staff reconcile the remaining 3%.

Another enterprise analytics project combines Epic data with relative value unit (RVU) information from the Medicare agency that administers payments. That agency assigns an RVU to each code that designates a medical procedure, and at the end of the month, the university collects the RVUs, using them to allocate departmental funds and calculate bonuses and salaries.

"For the most part, 90% of these values come directly from [Medicare]," Greenberg said. "We take that file every year and we combine it with our data and we produce these dashboards that give each department the ability to drill down at many levels and figure out who's generating RVUs, why are they generating them and what they are doing to generate them."

After proving Tableau's value, Greenberg's team implemented it for 30 other departments. At the start of this year, the team was transferred to the main IT group for the university "to provide a more institutional service." Under Tableau, the platform is an Oracle database. The team used Oracle Application Express (Apex) to create web forms for data entry, and OmniGraffle diagramming software helps the Mac-centric shop brainstorm new applications and data visualizations.

In retrospect, Greenberg said he could have been more sensitive to the human aspects of introducing new approaches into a traditional organization. "It can be unsettling to folks who are established in other roles," he said. Designing effective visualizations also can be a challenge. "If you don't have the skills and the knowledge of the data," he explained, "you're just going to build beautiful graphs that are wrong. There's a danger in that because things that look good and respond well and feel authentic -- that doesn't necessarily mean the data's right."

Integrating for a global view

Effective enterprise analytics often requires integrating the applications that hold the data. At Arc'teryx Equipment, a maker of outdoor clothing and accessories based in North Vancouver, British Columbia, two vastly different ERP systems that managed business functions and geographic regions had to be brought together under one umbrella.

According to business intelligence manager William Jackson, several years ago , Arc'teryx started using QlikView analytics from Qlik on a homegrown ERP platform that also handled customer relationship, factory and warehouse management. But its Finnish parent company Amer Sports Corp., which handled sales in some countries, ran SAP software.

William JacksonWilliam Jackson

Today, operational data from the two ERP systems is consolidated in a data warehouse, and QlikView and a newer product, Qlik Sense, run on top, analyzing the data from invoices, orders and customer and inventory records. "Getting it into the data warehouse had challenges," Jackson said, including "coming up with mappings for all of the different order channels, order types, return reasons and even just how shipments happen." And because the parent company is based in Europe, avoiding conflicts in the timing of extract, transform and load operations to get data from production systems into the data warehouse was challenging. There was server contention, and some operations tried to access data tables simultaneously.

With the two ERP systems integrated, Arc'teryx is able to use analytics to develop sales plans for geographic regions. "We know that, globally, our top 10 products are mainly either jackets or pants," noted Jackson. If 90% of countries have the same top-selling jacket, he explained, a country with low sales of that jacket probably represents an opportunity to sell more.

The Qlik enterprise analytics tools have become central to how about 100 Arc'teryx employees do their jobs, and another 100 -- across finance, marketing, operations and sales -- use them to varying degrees. "We've been able to take data in our production system and then present it to users in a way that they can actually make changes that impact the production of our goods as well as the delivery of those goods to our customers to help drive our customer satisfaction index," Jackson said. "Without having any sort of BI visualization tool, they definitely would not have that sort of insight available to them."

One QlikView dashboard functions as a demand-planning tool, so people can see how changes affect sales estimates and the company's ability to fill demand. Finance is mainly interested in top-line financials such as growth and net sales, Jackson said, as well as how discounts are being applied by region and trends that help them set budgets and manage the sales force.

Since adding Qlik Sense two years ago, Jackson is using the software's data market to bring in outside data and blend it with sales information from the ERP systems. "We're using that to identify and come up with a [market] basket analysis of how we can approach sales in specific regions around the world," based on weather, economic and population data, he said.

The company's wide use of enterprise analytics came with significant challenges, including training new employees to understand the data so they can present it to colleagues in a consistent manner. Jackson's solution was to identify power users who can be a first point of contact and effectively serve as extensions of the BI team.

One pitfall he didn't anticipate at the time was the importance of keeping data simple and understandable, along with providing user documentation. "I definitely would have brought in fewer fields and just made it a little more obvious and left things a little less wide open so that it wasn't as easy to get as lost in the data," Jackson said.

Ultimately, though, Arc'teryx, Oberweis Dairy and the University of Michigan Health System are all using analytics to extract more value from the data they're already collecting in their enterprise applications. In the process, they're advancing business operations in significant ways.

Article 1 of 9

Next Steps

Combine financial and HR analytics

Read a handbook on supply chain analytics

Use analytics with employee performance management

Computing trends push Amazon, Google into machine learning

Dig Deeper on Financial analytics and reporting

Get More Business Information

Access to all of our back issues View All