[updated November 2020]

We are 23 or so days into the Holiday season.  Do you know where your sales are?  Do you know them by item, category or client?  This isn’t one of those blog posts on this year’s sales compared to ’08 or ’07.  We’re going to go over some data points you or your boss may be concerned with. We are talking about building or using a Sell Through report we can create using an EDI 852.  As we go on, we will look at some retailers that offer it, what data is included and what data points inside the 852 are necessary for each of the retail formulas we will cover (Primarily sell thru).

Sell-through refers to a percentage of units shipped which are actually sold (In a given period, say: one month, season or year).  When a season is over, or a carrying contract is over, the goods are either marked down or sent back to the manufacturer.  Sell-through is always expressed as a percentage.  Let’s take a look at the formula for Sell Thru:  Sell through % = units sold / (units sold + on hand inventory).

For example: During the month of December 100 shirts sell. You received 300 shirts in receipts. You end August with 900 units – shirts of stock (End of Month Stock).  What were your BOM (Beginning On-Hand) units of shirts and what was your Sell-through?

Beginning of Month stock (BOM) = EOM 900 units – Receipts 300 units + Sales 100 units = 700 units

  • Beginning of Month stock (BOM) = EOM 900 units – Receipts 300 units + Sales 100 units = 700 units
  • Sell-through = Sales 100 units / Beginning Inventory (BOM) 700 = 14.3% Sell-through in December.

If you look at Sell through for the month then your formula looks like: sell through% this month = units sold this month/ (Units sold this month + on hand inventory). 

Understanding sell through is critical to being able to visualize and manage the velocity your products move from becoming ordered to being sold.  As your sell through increases you should/could see the following happen:

  • Returns will reduce proportionally
  • You will be more of a successful line/brand
  • More/or increased order potential

Wow.  Quite a start, Jim.  Let me take a swig off my now comfortably hot venti, caramel mochaccino while I contemplate this a wee bit.  I think someone told me we were receiving 852’s and not doing anything with the data.

Hold on a moment.  A mochaccino is a pricey, fancy drink – let’s go with the $5.00 drink you just mentioned for a moment.   You paid $5.00 for a beverage which will last you 45 minutes but you will go the whole week with no understanding or *visibility* of your company’s seasonal sales, delivery or other performance stats?  Let’s keep that $5.00 price tag at the back of your head.

To dive right into this, let’s make some assumptions:

  • Your company sells about 7-8 items into retail
  • Your company has 3.5 retail clients
  • Your company sends and receives EDI traffic for at least two of your retail clients

Now let’s look at some retailers you may work with.  Find a couple you are familiar with below (Each of these works with the 852 to some extent):

AAFES, Academy, Associated Wholesalers, Belk, Bloomingdales, Bon Ton, Burlington Coat Factory, Costco, CVS, Dillards, Dollar General, Eckerd, eToys Direct, Famous Barr, Federated, Food Lion, Fred Meyer, Goody’s, HEB, Holt Renfrew, Home Depot, Hudson Bay, JC Penney, KB Toys, Kroger, Kroger, Lands End, Lord & Taylor, Lowes, Macy’s, Modells, Neiman Marcus, NEXCOM, Nordstrom, Office Depot, Office Depot, Office Max, Pamida, Parisian, Petsmart, Rite Aid, Safeway, Saks 5th Ave, Sears, Sephora, Sports Authority, Super Value, Target, Toys R Us, Von Maur, Walgreens

What data is included in the 852?

This [EDI 852] document tells the manufacturer the distributor’s (Retailer’s) inventory and level of activity per product, per store. The standard 852 only transmits a “change” since the previous transmission. An all item refresh sends every field for every item this is often not done with the 852, but instead performed with semi-annual and annual inventory counts at the store or warehouse level.  The section directly below lists out the common attributes or points carried in various 852 documents.  Most retailers will not include all of them, but ask.  Just because they don’t currently doesn’t mean it can’t be easily included (And may already be for some of the trading partners). Also, if your company has the 852 integrated for reporting, they may not have mapped all the fields to your reporting tool.


  • Quantity Available (QA): Distributors Current Inventory Available
  • Stock Type (QA-reference segment): “P” = Managed, “N” = Non-Managed
  • Quantity on Order (QP): The Distributors Backorder with the Manufacturer
  • Quantity Damaged (DG)
  • Adjustment to Inventory Quantity (QT)
  • Quantity Transferred (QZ)
  • Calculated Reorder Point (PO)
  • Quantity Out of Stock (QO): Distributors Backorder to their End Customer
  • Quantity Sold (QS): Total Volume Sold since the last Transmission
  • Frequency(QS): Number of Sales that makeup the Total Volume
  • Quantity on Hold (HL)
  • Lost Sales (LS)
  • Planned Order Quantity (OQ)
  • Quantity Committed (QC)
  • Additional Demand Quantity (QD)
  • Quantity in Transit (QI)
  • Minimum Inventory Quantity (QL)
  • Maximum Inventory Quantity (QM)
  • Quantity Received (QR)
  • Quantity Returned by Customer (QU)

