Remapping Categories, Statuses and Fields

Tuesday, 14:00, at Northgate Retail. Maya has two windows open. On the left is a spreadsheet of the old system's categories, exported that morning: 214 rows, with a count of tickets beside each. Thirty-one of them begin with "Till -", one for each store that ever had a till problem bad enough for someone to add a category. Three of them are "Printing", "Printers" and "Printer - Floor 2". On the right is the new category list the desk agreed last month, after a week of arguing. It has fifteen names, each tied to a decision about who gets the ticket or which report it lands in. Jonah, who joined the desk in the spring, leans over. "Where does 'Till - Store 14 - Receipt Printer' go?" Maya has an answer. She does not have it written down. In the old system there are 214 of these questions, plus nine statuses and five priorities. There is also a custom field called "Store Number" that the new system has never heard of.

A mapping is a written rule for every value the old system could hold in a field. It says what value the new system will hold instead. One row per old value, the new value beside it, the count of tickets it affects, and the name of the person who agreed it. It is a table, and it is a table because the alternative is a memory. The memory belongs to the one person who will be asleep when the import runs. This article is part of our Migrating Ticketing Systems series. And it is about building that table for categories, statuses, priorities and the fields that have no home.

It covers why a mapping is a table and not a memory, then starting from the new design rather than the old list. Next come categories, many to few, and the Other bucket and how to keep it honest. We cover statuses when the new system has a fixed set, then priorities and the clock that comes with them. Next come the field that has no home and testing the map on fifty tickets. We finish with a short war story and the mapping table to copy, in that order.

A Mapping Is a Table, Not a Memory

Every field the import touches needs a complete mapping before the import runs. Complete means every value, including the ones used twice in nine years. The import does not know which values matter. It meets "Till - Store 14 - Receipt Printer" with the same indifference as "Hardware". If there is no row for it, it does what it was told to do with unknown values. That is usually to put them in a default or to stop. Neither happens at a convenient time. A mapping with gaps is a mapping that will be finished at midnight by whoever is in the room, from memory, under pressure. And that person will not be the one who designed the new list.

Each row of the table carries five things. It has the old value, the number of tickets that hold it, and the new value. It also has a one-line rule or note explaining the choice when it is not obvious, and the name of the person who agreed it. The count is there so the table can be read in order of consequence. A wrong row with 1,800 tickets behind it matters more than a wrong row with two. The name is there so that when Jonah asks "why does receipt printer go to point of sale and not printing", there is someone to ask. The answer is not "the import decided".

The table is also the only record of the old vocabulary that will exist in a usable form after the switch. Six months on, a manager will ask for a report that compares this year's "Access" tickets with last year's. Last year's were called "Permissions", "Logins", "Account - New" and "Account - Leaver". The mapping table is how that comparison gets made. Without it, the history exists and cannot be read, which is a special kind of loss. The records are all there, filed under words nobody uses.

Start From the New Design, Not the Old List

The new category list is designed first, from the work the desk does now. Only then is the old list mapped onto it. The temptation runs the other way: take the 214 old values, merge the obvious duplicates, and call the result the new list. That produces a shorter version of the old mess, with the same shape and the same missing decisions. And it carries every historical accident forward into the new system with a fresh coat of paint. The design of a category tree that stays useful is its own subject, in Designing a Category Tree That Stays Useful. And a migration is the one moment the desk can apply it without arguing about history.

The test for each new category is a decision it changes: who gets the ticket, how fast it must be answered, or which report it appears in. Northgate's fifteen each passed that test, which is why "Point of Sale" exists as one category rather than thirty-one. Every till ticket goes to the same two technicians. Each carries the same urgency because a store cannot trade without one, and appears on the same weekly report to the retail director. The store number is useful. It is a fact about the ticket, not a category, and facts about tickets go in fields. That distinction does most of the collapsing on its own.

Once the new list exists, the old list is mapped onto it many to few, and the direction matters. Mapping from old to new asks "where does each old value go", which always has an answer. Mapping from new to old asks "which old values feed this new one". That invites the answer "all the ones that seem relevant" and leaves the tail unmapped. Work down the old list, in order of count, and give every row a destination. Even the row with two tickets. Especially that one, because it is the one nobody will think about at midnight.

