Forty spreadsheets, forty layouts
Your production or sales statistics rely on member firms sending monthly or quarterly returns. You issued a template years ago. A few members use it. Others export from their own ERP and send whatever comes out: columns in a different order, product codes from their own catalogue, volumes in kilograms instead of tonnes, a hidden sheet with last year's figures on it, and a total that does not match the lines above it.
The analyst opens each file, copies the right cells into the master workbook, converts units, looks up which of your product categories the member's code belongs to, and emails the firm when something looks off. It takes days every period, and it is the same days every period, because each member sends the same odd file each time.
Why a template alone never fixed it
Members send what their systems produce. Asking them to reformat is asking a finance or production clerk to do extra work for a trade body, and many will not, or will do it inconsistently. So the reformatting lands on you.
- Each firm's ERP or accounting export has its own layout, which changes when they upgrade.
- Product and category codes differ by firm, and the mapping to your categories lives in the analyst's head or a private lookup sheet.
- Units and periods vary: calendar month against four-week periods, tonnes against kilos, value with or without duty.
- Checks for arithmetic and plausibility happen by eye, late, when the file is already in the master.
- Queries go by email and the corrected file arrives as a new attachment, sometimes with new problems.
What the cleaning costs
Analyst time is the obvious cost, and it is time taken from analysis, the part your members and policy team actually value. Publication dates slip. Errors creep in through copy and paste, and an error in a published figure is expensive to correct once it has been quoted.
Knowledge risk matters as well. When the analyst who knows every member's quirks is away, the collection slows down or stops.
An intake that learns each member's format
We build a return intake in front of your master dataset. Members keep sending what they send; the reshaping moves from a person to a set of rules your analyst controls.
- Returns arrive by upload to a member portal or to a dedicated mailbox, and each file is tied to the firm and the period.
- For each firm we set up a format profile: which sheet, which columns, which unit, which period basis. A new layout from that firm is detected and flagged rather than read wrongly.
- The member's own product codes are mapped to your categories through a mapping table the analyst maintains. Unmapped codes stop the file and ask for a decision once, not every period.
- Units and periods are converted using the rules you set, and every conversion is logged against the original value.
- Checks run on intake: lines sum to totals, values within a range of that firm's history, no missing categories that the firm normally reports.
- Anything that fails a check goes to a query list with a pre-written email to the firm, and the corrected file replaces the old one with both kept.
| What arrives | What the intake does | What the analyst sees |
|---|---|---|
| Firm's usual export, unchanged | Reads it through the profile, converts, checks | A clean row set, nothing to do |
| Same firm, new column layout | Stops and flags a format change | A prompt to update the profile once |
| New product code | Holds the lines as unmapped | A mapping decision to make |
| Total does not match lines | Raises a query | The file with the gap highlighted |
| Value far outside the firm's history | Raises a query | The value next to prior periods |
Where a member sends a PDF instead of a spreadsheet, we can extract tables with a document parser or an AI model, and those rows always go to the analyst for checking before they are accepted.
Collection week afterwards
Most returns land clean and go straight into the dataset. The analyst's list is short: a new layout here, an unmapped code there, a couple of queries. The master dataset keeps a trail from each published figure back to the member file and every conversion made on the way.
When the usual analyst is on leave, the profiles and mappings are in the system, so someone else can run the round.
Recognise your collection round?
- Most member returns do not use your template.
- The analyst keeps a private lookup of each firm's product codes.
- Unit conversions are done by hand in the master workbook.
- Errors are found after the data is combined, not when it arrives.
- Only one person can run the collection properly.