Tickets as Asset Intelligence: Building the Lemon List

Wednesday, 14:00, at Bellweather Logistics, in the quarterly budget meeting. Hollis, who runs the desk of four, has asked for forty new handheld scanners for the warehouse. Yvonne, the operations manager, has asked the reasonable question: "Why? The ones we have work." Hollis knows they do not. Every technician on the desk knows it, because every technician has reset, re-paired and rebooted a scanner this week. What Hollis does not have is a number. He has a feeling, several anecdotes, and a strong memory of last Tuesday, none of which is a budget case. Yvonne, politely, defers the item. The scanners go on working, in the sense that they go on generating tickets.

Asset intelligence is what the desk learns about its devices from the tickets it has already closed. It learns which individual machines fail more than the rest and which models fail more than other models. It learns which locations eat equipment and what all of that costs in hours. None of it requires new data. It requires the tag on every hardware ticket, which this series has already built, and a spreadsheet with the patience to count. This article is part of our Asset Data Inside Tickets series. It is about turning a year of closed tickets into the one page Hollis needed in that meeting.

It covers why the queue already knows which devices are bad, then per-device ticket counts and how to make them. The lemon list, its rule and what happens to a device on it follow. Next come per-model patterns and the fault rate per device, then the patterns that belong to places rather than machines. The replacement case built from tickets in hours and the pitfalls that make the counts lie follow. A short war story and the lemon list template to copy come last, in that order.

The Queue Already Knows

Every hardware fault the desk has handled in the last year is a closed ticket, with a date, a subject, a resolution and, if the desk has been following this series, a device tag. That is a dataset. It is not a tidy one. It was never meant to be one. It is the only honest record anyone in the organisation holds of how the equipment actually behaves, as opposed to how the maker's brochure said it would. The repeat-ticket method in Finding the Repeat Tickets reads the same closed list for faults that recur across the organisation. This article reads it for faults that recur on a machine or a model, which is a different question and needs a different key.

The key is the tag, and without it none of what follows works. A desk that has searched its tickets by requester cannot count faults per device, because the device has had three requesters and the count is split three ways. The whole investment of the earlier articles, the field, the sticker, the lookup at first touch, pays out here, in a column that can be sorted. If your desk has only started tagging recently, start the counts anyway with the months you have. Three months of tagged tickets will show the worst lemons. A year shows the models.

The counting is not clever and should not be. Intelligence here means arithmetic that nobody had done, applied to data nobody had looked at. The whole of it fits in a spreadsheet with three tabs. The desk that does this once a quarter knows more about its equipment than most organisations ten times its size, for the cost of an hour. (The organisations ten times its size have a dashboard. It is a very handsome dashboard.)

Per-Device Ticket Counts

Start with an export. Most ticketing tools will put a list of tickets into a spreadsheet file. The list you want is every ticket closed in the last twelve months with the tag field filled. It should show the ticket number, the date, the subject, the tag and the resolution. If you run QuerySolv, the tag is a custom field on the department's form. In QuerySolv, the tickets carrying it can be pulled through the REST API into the sheet. The API is the interface other programs use to ask the help desk for data. In any tool, a plain export is enough. Filter to hardware faults as best the data allows, by whatever your desk uses to mark them, and accept that the filter will be imperfect. A password reset that slipped in will not change the shape of anything.

Then count. On a second tab, put one row per tag from the inventory sheet. Beside each, put a count of the export rows carrying that tag. Write it in whatever spreadsheet you have as something like COUNTIF over the export's tag column. Sort descending. The top of that column is the lemon list, before you have defined the word. The bottom is the long tail of devices with zero or one ticket, which is most of them, and which is the reason the top stands out. The whole operation takes about twenty minutes the first time and five minutes each quarter after. The first time you do it you will find at least one device whose count makes a technician say "that one? really?"

Use a rolling twelve months, not the calendar year, because a device's behaviour does not reset in January. Another reason is that a count that includes last winter's faults and this autumn's is a count that can be compared with the same count next quarter. Keep the thresholds as counts, not rates, at the per-device level. A device is a device, and "three tickets" means the same thing for a laptop as for a printer. Rates come in at the model level, where fleet sizes differ.

The Lemon List

A lemon is a single device that has raised more hardware tickets than the desk's threshold in a rolling year. The lemon list is the named, published set of them, reviewed monthly, each with an action beside it. The threshold is the same one used for the repair-or-swap decision in Warranty and History at a Glance. For most small desks it is three. The list is what makes the threshold real: a device over three is on the list. A device on the list has an action, and the action has a name and a date. Without the list, the threshold is a number everyone agrees with and nobody applies.

The actions are few. A device at the threshold with its warranty running gets "watch, and whole-unit replacement claim at next fault", because the maker should be bearing this. A device at the threshold out of cover gets "swap at next fault, then retire". For that device, the spare is earmarked now so that the next fault is a ten-minute swap rather than a decision. A device well over the threshold, five or six in a year, gets "swap now", because waiting for the next fault is waiting for the requester to lose another afternoon. And a device whose tickets turn out, on reading, to be one dock, one user's habit or one bad network port gets removed from the list with the reason written down. That happens more often than you would think and is the point of reading the subjects rather than just the counts.

