Distributor data
Distributor sales reports in PDF and Excel: how to turn them into one dataset
Image generated with AIIf you work in sales, sales operations or business intelligence at an FMCG manufacturer, you probably know this situation. Files arrive from dozens of distributors, by email and through their web portals, in different formats. Some are PDFs that somebody has to retype. By the time the numbers reach the board they’re already late, and the first question at the meeting is whether they’re right.
I work with FMCG manufacturers on Trade Data Hub at Asseco, a service that collects and organises data from distributors. With distributor sell-out (secondary sales) data, most of the work is matching the numbers in each file to your own products, customers and periods.
Reading the file is the first step
There are good tools for extracting tables from PDFs and cleaning spreadsheet data. If you work with only a few distributors, a good extraction tool and a well-built spreadsheet can cover both the reading and the matching.
The trouble starts with what’s inside the table. A tool reads the line “BRAND X 0.7L” correctly. On your side, that line can cover three SKUs, for example three flavours in the same bottle size. “Shop 14, Main St.” in one file and “S14 MAIN STREET” in another can be the same outlet, with one customer code in your system. Matching them takes your own product and customer lists.
Every new distributor brings its own codes for products and outlets, so the matching grows with each partner, while tools can handle the reading. This matching is called mapping: each distributor’s product and outlet codes are linked to your own. Together with checking that every source has sent its data, it is what turns many files into one dataset.
One distributor can be several sources
When manufacturers count their reporting partners, they tend to count distributors. The files, though, often come from a distributor’s warehouses and branches separately, each exporting on a different schedule and sometimes in a different format.
You can see the scale in our own network. Trade Data Hub integrates 1,600+ distributors across 27 countries, giving 85+ FMCG manufacturers distributor sell-out visibility into 900,000+ outlets. Those distributors send data from 13,000+ individual data sources: warehouses, branches and other supply points.
So if you’re estimating how much work consolidation takes, count sources. A distributor with five branches can mean five files to check each time data comes in, and any one of them can be late, incomplete or formatted differently from last time.
Periods add one more layer. Some sources send data every day, others less often, and distributors close their month on different dates. Trade Data Hub brings these frequencies and calendars into one reporting calendar, so periods from different sources can be compared.
Products and customers in the distributor’s codes
Every distributor names your products in its own way. Its internal system (ERP) uses different codes and abbreviations, and even barcodes (EAN) don’t always match yours. Then come the harder cases. Some distributors report a whole brand as a single line. Others report a mixed set as one product, when on your side it’s several different SKUs. A multipack can also be counted in a different unit: one 12-pack in the distributor’s file, where you count 12 units.
With brand lines and mixed sets, the risk is that the brand total is still correct, so a check on totals won’t show the problem. Then you try to analyse sales by SKU, and the numbers stop making sense, because part of your portfolio is hidden inside “Brand X” lines and mixed sets.
Customer data has the same problem. Each distributor sends a list of outlets, each outlet has to be assigned to your customer code, and a shop served by two distributors should appear as one customer in your data.
In Trade Data Hub, products and customers are mapped to your codes, with local rules by market, partner and channel. Composite items are split back to SKU level: a multipack or a set by its defined composition, a brand line across your SKUs using market shares or the proportions in which they sold in the past. For a distributor that reports by brand, the SKU figures are calculated, so I’d treat them as an estimate in any analysis.
Until a distributor’s product is matched to one of your SKUs, its sales can’t be assigned to any of them. In OneView, the reporting layer that comes with Trade Data Hub, the Unmatched products with sales report shows the value and quantity of those sales, and lists each distributor’s products, as named in its own system, with the value behind each one.
Unmatched products with sales
| Distributor's product | Distributor | Unmatched quantity | Unmatched value |
|---|---|---|---|
| NEW FLAVOUR 0.5L CAN | Distributor A | 18,460 | 22,150.00 |
| MIX SET 6X0.33 | Distributor D | 2,900 | 17,450.00 |
| BRX ORIG 12PK | Distributor B | 1,060 | 12,720.00 |
| GIFT SET 2X0.7 | Distributor C | 224 | 8,960.00 |
| 33 more products | 2,256 | 13,320.00 | |
| Total | 24,900 | 74,600.00 |
Missing and incomplete reports
In a consolidated view, a missing report looks exactly like zero sales. Sales in a region seem to drop, and someone draws a conclusion in a meeting before anyone checks whether the file arrived.
Before you share a period’s numbers, check which sources haven’t sent their data. The standard OneView reports that come with Trade Data Hub monitor data quality and data flow. The Sales data report shows the share of days with data for each distributor and each of its branches, with overall completeness at the top, and the number of days since each one last sent data. It counts the distributors with no data for more than seven days and estimates the value of sales missing from your view. You see the gap and can ask the distributor for the file before the numbers go out.
Operational reports · Sales data · as of 30 Oct, last 30 days
| Distributor | Department | Estimated missing sales value | % days with data | Days since the last message |
|---|---|---|---|---|
| Distributor A | Distributor A North 01 | 0.00 | 100% | 1 |
| Distributor B | Distributor B North 01 | 0.00 | 100% | 1 |
| Distributor B South 02 | 1,200.00 | 97% | 1 | |
| Distributor C | Distributor C Central 01 | 27,400.00 | 70% | 10 |
| Distributor D | Distributor D Branch 03 | 14,200.00 | 67% | 11 |
| Distributor E | Distributor E East 01 | 0.00 | 100% | 1 |
A file that arrived but is incomplete, or mapped to the wrong products, is harder to catch, because the period looks normal. The check in the next section is one way to find it.
A distributor can’t sell more than it had
You can check distributor data against sell-in: what you shipped to the distributor. In principle, a distributor can’t resell more than it bought from you plus what it already had in stock, unless it also buys your products from someone else. Comparing your sell-in with the distributor sell-out it reports shows where the data doesn’t match.
The comparison works in both directions. If resales are above what the distributor could have had, the data is probably wrong, for example a product mapped to the wrong SKU, or the distributor bought from another supplier. A multipack counted in the wrong unit can make the figures too high or too low. A shortfall over several periods is a reason to check the data. An export that broke halfway leaves a report that looks plausible and is incomplete. Sometimes a distributor resells to a sub-distributor and doesn’t report those sales, and then the gap is real and worth knowing about.
You can run this test yourself with your own shipment data, period by period. Take the distributor’s stock into account, or compare over a longer window, so that normal stock movements don’t look like errors. I wrote about reading distributor stock in a separate article for drinks brands. In Trade Data Hub, the Sell-In vs Sell-Out Reconciliation report shows where your sell-in and the distributor reports diverge.
Take what distributors already send
My advice: don’t ask your distributors to change their format. Sending everyone a template is tempting. Each distributor exports what its system produces, though, and getting dozens of partners to change that takes time. Meanwhile the files keep arriving in the old format.
Do the conversion on your side instead. In Trade Data Hub we take what a distributor sends today as it is, from a PDF sent by email to a direct connection to the distributor’s system: Excel, CSV, XML, flat files, PDF reports, EDI and API feeds, delivered over SFTP, API, HTTP/S, a database connection or as an email attachment. Trade Data Hub processes incoming data every day, at whatever interval each distributor sends it. The format becomes part of the integration setup for that source, and from there every source goes through the same mapping and quality checks. Distributors’ stock (inventory) reports are mapped and checked in the same way as their sales reports.
Mapping needs regular maintenance. Distributors list new products, open branches and change their exports, and the rules have to change with them. Most of that work is maintaining rules: once a product or outlet is matched, later files from that source are matched the same way, until the distributor changes its export.
Andrzej Masłowski
Deputy Director of Sales and Implementations
Asseco Business Solutions
Andrzej runs sales and implementation for Trade Data Hub and Trade Terms & Settlement. His work is turning the files distributors send, in whatever format they send them, into one dataset: the mapping to your own product and customer codes and the checks that show what is missing.
If your team still spends days matching distributor files before anyone sees a number, talk to him.
Frequently asked questions
Q: Is a PDF extraction tool enough to consolidate distributor sales reports?
A: It handles the first step well: reading the tables out of each file. Consolidation also needs each distributor’s products and customers mapped to your own codes, plus a check for missing or incomplete reports. With only a few distributors, a spreadsheet can cover that. With many sources, the mapping becomes the larger part of the work.
Q: Why do distributor reports show the right brand total but the wrong sales by SKU?
A: Some distributors report a whole brand as one line, or a mixed set as one product. The brand total is still correct, but sales by SKU stay distorted until those lines are split back to your SKUs. A set is split by its defined composition. A brand line is divided using market shares or the proportions in which your SKUs sold in the past, so for that distributor the SKU figures are an estimate.
Q: How can an FMCG manufacturer automate distributor sales reporting?
A: Automate the steps that repeat with every file: collecting it, mapping the distributor’s product and outlet codes to yours, and checking which sources haven’t sent their data. In Trade Data Hub, incoming data is processed every day. A product or outlet matched once is matched the same way in later files from that source, until the distributor changes its export. Standard reports show which sources haven’t sent data and which codes still need matching, so your team works only on those cases.
Q: Do distributors need to change the format of their reports?
A: Usually not. Trade Data Hub accepts what distributors already send: Excel, CSV, XML, flat files, PDF reports, EDI or API feeds, including files sent by email. Each source is then mapped to your own product and customer codes.
Start with what you receive today
If distributor files still reach your team as attachments, and someone spends days matching them before anyone sees a number, our team can look at it with you. A useful starting point is a list of your sources and the format each one sends. On the Trade Data Hub page you’ll find how it works and how to reach the team.
