WO1995030201A1

WO1995030201A1 - Method and apparatus for real-time tracking of retail sales of selected products

Info

Publication number: WO1995030201A1
Application number: PCT/US1995/005374
Authority: WO
Inventors: Michael C. Scroggie; Robert N. Billings; Kevin R. Shields; Eric N. Williams
Original assignee: Catalina Information Resources, Inc.
Priority date: 1994-05-02
Filing date: 1995-05-01
Publication date: 1995-11-09
Also published as: AU2463895A

Abstract

A system for generating timely sales performance reports based on sales data recorded at point-of-sales (POS) terminals (14) in multiple stores (10) and transmitted to a central data processing site (18) on a periodic and frequent basis. Target items sold in each retail store are detected by their unique product codes and data records pertaining to these sales of target items are captured at the store, for transmittal to the central processing site. The data records are 'cleaned' (34) to reduce the occurrence of anomalous or erroneous data, and consolidated into a relational database (40) that can be queried by users to obtain various sales performance reports. The database contains standard measures of sales performance derived from the data collected and the reports permit users to assess the effectiveness of a sales promotion, and to obtain early warning of distribution voids indicative of inventory or stocking problems.

Description

METHOD AND APPARATUS FOR REAL-TIME TRACKING OF RETAIL SALES OF SELECTED PRODUCTS

BACKGROUND OF THE INVENTION

This invention relates generally to systems for processing retail sales data and, more particularly, to systems for using retail sales data gathered at the point of sale (POS) for purposes of inventory control and performance analysis. There has long been a need in the retail sales business for a more efficient approach to inventory control, especially for items that are delivered directly to stores from specialty suppliers. These direct store delivery (DSD) items, such as soft drinks and ice cream, are typically delivered to each store by the supplier, rather than through a retailer central warehouse. Because many of these items are relatively fast moving, the retailer and the supplier often face a difficult task in deciding on the quantity of product to deliver to each store. The difficulty is compounded by the profusion of sizes and, in many cases, flavors for each product. Typically, the retailer has no way to determine accurately how big the next day's shipment of, for example, ice cream should be, and the supplier may have to ove^rstock each delivery truck and defer a final decision until the truck reaches the store. The retailer or the supplier truck driver can then take inventory and finalize the order. This is but one example of an inventory control or restocking problem.

Another important consideration is the efficient use of shelf space in retail stores. Shelf space is a limited and, therefore, valuable commodity to retailers. If shelf space is allocated to a new product, which does not sell as well as expected, the retailer would prefer to reallocate at least some of the shelf space. Unfortunately, however, sales performance data for the new product may not become available for days, or even weeks, after its introduction.

Obviously, a high level of inventory causes inefficient shelf space utiliza¬ tion and increases product spoilage and customer returns of spoiled items. Too little shelf space may mean lost sales because of out-of-stock conditions. One solution is to make more frequent deliveries, but this approach increases distribution costs and requires higher quantities of products in the distribution pipeline. Various systems have been proposed to provide inventory control information to the retailer. For example, the system disclosed in U.S. Patent No. 3,899,775 to Larsen discloses the use of multiple POS terminals from which sales data are transmitted to an in-store processor to provide management reports on items such as inventory, sales rates and checker productivity. Other patents suggesting the use of scanned sales data for inventory control are Harris (U.S. Patent No. 3,737,631), Gechele et al. (U.S. Patent No. 3,770,941), and Kawashima et al. (U.S. Patent No. 5,168,445).

These and other proposed similar systems focus on inventory control from the retailer's perspective, providing a retailer with a historical view of what sold in the not-too-recent past and, therefore, what to order in the future. Such systems do not accurately reflect the customers' desires and usually result in too many of the wrong products and too few of the right products being in the store at any given time.

Although POS product scanning systems have been installed in retail stores for some years, there has been no way, prior to the present invention, to utilize the sales transaction data in such a way as to provide timely and useful sales performance analysis data and inventory control data. One difficulty has been that the sheer mass of data gathered at large retail stores has acted as a deterrent to die development of efficient tools of this type. There are tens of thousands of package goods manufacturers selling products to several hundred food retailers at thousands of retail store locations. It is difficult, and often impossible, for a manufacturer to obtain performance information from hundreds of retailers about the sales performance of a single product of interest. A report with such information, if available at all, is likely to be several weeks old and may contain significant errors. Details that would be useful to the manufacturer, such as the identity of low-volume stores with respect to a particular product, may be missing from the report.

Ideally, what is needed for efficient inventory control is a system that meets three related goals: satisfying the customer by correctly anticipating customer needs, satisfying the retailer by maintaining the needed products in stock without inefficient use of shelf space, and satisfying the manufacturer or distributor by providing a cost-effective way for the needed products to be distributed. The present invention is directed to these ends.

SUMMARY OF THE INVENTION

The present invention resides in a method and related apparatus for creating and maintaining a database of retail sales transactions, based on sales data transmitted from store locations on a periodic and frequent basis. Briefly, and in general terms, the method of the invention comprises the steps of monitoring store computer data relating to sales transactions in multiple retail stores; capturing target sales data pertaining to sales of at least one preselected target item in each of the multiple stores; periodically transmitting the captured target sales data to a data processing site; receiving the transmitted data at the data processing site; integrating the received data into a database; and generating a selected sales performance report from the database in response to a user query.

