Messy data = Bad decisions: How to make sure your data measures up

by | Jul 10, 2015

By Rob Davis, SPS Commerce


Point of sale (POS) data can be messy. Making sure it’s cleaned up before you analyze it is critical to making informed decisions for product and sales forecasts, assortment planning, promotion analysis and much more. Most retailer POS systems were not developed with data sharing in mind, and there are numerous points for data inconsistencies to be introduced (e.g., different systems between stores, e-commerce and mobile; multiple currencies; differing inventory tracking methods). So when a supplier receives POS data from one or more retailers, data cleansing and review are critical steps before it can be analyzed.

Poor data = poor decisions. Accurate data = accurate insights.

At SPS Commerce, we receive POS data from hundreds of retailers, and we know firsthand the time and expertise needed to transform the data into usable information. The following are ways to improve the chances that your data is accurate, complete and ready for analysis.

Be on the same page

Retailers today share data via a variety of means, including retailer portals, buyer spreadsheets/reports, and EDI documents. The files will most likely have their own definitions and terms, but you want to make sure you’re comparing apples to apples. For example, you need to know the underlying calculations that go into “Gross Margin,” since its definition can vary. The same is true for a “Same Store Sale.” If a supplier and retailer define those differently, it’s sure to create confusion.

It’s important to formalize data so all retail sales are represented uniformly. If suppliers want to do this themselves, they need to study each file and understand each retailer’s data at a granular level. They also need to recognize that each retailer may provide data in a different format or level of granularity, such as store-level UPC information. They may roll it up and show it at a regional, departmental, or category level, and not at the depth needed to make a decision.

SPS Commerce captures this data, cleans it, and provides it in a uniform way so suppliers can accurately see how they’re performing at one or more retailers, by region, store, and product level.

Accuracy

Suppliers also need to ensure the data is as accurate as possible. POS data is inherently noisy and messy, coming from a variety of systems. In many cases, the systems can be older and without proper quality control checks. The following are tips we recommend to ensure accuracy:

  • Be sure you’re getting new data, not the previous week’s file under a different name. The data needs to be for the time period you’re looking for, and not a repeat of an old one.
  • Make sure your top products are represented at the store level, and that you’re not missing stores. A zero sales data point could mean missing data, and not a complete lack of sales.
  • Look for outliers that may occur based on thresholds by types of products, seasonality, and so on. Data points should fall within expected norms.

Over the last 10 years, we’ve created a whole battery of tests to validate the POS data. In many cases, there are errors in the original file. When this happens, you’ll need to request correct data from your retailer partner. That’s where relationships with the retailers help.

Relationships help

Most issues require the supplier to ask the retailer to confirm the data’s accuracy and correctness. That usually requires a full-time staffer who can regularly reach out to retailers, because keeping up to date on which individual to call within an organization can be a challenge in itself.

Suppliers need solid relationships with their retailers if they want to request the corrected data, which can sometimes feel like Oliver Twist asking for more gruel. It feels worse if you have to do it often.

SPS has retailer relationships and manages that process on a supplier’s behalf. If we see data issues impacting one or more suppliers, we try to resolve it and/or know whom to contact at the retailer to secure a new file. The retailer knows that fixing the SPS data file will help numerous suppliers at once, making it more advantageous to respond with new data in a timely manner.

Suppliers who want to understand their sales performance across their retail channels, and do so themselves, will need to become vigilant in how they collect, cleanse and analyze data. I’ve discussed only a few of the steps you need to take if you want to do this all by yourself, and they’re steps you need to repeat again and again.

Or you could speak with SPS Commerce to learn how we can help you manage data, so you can instead use your resources to analyze product sales and promotions in a timely manner.

analytics-graphic

SPS Commerce Blog Team

SPS Commerce Blog Team

The SPS Commerce Blog Team is your resource for insider knowledge, news, research reports and best practices that will boost your business.
SPS Commerce Blog Team

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Did you like what you read? Get updates like this delivered directly to your inbox.

Subscribe to our blog to receive weekly retail and supply chain news, valuable resources, expert tips and more.