Data Warehouse Informs Decisions at Red Apple

MICHAEL GARRY

NEW YORK — Red Apple here has begun leveraging a data warehouse system to uncover insights into labor and ordering requirements as well as develop a better understanding of consumer buying habits at specific supermarkets across its 34-store Gristede's chain.

Red Apple received the refrigerator-sized data warehouse — a Data Warehouse Appliance from Teradata, Dayton, Ohio — at its Manhattan headquarters in February, and after an evaluation period, purchased the technology in April, said Chris McCrae, Red Apple's chief information officer, who joined the company 20 months ago. The appliance includes analytical software for rapidly addressing complex queries.

The data warehouse is helping McCrae to “change the culture” at Red Apple and “make IT part of business decisions.”

Red Apple also operates around 400 convenience/fuel stores in New York, Pennsylvania and Ohio as well as a fuel refining subsidiary, but so far the data warehouse is being applied only to the Gristede's grocery outlets located throughout Manhattan.

The data warehouse, which holds up to 20 terabytes of data, has been populated with two years' of POS transaction data (about 200 million rows), as well as data from time-and-attendance, payroll, loyalty card and direct-store-delivery receiving and other vendor systems, adding up to under one terabyte so far. POS data and time-and-attendance are added daily, payroll weekly, and receiving and electronic data interchange (EDI) in real time. Base data is backed up at Red Apple's warehouse in the Bronx.

Red Apple takes a “ragu approach” to the data warehouse, adding all manner of data to generate financial and marketing reports, said McCrae. These are reports “we were not able to get before or took a long time to get, which didn't facilitate timely decision making.” Previous reporting was done quarterly and the reports, based in silos of data housed in SQL servers or in applications, were unable to be “spliced together,” he said.

The data warehouse was incorporated into Red Apple's existing Microsoft Windows and SQL environment, including Microsoft Business Intelligence and Workflow system for delivering data and reports to users. “There was no paradigm shift for users other than the performance increase,” said McCrae, though some managers and department managers are seeing certain reports for the first time.

HEAT MAP

One new report is a “heat map,” created in seconds, that delineates the transactions per hour generated by each store, from which the busiest time periods can be seen.

The transaction report has been merged with time-and-attendance data for hourly employees, allowing, for example, store managers to determine whether front-end labor matches demand, noted McCrae. This helps guide the scheduling process, which has been done manually, and has resulted in a reduction of overtime hours, he said, declining to be specific.

In addition, Red Apple has “automated alerts that indicate when actuals are close to or over budgets for labor,” he said. “We also have similar alerts for part-time workers who have a number of weeks in a row of full-time hours.”

Transaction data has also been married to receiving data to provide a “pseudo-perpetual inventory” at each store, said McCrae. This enables department managers to assess the accuracy of ordering, initially for such perishables as dairy, and prevent over-ordering and shrinkage at underperforming stores.

The data warehouse is also being used to give Red Apple “a better handle on knowing the customer,” said McCrae. Whereas the company was previously only able to consider average metrics for basket size — dollars and number of items per transaction — the new technology delivers a much more refined and detailed analysis of consumer purchasing trends. “We now have a better understanding of what customers are buying, which impacts our marketing and pricing decisions,” he said.

The data warehouse also informs SKU rationalization decisions. Items that would have been slated for chainwide discontinuation because of poor average sales may be retained at stores where they are found to sell, he said.

While the data warehouse has been used for standard reporting, it is also being employed on an “ad hoc” basis by Red Apple managers. “As we provide these reports, people are realizing they can be the driver, and come to view [the IT department] as their partner,” said McCrae. “So we're fielding more requests.” These include queries — such as what are the top 100 products per commodity in sales and profits compared with the previous month, quarter or year — that influence buying and marketing decisions.

COST AND ROI

The initial cost of the data warehouse, said McCrae, was $250,000, adding that the ROI is estimated conservatively at 18 to 24 months. He considers the system a “cost-effective way” of getting results similar to what a host of separate applications would provide.

But Mike Griswold, vice president, research, AMR Research, Boston, pointed out that while a data warehouse can process and analyze data, it “can't serve as the executional system. You need to act on the information provided from the data warehouse. The data warehouse won't create labor schedules or perishable orders.”

In evaluating the Teradata data warehouse, McCrae compared it with an equivalent SQL server as well as other competing systems and open source systems. He expected the Teradata appliance to be out of his price range, given that large retailers such as Wal-Mart Stores are among its customers, but found out that mid-market models were available.

Griswold considers it “a little bit unusual” for a retailer the size of Gristede's to have a data warehouse, “not because these companies don't want to, but because these types of applications can get expensive very quickly.”

Still, “more and more small chains are looking into data warehouses,” said Thomas Murphy, president, Peak Tech Consulting, Colorado Springs. “This is in part because they are finding the information necessary to compete, and because [vendors] have structured offerings that fit their needs and budgets.” Other data warehouse providers include IBM, Oracle, SAS, Kalido and Infobright.