More specifically, the captured target sales data transmitted to the data processing site includes, for each target item, store identification data, a record of the current date, a code uniquely identifying the target item, data indicative of the number of these target items sold, and data indicative of the total amount spent on the target item. In the preferred embodiment of the invention, method further comprises the steps of deriving various standard measures of sales performance from the target sales data received at the data processing site; and using selected ones of the standard measures of sales performance in the step of generating a selected sales performance report. Also in the preferred embodiment, the step of monitoring the store computer data includes passively receiving data from a store communications loop connecting multiple point-of-sale (POS) terminals at the store; and the step of capturing target sales data includes checking item sales records for presence of an item identifying code that matches that of a target item and, if a match is found, saving the sales record relating to the matching item. The step of generating a selected sales performance report includes possible steps of generating various specific reports. Some of these steps include generating a distribution void report that identifies stores at which a target item has not been sold during a selected reporting period; generating a distribution void summary identifying potential under-stocking problems that result in lost sales; generating a potential out-of-stock report based on sales of a target item being below a baseline sales level by more than a preselected percentage; generating an order assistance report to assist in placing replenishment orders based on actual sales of a target item in relation to short-term sales projections; generating a list of target items and associated stores at which the sales price of the item has been lowered by more than a preselected percentage below a previously established base price; or generating a merchandizing effectiveness report indicative of improvement in selected standard sales performance measures in response to a promotion lowering the price of a target item.

In terms of novel apparatus, the invention may be defined to comprise means for monitoring store computer data relating to sales transactions in multiple retail stores; means for capturing target sales data pertaining to sales of at least one preselected target item in each of the multiple stores; means for periodically transmitting the captured target sales data to a data processing site; means for receiving the transmitted data at the data processing site; means for integrating the received data into a database; and means for generating a selected sales performance report from the database in response to a user query. The invention may also be defined in apparatus terms of varying scope similar to that used above in defining the invention as a method.

It will be appreciated from the foregoing that the present invention represents a significant advance in the field of retail sales performance analysis and retail sales inventory control. The invention provides extremely timely reports of sales performance of selected target items, based on currently observed sales transactions rather than historical data. Therefore, manufacturers can make informed decisions involving the timely restocking of items to minimize lost sales in retail stores, and pertaining to the effectiveness of sales promotions. Other aspects and advantages of the invention will become apparent from the following more detailed description, taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

FIGURE 1 is a block diagram providing an overview of the present invention; FIG. 2 is a block diagram illustrating the major components involved in data capture at retail store sites;

FIG. 3 is a flow diagram showing the functions performed in capturing sales data at the store sites;

FIG. 4 is a flow diagram providing an overview of the data production process;

FIG. 5 is a flow diagram depicting the data cleaning process;

FIG. 6 is a flow diagram depicting the data baselining process;

FIG. 7 is a flow diagram depicting the distribution void application;

FIG. 8 is a flow diagram depicting the application for producing distribu- tion void summary reports;

FIG. 9 is a flow diagram depicting the potential out-of-stock application;

FIG. 10 is a flow diagram depicting the order assistance application;

FIG. 11 is a flow diagram depicting the price check application; and

FIG. 12 is a flow diagram depicting the merchandising effectiveness application.

DESCRIPTION OF THE PREFERRED EMBODIMENTS

Overview:

As shown in the drawings for purposes of illustration, the present invention pertains to a method and system for providing timely reports for use in sales performance analysis and inventory control, based on sales transaction data gathered from retail stores. Various attempts in the past to provide reports of this general type have not met the needs of customers, retailers and manufacturers, particularly manufacturers that deliver products directly to retail stores. In accordance with the present invention, sales transaction data for selected products are gathered in real time at point-of-sale (POS) terminals in thousands of retail stores, transmitted through communications links to a central site, and stored in a database. Clients or users, who are typically product manufacturers, can access the database through conventional computer terminals and obtain timely reports relating to sales of specific products.

FIG. 1 shows the flow of data among the various components in accordance with the invention. Multiple store locations, indicated by reference numeral 10, each have a data capture computer 12 connected to a conventional store processing loop 14, as will be discussed in more detail below. The data capture computer 12 is separate from and independent of a conventional store controller (not shown) used to control multiple POS terminals in a store. In a conventional POS system, all of the data sensed by scanners at the terminals is transmitted along a communication path referred to as the store loop. The data capture computer 12 is connected to the store loop data flow, as indicated at 14, in such a manner as to passively collect data pertaining to each transaction at each POS terminal. Sales data are collected and written into a data storage device 16 when specific items are scanned at the POS terminals. Each product or item is recognizable by its unique bar code, referred to as the uniform product code (UPC), printed on the product package.