Categories: Many to Few

Start the category mapping with the old list exported with counts and sorted by count, largest first. The top twenty old values usually hold most of the tickets, and those twenty rows deserve a conversation each. What did this value actually mean, who used it, and does the new destination preserve what the desk needs to know? Below the top twenty, the rows get quicker. Below the top hundred they are mostly abandoned experiments with one or two tickets behind them. Those map by common sense and a glance at a ticket.

Group the old values by what the desk did about them, not by what they were called. "Printing", "Printers" and "Printer - Floor 2" are the same work and map to the same place. "Till - Store 14 - Receipt Printer" is a printing problem by name and a point-of-sale problem by who fixed it and how fast. It maps to "Point of Sale" because the new list was designed around the decision, not the device. When two technicians disagree about a row, open three tickets that carry the old value and see what was done. The tickets settle the argument faster than the names do.

Carry the old value with the ticket when it says something the new category does not. A migrated ticket that was "Till - Store 14 - Receipt Printer" and is now "Point of Sale" has lost the store and the device. Both were useful. The store number belongs in a field, as the next sections cover. The device can live in a one-line note at the top of the ticket. The rule is that a mapping may lose precision in the category, because the new list is deliberately coarser. But it may not lose information from the ticket. Coarser is a design choice. Lost is an accident.

Retiring and merging the old names is the same operation the desk should be doing every quarter anyway, with the same care about history. Retiring, Merging and Renaming Categories Without Losing History covers it for a desk that is staying put. A migration is that article performed all at once, on every category, on one day. That is a good reason to have the table and a bad reason to do it from memory.

The Other Bucket

"Other" is the new category for old values that genuinely have no home. It is the most dangerous row on the table because it is the easiest to fill. Every value nobody wants to think about can go there, and the import will happily put it there. And the result is a new system in which "Other" is the largest category on day one. That is the swamp the desk was migrating away from, rebuilt in an afternoon. "Other" should hold less than a tenth of the migrated tickets. If it holds more, the mapping is not finished.

The honest use of Other is for old values that were themselves "Other", "Misc", "General" and "Unknown", plus values with a handful of tickets that no new category describes. Both are legitimate. What is not legitimate is a value with four hundred tickets behind it landing in Other because nobody could agree where it went. Four hundred tickets is a category the new list is missing, or a conversation the desk has been avoiding. Have it before the import, because afterwards the four hundred are indistinguishable from the rest of the bucket.

Nothing lands in Other without its old label travelling with it. The migration might carry old values in a note at the top of the ticket, a legacy text field, or a tag. Whatever mechanism it uses, every Other ticket must still say what it used to be. Other is a holding area, not a destination. A month after the switch, sort the Other tickets by their old label. The labels with a dozen tickets each are telling you about a category the new list needs. A bucket you can still sort is a backlog of decisions. A bucket you cannot is landfill.

Statuses: When the New System Has a Fixed Set

Statuses are mapped by meaning, not by name, and the meaning of a status is who holds the next step. The old system had nine: New, Open, In Progress, Waiting - Customer, Waiting - Internal, Parked, Escalated, Resolved and Closed - No Fault. Many tools ship with a fixed set of statuses that cannot be extended. Even where they can be, the new desk should not be inheriting nine when it needs five. So the question for each old status is not "what is this called in the new tool". It is "when a ticket was in this state, whose move was it", and the answer places it. The way to think about a status, and why fewer of them win, is in Pending User vs Pending Vendor vs On Hold. The migration applies that thinking in a table.

Most old statuses collapse cleanly once the question is asked. New and Open both mean the desk has the next move and nobody has started; they map to the new system's first state. In Progress means the desk has the move and someone has started. Waiting - Customer and Waiting - Internal both mean the desk is waiting on somebody else. They map to whichever waiting state the new system has, with a note saying on whom. Parked is the awkward one: on most desks it meant "we have stopped thinking about this", which is not a status but a confession. Each parked ticket should be read and placed honestly, usually as waiting with a named reason or as closed with a note.

