How to export a sales file that will actually match

Attribution projects rarely fail on the matching. They fail on the export, three weeks earlier, when somebody ticked the default columns in a CRM report builder and sent the result on with a message saying here you go. The export is the quiet decision that determines the ceiling on everything afterwards, and it is almost never treated as a decision at all.

The export is not the boring bit before the project. It is the project. Everything downstream is arithmetic on whatever you chose to include.

This article is the field list and the traps, written to be usable by whoever actually runs the export — which, in most mid-market businesses, is a sales operations person doing it as a favour between two other things.

The two files, and why it must be two

Revenue attribution needs a sales file of closed transactions and a source file of leads, calls or campaign records. They are separate exports from usually separate systems, and the matching happens between them rather than inside either one.

People frequently try to shortcut this by exporting one file from the CRM with a source column already on it. Sometimes that works. Usually it does not, because the source field in a CRM is populated at lead creation and is then frequently overwritten, left blank, or filled with whatever the salesperson believed at the time. A single-file export inherits every one of those problems and gives you no way to see them.

Two files also mean the source data can come from where it is actually reliable: the call tracking platform, the form handler, the ad platform's lead export. Those systems record the source mechanically rather than from memory.

The sales file: four required columns

A sales export needs a row identifier, a customer identifier that can appear on the lead side, the transaction amount, and the close date. Four columns will produce a working match. Additional columns help humans review; they do not help the matching.

The close date column causes more trouble than the rest combined. In most CRMs there are at least three date fields that could plausibly be called the close date, and they can differ by months. Pick the one finance uses to recognise revenue, and write down which one you picked, because the next person to run this export will pick a different one.

The source file: three required columns

A source export needs the same customer identifier, the channel or campaign, and the date the record was created. If the system also records a call duration or a form URL, take those too, because they make review far easier.

The identifier has to be the same kind of thing as on the sales side. This sounds obvious and is violated constantly: a call log keyed on caller ID matched against a CRM keyed on the office switchboard number will match nothing, because those are different numbers belonging to different people.

Where a source system offers both a raw and a cleaned version of a number, take the raw one. You want the tool doing the matching to see what the system actually captured, not somebody else's opinion of what it meant.

The date range trap

The source export must start earlier than the oldest sale in the sales export, by at least the length of your longest realistic sales cycle. Matching two files covering the same window is the most common single cause of a disappointing first result.

The logic is simple once stated and almost never applied. A sale closing on the fifth of March may have started as an enquiry in the previous August. If the lead file starts on the first of January, that enquiry is not in it, so the sale is unattributable — not because the marketing did not work, but because the evidence was excluded by the export.

There is no downside to going too far back other than file size. There is a very large downside to not going far back enough, and it presents as a marketing problem rather than a data one, which is why it survives so long.

Six columns to leave out on purpose

An attribution export should carry the minimum personal data that makes matching and review possible. Addresses, dates of birth, payment details, national identifiers, free-text notes and health or case details should all be excluded, because they add risk without adding a single match.

This is not a compliance formality. Every column you export is a column that exists in another system, gets copied to a laptop, and appears in a support ticket when something goes wrong. The correct instinct is not what might be useful, it is what is necessary.

• Full postal addresses. A postcode may occasionally help disambiguate; a street address never does.
• Dates of birth. Never used in matching, always sensitive.
• Payment or card details. There is no version of this that belongs in a marketing export.
• National identifiers. Same answer, more so.
• Free-text notes. These contain everything anybody ever typed, including things nobody intended to store.
• Clinical, legal or case detail. If the row describes what a person is dealing with, it does not belong in an attribution file.

Do not clean it first

Export the data as the system holds it. Deduplication, formatting and merging should happen in the tool that does the matching, where every transformation is visible and reversible, rather than in the source system where it is permanent.

The instinct to tidy is strong and usually well-meant. It is also the reason a match rate improves and nobody can explain why. When a merge happens inside the CRM, the record of what was there before is gone, and if the merge was wrong the error is now permanent and invisible.

