A depletion export is a ledger, not a report. It records transactions that happened — which account bought which product, in which period, and how much of it. Every question you actually want answered is a question you have to build on top of that. Read the file for what it is and it will tell you a great deal. Read it as though it were already a summary and it will mislead you politely, without ever throwing an error.
Most of the damage happens in the first ninety seconds, before any analysis starts: a unit assumed rather than checked, a period boundary that cuts through the middle of a month, a display name treated as an identity. None of these announce themselves. They just make the totals slightly wrong in a way that survives every pivot you build afterwards. What follows is a short orientation for the person who actually pulls the file — what one row means, which fields carry signal, which ones lie, and the three pivots worth saving so you never rebuild them again.
The grain of the file
Before you sort anything, name the grain. The grain is what a single row represents, and in a depletion export it is almost always one account, one product, one period. A bar that took three SKUs in March produces three March rows, not one. That is the entire shape of the file. Everything else you will ever look at — brand trends, territory rollups, account health — is an aggregation of that one unit.
It also explains the row counts, which surprise people the first time. One importer's export, read row by row, came to roughly thirty thousand rows covering just over thirteen thousand accounts: a little over two rows per account, because most accounts carry only one or two items while a few carry many more.
~30,000
rows in a single depletion export
13,109
distinct accounts listed in that same file
Knowing the grain tells you when summing is legitimate and when it is not. Summing quantity across products for one account in one period is fine. Summing quantity across products that are measured in different units is not fine — and nothing in the spreadsheet will stop you doing it. The grain is also why an account that stops buying leaves no row behind: no transaction, no row. There is no zero to sort on, because a zero was never a transaction.
Check the unit and the period boundary first
These two checks come before everything, including your first pivot. They are boring and they take ten minutes.
01Find the quantity column and establish what one unit of it is. Cases, bottles, gallons, or a converted case equivalent — beer volumes are commonly normalized to a standard case (24 × 12 oz, or 2.25 gallons), wine and spirits to a nine-liter case. If the header just says Quantity, ask; do not guess.
02Check whether the unit is the same on every row. Some files carry both a physical-case count and a converted equivalent in adjacent columns. Some carry a single quantity column whose unit varies by product type.
03Work out how draft is converted. A half-barrel keg is not one case, and the ratio your file uses changes the shape of every draft-heavy account's trend. If kegs and packaged goods sit in the same column under the same unit, you need to know how they were made comparable.
04Establish the period boundary. Calendar month, distributor fiscal month, 4-4-5, or week-ending — they do not line up with each other, and a supplier's month rarely matches a wholesaler's. A trend that turns on a boundary difference is not a trend.
05Find where coverage actually ends. The last period in the file is usually partial, and the gap between the transaction and its appearance in the file is a day or two at best, and often considerably longer depending on how the distributor reports.
06Write both answers — the unit and the boundary — in a cell at the top of the sheet. Every pivot you build inherits them, and you will forget by Thursday.
The two questions
What does one row mean, and what does one unit mean? Every total, trend and percentage you produce rests on those two answers. Get them wrong and the arithmetic still works perfectly — it is simply describing something other than your business.
The fields that carry signal
Exports vary by distributor and by how the feed was configured, but a small number of fields do nearly all the work. If you have these, you can answer most of what a sales director will ask you.
Account identifier. The number, not the name. This is the only stable handle on a venue, and it is what you group on.
Distributor or wholesaler code. The same venue can be served by two houses, and volume moving between them looks like decline on one side and a new account on the other.
Product or SKU code. Pack size usually lives here rather than in a separate column, which matters the moment someone switches from 24-packs to 12-packs.
Brand. The level most conversations actually happen at. If the file does not carry it, build a SKU-to-brand mapping once and reuse it — it will outlast several exports.
Period. Whatever its boundary, this is your x-axis for everything.
Quantity, plus its unit. Treat these as one field. A quantity without its unit is not a number yet.
If the file also carries premise type, chain affiliation, or state, keep them — they make territory and channel cuts possible without a lookup table. Dollar values are worth having for ranking, but they move with price and promotion as much as with behavior. Volume is the cleaner read on whether an account is actually depleting your brand.
The fields that mislead
None of these are defects. They are what a transaction file is when it is assembled from many sources over many periods. But each one will cost you an afternoon if you meet it for the first time inside a finished pivot.
Display names. The same bar shows up as "Joe's Bar & Grill", "JOES BAR AND GRILL", and "Joes Bar Grill #2" across three periods and two distributors. Group on the identifier; keep the name only so a human can read the row.
Mixed units in one column. The most expensive error in the file, and completely silent. Sum it and you get a number. It just is not a volume.
The partial final period. The most recent period is almost always incomplete, so it always looks like a decline. A good share of the at-risk lists people build by hand are really this.
Subtotal and total rows. Some exports interleave rolled-up rows with detail rows, or append a grand total at the foot. Sum blindly and you double-count. Check the row count against the sum of the detail.
Blank versus zero. A zero is a reported transaction of nothing. A blank usually means no row exists — which is a different, and far more interesting, fact.
One venue, several account numbers. Across distributors, the same physical bar frequently carries a different identifier in each. Cross-distributor deduplication needs name and address matching, and it is genuinely hard. Know which of your numbers are affected before anyone quotes them.
Treat the final period as provisional, always. Depletion data lags before it lands — a day or two at the fastest, longer where a distributor reports weekly or monthly — and the last period in any file is a boundary artifact as often as it is a signal. Wait for a second consecutive thin period before you call an account slipping, and never open a conversation with a distributor on the strength of one incomplete month.
Three pivots worth saving as templates
Build these once, against a clean export, and save the workbook. Next month you point the same template at a fresh file. Nearly everything anyone asks for is one of these three, or a filter applied to one of them.
Brand by period. Rows are brand, columns are period, values are quantity summed. This is the portfolio view: which brands carry the book and which are quietly funded by the others. Read it across, not down — the level tells you less than the slope.
Account by period. Rows are account identifier, columns are period, values are quantity summed across SKUs. The biggest sheet and the most useful one. Sort descending by what an account took in the prior year — from your archived copy if this file's window does not reach back that far — then look at the right-hand columns. The empty cells at the end of a long, populated row are the story — that is an account with a habit that stopped.
Distributor last-sale. Rows are distributor, one value column: the latest period carrying any quantity at all. It looks trivially simple and it answers a question nobody otherwise asks — which of your houses has not moved a case in ninety days. Run it monthly and it takes about a minute.
One habit worth forming with all three: keep the export itself. A single file only reaches back as far as its own window, so the comparison that reveals a lapsed account — this period's list against last year's — is only possible if you still have last year's file. The archive is doing more work than the pivot is.
Doing this every night
The manual version is real work, and it works. It just tends not to survive a busy quarter, and the value sits in doing it every week rather than once. Sightglass reads your VIP depletion feed nightly and keeps every snapshot, so the history outlives any single export window rather than resetting with it. It flags accounts slipping, lapsing or losing a SKU, surfaces the ones worth calling, and drafts the outreach — to the account and to the distributor — for a person to approve. Nothing sends without that approval, and approved mail goes from the rep's own mailbox so replies come back to them.
Your reporting stays exactly where it is. This runs on the data you already pay for, and when the data is too fresh or too thin to support a conclusion, it says so instead of guessing.
Want to see it on your book?Twenty minutes, screen shared. No file required.Book a 1-1 demo