Publish the list to the desk, not just to the lead. A technician at first touch who sees that LT-0147 is on the lemon list with "swap at next fault" beside it does not diagnose. They fetch the spare. The fault that would have been a fifth hour of investigation is a fifteen-minute swap. The list is the desk's shared memory of which machines not to fight for, and it works only if everyone can see it. Review it monthly, in ten minutes, in the same meeting as whatever else the desk reviews monthly: devices added, devices actioned, devices removed and why. A list without actions is a complaint with columns.

Per-Model Patterns

The per-device count finds the bad machines. The per-model count finds the bad purchase, which is the more valuable discovery. That is because a bad machine costs one swap and a bad purchase costs forty. A model here means the exact make and specification the organisation bought in a batch, written identically in the inventory sheet's model column every time. That is why that column's rule mattered. Join the export to the sheet on the tag to pull in each ticket's model, then count tickets per model on a third tab.

Then divide by the fleet. Forty-one tickets on a model the organisation owns twenty-five of is a different fact from forty-one tickets on a model it owns two hundred of. A raw count hides the difference. Tickets per device per year, the count divided by the number of devices of that model in the sheet, is the rate that lets models be compared. The table below is Bellweather's third tab for one year, invented for this article but shaped like every real one I have seen. The old scanners are the problem, the thin laptops are a smaller problem nobody had noticed, and everything else is fine.

Read the rate column and then the subjects column, in that order. The rate says where to look; the subjects say what you will find. A high rate with one dominant subject, the cracked hinge, is a design fault in the model. In that case, the action is to stop buying it and claim every hinge while the warranty runs. A high rate with scattered subjects is a model that is simply worn out, and the action is a refresh. A high rate on a shared device in one place, the label printers, might be the model or might be the place, which is the next section.

Patterns That Belong to Places, Not Machines

Some devices fail because of where they are, and the count will blame the device. A label printer in a dispatch bay with a roller door that opens to the yard in January will jam more than the identical printer in the office. A scanner that lives in the cold store will lose its battery faster than one that lives on a desk. The inventory sheet's location column is what separates the two. Count tickets per location for shared devices, and per location and model together, and see whether the rate follows the model or the room. At Bellweather, the dispatch label printers jammed at twice the office rate, and the office printers were the same model. The problem was dust, and the fix was a cover and a weekly wipe, not a purchase.

The same applies to people, in a way that must be handled carefully. One requester's laptop may have four tickets because the laptop is a lemon or because the requester works from a van, charges it in the cab and drops it weekly. The subjects usually say which. The lesson is not that the requester is at fault; it is that the device is in the wrong role. The action is a rugged model for the vans rather than a fourth repair of a thin one. The desk's job is to notice the pattern and change the equipment, never to make the pattern a conversation about the person.

A pattern in the failing-hardware signals a device gives off before it dies, disk errors, battery health, fan speed, is a different kind of intelligence, collected from the machine rather than the queue. It belongs to the article linked. The two meet on the lemon list: a device the queue says is failing and the machine says is failing is a device to swap this week.

The Replacement Case Built From Tickets

The replacement case is one page that says what the old equipment costs the organisation in hours, what the replacement would cost in hours at the better model's rate, and what the difference is. The money is left to purchasing to fill in. Build it in hours because hours are what the desk can count and because hours are what management will recognise as real. A figure in money that the desk invented is a figure the finance team will take apart. A figure in hours that the desk measured is a figure they will convert themselves. The method for turning hours into a case management will read is in Explaining Technical Things to Management. Here is the arithmetic that feeds it.

Take Bellweather's old scanners as the worked example, with every number invented for this article and stated as a range the way an honest case should. Ninety-six tickets a year, at a desk time of thirty to forty-five minutes each, is roughly forty-eight to seventy-two desk hours a year. Each fault also stops a warehouse picker for somewhere between fifteen minutes and an hour while a scanner is reset or swapped. That is another twenty-four to ninety-six hours a year of warehouse time. It is counted separately because it is a different team's time and the operations manager will recognise it. The newer model, at its measured rate of 0.7 tickets per device, would produce about twenty-eight tickets a year across forty devices. That would be fourteen to twenty-one desk hours and seven to twenty-eight warehouse hours. The difference is in the region of thirty to fifty desk hours and twenty to seventy warehouse hours a year. The case says so, as ranges, with the export attached.

Then stop. Do not multiply by a loaded hourly rate the desk does not know, do not add "productivity" for the whole warehouse, and do not round the range up to its top end for effect. The case is strong because it is modest and checkable. Yvonne can open the export and count the tickets herself. The fact that she can is why she will not. Put the ask in one line at the bottom, replace forty scanners with the newer model, and let purchasing put the price beside it. Ranges, not points, and hours, not money, are the rules the library's return-on-investment articles apply to self-service. They apply here unchanged.

