How to replace client names with IDs before uploading sales data

Most firms would let an analytics tool see their numbers and would never let it see their client names. The two can be separated in ten minutes with a spreadsheet, and nothing about the analytics changes. This guide sets out how, what it does and does not achieve legally, and how to check nothing broke.

What to replace

The measures need the identifier to be stable across rows and across periods. They never need the name.

Building the mapping

1. List the distinct client names in the export.
2. Assign each an identifier that will not change: a running number with a prefix, or the client code the firm's own systems already use.
3. Save the mapping as a spreadsheet in the firm's own storage, with access limited to the people who need names.
4. Replace the name column in the export with the identifier column, using a lookup.
5. Do the same for salesperson names.

Reuse the same mapping every period. A client whose identifier changes between quarters becomes two clients in the roll-up.

Checking the file still reconciles

Before the pseudonymised file goes anywhere, check three things against the original:

• Row count is the same.
• Total value is the same.
• The number of distinct client identifiers equals the number of distinct client names.

If the third check fails, two clients were given the same identifier, or one client had two spellings of its name and got two identifiers. Both will show up later as a variance in the roll-up, so it is cheaper to catch them here.

What it achieves legally

Under UK GDPR and the EU regulation, data that can be re-identified with information held separately is pseudonymised, and pseudonymised data remains personal data. The firm still needs a lawful basis and a data processing agreement with any vendor. What changes is the risk: a vendor holding identifiers and figures cannot identify anyone without the firm's mapping, and the security measures expected of it, and the consequences of a breach, are correspondingly lighter. Many firms' compliance teams will approve a pseudonymised upload without the full vendor review a named dataset would need.

Anonymisation, where re-identification is impossible for anyone, is a higher bar and is rarely achievable for sales data, because the firm always keeps the mapping. Say pseudonymised, not anonymised.

A worked example

Before:

After:

Three rows, 728,000 in total, two distinct clients, before and after. The mapping, two rows, stays in the firm.

Where it goes wrong

Spelling variants. "Northgate Holdings" and "Northgate Holdings Ltd" become two identifiers. Normalise names before mapping, or map both spellings to one identifier.

Identifiers that change. A mapping rebuilt from scratch each quarter with a new running number. Keep the mapping and append to it.

Names hiding in other columns. A notes column, an invoice reference containing the client name, an email address as the contact. Check every text column.

Calling it anonymised. It is not, and a compliance team will notice.

Upload with identifiers

Covirage works entirely on identifiers, and on Enterprise runs in a separate pseudonymised tenant. The upload page reads the file in the browser and checks it before anything is stored, and the glossary entry on pseudonymised data has the short version of the legal position.