Building a lapsed-account report by hand

By the Sightglass team9 min read17 July 2026

The hardest part of a lapsed-account report is the part you do first: saving the files. Everything after that is ordinary spreadsheet work — a lookup, a pivot, a sort. But an export reaches back only as far as the window you pull it for, and a lapsed account is only visible against its own history. A depletion export lists accounts that bought something in the window it covers. An account that bought nothing leaves no row to find. You can only see it by holding this month's file up against the last twelve.

What follows is the whole build, in the order you would actually do it: keep an archive, normalize the account key, collapse everything to one row per account per month, flag the gaps, rank what's left. It takes an afternoon the first time and about twenty minutes a month after that. The formulas are not the work. The work is that the same bar arrives in your files under three different names, and that the whole thing quietly stops getting done in a busy quarter.

Start the archive before anything else

Pull your depletion export on the same day every month and save the raw file untouched. Not a cleaned version, not a copy with your pivot already in it — the file exactly as it came. Name it by period, 2026-08-depletions.csv, and never overwrite. All of your working is done in copies that point back at the archive.

This sounds like housekeeping and it isn't. Every downstream step in this build depends on having a run of consecutive periods to compare. A single gap in the archive breaks the twelve-month history test for every account, and it breaks it silently — the sheet keeps producing numbers, they're just wrong. Which is worse than producing nothing.

One export per calendar month, pulled on a fixed day, from a recurring calendar reminder — not from memory.

The raw file goes in the archive folder read-only. Working copies live somewhere else.

Log the pull date alongside the period the file covers. They are not the same thing and you'll need both later.

If you miss a month, write it down. A known hole you can work around; an unknown one poisons the report.

The whole trick

Every other step here is spreadsheet work you could learn in an hour. The archive is the only part that has to start now — the comparison is only as good as the run of files behind it. Twelve months from now this report either exists or it doesn't, and that was decided by whether you started the folder today.

Normalize the account key

This is where the build actually gets spent. Before you can say an account stopped ordering, you have to be certain that the thing that appeared in March and the thing that appeared in June are the same bar. Across multiple distributors, they very often don't look it.

Name variants. The same venue arrives as "The Anchor & Hope", "ANCHOR AND HOPE", and "Anchor & Hope Tavern LLC" — invented names; yours will be worse. Strip case, punctuation, a leading "The", and entity suffixes (LLC, Inc, Co) before you compare anything.

Trade name versus licensee. One distributor files a venue under its DBA, another under the license holder's legal entity. Neither is wrong. They simply never match on a name join.

Account numbers belong to the distributor, not to you. They are unique inside one distributor's system and nowhere else. Two distributors will happily use the same number for two different bars. Always key on distributor plus account number — never the number on its own.

Address matches better than name. Street number and ZIP change far less often than a name does. Suites are the exception: "Ste 200", "#200" and "Unit 200" all need flattening to the same string first.

Multi-location operators. Decide once whether locations roll up. Keeping them separate is usually right — draft lines and shelf space are per location — with a parent column if you also want the group view.

Renames and changes of hands. A venue that sells disappears under one name and reappears under another at the same address. That isn't churn. It's a new operator, and worth a call for a completely different reason.

Two accounts at one address. A bar with a package window can legitimately be both an on-premise and an off-premise account, and what a given license permits varies by state. Don't merge them just because the street matches.

The way through is a crosswalk: a separate tab that maps every distributor-plus-account-number pair you have ever seen to a stable account ID you own. Build it once by sorting your stacked history by normalized name and by address, eyeballing the clusters, and assigning IDs. After that, maintenance is small — each month you only review keys that aren't already in the crosswalk. Keep the original name and number on every row. When something looks wrong six months from now, you will want to see what the file actually said.

If an entire distributor's book appears to lapse in the same period, suspect the file before you suspect the accounts. Distributors renumber accounts after a system change, and a re-keyed book looks exactly like a mass walkout. Check whether the same volume has reappeared under new keys at the same addresses before anyone picks up a phone.

One row per account per month

With the crosswalk in place, the build itself is mechanical. The goal is a grid: accounts down the side, periods across the top, cases in the cells.

01Stack every monthly export into one long table, and stamp each block with the period it came from before you append it. Use the period you pulled it for, not a date column inside the file — those can carry order dates, invoice dates, or ship dates depending on the source.

02Join the crosswalk on distributor plus account number so every row carries your own stable account ID.

03Sum cases by account ID and period. Sum cases, not dollars. Price changes, depletion allowances and mix shifts move the dollar figure for reasons that have nothing to do with whether the account is still buying.