The collected data records in the data storage device 16 are periodically transmitted to a store data collection center 18 over a communication path 20 that may take the form of a telephone line with appropriate modems (not shown) connected at each end. Depending on details of design, the data may be transmitted daily, hourly, every few minutes, or even very few seconds. At the store data collection center 18, the transmitted data records are received by a store communication computer 22, which passes the received data to a store database computer 24, which, in turn, accumulates the received data in a targeted UPC data storage device 26. The store communication computer 22 also transmits control information to the store data capture computers 12. In particular, the store communication computer 22 sends updates to a target UPC list, contained in files 28 at the store data collection center 18. The target UPC list defines those items for which data will be captured at the store locations 10. There may be one or more store data collection centers 18, each of which communicates with a single data production center 30, through a production center communication computer 32. The communication link 33 between the store data collection center 18 and the data production center 30 may also be a telephone line, or may be part of network of interconnected computers. Communication over the communication links 20 and 33 may optionally include data compression and decompression steps, to speed up the transmission time, and may also include appropriate encryption and decryption steps for security purposes. Details of implementation of data compression and encryption are not, however, within the scope of the present invention. The data production center 30 includes a data cleaning and preprocessing computer 34 and a database building computer 36. The data cleaning and preprocessing computer 34 performs a desirable, although not absolutely necessary step of filtering the data for anomalies and errors. The various processing steps involved in data cleaning and preprocessing are further discussed below. After preprocessing, the data may be stored temporarily in a processed data storage device 38, from which the data records are retrieved by the data building computer 36 and integrated into a cumulative database containing all the collected and preprocessed data from all the stores and pertaining to all the items for which data has been captured. Incoming data will be held in the cumulative database 40 for some selected time period before being routinely purged from the system or archived for future analysis. As will be further discussed below, the cumulative database is preferably managed by a commercially available relational database system permitting easy access and manipulation of the database records. Access to the database by users is afforded through a client computer 44 at a remote site. The client computer 44 communicates with a report generator computer 46, which, in turn, accesses the database 40 and generates timely reports for display or printing at the client computer site.

The functions mentioned in this overview will now be discussed in more detail. It will be understood, however, that the separate computers referred to in the description are for purposes of illustration. Some of the functions described may be performed equally well in a single processing unit operating in a time-sharing or multi¬ tasking mode.

Store Site Data Capture:

As shown in FIG. 2, a typical store has multiple scanners 50 and corresponding POS terminals 52. These are connected through the communication loop 14 to a store POS controller 54, as is conventional. To capture data for use in accordance with the invention, a loop attachment device 56 passively monitors data on the store loop 14 and transmits the data to the data capture computer 12. The latter contains, in hardware or software form, transaction logging logic 58. The data capture computer 12 operates in conjunction with the data storage device 16, which may be a disk drive or other mass storage device. The data storage device 16 contains a tracking target file, i.e., a list of all the "target" items for which data will be collected for purposes of the invention, and a product movement data log file, in which item-level sales transaction records are recorded for all product sale items detected by the POS terminals via the loop attachment device 56. In the system as illustrated, the data capture computer 12 communicates through a modem 60 and a communication line 20 to the store data collection center 18. Transmission of data records over line 20 is performed on a regular and periodic basis, such as every day, hour, minute, or shorter time. The transmission can be initiated on a timed basis from the data capture computer 12 or from the store communication computer 22 (FIG. 1). The loop attachment device 56 is typically installed as a circuit board in an expansion slot of the data capture computer 12, which may be a conventional personal computer. The device 56 operates passively, i.e. it does not transmit any data onto the store loop 14 and does not, therefore, interfere with normal operations of the store loop, the scanners 50 or POS controller 54. The loop attachment device is not, however, non- invasive, because direct electrical connections are made to the loop. Non-invasive store loop monitors are subject to errors because of electrical interference with the detected signals. The design details of the loop attachment device 56 depend on the particular type of store POS controller network employed and on other factors. For example, specific terminal nodes for the system may need to be defined and interfaced with the store POS controller 54, a simple network access may be all that is needed. Some POS systems allow an asynchronous communications protocol that enables a simple serial input port of the store controller 54 to be used as the store loop attachment device.

The functions performed in the data capture computer 12 at each store in logging data to the data storage device 16 are shown in more detail in FIG. 3. Data input, indicated by block 64, is obtained from the store loop and first checked to determine if it contains UPC data, as indicated in block 66. (Other types of data are also detected and read from the store loop.) If a UPC data record is detected, d e next question posed by the processing logic (in block 72) is to determine whether this UPC item is already in a list created for the current customer transaction. If the UPC is already in the list, a list entry for the UPC needs only to be updated, as indicated in block 74, and then a return is made to the wait state to await interruption to process any subsequently received data (block 76) to await the next data input. If the item is not in the list, it is added to the list, as shown in block 78, before returning to the wait state. If the data input is not UPC data, as determined in block 66, a test is made (in block 80) to determine if it is "tender data," i.e., data relating to the tendering of payment by a customer. Detection of tender data is used to determine the end of a transaction. If the input data is not tender data, the computer returns to the general wait state, as indicated in block 82. When a tender data record is detected, the items in the product movement list are logged to the log file on storage device 16, as indicated in block 84, and are then cleared from the list, as shown in block 86 in preparation for processing the next transaction. Then the computer returns to the general wait state, as indicated in block 88.

It will be understood that the processing steps described with reference to FIG. 3 have to be performed for all POS terminals in the store simultaneously and, therefore, the programming logic involved is somewhat more complicated than that shown in the figure. Conventional programming techniques involving multitasking or the use of re-entrant code may be employed to achieve execution of the processing steps of FIG. 3 for all POS terminals simultaneously.

Transmission of Data from the Store:

Either on a real-time basis or at regular selected intervals, retail sales transactions are summarized (e.g., by UPC, time, lane, etc.) and formatted by the data capture computer 12, in accordance with programmed instructions contained in the random access memory (RAM) of the computer, and transmitted to the store data collection center 18 (FIG. 1). In a presently preferred embodiment of the invention, communication between the data capture computer 12 and the store data collection center 18 is by means of a conventional modem 60, using dial-up or switched network telephone lines for the communication link 20. During a communications session between the data capture computer and the store data collection center 18, data corresponding to actual item level retail sales transactions are uploaded to the data collection center. During the same session, the data collection center may remotely update or change the operating program stored in the RAM of the data capture computer 12, and may perform testing, as desired.