There is a narrower version of this that is fine: removing obvious test records, internal transactions and staff accounts. Those are exclusions rather than transformations, and they should still be listed somewhere so the totals reconcile.

Reconcile the total before you send it

The sum of the amount column must equal the closed revenue figure finance reports for the same period. If it does not, find out why before matching anything, because every downstream number inherits the discrepancy.

This one check prevents the worst meeting in attribution: the one where the marketing report says eleven million, the finance report says nine point four, and the next forty minutes are about the gap rather than about the marketing. Refunds, cancellations, tax treatment, intercompany transactions and multi-year contracts all cause it legitimately.

Reconciling does not mean forcing the numbers to agree. It means knowing exactly which items explain the difference, and being able to say so in one sentence.

File format details that waste a day each

Export as CSV with UTF-8 encoding, quoted fields, ISO dates and no formatting applied to numeric columns. Each of these is a small choice that costs an afternoon when it is wrong.

• UTF-8, not a regional code page. Accented names arriving as mojibake is an encoding fault, not a data fault, and it is easier to fix at the source.
• ISO dates, in the form 2026-03-05. Ambiguous formats where the day and month can be transposed will be transposed, in a subset of rows, silently.
• No thousands separators or currency symbols in numeric columns.
• Quoted fields, because a company name containing a comma will otherwise shift every subsequent column on that row.
• One header row. Merged headers, title rows and totals rows all have to be stripped by somebody.

CloseRev maps columns automatically and shows you the mapping before anything is processed, so a header it guessed wrong is corrected in a dropdown rather than by re-exporting. Manual mapping is always available; the automation is a convenience, never the only route.

Who should actually run the export

The export should be run by whoever owns the system of record, with the field list written down in advance by whoever will do the matching. Splitting it that way prevents the two most common failures: guessed columns and unrepeatable one-off reports.

In practice a marketing manager asks a sales operations colleague for the data, the request is verbal and vague, and what comes back is a report built for a different purpose with the columns that happened to be on it. Nobody did anything wrong and the file is unusable.

A written field list changes that entirely, because it converts a favour into a specification. It also means the second export, three months later, can be produced by somebody else without a conversation.

What to do when the CRM cannot export what you need

If the required fields cannot be exported together, export them separately with a shared key and join them, rather than accepting a compromised single file. A two-file join you control is better than one file missing the close date.

This comes up more with older systems and with heavily customised installations where the reporting layer has not kept pace with the object model. It is annoying and it is nearly always solvable, because almost every system can export a list of deals and a list of contacts, and those two share an identifier.

The temptation at this point is to ask for API access and build something. Resist it for the first cycle. Prove the attribution is worth having with files before anybody invests in an integration, because a surprising number of these projects discover in week two that the source data is not being captured at all — and no integration fixes that.

The refunds question

Refunds, cancellations and clawbacks should be reflected in the revenue file, because a channel that generates sales which later reverse is not performing as well as the gross number suggests. The simplest treatment is to export net revenue and say so.

Gross versus net is a real decision with no universally correct answer, and the mistake is not picking the wrong one — it is picking silently and then changing your mind next quarter. In categories with meaningful return rates, gross attribution can rank channels in exactly the wrong order, because the cheapest traffic is often the least durable.

If the reversal happens long after the close date, decide whether it belongs to the period of the sale or the period of the refund. Finance will already have a convention for this. Use theirs.

The handover note that saves the project

Write down what you exported, from which system, over which date range, using which date field, and what you excluded. Five lines. It is the difference between a repeatable monthly process and a forensic exercise every quarter.

Attribution becomes valuable when it is trended, and it can only be trended if the same export is produced the same way each period. Almost every business that abandons attribution does so because the second export was built differently from the first, the numbers moved for reasons nobody could explain, and confidence never recovered.

An attribution number is only as trendable as the export behind it is repeatable. Write down how you produced it, or you are starting again every quarter.