04Pivot it: account ID down, period across, cases in the cells. Blanks are what you're hunting — but keep a separate list of which periods your archive actually covers, so a blank in a month you never exported is never confused with a blank in a month you did.

05Add the derived columns: number of periods with any volume, the most recent period with volume, cases in the trailing twelve periods, and average cases per active month.

06Attach the context a rep will need on the call — territory, the assigned rep, the distributor, and which brands and SKUs the account used to take.

Pick one unit and hold it for the whole sheet. Physical cases and equivalent cases both have their uses, and mixing them halfway down a column produces a report that is confidently wrong. If your portfolio moves kegs alongside packaged goods, settle the conversion before you build anything, write it down next to the sheet, and use the same one every month.

Flag the gaps, then rank them

Two tests, applied in order. First, does the account have a history worth caring about — say, volume in at least six of the last twelve periods, above whatever monthly floor makes a call worth a rep's morning. Second, are the two most recently completed periods both empty. An account that passes both is your flag.

Two periods, not one, and that matters. A single empty month is ordinary: an ordering cadence that runs every six weeks, a distributor cutoff that landed on the wrong side of a month end, a slow January, an order placed on the 2nd instead of the 30th. Chase those and you will burn your credibility with reps inside a month. Two consecutive empty periods against an account that has been ordering in most months is a pattern, and a pattern is worth a phone call.

Then rank. Sort the flagged list by cases taken in the twelve months before the gap opened — not by lifetime volume, which flatters accounts that were big three years ago and have been drifting ever since. Prior volume is a measure of what is at stake and where to spend the week, not a forecast of what will come back. Put the top twenty rows in front of the rep who owns them and stop there. A list of six hundred names gets read once and never again.

Account name, address, distributor, and the rep who owns it.

Last period with any volume, and how many periods have passed since.

Cases in the twelve months before the gap, and the monthly average behind that number.

The brands and SKUs it used to take — a bar that dropped one SKU is a different conversation from one that dropped the whole book.

Two cautions before anyone calls. Depletion data lags roughly a day or two, so the most recent period is always incomplete — evaluate on completed periods only, and never let a thin, still-filling month trigger a flag. And seasonal SKUs will look like churn on schedule: an account that only ever takes an Oktoberfest or a summer cider goes quiet every year without anything being wrong. Either exclude seasonal items from the history test or compare each account to the same months last year.

How often to run it, and why it stops getting run

Monthly, a few days after the new export lands. The file is monthly, so a weekly rebuild mostly re-reads the same numbers. Refresh it once a month, on a fixed day, right behind the archive pull — then work the flagged list through the weeks that follow.

The recurring cost is smaller than the first build but it is never zero. Two minutes to archive. Ten to stack and refresh the pivot. The rest goes on the crosswalk — reviewing only the account keys you haven't seen before, which is where new venues, renamed venues and re-keyed distributor books all show up wearing the same disguise.

Scale is the quiet problem. In one importer's monthly depletion export that we analyzed, the numbers looked like this:

~30,000

rows in a single monthly export

13,109

distinct accounts listed in that one file

Thirteen thousand accounts is not a list you eyeball. It is a list you have to trust your keys on, which means the crosswalk has to stay current or the whole report degrades without announcing it.

And that is how the manual version dies. Not from difficulty — the build is genuinely an afternoon. It dies because it is the fifth priority in a quarter with a price increase, a distributor transition and a national account review. The person who built it takes two weeks off. A month gets skipped, then another. The crosswalk goes stale, the twelve-month history test starts failing quietly, and the sheet keeps producing a number that nobody quite trusts any more. The value was never in building it once. It was in running it every month for two years.

Or have it run every night

This is what Sightglass does on the VIP data you already pay for. It reads the depletion feed nightly and keeps every snapshot, so the history accumulates well past any single export's window — the archive step happens whether anyone remembers it or not. It watches for the gaps and hands each rep a short ranked list of their own accounts with the reason stated plainly and the outreach already drafted, to the account and to the distributor. Nothing sends until a person approves it, and approved mail goes from the rep's own mailbox so replies come back to them.

Leaders get the same thing as conclusions rather than a grid: lapsed accounts, distributors that have gone quiet, concentration, territory by territory. It arrives in Slack, an email digest, or the dashboard. And because depletion data lags a day or two, it stays quiet rather than flagging a gap the data can't yet support. Your VIP subscription and your existing reporting stay exactly where they are.

Want to see it on your book? Twenty minutes, screen shared. No file required. Book a 1-1 demo