In the presently preferred embodiment of the invention, periodic communications sessions are scheduled and initiated by the store data collection center 18. Once a session has been established, the data capture computer 12 issues commands to upload the sales data that it has logged since the last communications session. Record Format for the Transmission File:

The data transmission from the store locations has the following four possible record formats for transmission to the store data collection center 18 in the presently preferred embodiment of the invention. One format is an item-level format for each UPC; one is a summary format for transmitting totals; and the other two are special formats for transmitting information pertaining to situations in which the data capture system is inoperative for some reason. More specifically, the record formats are: ccc ssss mmddyyyy hhmmss LLL uuuuuuuuuuuu MUM cccccccc 1 t t t t description cc ssss ddyyyy hhm ss LLL 999999999999 SSSSSSSS 00000 ccc ssss mmddyyyy hhmmss 999999999998 r r r reason up ccc ssss mmddyyyy hhmmss 999999999997 r r r reason down

where the symbols in the format have the following meanings: ccc = a three-digit store chain number,

15 ssss *= a four-digit store number (within a store chain), mmddyyyy = the current date (month, day and year), hhmmss = the time of the transaction (hour, minute, second) if applicable,

LLL = the lane number in the store checkout if applicable, uu...uu = a twelve-digit UPC (with leading zeros if necessary),

20 ##### = the number of items sold with this UPC

CCCCCCCC = the total amount in cents spent on the UPC item, ttttt = the total number of transactions seen containing this UPC, description = 18-characters containing either: description for a new or changed price look-up (PLU),

25 all hyphens for PLUs whose descriptions have not changed, all blanks for PLUs whose descriptions are unknown at the store. The second, third and fourth alternative formats shown above contain special "UPC" codes. When the code is all 9's, the record is a summary record and the other fields other than the store identification and date fields carry the meanings:

30 $$$$$$$$ - total sales rounded to the nearest dollar,

SUBSTITUTE SHEET RϋtE 26 ooooo = number of orders.

When the "UPC" code is 999...998, the other fields have the meanings: rrr = a code indicating why die system went up, reason up = 18-character description of why the system went up. Similarly, when the "UPC" code is 999...997, the other fields have the meanings: hhmmss = the time the system when down, rrr = a code indicating why the system went down, reason down = 18-character reason why the system went down.

The system-up/down data records permit appropriate allowances to be made in subsequent processing if the data capture system is out of operation for some reason. For example, if the whole store system is down for an hour, presumably no data has been lost, but if the data capture computer alone is down, there may be a temporary inability to capture sales data pertaining to targeted items, and it may be necessary to interpolate from available data, or at least to draw the user's attention to the lack of raw data.

It will be understood that the sequence in which the fields appear in the data record shown above is not critical to the invention. Moreover, modifications may be made to the choice and length of the fields included in the raw data transmitted to the data collection center.

The Data Production Process:

Data production, as indicated within block 30 of FIG. 1, is the process whereby raw captured data records gathered from the store POS scanners are cleaned and preprocessed, and then integrated into the cumulative database 40. Some of the steps of cleaning and preprocessing are more desirable than necessary, but are described here for completeness. An overview of the data production process is provided in FIG. 4. Input to the process is obtained by regular data transmissions from the stores, in the form of scanner level transaction files, as indicated at 90. These data records are handled by the data production process either at an item level of selection, or a store level or selected store grouping level, as indicated in block 92. The data cleaning and preprocessing functions are shown only generally in block 94. The steps, which are further discussed below, include data cleaning, "baselining," quality control at various levels, and required data translation. After this preprocessing, the data records are stored as a store transaction database 96 in the processed data storage device 38 (FIG. 1). This transaction data base is then used to build, as indicated in block 98, the cumulative database 40 used to respond to queries and generate reports. Building the database 40 involves deriving various standard performance measures from the store transaction data, as will be explained below.

The database 40 is accessed by users 100 through a database user interface program 102, which, in turn communicates with the database through a database interface module 104. The database 40, and the necessary software components to build it (98) and to access it (102 and 104) may utilize commercially available relational database software, such as Oracle release 7.1 and with Forms release 4.0, by Oracle Corporation, 500 Oracle Parkway, Redwood Shores, California 94065, SYBASE SQL Server by SYBASE 6475 Christie Avenue, Emeryville, California 94608, or ON-LINE by INFORMIX 4100 Bohanon Drive, Menlo Park, California 94025. The invention is presently implemented using data structures consistent with a database system marketed under the name EXPRESS by Information Resources, Inc. of Chicago, Illinois. The database created using EXPRESS formats is called an INFOVIEW database. The database user interface program 102 used to access is DataServer, which is available for license from the same company. The database interface module 104 for DataServer is known as the DataServer Bridge Companion. The specific software used to build and access the database 40 is not critical to the invention.

Data Cleaning:

Data cleaning is simply a process of filtering the raw transaction data to eliminate or compensate for possible anomalies and errors. Some of the reasons for data cleaning do not apply when data records are processed in daily or more frequent batches.