The Pitfalls That Make the Counts Lie

The counts lie in four predictable ways, and each has a check. The first is counting the person rather than the device, which happens whenever the tag field was filled from the requester's name without checking the sheet. That way, a laptop a user had two years ago inherits the tickets on the one they have now. The check is the sheet's assigned-to history, or simply reading the subjects, and the fix is upstream, in the first-touch lookup. The second is the dock, the monitor or the network port that is the real fault behind a run of "laptop" tickets. Three tickets on a laptop that all mention the screen going black when docked are three tickets on the dock, and the laptop is innocent. Read the subjects before condemning the device.

The third is survivorship. A device the desk swapped and retired in month four drops out of the fleet count and takes its tickets with it. So a model whose worst units have already been replaced looks healthier than it was. A desk that has been quietly swapping lemons for a year will find its per-model rate flattering the model that caused the swaps. Keep retired devices' tickets in the model count for the year they were in service. Note how many of the model were retired early, because that number is itself part of the case. The fourth is small numbers: two tickets on a fleet of three is a rate of 0.7, and means nothing. Report counts alongside rates, and treat any model with fewer than about ten devices as a list of individual machines rather than a population.

There is a fifth, which is not a lie but a limit. The counts show what the desk recorded. A desk whose warehouse staff reboot their own scanners and never raise a ticket has a count that describes the patient ones. The matched rate from the earlier articles, the share of hardware tickets with a tag, is one honesty check. The other is to ask the floor. If the pickers say "oh, we just power-cycle them, it happens all the time", the ninety-six is a floor. When that happens, the case should say so. Undercounts in your favour are still undercounts.

A Short War Story: The Forty Scanners at Bellweather Logistics

At Bellweather Logistics, the forty older handheld scanners had been raising tickets for two years before Hollis counted them. In that time the desk had re-paired, rebooted, swapped batteries on and finally retired six of them, one at a time, each as a one-off. Meanwhile, the budget item for replacing the rest was deferred in three successive quarterly meetings for want of a number. When Mateo finally built the three tabs, the old scanners stood at ninety-six tickets in the year against fourteen for the twenty newer ones, a rate more than three times higher. A quarter of the tickets were raised during the peak month when the warehouse could least afford them. The retired six turned out to have been the six that had failed most. That meant the surviving thirty-four were already the better ones. Yvonne read the single page, opened the export, and counted eleven tickets in one week in the peak month herself. She approved the replacement in the meeting, with the question "why didn't you show me this a year ago?" Hollis had no answer he liked. The two deferred years had cost the desk somewhere between ninety and a hundred and forty hours and the warehouse rather more, by the desk's own modest arithmetic. A fault the desk has met ninety-six times is a fact; the desk had been presenting it as a feeling.

The Lemon List

The list below is the one-page form the desk publishes monthly. The rule is at the top so that nobody argues about it. Then come the devices over the threshold with an action each, the per-model summary underneath, and a short record of what left the list and why. Fill it from the three spreadsheet tabs, keep every month's copy, and put the current one where every technician will see it at first touch. The columns are deliberately few. A device's whole history is one search away; the list only needs to say that the search is worth making and what the desk has already decided.

THE LEMON LIST — MONTH: ________   REVIEWED BY: ______________
Rule: a device is on this list when it has ____ or more hardware tickets
      in the last 12 months (the desk's threshold).
Source: ticket export, tag field, 12 months to ________   Matched rate: ____%

DEVICES OVER THE THRESHOLD
Tag      Type     Model            Tickets  Last three subjects           Cover until  Age   Action                       Owner     By
______   ______   ______________   ____     ___________________________   __________   ____  [ ] watch; claim at next fault  ______   ____
                                                                                              [ ] swap at next fault; retire
                                                                                              [ ] swap now; retire
                                                                                              [ ] remove from list (reason)
______   ______   ______________   ____     ___________________________   __________   ____  [ ] ______________________    ______   ____
______   ______   ______________   ____     ___________________________   __________   ____  [ ] ______________________    ______   ____

PER-MODEL SUMMARY (fleets of ten or more)
Model (as in the sheet)       Fleet   Tickets 12m   Per device   Commonest subject        Action
__________________________    ____    ____          ____         ____________________     ______________________
__________________________    ____    ____          ____         ____________________     ______________________

LEFT THE LIST THIS MONTH
Tag      Why (swapped / retired / tickets were the dock or the port / fell below threshold)
______   ______________________________________________________________
______   ______________________________________________________________

Spares earmarked for "swap at next fault": ______________
Replacement case raised this month:  [ ] no   [ ] yes: model ____________, ____ devices

Bellweather's list has run to between four and nine devices in any month since it started, which is about what a fleet of a few hundred produces when the lemons are actually being swapped. The per-model summary changes more slowly and is the part management reads. Hollis now brings the current month's page to the quarterly meeting whether or not he is asking for anything. That has had the useful effect that when he does ask, nobody is surprised. The tickets knew about the scanners all along. The list is just the desk agreeing to listen.

Frequently Asked Questions