Reading through the list of attributes or data carried in the 852, it is apparent which ones we need in order to create a running “Current Sell-through Percentage”: QA (current inventory) and QS (Quantity sold).  These two elements seem to be the most common throughout my list of retailers who send 852’s – so it makes sense to work with them.  Take a look at the list of attributes.  There is a huge amount of information which the 852 is built to carry.

Your solution should track Item and Location to a depth which satisfies providing you with the insight you are looking for. It has your current items included (Even if integrated to Item Master or PLM).  Some fields or attributes you should track, include:

You have included information on locations.  These are the stores for all locations your products are carried.  In addition, the more attributes which can be included for a given location, the better.  What kind of attributes are we interested in?  Consider the following:

  • Shipping region (FedEx or UPS)
  • Mall name
  • Store number
  • Climate (Is it cold there – Hawaii moves different stuff than Alaska)
  • Economic (Is this a depressed metro area, not depressed, etc.?)
  • Address info (City, State, ZIP)
  • Hub information (Include store number, division, flagship, outlet etc.)
  • GLN (Global location number if applicable)

There is a host of information you can track for locations as well as items.  I have listed a very few to get started or get you thinking of others.  Depending on the size of your company, sales, etc. you can go to specialists who can help you add to this list.  Several actually sell the mall names, clustering information and very well built demographic models for each.

A solid reporting solution will allow you to differentiate and tie four areas by transaction event.  In context, the transaction in this case is the 852, the four areas include: time, item, location and trading partner (Whether client or vendor).

Take another sip of that $5.00 coffee.  Realize that [For far less than] your cost to drink the same drink daily, you could be on your way to having a weekly report which tells you what your sales are doing with any given retail relationship.

What additional information can the 852 provide?

  • Highlight anomalies by location.  For example, you are looking at a sell through by item, by store and you see one store shows a significant dip (Much lower percentage) compared to others.  What could this indicate?  Store personnel haven’t put out the product and it’s sitting in the back room.
  • When you sold the program to the retailer, you really sold them a ‘business plan’ and if it is coming to fruition when you engage with your buyer it becomes an amazing talking point to help grow the relationship further.  Conversely, if you are not hitting your ‘plan’ you need to take whatever steps possible to rectify the situation – collaborating is really facilitated when you have numbers to discuss.

That coffee of yours is cold, right about now and you only drank half of it.  Throwing away about $2.50.

[What do you do now?  You want the report so what is the best way to get it?  The easiest way is to call someone else to put it altogether for you.  There are a host of solutions.  When I wrote this in 2009, I was using ProClarity… things are so much easier now.]

If you are new to this, I would suggest one of two areas:

  • Go to your EDI provider (I don’t care if they are a VAN, or hosted EDI).  Find about the reporting services they offer (No sense re-building the wheel before you know what kind of wheel you want).  See what solution(s) they offer.
  • Find out what the cost to receive the data via JSON, CSV.
  • If your company uses a solution like PowerBI, SAP Data Warehouse Cloud (It would be fun and very useful for this use case), Tableau, or another data visualization tool then life is easy-peezy.  You can do this with Excel and pivot tables, but PowerBI desktop is free and makes it a lot easier.

If you are a non-technical person and have no resources in this area (Can’t write SQL), or if the costs come in too high, we can provide a basic system to get you started or some alternatives. There are fantastic systems on the market and you can build systems for free (Minus labor) using open source.  

Reports that help you drive your business more effectively should be equated to the speedometer in your car.  You won’t drive your car without a working speedometer will you?  If the speedometer doesn’t work you will pay to get it fixed, or will you just ‘follow’ traffic?  Buy your business a fancy cup of coffee regularly, and it will return the favor by delivering information that will drive your business more effectively!

If you need help with your EDI 852, or any integration support, don’t hesitate to contact us, here.