Getting usable data out of a c-store back office

Why this matters

Getting usable c-store back office data out of an operator is a different job from analysing it, and it is the job that consumes the time. A regional chain agrees to share its data with you. Three weeks later a zip file arrives containing four CSVs, no documentation, a column called DEPT_CD with 34 distinct values and no lookup table, and a date column where some rows are 2026-07-14 and others are 07/14/26. Nobody at the chain can tell you what the fourth file is, because the person who set up the export left in 2024.

This is the normal experience of receiving convenience data, and it is not a sign of a badly run business. It is the predictable output of a system built to run one operator's stores rather than to publish to outsiders. The back office is an operational tool; the export is an afterthought bolted onto it.

The channel makes this worse than it needs to be. NACS records 151,975 US convenience stores as of the end of 2025, of which 95,672, some 63%, belong to companies operating ten stores or fewer. That is a market where most of your counterparties have no data team, no analyst, and no ability to modify an export even when they want to help.

This page is a method for turning that zip file into something you can trust.

The methodology

Step 1: identify what each file actually is

Back-office exports vary by vendor, but the same four shapes recur, and naming them correctly determines everything downstream.

Identify the grain before you read a single value. The most common analytical error in this channel is treating an item-file row, which has no time dimension, as if it were movement, or summing an invoice file and calling it sales. Both produce numbers, and neither number means what the report title says.

Step 2: resolve the department tree

DEPT_CD with 34 values and no lookup is the standard obstacle. The department tree is bespoke per operator, maintained by hand, and it is the axis every category number rolls up on.

Three routes, in order of reliability. Ask for the lookup table, which usually exists inside the back office even if it was not exported. Failing that, derive it from the item file by reading item descriptions grouped by code, which works because department membership is dense and descriptions are human-readable. Failing that, ask for a single report from the back-office UI with department names on it and reverse the mapping from the totals.

What you must not do is guess from the code values. DEPT_CD 12 is not the twelfth department in any predictable ordering, and a guessed mapping produces category analysis that is confidently wrong.

Step 3: check the item file against the movement file

Join movement to the item file on UPC and count what fails to match. This one check predicts more downstream trouble than anything else you can run in the first hour.

The first row is the one that changes conclusions. Items sold and later deleted from the file are invisible to any analysis joined through the item file, and they are disproportionately the discontinued and seasonal items, which is exactly the population an assortment review cares about.

Step 4: establish the period boundary

Convenience operators close periods on schedules that suit their accounting, not the calendar. Weeks may start Monday, or Sunday, or Saturday. A "month" may be four weeks or five. Fiscal periods may not align with anything.

Do not infer this from the data. Ask, get the answer in writing, and record it next to the dataset, because a week-boundary mismatch is silent: every number computes, and a year-over-year comparison is quietly comparing 52 weeks against 53 or shifting every week by a day.

Step 5: quantify the coverage before analysing anything

Establish what fraction of the estate and the period the file actually covers. Count distinct stores per week and look for stores that appear and disappear. A store that closed mid-period, or a store whose polling failed for three weeks, both show up as a demand decline unless you check.

This is the pass most often skipped and most often responsible for a wrong headline. A 6% category decline is a category decline if the store count is constant and an artefact if it is not.

Worked example

Sunrise Market, an illustrative eleven-store operator, sent a quarterly export. The five steps produced the following before any analysis began:

The last two rows changed the answer. The category read the operator had asked for, why packaged beverages declined 6% in Q2, resolved almost entirely once the five missing store-weeks were accounted for. Two stores went dark for portions of the quarter, and the "decline" was mostly their absence. On a like-for-like store basis the category was down 1.4%, which is a different finding and prompts a different conversation.

The week-boundary confirmation mattered nearly as much. A prior analysis had assumed a Sunday start, which shifted every week by one day and moved a holiday weekend from one week into another. That is enough to invent a promotion effect that never happened.

The three-code residue

Three department codes covering 1.2% of sales could not be resolved. The right treatment is to carry them explicitly as unassigned rather than distributing them proportionally or dropping them. Distributed, they contaminate every category with a small unexplained amount. Dropped, the totals stop reconciling against the operator's own reports, which destroys trust in the whole analysis at the exact moment you present it.

Requesting c-store back office data without burning the relationship

Most of the problems above are cheaper to prevent than to repair, and prevention happens in the request. The constraint to hold in mind is that the person on the other end is usually an owner or a general manager, not an analyst, and every additional field you ask for is work they will do personally.

Ask for these five things, in this order, and stop:

1. The item file and the movement file for the same period. Both, or the join test in step 3 cannot run at all.
2. The department lookup table. One small file that saves days. It almost always exists inside the back office even when it is not part of the standard export.
3. The week-start day, in writing. One sentence in an email. This is the cheapest question on the list and prevents the most expensive class of silent error.
4. A store list with open and close dates. This is what turns a coverage gap into a known quantity rather than a mystery decline.
5. One report from the back-office UI covering the same period. The reconciliation target. If your totals do not match their screen, you fix your understanding rather than presenting a number they will not recognise.

What not to ask for in the first request: custom date ranges, custom groupings, anything requiring a report to be built, or a recurring automated feed. Those are reasonable asks once you have demonstrated that the first extract produced something useful to them, and they are relationship-ending asks before that.

Reconcile before you analyse, always

The fifth item deserves its own discipline. Take their report, take your computed totals, and reconcile to the dollar before any analysis leaves your desk. Differences will exist, and each one is diagnostic:

The last row is the most common and the most embarrassing to discover in a meeting. Back-office exports vary in whether sales are tax-inclusive, and nothing in the column name tells you which you have.

One more thing worth asking for

If the operator will send anything beyond the five items above, ask for a transaction-level or basket-level extract rather than more movement history. Movement tells you what sold; baskets tell you what sold together, which is the only route to attach rate and daypart analysis and the one thing a summarised export can never be reconstructed into.

Most operators cannot produce it, and it costs nothing to ask.

Doing this in Scout

Steps 3 through 5 are recurring checks rather than one-time work: every new export needs the same join test, the same coverage count, the same boundary confirmation. Scout is built for that shape. You connect a data source, it tracks freshness and coverage as new data lands, and the checks live as saved views that re-run rather than as a notebook someone reruns by hand.

Being accurate about the limits: Scout can be the back office, or read from the one you have. Both are supported, and the extraction work below is what you do in the second case. PDI connects directly, which is why replacing the reports rather than the system is a real option there; for other back offices the starting point is still an export. Either way the system of record stays where it is, holding the balances and transmitting the order. What Scout handles is the part after the data exists, which is where the recurring cost sits. And it reports what you connect, so an eleven-store operator's file tells you about eleven stores. Comparing that to a category benchmark needs syndicated data, which is a separate source with its own definitions to reconcile.

Summary and further reading

• Identify each export's grain before reading any value; item files, movement, invoices and baskets answer different questions and are routinely confused.
• The department tree is bespoke and usually unexported. Ask for the lookup, derive it from descriptions, or reverse it from a UI report, but never guess from the codes.
• Join movement to the item file and count failures first: items sold then deleted are invisible and are disproportionately the ones an assortment review cares about.
• Confirm the week boundary in writing and count store-weeks before analysing. Coverage gaps and boundary shifts both masquerade as demand changes.

Further reading: auditing the pricebook itself, and syndicated versus panel data for what a single operator's file cannot tell you.