Two old statuses are not statuses at all, and the mapping is where that gets fixed. "Escalated" describes a flag on the ticket, not where it sits in the workflow. An escalated ticket is still open, still in progress, still waiting, and the escalation is a second fact about it. Some systems carry escalation as a flag separate from both status and priority, QuerySolv among them. Where the new system does, "Escalated" maps to the status the ticket was actually in plus the flag. Where it does not, the flag becomes a note or a tag, and the status is set by whose move it is. "Closed - No Fault" is a closed ticket with a resolution reason, so it maps to closed. The reason goes where resolution reasons go in the new system, or into the resolution note. The old desk had encoded two different kinds of information in the status field because it was the only field anyone looked at. The new desk does not have to.

Write the status map as a separate small table with nine rows. Every technician will read it in the first week when they ask "where did my parked tickets go". The answer, "they are in waiting, with a note saying why, or closed if nobody could say why", is one they will accept from a table and resist from a person.

Priorities and the Clock

Priorities map by what they meant about impact and urgency, not by their number, and the trap is that both systems use numbers. The old desk's P1 to P5 and the new desk's three or four levels are not the same scale with the middle removed. Read the old definitions, if any exist, and the tickets if they do not. A desk that had five levels usually used two of them for everything and kept the others for arguments. Map each old level to the new level whose definition fits the tickets that actually carried it. The design of priority levels that a small desk can honour is in Priority Tiers That Are Realistic. That design should be settled before the mapping, for the same reason the category list is.

Priorities carry clocks, and the clocks are the part of the map nobody reads until a report turns red. The new system may set service-level targets per priority on a business-hours schedule, as QuerySolv does. In that case, the priority map is also a map of promises. Every old ticket that moves to a new level then inherits that level's target. Decide what that means for migrated open tickets before the import. Some tools start the clock from the moment of import, which makes a three-week-old ticket look fresh. Some tools honour the original creation date, which makes it look breached on arrival. Neither is wrong, but one of them is going to be in the first report. The person reading the first report should have been told which. Test it with the fifty, note the behaviour in the mapping table, and tell the desk.

Do not use the migration to quietly change what requesters were promised. Suppose the old P2 carried a one-day response promise and maps to a new level with a half-day promise. The desk has just made a commitment to every open P2 that nobody agreed to. Where the new levels differ from the old, the service-level document is updated first and announced, and the mapping follows it. The map records a policy. It does not get to write one.

The Field That Has No Home

Every old system carries fields the new one does not have. And each needs a stated fate, because a field that is silently dropped is information that is silently lost. Northgate's "Store Number" is the common kind: a custom field added years ago that the desk genuinely uses. Others are usual suspects: "Asset Tag", "Cost Centre", "Building" and "Vendor Reference". There is also a field called "Flag 2" that nobody has set since the person who added it left. The first question for each is the same as for any data: who will read this in the new system, and when. The second is where it can live.

Four homes are usually available, in descending order of usefulness. The first is a matching custom field on the new system's form, where the tool offers one and the field earns it. Store Number earns it, because it changes who goes where. The second is the first internal note on the migrated ticket, where the value is worth keeping but not worth a field. Most tools distinguish an internal note from a public reply, and the note is invisible to the requester and searchable by the desk. The third is a prefix or suffix in the ticket subject, which is crude, searchable, and sometimes the only option. And, for "Flag 2", there is a line on the mapping table that says "dropped, nobody could say what it was for". It has a name beside it, so that the dropping was a decision.

The universal fallback is the legacy block, and I would use it on any migration regardless of what else is available. It is a single internal note at the top of every migrated ticket, in a fixed format. It carries the old ticket number, the old category, the old status, the old priority, and every custom field value the ticket had. It costs nothing but a column in the import. It is searchable. And it means that whatever the mapping got wrong can be reconstructed later, ticket by ticket, from the ticket itself. The mapping table is the desk's memory of the rules. The legacy block is each ticket's memory of itself. Keep both, and the migration becomes reversible in every way that matters.

Testing the Map on Fifty Tickets

The map is tested on the same fifty real tickets used for the test import described in Why Ticket System Migrations Go Wrong. And it is tested by someone who did not write it. The author of a map reads it and sees intentions. A technician who has never seen it opens the migrated ticket and reads the title and the first message. Then they answer one question: "would I have put this here?" Every "no" is a row to revisit, and every "I don't know" is a row whose note is not clear enough. Count the Other tickets in the fifty while you are there; more than five is a map that is not finished.