When reports used to be available only on a weekly or longer basis, there was a risk that stores would contribute cumulative sales figures that would distort the apparent sales performance of a product. With daily or closer to real-time data gathering, cleaning to remove anomalies of this sort is, for the most part, unnecessary. Cleaning is also used to compensate for errors introduced by possible ambiguities of some transactions. For example a six-pack of a beverage might be recorded as one unit at one price or as six units at a different price. Since some of the standard measures of performance involve numbers of units (e.g. number of units moved (sold) or average price per unit), it is easy to see how different reports might be generated depending on how the sales transactions were recorded.

In addition to errors arising from multi-pack transaction handling, there are a number of other known classes of errors in sales transaction data. These error classes include: bad prices (errors in retailer price files), no prices (retailer omissions), bad volume reporting (errors in retailer data processing), data alignment problems (misalignment of sales data with wrong price structure), missed data (field data collection omission), duplicate or low data (data transmitted twice or incomplete), missing PLU codes, and key items missing from data records. It will be apparent that many of the classes of errors on this list are eliminated in the invention because the sales transaction data are collected on a regular frequent basis at the point of sale, so there is less chance of errors in reporting by retailers. Of course, some types of errors remain a problem in the present invention as well, and it is desirable to eliminate or reduce them wherever possible. However, it will be appreciated that, because many types of errors and anomalies are inherently eliminated by use of the invention, data cleaning is not quite the necessity that it would be without use of the invention.

The basic functions of data cleaning are illustrated in FIG. 5. First, store transaction data 110 are processed through a function 112 that detects any PLU (price look-up) codes that need to be translated. Price look-up codes are store-specific codes used on some items, and they need to be translated to corresponding UPC designations before entry into the database 40. Block 114 describes a dictionary function that is next performed to check for several possible anomalies. Specifically, the price of the item is checked for legitimacy. Also, any multiple records for a single UPC are combined into a single sales record. Finally, the dictionary is used to perform "HICONE" processing, an example of which is the ambiguity between the two ways of recording a multi-pack item. To perform the dictionary functions, the system maintains a dictionary of every item (by UPC) that has ever been sold in any of the stores. The dictionary contains information about the category, manufacturer, brand, size, multi-pack handling, and multiprice handling (if applicable), as well as various attributes of the product, such as flavor , style and so forth. After processing through the dictionary, the resulting data records are more logically consistent and better suited for the type of processing that is to follow.

Another level of data cleaning is store-level quality control (block 116), whereby the sales data records for each store are checked for store-level data problems, such as an excessively high or excessively low volume for a particular store. This processing step computes as many as thirty-two distinct measures of price, volume, product promotion, inclusion of major products, inclusion of major vendors, and product movement distribution. The various measures are compared with each other for consistency and are compared with historical data for the same store. The system has as one of its goals the identification of corrupt files at the store level, but it does so without tripping false alarm signals that might otherwise be caused by normal anomalous events, such as public holidays or store promotions.

The next level of data cleaning is to impute records to fill in data gaps left by stores with bad or missing data, as indicated in block 118. As mentioned above with reference to the data record formats, a data record is transmitted by each store whenever the store processing system goes down or comes back up. This type of data gap is filled by an interpolation process. Similarly, data records identified as bad may be replaced or modified. In block 120, the data cleaning process examines the sales history for each UPC across all stores, and identifies any apparent anomalies.

Finally, the a cleaning process may include the ability not only to check for bad data, but to suggest solutions and causes of data problems, as indicated in block 122. Again, this level of complexity in the data cleaning and preprocessing steps is not believed to be necessary for the present invention in its broadest form, but may desirable in some situations. Baselining:

Baselining is a term used to describe a process of determining expected product performance in the absence of promotional activity. FIG. 6 shows the principal functions performed in baselining for the present invention. Collected store data records that have appropriately cleaned, indicated at 130, are processed through a first baselining process 132, which is concerned with baselining the data at the product (UPC) level by store, and through a second baseline process 134, which is concerned with baselining of total store sales and transaction volume.

In product-level baselining of product performance (as measured by selected "standard measures," to be described) it is first determined if there has been promotional activity (signified by a price decrease of, for example, greater than five percent), as indicated in block 136. If promotional activity is detected for that product, the existing baseline is carried forward without change, as indicated in block 138. In either case, the product performance is seasonally adjusted accordingly, as indicated in block 140. Then the seasonally adjusted record is further adjusted for a day-of-week effect, as indicated in block 142. Finally, if there is promotional activity, the adjusted record is accumulated into the baseline performance measure on a rolling average basis, as indicated in block 144.

Total store sales and transactional volume baselining involves a similar set of process steps, except that the data measures being processed are different ones. In block 146, performance is checked against an existing baseline for promotional activity. If there is promotional activity, the existing baseline is carried forward (block 148). If there is no promotional activity, the performance measures are adjusted for season (block 150) and day-of-week effects (block 152), and then accumulated into the baseline measures on a rolling average basis, as indicated at 154.

Standard Measures of Performance:

Standard measures of performance are sales performance parameters derived by arithmetic manipulation of the raw data records imported from stores and cleaned as necessary for a given application. The standard measures become part of the cumulative database 40 and are then available in response to queries from users. For completeness, the standard measures are described below:

Measure Description Unit Sales Sales expressed in physical units for a product within a specific time period and set of stores.

