There are different ways for suppliers to obtain POS data from their retail partners. Many retailers offer reports or have their own web portals. Walmart’s Retail Link offers the most detailed view. Unfortunately other retailers’ portals make it difficult to impossible to get deep dive views (e.g.: SKU and door level). Happily there is another choice: An EDI document called an 852.
These documents are typically available to any supplier that wants them. Once set up, the process can be automatic (That is until it is not – more on that later). And the delivery of the selling is automatic as well.
The challenge is that these documents are unstructured, nonstandard and incomplete. And they are sent as one week “snapshots”. Another note of caution: Almost always there is no way to get history prior to turning on the document.
What is involved?
Because the EDI Point of Sales standard allows for a wide degree of variability - and because the POS documents do not contain all the information needed for retail sales analysis - there is no easy mapping or translation. Rather the process involves scrubbing, synthesizing as well as adding business rules, proprietary formulas and algorithms. And the nature of this data is highly dynamic, so the parsing framework must be amenable to change.
Because EDI 852 files are so large, the code for these parsers must also be highly efficient. While some of our clients have been able to process the data correctly, their code could take 48-72 hours to complete the processing – they could not even start to run reports until Wednesday or Thursday of each week. So the code needs to be highly efficient and should run on fast hardware.
Our approach has been to first run the EDI 852 documents through a pre-parser, as the data files we receive often contain multiple 852’s, or 852’s and 856’s or even other data. Then we use various parsers to scrub POS, store lists, item description files and other relevant data sets and upload them to their separate tables. All in all this involves hundreds of stored procedures and computer programs coded over several people-years that are constantly updated.
We have learned that there are many variables we need to be close to: change in layout, incomplete layout, unrecognized characters that cannot be handled, error checking and handling of even minor discrepancies. There are times we need to reprocess data. We even reformat data such as item files for more efficient and effective import. Mid-week we might discover a retailer miss-sent data. Or mid-season a retailer changes the EDI format. We also run periodic validation.
While the task is daunting, we have come across companies that have accomplished this on their own. Perhaps you can as well.
Please feel free to email me at: This email address is being protected from spambots. You need JavaScript enabled to view it. if you have any comments or questions you wish to share offline.
