Managing Complexity Through Data Warehousing

Coca Cola Japan uses a solution from NCR Teradata to manage almost 1 million soft-drink vending machines.


Moreover, in recent years Coca Cola Japan, which franchises 12 bottling companies in Japan, has faced increased competition from other beverage suppliers, including entrants from other industries who see strategic opportunities to expand into the high-margin vending machine channel.

During this same period, promotional costs and distribution expenses have increased, while lower-margin products have grown as a percentage of sales.

Using Vending Machine Data

In this ultra-challenging environment of stiffening competition, diversifying consumer needs and desires and rising costs coupled with lower profitability, Coca Cola Japan group was prompted to examine ways it could better exploit data from vending machine sales, in order to reverse the declining market share trend while improving profitability.

Coca-Cola West Japan Co. (CCWJ), Mikuni Coca-Cola Bottling Co. (CCBC) and Hokuriku CCBC were among 12 bottlers who became involved in this
initiative.

These companies were already capturing column-by-column data from vending machines, but it was used almost exclusively for settlement. The companies had no good way to capitalize on this untapped goldmine of detailed information on exactly what was selling where and when and at what price.

Their goal became the development of a system that would enable them to leverage this potential wealth of data for both strategic and operational purposes, to support management and analysis of sales, refill ratios, stock-outs, machine failures, refill planning support, location performance and all aspects of category management.

Working with NCR Teradata, Coca Cola bottling partners developed an extensive data warehousing application to accomplish these objectives. More recently, the system was linked by Hokuriku CCBC directly to its vending machine column control system, creating a comprehensive data management architecture that can be used for everything from high-level strategic analysis to supporting daily route operations.

Data from the vending machines is collected by the system in two ways. About 20,000 Coca Cola vending machines are wirelessly networked online, so that data from their operations flows into the system automatically. Data from off-line (i.e. non-networked) machines is collected by route salesmen on hand-held terminals when they visit each location and uploaded to the host system when they return to the office at the end of their routes.

The information collected includes sales data by column, including volume and price; sold-out information, including column, product, time and duration; and machine breakdown information, including time and cause.

The data is stored in the data warehouse and analyzed by marketing people and operations managers at each bottler. Aggregated sales information by vending machine is sent to Coca Cola Japan and to The Coca Cola worldwide headquarters. Data is maintained in the warehouse for at least three years in granular form.

Among the most important jobs in vending operations is deciding which vending locations need to be visited when, so route salespeople don't waste time visiting unnecessary locations, notes Atsumasa Shimizu, managing director of Hokuriki CCBC and president of HISCOM, a subsidiary of Hokuriki CCBC, which specializes in vending software technology applications.

For this application, real-time sales information provided by online machines is most beneficial, but historical data is also helpful in analyzing trends that can be used for route planning.

"The secret of improving operations is to increase the length of visit or call intervals while prohibiting sold-out situations," Shimizu points out. Normally, increasing the interval between visits would lead to more stock-out occurrences. By leveraging real-time, "fresh" data from the online, networked vending units, the bottlers have been able to greatly improve forecasting, resulting in better route sales productivity coupled with fewer stock-outs.

Mikuni CCBC, the first to implement the data warehouse system in 1998, for example, achieved a 13.6 percent decrease in stock-outs combined with a five percent increase in refill rates and a 10 percent increase in route salesman productivity in the first year. Both Mikuni route salesmen and high-level managers make decisions based on information accessed in the data warehouse. These and other benefits from the system resulted in improved profitability to the tune of 800 million yen a year.

Already have an account? Click here to Log in.

Enhance Your Experience.

When you register for FoodLogistics.com you stay connected to the pulse of the industry by signing up for topic-based e-newsletters and information. Registering also allows you to quickly comment on content and request more infomation.

OR

Complete the registration form.

Required
Required
Required
Required
Required
Required
Required
Required
Required
Required
Required