Volume Sales Sales converted to an equivalent volume (e.g., cases, gallons, etc.). Volume sales are obtained by multiplying a product's Unit Sales by a predetermined conversion factor. Dollar Sales Sales expressed in dollars scanned at checkout for a product within a specific time period and set of stores. Temporary price reductions set by in-store trade deals are taken into account, but discounts due to coupons are not.

Average Price per Unit Dollar sales divided by Unit Sales scanned at checkout for a product.

% Units with Any Price For a set of stores within a given time period, this measure Reduction provides the proportion of a product's Unit Sales with any in-store temporary price reductions. This measure does not include coupon activity. Avg. Base Price per Unit The Base Price is the unit price that would be expected during a "non-merchandized" (i.e. no temporary price reduction in effect) periods. The Base Price is updated when a price change is in effect for six consecutive weeks.

Base Volume (Units) Base Volume is an estimate of what a product's volume Sales would be in absence of an in-store deal with a temporal price reduction. Base Volume is used to determine the effectiveness of trade promotions and provide short-term forecasts for product replenishment. If no equivalency is specified, Base Volume is expressed in units. Base Dollars Base Dollars is an estimate of what a product's Dollar

Sales would be in absence of an in-store deal with a temporary price reduction. Base Dollars is the difference between Dollar Sales and Incremental Dollars.

Incremental Volume (Units) The difference between Volume Sales and Base Volume is

Incremental Volume. Incremental Volume represents additional volume sold due to a temporary price reduction. If no volume equivalency is specified, Incremental Volume is expressed in units. Incremental Dollars Incremental Dollars represents additional Dollar Sales due to a temporary price reduction. This measure can take on negative values if a price reduction does not result in sufficiently higher Volume (Sales).

Items Moved Indicates how many different items (different UPCs) were sold. The Items Moved measure definition depends on the type of aggregation specified:

(1) For pre-produced geographies (geographical areas) and time periods, the measure indicates how many constituent UPCs sold at least one unit of the specified product.

(2) For custom (preselected) time periods, the measure yields the maximum of the daily Items Moved for a product.

(3) For custom market aggregates, the measure yields the average number of Items Moved per store for a product.

(4) For custom product aggregates, the measure yields the sum of the Items Moved for the constituent products or UPCs.

% Store Selling (1) For pre-produced geographies and time periods, this measure yields the proportion of stores selling at least one unit of the specified product. (2) For custom time aggregates, this measure yields the maximum of the daily % Stores Selling for a product.

(3) For custom market aggregates, this measure yields the actual % Stores Selling.

(4) For custom product aggregates, this measure yields the maximum of the % Stores Selling for the constituent products or UPCs.

% Stores w/Any Price (1) For pre-produced geographies and time periods, this Reduction measure yields the proportion of stores supporting a temporary price reduction for the specified product.

(2) For custom time aggregates, this measure yields the maximum of the daily % Stores for a product.

(3) For custom market aggregates, this measure yields the actual % Stores. (4) For custom product aggregates, mis measure yields the maximum of the % Stores for the constituent products or

UPCs. Transactions For products at the UPC level only, this measure yields the raw number of shoppers purchasing the specified UPC. (No product aggregations above the UPC level are permitted for this measure.) Transactions/ 100 Shoppers For products at the UPC level only, this measure yields the equivalent number of shoppers purchasing the specified

UPC per 100 store shoppers. (No product aggregations above the UPC level are permitted for this measure.)

Units per Transaction This measure yields the average number of units purchased per buyer, derived by dividing Unit Sale by Transactions for a particular UPC.

Units per 100 Shoppers The Unit Sales per 100 store shoppers. A "store shopper" is defined as a shopper who makes any purchase in the store on a given day. This measure is used to compare a product's sales performance among stores by removing the impact of store traffic (i.e. shoppers). Similarly to units per dollar ACV (all commodity volume), Units per 100 Shoppers can be used as a measure of a product's velocity (i.e., turns).

Dollars per 100 Shoppers The amount of Dollar Sales per 100 store shoppers. % Unit Increase A measure used to indicate effectiveness of trade promo¬ tion, % Unit Increase is derived by calculating the percentage increase in Unit Sales over Base Units during periods of in-store promotion with a temporary price reduction.

% Dollar Increase The percentage increase in Dollar sales over Base Dollars during periods of in-store promotion with a temporary price reduction. % Dollar Increase can take on negative values if the temporary price reduction does not result in sufficiently higher Volume Sales.

Examples of User Applications: As will be apparent from the examples to be described, the application flow diagrams in FIGS. 7-12 have a number of features in common, namely those functions that relate to collection and production of the database, to client/user selection of query parameters, and to generation of a displayed or printed report. These features will be described only for the first of the examples (FIG. 7). Each example provides a specific technique for tracking the sales performance of a selected target item, or group of target items, for which data has been collected.

Distribution Void Application:

The objective of this application is to minimize lost sales by recognizing and diagnosing various problem stocking situations. When there is no movement, i.e. sales, of a target item at a particular store, it is probable that the item is out of stock, indicating a "void" in the distribution pattern for the item. The application gives clients the ability to track product distribution as it builds up through multiple retail sales outlets based on actual timely sales performance rather than from sample data projections. As indicated in block 160, target item data records are collected on a regular (e.g. daily) basis, and transmitted to the data production center, as indicated in block 162 and as described above with reference to FIGS. 1 and 2. The data records are subjected to cleaning and other preprocessing, as indicated in block 164; then target data over a selected time period are chosen for further processing of client queries, as indicated in block 166. The client selects a store or group of stores (block 170), and selects a target item or group of items (block 172). Finally, the client enters application parameters, if required, (block 173), pertinent to the application being requested. Then the search is made, as indicated in block 174, based on the client's input selections.