Choose the fifty to be awkward. Ten from the top categories, to check the common path. Ten from the tail, with old values that have two or three tickets each. Five parked, five escalated, five closed with a reason code. Five with every custom field filled in, and five with none. Include two that were reopened, because reopened tickets have had more than one status. The map should land them where they ended, not where they began. A test set made of easy tickets passes and proves nothing. The import will not be choosing easy tickets.

Fix the map, run the fifty again, and keep the second result with the mapping table as the evidence that it was tested. Then stop. A map that passes fifty awkward tickets will be wrong about a few dozen among thousands. And those are found and fixed one at a time in the first month, from their legacy blocks, by the technicians who open them. Perfection before the import is a way of never importing.

A Short War Story: The 214 Categories at Redbrook Engineering

Redbrook Engineering's desk lead, Victor, mapped the old system's categories himself, at speed, on the evening before the import. He used a list with no counts and worked in the order the old system happened to show them. "Printing" went to Hardware. "Printers" went to Software because he had been reading a row about print drivers. The forty-odd values with a workshop or a building in their name went to Other. That was because the new list had no row for locations and it was past eleven. The import ran perfectly. The first monthly report out of the new system showed Other as the largest category at a little under half of all tickets, with Hardware and Software close behind. The engineering director had been promised better reporting. In front of the management meeting, the director asked whether the desk now knew less about its own work than it had before. Re-mapping took three weeks of two technicians' spare hours. They worked from the tickets because the old labels had not been carried across, and some tickets were never placed with confidence. Victor wrote his lesson on the first page of the next migration's mapping table. A map written by one tired person from memory is not a map. The forty minutes it would have taken to carry the old label on each ticket would have saved three weeks.

The Mapping Table

The mapping table is one sheet per field, with the categories sheet first because it is longest. A header names the migration, the field, the number of old values and the number of new ones. Every row has the five columns. Rows are sorted by count, largest first, so that a reviewer reads the consequential decisions before the trivial ones. The legacy block format sits at the bottom of the first sheet, because the import will need it and the technicians will need to recognise it. Below the template is the worked example from Northgate's category sheet, which shows how the rule column is used when the destination is not obvious.

MAPPING TABLE — ONE SHEET PER FIELD
Migration: ______________________   Field: ______________   Prepared by: ______________
Old values: ____   New values: ____   Tickets affected: ______   Tested on 50 tickets: [ ]

Old value                      Count   New value             Rule / note                       Agreed by
_____________________________  _____   ____________________  ________________________________  _________
_____________________________  _____   ____________________  ________________________________  _________
_____________________________  _____   ____________________  ________________________________  _________
(one row for every old value; sort by count, largest first; no row left blank)

"Other" check:  tickets mapped to Other ______  of ______ total  (must be under one tenth)
Old labels carried on every ticket:  [ ] yes, in the legacy block below

Legacy block (one internal note at the top of every migrated ticket)
  MIGRATED FROM OLD SYSTEM
  Old ticket number: ________   Old created: ________   Old closed: ________
  Old category: ______________   Old status: __________   Old priority: ____
  Old fields: ______________ = ________   ______________ = ________

Statuses sheet:  old status -> new status, by whose move it was; flags noted separately
Priorities sheet: old level -> new level, by impact and urgency; the clock behaviour noted
Fields sheet:    old field -> new field / first note / subject prefix / dropped (with a name)

Reviewed by (did not write it): ______________ on ________   Result: ________

The example below is the top of Northgate's category sheet after Maya and Jonah had worked through it together on the Wednesday. The rule column is doing the work. It records why receipt printers are point of sale and why a location is not a category. That way, the next person to ask Jonah's question gets the answer from the table.

The full sheet ran to 214 rows and took Maya and Jonah most of two afternoons. Maya described that as the longest she had ever spent on a spreadsheet and the shortest a migration had ever taken her. When the import ran, Other held a little over three percent of the tickets, all of them with their old labels in the legacy block. Jonah's question about Store 14 got asked twice more in the first week, by technicians who had not been in the room. Both times the answer came from the table.

Frequently Asked Questions