DESCRIPTION
SYSTEM AND METHOD FOR COLLECTING, TRANSFERRING,
AND ANALYZING INFORMATION FROM POINT-OF-SALE
DEVICES TECHNICAL FIELD
The present invention relates generally to collecting and analyzing information, and more particularly to systems and methods of collecting and analyzing information from disparate systems, including but not limited to Point-of-Sale Devices (POS Systems) and Warehouse Inventory and Sales Systems.
BACKGROUND ART
Retail business owners, distributors and their suppliers have a need to be able to collect and analyze information regarding product movements and inventory levels. These parties are primarily interested in product sales and on hand inventory on a daily, weekly, monthly, quarterly, and/or yearly basis. A retail business owner is concerned with information regarding his own stores, while the interest of the supplier and distributor is across all stores in which their products are sold. Currently, most retail environments, stores and restaurants, gather this information using point-of-sale (POS) devices located in each store. These POS devices usually come in the form of a "cash register", which is used for inputting product unit sales.
Many of these POS devices have some reporting capabilities but are often limited to product unit sales and product dollar sales entered on that POS device. Retail business owners must manually combine information collected from all the POS devices to review how the business is performing. This is a time consuming task.
This task is further complicated in the case of retail business owners who own multiple stores, and have multiple POS devices in each store.
Suppliers and distributors to retail also have a difficult time combining sales across different retail environments. Suppliers need this information to track the movement of their products to the consumer. These sales numbers are critical to tracking market trends. The problem occurs because most retailers have different systems and combining this information is very difficult. For an individual retailer, one solution to this problem is to purchase high-end POS systems that combine information from multiple POS devices and multiple stores. These POS systems are very expensive, ranging from $20,000 to $40,000 per store. In addition, all stores must be equipped with the same hardware and same product unit definitions to combine information from multiple stores.
Suppliers need detailed information from their distributors in order to manage the flow of goods in the supply chain. Many suppliers deal with hundreds of distributors and often do not have a cost effective way of collecting this information. There is no easy way to combine all the distributors into one number as well as find out where the product is being shipped to retail.
Even after the information is collected, retail business owners must still analyze this information to determine how their stores are performing. Many retail business owners simply cannot afford to hire a business analyst to review this information. The issue for Suppliers is more complicated because the information needs to be collected and then put in a standard format to be analyzed effectively. This is a very difficult process to setup and maintain.
In addition, since most retail environments and distribution centers are open for long hours during the day there is only a small amount of time, usually less than 8-10 hours, available to collect the POS data. Therefore, it is important that the collection process be scalable across multiple locations. In order to collect the most valuable data a substantial amount of time is needed. For example, one POS system may take 30 minutes to collect daily transactions. In an eight-hour time frame only 16 stores can be completed. Retail business owners and suppliers who need to collect from hundreds if not thousands of stores would need a substantial amount of resources in place to complete this task.
Thus, what is needed is a system that allows retail business owners, distributors and suppliers the ability to collect product unit volume sales, product unit dollar sales data, and inventory from multiple disparate systems, at a low cost. In addition, a system for analyzing this information is needed. The present invention provides such a system.
DISCLOSURE OF THE INVENTION The present invention discloses a system and method for collecting, transferring and analyzing information from disparate systems, including but not limited to Point-of-Sale Devices and Warehouse Inventory and Sales Systems. The system uploads the data from the POS systems or Warehouse Inventory and Sales Systems through the Internet to a file transfer protocol (FTP) server. The data is then standardized according to both franchise level and industry level standard definitions. Historical information is maintained for each store (product lists, product pricing, etc.) and data can be compared across competitors using
this system and method. The system and method works with all POS devices and Warehouse Inventory and Sales Systems that can at a minimum produce an electronic report.
This system and method eliminates the need for high-end information technology (IT) systems to poll and review reports from stores. The system and method can work as long as the IT system can output an electronic report. It does not require the same system to be used across stores, nor does it require the data to be defined the same in each store. The system and method requires an additional piece of hardware, known as the Marketing Intelligence Gathering and Transformation Apparatus (MIGATA), to be connected to the POS system. The cost of the MIGATA is approximately $500, plus a low monthly access fee. It turns a low- end IT system into a high-end reporting tool. The system and method also relieves the need for franchises/store owners/distributors/suppliers to maintain hardware, software and IT personnel to manage the POS data.
The system and method manages the changes in products, prices, and promotions automatically without intervention. If a menu item is added, it is automatically matched to franchise and industry level standard definitions. If a menu item is changed or moved to another product locating unit (PLU) position, the process automatically captures that and keeps all history together on the menu item. For example, if Coke has PLU position #1 today and #3 tomorrow, all historical information for Coke sales is kept together automatically without the use of stock keeping units (SKUs).
It automatically matches data from one IT system to another without a unique coding structure existing on the registers or inventory systems, such as SKU's used in the consumer package
goods segment. For example, Coke sold in by one distributor can be compared to Coca-Cola in another distributor, even if the naming structure is not the same across systems.
More importantly, the system and method allows the retail business owner, distributor or the supplier to view their data over the Internet and be able to view market changes. The system and method can gather independent store data, roll the data up to a common structure, and then compare individual stores, groups of stores, geographies to competitor performance within similar segments or different segments (soft drink sales in one chain compared to another, sub sandwich sales compared across franchises, etc.) - even though different systems are used across different companies and different naming is done across companies.
From an administrative viewpoint, new stores, facts, and register types and warehouses can be added with little to no coding changes to the system. Most of the loading process is table driven and only table entries need to be made in order for locations to be added.
The process is scalable. By decentralizing the collection process the number of locations that can be serviced increases dramatically. For example, consider a retail chain having six stores in one market and where each store requires 30 minutes to process. A MIGATA box could be placed in each of the stores to collect all of the data from that store. After all the data is collected then the information is transmitted to a central source. In this example the collection time needed at the central source would be reduced from 3 hours to less than one hour.
Fig. 1 is a block diagram illustrating one embodiment of the present invention.
Fig. 2 is a flowchart illustrating the general data collection, transfer, and analysis process.
Fig. 3 is a flowchart illustrating the POS data collection process. Fig. 4 is a flowchart illustrating the data uploading process.
Fig. 5 is a flowchart illustrating the data parsing process.
Fig. 6 is a flowchart illustrating the lookup loading process for files with unique identifiers.
Fig. 7 is a flowchart illustrating the industry standard assignment process.
Fig. 8 is a flowchart illustrating the lookup loading process for files without unique identifiers.
Fig. 9 is a flowchart illustrating the data loading process.
Fig. 10 is a flowchart illustrating the summary generation process.
BEST MODE FOR CARRYING OUT THE INVENTION
For exemplary purpose, a POS system is described in detail below. However, the present invention may be used with other systems as well.
Referring to Fig. 1, a first set of three point-of-sale (POS) devices 10, 12, and 14 are connected to a first Marketing
Intelligence and Transformation Apparatus (MIGATA) 16, a second set of three POS devices, 24, 26, and 28 are connected to a second MIGATA 30, and a third set of three POS devices 32, 34, and 36 are connected to a third MIGATA 38.
The first set of POS devices and MIGATA 16 are located in an individual store 18. The second set of POS devices and MIGATA 30
are located in a second individual store 40. The third set of POS devices and MIGATA 38 are located in a third individual store 42.
The MIGATAs are connected to a File Transfer Protocol (FTP) server 20 using the Internet. The FTP server 20 can also be accessed by multiple users 22 using the Internet.
The POS devices are typical POS devices found in retail stores or restaurants. These devices contain product level sales, product group sales, promotions and discounts, and sales tax data.
In one embodiment of the present invention, multiple POS devices may be connected to one MIGATA.
The MIGATAs shown in Fig. 1 are conventional PC computers well known in the art. It should be noted that any type of computer could used as long as such a computer has a means for collecting information from an IT system, means for storing the collected and formatting information, and means for transmitting the information. Each MIGATA contains a standard operating system commonly found in the art such as Windows 98. Other operating systems could be used as well. Each MIGATA also contains a MIGATA Application program that is discussed in detail below.
The individual stores 18, 40, and 42 are typical retail stores or restaurants. In one embodiment of the present invention, more than three stores are connected to the FTP server 20.
The FTP server 20 is of the conventional type found in the art. The server 20 is connected to multiple users (i.e., franchise store owners, franchise companies, external companies) using the
Internet. The server 20 contains a FTP server application program that is discussed below.
General Overview of the System and Method
Referring to Fig. 2, in step one, POS Data Collection, lookup data and fact data is collected from a POS device using the MIGATA. In step two, the data is formatted into a specific format using a data parser application program loaded on the MIGATA computer. Alternatively, the formatting step may be performed on the FTP server. In step three, this data is uploaded or transferred to the FTP server where it is temporarily stored. In step four, the formatted data is then loaded into a database using a data loading application program. Finally, various types of reports or summaries may be generated using a report generating application program.
POS Data Collection
Running on each MIGATA device is a custom software program called the MIGATA application program (see Fig.3). This application program is responsible for collecting and formatting the information from IT system and is written in Visual Basic.
Additional software languages such as C, C++, or other equivalent software languages could be used as well. Additional software programs control the transfer of the collected information and the downloading of software program updates to software contained within the PC.
The MIGATA application program is custom designed depending on the type of POS device. For example, many POS devices capture different information and use different formats for storing that information. The MIGATA application program must recognize what is available on the POS device and pull the appropriate data. During initial setup of a retail store or distribution center, a determination is made concerning what information is collected and
how that information is formatted and stored for each POS device in the store. If the system is open point-of-sale (OPOS) compliant, the MIGATA application program will perform an Open Database Connectivity (ODBC) call to the POS device database or a flat file extract. If the POS device has a proprietary system, then the MIGATA application program will use the manufacturer's reporting software program to perform the necessary extraction of information. Once determined, the MIGATA device is loaded with the appropriate MIGATA software (and any additional necessary software) before shipment to the franchise store. If the store replaces its old POS device with a new POS device, then new MIGATA application software for that new POS device can be downloaded to the store.
Referring to Fig. 3, the MIGATA application program reads a file to determine when to collect data from the POS device. This file is created during the initial setup process and is located in the MIGATA. This file may be updated to change collection time and frequency by sending a new file from the FTP server to the MIGATA. The time to collect data is determined by the franchise or business owner. Most businesses require that information collection from the POS devices occur on a daily basis. Information could be collected according to other schedules as well. For example, information may be collected on a monthly, quarterly, or annual basis.
Lookup File Extraction
The information collected and stored on any given POS device may change on a daily basis. For example, one day French Fries
could occupy the number 2 position on the POS device and the next day French Fries could be the number 3 position on the POS device. As a result, information relating to French Fries sales may be stored in multiple memory locations on the POS device. In addition, new products could be added or the price for an existing product may change. To maintain an accurate history of the information on a POS device, the MIGATA application program must be able to recognize this change and respond accordingly.
To accomplish this, the MIGATA application program extracts lookup files from the POS device. These lookup files contain information regarding how and where product information is stored on the POS device. The MIGATA will compare the contents of a newly created lookup file against a master lookup file and only upload the additions and updates. Fact File Extraction
After these lookup files have been extracted, the MIGATA application program extracts fact files from the store POS device and writes this information to a directory on the MIGATA. This information can include (but is not limited to) product level sales, product group sales, promotions and discounts, and sales tax. Any information captured by the POS device can be extracted and loaded. The MIGATA application program will parse the information and write the fact data to a file format that is standard across all register types. The system validates that the information extract performed successfully and the stored information is ready to be transferred. Up to 7 days of information is stored locally on the MIGATA in case the system fails. The historical information can be extracted at any time.
Data Uploading
At a set time, the lookup and fact files are transferred (or uploaded) to a directory on the FTP server (see Fig. 4) using an Uploading application program. Each store has its own user name, password, and directory on the FTP server. This is put in place to prevent one store from being able to see the files of another store.
First, the Uploading application program extracts local telephone numbers used to access the Internet. Next, the Uploading application program reads all lookup and facts files collected during POS data collection and upload these files to a store directory located on the FTP server. The files are encrypted using public key cryptography in order to prevent unauthorized access to the data while it is being transferred over the public Internet. The program validates that the lookup and fact files were transmitted to the FTP server. Periodically, the program checks the FTP server for error files related to this upload of lookup and fact files. If no error files exist, the program does nothing. If error files do exist, POS data collection is performed again.
Infoloader Software Program
A FTP server application program (or Infoloader program) is loaded on the FTP server. The Infoloader program continuously monitors stores to determine if any new files need to be uploaded to the FTP server. The Infoloader program can be set to check from every 10 minutes to every 6 hours. The Infoloader program performs all loading, summarization, and error checking. In the event of an error in the uploading process, the Infoloader program will let the MIGATA device know if a file needs to be retransmitted.
All of the Infoloader coding was custom written to allow for dynamic processing. Although this program is written in Visual Basis, other programming languages could be used as well. New register types, new facts, and validation files, on a store-by-store basis, can be handled by this process while keeping all historical information together.
If a new POS device type is used, the Infoloader program for that POS device can be written in a matter of hours. Very quickly, new POS devices can be added to the process. No changes are needed for the actual data-transferring step.
In alternative embodiments, the Infoloader program may actually include code that formats data received from the MIGATA device. This code would be necessary for situations where the
MIGATA device does not actually perform any formatting and simply transmits data from the POS devices to the FTP server.
Data Loading
The data loading process can be broken into two general categories: 1) Lookup Loading; and 2) Fact Loading. For each of these categories, the loading process splits into when there is an id column available and when there is not. For instance, one type of POS device has a report that contains product sales. This report includes the PLU identifier used in the store. Another report shows coupon sales. This coupon sales report does not include any kind of unique identifier, only a description of the type of coupon. Processing of these different files is handled in two different ways.
All loading of all information is performed dynamically. This process is table driven so that if a new type of information is
gathered, the database administrator will only need to create the information tables and make a few entries into support tables and the process can automatically process the new type of information without any code changes to the data transferring software.
Lookup Loading
Each store has an entry in the store table and multiple entries in the store lookup table. The store lookup table indicates what lookup files should be expected for this store. The store table indicates where the files should be located. In addition, there is a supporting table called lookup. The lookup table contains information indicating what table is used for the lookup, what file to look for in the process, the actual id column used in the lookup table (such as product_id) and the description column used in the lookup table (such as product_desc), and if there is a grouping column available (for products falling into product groups). Based on this data, it can process changes and additions to the different lookup tables.
If there are any changes or additions, the system administrator is automatically notified and a report is generated to understand the changes and validate the data being sent up is not corrupted in any way.
For those files with unique identifiers, the system checks to see if the record exists. If it does, it checks to see if the description is the same. If it differs, then a new record must be inserted into the database. The old record must be preserved in order to maintain history. When the new record is inserted, the standard franchise id is added to the record along with the industry standard id. If the description is the same, then some other part of the data has been changed and those fields are updated. In the case of
validation items that have a grouping column (such as products into product groups), an extra validation is made to see if the product group has changed. This change has a profound impact on how data is aggregated. In the case where no unique id exists, a unique id is generated. From this point, the franchise standard identifier and the industry standard identifier are assigned. This is a critical step because of maintaining history of an item and being able to rollup data across a franchise and across industries. There is a standard franchise id and industry standard id kept because data may be relevant at the franchise level that is not relevant for the entire industry. For example, a franchise might want to know the difference between when a ham sandwich is sold on its own or within a value meal. But for reporting across the industry, this can be removed and total ham sandwich sales can be analyzed.
Unique ID Lookup Loading
Fig. 6 illustrates one embodiment of the lookup loading process. The Infoloader program reads the store lookup table to determine what lookup files should be received from the store. Next, the Infoloader program determines whether the lookup files contain unique ids. If the lookup files do not contain unique ids, then the lookups are loaded according to the description only. The description only lookup loading process is discussed below.
If the lookup files do contain unique ids, the lookup files are loaded into a temporary table. Next, the Infoloader program determines if the unique id exists in the store's lookup table. If the
unique id exists, the program determines if the description or grouping column is different.
If the description and grouping column are the same, the program updates the data fields in the lookup table excluding the lookup id, description, franchise lookup id, and standard industry lookup id data fields.
If a unique id does not exist, the program writes a record to the load results table to notify the system administrator and the franchise owner that a new lookup has been created for the store. The program then reads the franchise table to locate a similar entry. If there is a similar entry in the franchise table, the program assigns the franchise id to the file, keeps the store id for reference, and inserts the file into the store's lookup table. If no similar entry is found, an unknown value of "9999" for the franchise id is assigned and the file is inserted into the store's lookup table. Referring to Fig. 7, the program then assigns an Industry Standard id following the same procedure used for assigning the franchise id.
If the description or grouping column is different, the program copies the old record, assigns a new store lookup id, and inserts the lookup into the lookup table. In addition, the program again notifies the system administrator and the franchise owner that a new lookup has been created for the store. The program again reads the franchise table to locate a similar entry. If there is a similar entry in the franchise table, the program assigns the franchise id to the file, keeps the store id for reference, and inserts the file into the store's lookup table. If no similar entry is found, an unknown value of "9999" for the franchise id is assigned and the file is inserted into the store's lookup table. Referring to Fig. 7, the
program then assigns an Industry Standard id following the same procedure used for assigning the franchise id.
Description Only Lookup Loading Referring to Fig. 8, the Infoloader program loads the lookup data into a temporary table. The program determines if the description of the lookup exists in the store's lookup table. If so, the program determines if there is a grouping column in the lookup date. If not, the program updates the data fields excluding the store lookup id, description, franchise lookup id, and standard lookup id.
If there is a grouping column, the program determines if the grouping column in the lookup data matches the grouping column in the store's lookup table. If so, the program updates the data fields excluding the store lookup id, description, franchise lookup id, and standard lookup id. If not, the program copies the old store record and assigns a new store lookup id. The program writes a record to the load results table to notify the system administrator and the franchise owner that a new lookup has been created for the store. The program then reads the franchise table to locate a similar entry. If there is a similar entry in the franchise table, the program assigns the franchise id to the file, keeps the store id for reference, and inserts the file into the store's lookup table. If no similar entry is found, an unknown value of "9999" for the franchise id is assigned and the file is inserted into the store's lookup table. Referring to Fig. 7, the program then assigns an Industry Standard id following the same procedure used for assigning the franchise id.
If the description in the lookup file does not exist in the store's lookup table, the program writes a record to the load results table to notify the system administrator and the franchise owner
that a new lookup has been created for the store. The program then reads the franchise table to locate a similar entry. If there is a similar entry in the franchise table, the program assigns the franchise id to the file, keeps the store id for reference, and inserts the file into the store's lookup table. If no similar entry is found, an unknown value of "9999" for the franchise id is assigned and the file is inserted into the store's lookup table. Referring to Fig. 7, the program then assigns an Industry Standard id following the same procedure used for assigning the franchise id.
Fact Loading
Once the lookup files have been loaded, the Infoloader program then loads the fact files. Referring to Fig. 9, the Infoloader program determines the fact files to be loaded for the store by reading the store facts table. For each store, there is an entry in the store table, the store fact table, and the fact table to aid the loading process. The store table indicates where to find fact files for each store. The store fact table indicates if the store has id columns on the record and if the fact from the store has grouping data available.
Next, the Infoloader program reads the fact table to determine how the record should be processed. It indicates the name of the file for the fact, what table to update, what are the key fields for the fact on inserting, and the description column for the table. It also includes several entries on how the program should generate the standard query language (SQL) for inserting the data into temporary loading tables, its permanent location, and how to treat the data on rollups to different aggregations (such as daily franchise totals or grouping totals).
Referring again to Fig. 9, the Infoloader program determines whether the incoming file has an id column. If the store does not have id columns in the incoming file, then the data is loaded into a temporary table based on the description. At that point, the store lookup table is referenced to determine the correct franchise identifier for that item description.
If the store has id columns in the incoming file, then the data is loaded into another temporary table before it is permanently converted to the franchise standard identifier. After the data has been converted to standard franchise IDs, then it can be loaded into the database. If a store identifier were used in the loading process, history would not match over time. If
Coke had an ID of "1" from the store today and an ID of "2" tomorrow, the history of the data would be lost. Instead, the process converts the data to standard franchise identifiers before loading into the permanent tables. In this case, Coke sales will always be Coke sales no matter how the register changes. This change is possible because of the lookup loading process above. If reporting needs to be done at the industry standard level, then that can be done at report time.
If the fact data already exists on the database for that day, the error table is updated with an error message and all the old data is sent to an errors table so it can be recovered if needed. At the end of the load, the administrator can review the error table for problems, so they can be corrected.
If data can exist at a low level and a grouping level, the system performs other checks. For example if one store has a sophisticated POS system and can report sales at the product level and the owner's second store has a cheaper system and only reports
at a product grouping level, the system must be able to handle this. The loader will automatically "push down" the data or "pull up" the data based on the level of data sophistication that is available from the store register system. In the case of "pushing down" the data, the system cannot fill the gaps on what the lower level data looks like for that store, but it can reproduce the data for the group at the lower level. This is critical when reporting data at different levels between stores.
Once the store level data is loaded, franchise summaries (see Fig. 10) and other summaries are created and the current day, month, quarter, and year are updated.
A decision support Relational On-Line Analytical Processing (ROLAP) system is used to analyze the summarized data and produces a variety of reports. The ROLAP program currently being used is by MicroStrategy and it allows the user to compare information over an infinite number of dimensions. ROLAP architecture is essential because of the number of dimensions that are possible.
Notification of the availability of reports may be delivered to users using e-mail, paging systems, or faxes. Reports contain information regarding product unit volume, product unit sales, product category volume and sales, promotional discounts and coupon usage, single store totals, multiple store totals, sales taxes totals, and other combinations specified by the user. Thus, although there have been described particular embodiments of the present invention of a new and useful "System and Method for Collecting, Transferring, and Analyzing Information from Point-of-Sale Devices," it is not intended that such references
be construed as limitations upon the scope of this invention except as set forth in the following claims.