A practical checklist for getting a CSV or Excel export from a CRM, ERP or billing system into a state where coverage and share measures can be computed from it: one row per fact, a header row that names the column, identifiers instead of names, one date format, no subtotals, and a control total from the source system to reconcile against.
Most analysis problems are export problems. A file with subtotal rows, three date formats and customer names spelt four ways will produce a wrong coverage figure with no error message. This guide is the checklist for getting an export out of a CRM, ERP or billing system in a state that can be analysed.
| # | Rule | What fails without it |
|---|---|---|
| 1 | One row per fact | Roll-ups double-count or cannot be built |
| 2 | One header row, plain column names | Columns cannot be mapped |
| 3 | Identifiers, not names | Duplicates, splits, personal data |
| 4 | One date format, ISO if possible | Periods misassigned; dates read as text |
| 5 | No subtotals or totals inside the data | Every total is wrong by the subtotals |
| 6 | Numbers as numbers, not text with currency symbols | Sums fail or drop rows |
| 7 | A control total from the source | No way to know the file is complete |
| 8 | The period and filter written down | The next export is not comparable |
The right shape is long, not wide.
| Wrong: wide | |||
|---|---|---|---|
| Customer | Jan | Feb | Mar |
| 4471 | 12,000 | 9,500 | 14,200 |
| Right: long | ||
|---|---|---|
| Customer | Month | Revenue |
| 4471 | 2026-01 | 12,000 |
| 4471 | 2026-02 | 9,500 |
| 4471 | 2026-03 | 14,200 |
Long can be rolled up any way. Wide has already chosen.
Export the customer ID, the rep ID, the product code. Keep the lookup from ID to name in the source. For an analysis that runs on identifiers, the name never needs to leave the building, and the roll-up is exact because IDs do not have spelling.
One format, ideally year-month-day. A column that mixes day-first and month-first dates will be read wrongly for every day under thirteen, silently. Most systems have an export setting for this; use it.
Reports that include subtotal rows every twenty lines are reports, not exports. Use the raw export or the data view. If subtotal rows cannot be avoided, they carry a marker in some column, and they are stripped by that marker, not by eye.
Before exporting, note the total the source shows for the same period and filter. After exporting, the file's column total should equal it. It is the single most useful check in this guide, and it takes a minute.
file total = source total for the same period and filter
| Check | File | Source | Result |
|---|---|---|---|
| Rows | 48,211 | 48,211 invoice lines | Match |
| Revenue, March | $4,182,400 | $4,182,400 | Match |
| Distinct customers | 1,204 | 1,204 | Match |
| Dates parse | 48,211 of 48,211 | Pass |
Four lines. If any fails, the export is fixed before anything is computed on it.
Exported from a report, not a data view. Subtotals, merged headers, page breaks.
Names instead of IDs. The roll-up splits Acme into three and shares are wrong.
Dates as text. Every period figure is wrong and nothing warns you.
No control total. A filtered export of half the year is analysed as if whole.
A file that passes the checklist maps once and computes every period. Covirage validates an uploaded file against these rules and reports what it found before anything is computed. The Excel analysis solution describes the setup, and the files not connectors guide covers why the export is the integration.
Each row is one invoice line, one order, one call, one holding. Not one row per customer with twelve month columns, and not one row per invoice with lines concatenated. A wide monthly layout can be unpivoted, but it is better to export it long in the first place.
Names are not unique, they change, and they are personal data. Customer 4471 is the same row in every export; Acme Ltd, ACME Limited and Acme are three. Export the identifier and keep the name lookup at home.
The total the source system shows for the same period and filter: revenue for the month on the finance screen, say. The file's total should equal it. If it does not, the export is filtered, truncated or double-counted, and that is found before any analysis is.