For this application, the specific processing involved includes a step of checking the target data for "zero movement" in the selected store or stores, as indicated in block 176. The search may continue (block 178) for additional selected target items or stores. Any zero-movement target item is incorporated into a client query database (block 180), since this is probably indicative of a distribution void. In the presently preferred embodiment of the invention a distribution void is defined as zero sales over the most recent fourteen days. Then, the retrieved data items are prepared for inclusion into a client report (block 182), for display (block 184) and possible printing (blocks 186, 188), after which a return is made to a wait state (block 190).

The report generated in this application not only identifies the distribution voids, but also indicates whether or not the product has sold for the eight most recent seven-day rolling periods, for the stores that have the voids. This helps the user identify any potentially chronic problem stocking situations. The same tool also facilitates tracking of sales volume build-up for new products.

Distribution Void Summary Application: FIG. 8 shows a related application for producing a distribution void summary report. In processing this client query, the database system first examined each target item for movement (sales) at each store, as indicated in block 200. The system produces a query database of stores without target item movement (block 202) and a query database of stores widi target item movement (block 204). Both query databases are integrated into the preparation of the client reports, which may include any of the reports shown in the figure, such as % of stores selling at least one unit, units per 100 shoppers, average base price, and so forth.

The Distribution Void Summary report ranks target products by "Potential Additional Dollars," suggesting potential sales gains if the specified product were 100% in stock. The purpose of the report is to provide a tool to evaluate stocking conditions quickly without having to perform physical audits of the inventory. In the present implementation of the application, time periods are limited to seven-day periods from Monday through Sunday. The report uses the following key measures:

% Stores Selling: The percentage of stores selling at least one unit of the specified product over all of the weeks selected.

% Store-UPC- Weeks Distribution Void: A measure which views distribution void rates in three dimensions. For example, a brand with 10 UPCs in 100 stores over 4 weeks has a total of 10 * 100 * 4 = 4,000 possible selling situations. If one UPC did not sell in the total of 100 stores for 4 weeks, then a total of 1 * 100 * 4 = 400 store-UPC- weeks had a void situation. The void rate would be 400/4,000 = 10%. This 10% figure repre¬ sents possible lost sales. Base Sales: An estimate of what sales would be in absence of a temporary price reduction.

Units per 100 Shoppers: The average units sold for every 100 shoppers over the specified time period and set of stores.

Average Base Price: The average non-reduced (everyday) price over the specified time period and set of stores. 100% In-Stock

Opportunity: What non-promoted (base) sales might be if the specified product had 100% store-UPC-week distribution, calculated as follows: (Base Units * Avg. Base Price) / [1 - (% store-UPC-weeks void / 100)]

Potential Additional $: The incremental dollar opportunity resulting from a perfect

(100%) stocking situation, calculated as follows:

100% In-Stock Opportunity - (BaseUnits * Avg Base Price).

Potential Out-of-Stock Application:

In this application, the query processing includes, as shown in FIG. 9, determining if the expected sales levels have been met for the target product or products, as indicated in block 210. A comparison is made between the actual sales (from scanned data) and a baseline sales level maintained in the database using the baselining techniques discussed earlier. An out-of-stock condition is defined as occurring when the actual Unit Sales measure for a target item is less that the Base Volume of sales by some selected percentage. Again, the assumption is that unusually low sales of a product are indicative of a potentially low stock of that item, just as zero movement is used to identify an out of stock condition in the distribution void application.

Order Assistance Application:

In this application, shown in FIG. 10, recent period actual sales are compared with short-term base forecasts in order to develop order requirements, as indicated in block 220. the order requirements are stored (block 222) and incorporated into client reports, which may include order requirements by total quantity, by UPC or by store location (block 224), or direct store delivery vendors replenishment orders (block 226), or electronically transmitted reports to control truck loading and routing to stores (block 228). Price Check Application:

In this application, shown in FIG. 11, an exception-based report is generated showing the price decreases by store and UPC over a selected time period. Only those UPCs and stores that had a price decrease greater than 5% compared to the base (everyday) price are included in die report. A manufacturer may use the report, for example, to monitor store prices when a reduced-price promotion is in effect. A target item is checked for a price change (block 230) and, if there has been a price change, a query database of price changes is updated (block 232) for presentation in a report to the client making the query. The key measures used in the report generation are Actual Price and Base Price.

Merchandising Effectiveness Application:

Another important application is to analyze the effectiveness of promotions of target items in various stores. The effectiveness is determined on a selected percentage improvement in a performance parameter, such as Unit Sales or Dollar Sales over a selected period. The key measures used in determining effectiveness include base price, actual price, base units, incremental units, % unit increase, % price decrease, and incremental dollars. Stores meeting the effectiveness criteria, as determined in block 240, are sorted and ranked based on their performance in the sale of the target product, as indicated in block 242. Stores are sorted into those non-responsive to the promotion, as determined in block 244, and tiiose responsive to various selected levels, as determined in block 246. For example, the stores may separated into categories of "highly responsive," "moderately responsive" and "responsive. " A query database is generated, as indicated in block 248, for display and printing for the client making the query.

Summary:

It will be appreciated from the foregoing that the present invention represents a significant advance in the measurement of sales performance, for purposes of both performance analysis and inventory control. An important aspect of the invention is that it provides timely reports based on sales data records of which the most recent are no more than a day old. As faster communication technology becomes more readily available, sales data records may be accumulated in a real time mode, so that potential out-of-stock conditions and problems with fulfilling restocking orders can be detected well in advance and corrected with appropriate shipping instructions.

It will also be appreciated that, although a number of embodiments and configurations of the invention have been described in detail for purposes of illustration, various other modifications may be made without departing form the spirit and scope of the invention. Accordingly, the invention should not be limited except as by the appended claims.

Claims

1. A method for generating timely reports of sales of selected items in multiple retail stores, the method comprising the steps of: monitoring store computer data relating to sales transactions in multiple retail stores; capturing target sales data pertaining to sales of at least one preselected target item in each of the multiple stores; periodically transmitting the captured target sales data to a data processing site; receiving the transmitted data at the data processing site; integrating the received data into a database; and generating a selected sales performance report from the database in response to a user query.

2. A method for generating timely reports of sales of selected items in multiple retail stores, the method comprising the steps of: recording, in an in-store computer system at each of a plurality of retail stores, a list of target data items for which sales performance data reports are required; monitoring in each retail store data pertaining to sales transactions as they take place; capturing target sales data pertaining to sales of any of the items on the list of target items; periodically transmitting the captured target sales data to a data processing site; receiving the transmitted data at the data processing site; preprocessing the data to reduce the occurrence of erroneous or anomalous data records; integrating the received data into a database; and generating a selected sales performance report from the database in response to a user query.

3. A method as defined in claims 1 or 2, wherein: the captured target sales data transmitted to the data processing site includes, for each target item, store identification data, a record of the current date, a code uniquely identifying the target item, data indicative of the number of these target items sold, and data indicative of the total amount spent on the target item.

4. A method as defined in claim 3, wherein: the sales data transmitted to the data processing site further includes a record of any time that the step of data capturing was inoperative for any reason; and the step of preprocessing the data includes interpolating to compensate for missing data records.

5. A method as defined in claims l, 2, or 3, and further comprising the steps of: deriving various standard measures of sales performance from the target sales data received at the data processing site; and using selected ones of the standard measures of sales performance in the step of generating a selected sales performance report.

6. A method as defined in claims 1, 2, or 3, wherein: the step of monitoring the store computer data includes passively receiving data from a store communications loop connecting multiple point-of-sale (POS) terminals at the store; and the step of capturing target sales data includes checking item sales records for presence of an item identifying code that matches that of a target item and, if a match is found, saving the sales record relating to the matching item.

7. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating a distribution void report that identifies stores at which a target item has not been sold during a selected reporting period.

8. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating a distribution void summary identifying potential under-stocking problems that result in lost sales.

9. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating a potential out-of-stock report based on sales of a target item being below a baseline sales level by more than a preselected percentage.

10. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating an order assistance report to assist in placing replenishment orders based on actual sales of a target item in relation to short-term sales projections.

11. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating a list of target items and associated stores at which the sales price of the item has been lowered by more than a preselected percentage below a previously established base price.

12. A method as defined in claims 1, 2, or 3, wherein: the step of generating a selected sales performance report includes generating a merchandizing effectiveness report indicative of improvement in selected standard sales performance measures in response to a promotion lowering the price of a target item.

13. Apparatus for generating timely reports of sales of selected items in multiple retail stores, the apparatus comprising: means for monitoring store computer data relating to sales transactions in multiple retail stores; means for capturing target sales data pertaining to sales of at least one preselected target item in each of the multiple stores; means for periodically transmitting the captured target sales data to a data processing site; means for receiving the transmitted data at the data processing site; means for integrating the received data into a database; and means for generating a selected sales performance report from the database in response to a user query.

14. Apparatus as defined in claim 13, wherein: the captured target sales data transmitted to the data processing site includes, for each target item, store identification data, a record of the current date, a code uniquely identifying the target item, data indicative of the number of these target items sold, and data indicative of the total amount spent on the target item.

15. Apparatus as defined in claim 14, and further comprising: means for deriving various standard measures of sales performance from the target sales data received at the data processing site; and wherein the means for generating a selected sales performance report uses selected ones of the standard measures of sales performance.

16. Apparatus as defined in claim 13, wherein: the means for monitoring the store computer data includes means for passively receiving data from a store communications loop connecting multiple point-of-sale (POS) terminals at the store; and the means for capturing target sales data includes means for checking item sales records for presence of an item identifying code that matches that of a target item and, if a match is found, saving the sales record relating to the matching item.

17. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating a distribution void report that identifies stores at which a target item has not been sold during a selected reporting period.

18. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating a distribution void summary identifying potential under-stocking problems that result in lost sales.

19. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating a potential out-of-stock report based on sales of a target item being below a baseline sales level by more than a preselected percentage.

20. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating an order 3 I assistance report to assist in placing replenishment orders based on actual sales of a target item in relation to short-term sales projections.

21. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating a list of target items and associated stores at which the sales price of the item has been lowered by more than a preselected percentage below a previously established base price.

22. Apparatus as defined in claim 13, wherein: the means for generating a selected sales performance report includes means for generating a merchandizing effectiveness report indicative of improvement in selected standard sales performance measures in response to a promotion lowering the price of a target item.