Volume by Category Over Time: The Trend Sheet

Tuesday, 10:00, at Meridian Parts. The operations director has asked Hana, who runs the desk, a question that sounds easy: "What are all these tickets actually about?" She has the answer on screen: a chart of last month's six hundred tickets split by category. She is looking at it with the particular unease of someone who knows the answer is true and useless. Printing is one slice. Remote access is another. Password resets are the biggest. The chart says what the month was made of. It says nothing about whether remote access was bigger or smaller than it was in March. It does not say whether the printer slice has been shrinking since the warehouse got new printers, or whether anything on it is heading somewhere. It is a photograph of a river.

Volume by category over time is the count of tickets in each category for each month, laid side by side so that the direction of every category shows. A category, for this purpose, is whatever label your tool puts on a ticket to say what kind of thing it is about. It could be a category field, a type, a tag, or a custom field on the form. The name does not matter; the consistency does. One month's split is a snapshot; twelve months of splits, in a grid, is a set of lines, and the lines are where the information lives. This article is part of our Ticket Volume Analysis series. It is about building that grid, which I will call the trend sheet, and about the handful of shapes a line on it can take.

It covers why one month's split tells you almost nothing, then building the sheet. Next come the four shapes: the category that grew, the one that vanished after a fix, the one that appeared with a new system, and the flat line nobody questions. Reading "Other" and the mis-filed follows, then turning the sheet into three lists each month. A short war story and the category trend sheet to copy finish the article, in that order. The systematic hunt for the sources behind the biggest lines belongs to the Hotspot Analysis series; this article is the sheet that tells you which lines to hunt in.

Why One Month's Split Tells You Nothing

A category's share of one month answers one question: what proportion of the desk's time went where. It cannot answer any question with the word "still", "yet", "since" or "getting" in it, and those are the questions that matter. "Printing is one in seven tickets" is a fact. "Printing was one in four a year ago and has fallen every quarter since the new printers" is a result. "Remote access was one in thirty and is now one in eleven" is a warning. The share is the same kind of number in all three sentences; the difference is the second month, and the third, and the twelfth, lined up next to it.

Shares also lie by construction, because they must add up to a whole. If password resets fall by half, every other category's share rises, and a reader sees printing "growing" when printing did nothing at all. Counts do not have this problem; a count is a count. The trend sheet is built from counts, with shares as an optional line at the bottom for the reader who insists. Add a note that the shares will move whenever any one count does. A pie chart has never once wanted to be understood. A grid of counts over twelve months has no choice.

Building the Sheet

The sheet is a grid: one row per category, one column per month, the count of tickets in each cell. It has thirteen months across so that this month sits beside the same month last year. Add a total row at the bottom and a row for created per working day, so a short month is not mistaken for a quiet one. In plain spreadsheet words, each cell is: count the tickets whose category equals this row's and whose date falls in this column's month. Build it from an export of the ticket list with a date column and a category column, and bring it up to date on the first working day of each month. The first version takes an afternoon; every month after that takes twenty minutes. If you run QuerySolv, the standard volume report tells you how many tickets arrived each month. The sheet below is the spreadsheet you build next to it. That is true of nearly every small-team tool. They count tickets well and keep their opinions about your taxonomy to themselves.

Decide which date drives the column, and decide once. The created date measures when the demand arrived, which is what you want for reading the organisation. The closed date measures when the desk finished, which is what you want if your categories are set at close, as they should be. A ticket that arrives saying "it's broken" is not categorised honestly until someone has found out what was broken. The case for categorising at close is made in Categorize at Close, Not Just at Open. If you follow it the sheet should use the closed date. Understand that a category's count then lags the real arrival by the desk's resolution time. Write the choice on the sheet.

Keep the rows stable. The sheet is only readable if a row means the same thing in every column, and category trees have a way of being tidied in the same quarter someone starts reading them. When two categories are merged, add the old counts together back through the history so the merged row has a full line. When a category is split, the history cannot be split without re-reading the tickets, so draw a mark at the month of the split and start the new rows there. When a category is renamed, nothing has changed and the row simply gets a new label. The discipline of changing categories without breaking the record is in Retiring and Merging Categories. The sheet is the first thing that breaks when that discipline is skipped.

The Category That Grew

A line that rises steadily, month on month, with no single month standing out, is the shape of something getting bigger. It can mean more people using the thing, the thing getting worse, a policy that routed new work to it, or a change in what gets filed there. It is the hardest shape to see in daily work, because no individual day or week is unusual; it is only unusual compared with six months ago. At Meridian, the remote-access row ran 20, 22, 25, 31, 38, 47 and 55 across seven months. No one month was alarming. The seventh was nearly three times the first.

The first question for a rising line is "more people, or more problems per person?", and the headcount answers it. Meridian's field sales team had doubled from thirty to sixty over those seven months, which accounts for the row doubling. The rest of the rise, from forty to fifty-five, was the remote-access gateway struggling with twice the connections it was sized for. Two causes, two actions: the first is a fact for the budget, the second is a ticket for whoever owns the gateway, with the sheet attached. Divide the row by the number of users of that system where you can, because a per-user rate separates growth from decay, and growth is not a problem the desk can fix.

The second question is whether the rise is a hotspot: a single source behind most of the tickets in the row. A rising row of a hundred tickets about remote access might be a hundred different problems or three; read twenty of the tickets and find out. If it is three, the row has given you the problem record, and the hunt for it, across devices, applications and groups, is the method in the hotspot series linked above. If it is a hundred different things, the row is telling you about load, and the answer is capacity, articles or a change of tool. The sheet does not know which. It only knows the line went up, which is enough to make someone read the tickets.

The Category That Vanished After a Fix

A line that falls to near zero and stays there is the best shape on the sheet, and it is the proof, more convincing than any report, that a fix worked. Meridian's warehouse had label printers that dropped off the network several times a day, and the row for them ran at around forty tickets a month for a year. In the eighth month the printers were replaced. The row ran 42, 38, 3, 2, 1, 2 and 0. Nobody needed to be told the replacement had worked; the sheet said so in the only language management reads without translation, which is a column of numbers going to nothing.

Resist the two temptations a vanished line offers. The first is to take the credit without checking. A row can fall because the thing was fixed, because the thing was replaced, or because the people who used it left. It can fall because the tickets are now being filed under a different name, or because users gave up reporting it. The first two are good; the third is neutral; the fourth is an error in the sheet; the fifth is bad news dressed as good. Before anyone claims the fall, check that no neighbouring row rose by a similar amount in the same month. That is where re-filed tickets go. Also check that the users are still there and still using the thing. The honest arithmetic for attributing a fall to a specific change, with a control category and a range, is in Deflection Math Without Fooling Yourself. That arithmetic applies to a fix exactly as it applies to an article.

The second temptation is to delete the row. A category that has been at zero for three months looks like clutter, and the instinct is to retire it and tidy the sheet. Do not. Leave a dead row on the sheet for at least a year, at zero, because a line at zero is the cheapest monitoring there is. The month it stops being zero, you will know within thirty days that the fault has come back, filed under its own name. A row that was deleted gives you nothing, and the returning fault scatters itself across whatever categories are left. The war story below is about exactly this, and it cost a day and a half.

The Category That Appeared With a New System

A new row appears on the sheet when the organisation gets a new system. Its first year has a shape that is the same almost everywhere: a launch spike, a fall over three or four months, and then a settle at some level that never goes back to zero. Meridian's new quoting system produced 90 tickets in its launch month, then 60, 35, 28, 25 and 26. The settle, at around twenty-five a month, is the most important number in the row. It is the permanent load the new system has added to the desk, forever, and it was in nobody's business case. The launch spike is what everyone expects and plans for. The settle is what everyone forgets, and the desk carries it.

Read the settle against the launch and against the system's user count. A settle at a quarter of the launch is ordinary. A settle at two-thirds of the launch, six months in, is a system that is not bedding in. The conversation with its owner should happen before the desk quietly absorbs it as the new normal. A settle that keeps drifting upward is a rising line and gets the treatment of two sections ago. Write the settle level on the sheet with a note: "permanent load from the quoting system, about twenty-five a month". In a year's time someone will ask why volume is higher than it was, and the answer will be on that line.

A new system also appears on the sheet before it has a row, as a bulge in "Other", or in whichever existing category is nearest. Meridian's "Other" row grew from thirty to seventy in the two months before anyone created a quoting-system category. A reader of the sheet who saw "Other" doubling and asked why would have found the new system a month earlier than the desk did. Create the row the week the system is announced, not the month after it goes live, and the launch spike will land in the right place.

The Flat Line Nobody Questions

The most expensive line on most sheets is the one nobody looks at because it never changes. Meridian's password-reset row had read about eighty a month for two years, with a little rise after each holiday, and it had become furniture. Eighty tickets a month at ten minutes each is about thirteen hours a month, a hundred and sixty a year, four working weeks of one person. All of that is spent on a category that a self-service reset would remove almost entirely. The line was flat because the problem was stable, and the problem was stable because nobody had been asked to make it go away.

Read the flat lines once a year, deliberately, with the question "why is this still here?". For each one, estimate the monthly hours it costs: the count times the typical hands-on time for a ticket of that type. Ask what would make the line fall. Some flat lines are the cost of doing business; account unlocks for a workforce that works shifts will never be zero. Some are the cheapest wins on the desk, hidden by their own regularity. The password-reset case in particular has a whole series of its own elsewhere in this library. The sheet's job is only to notice that eighty a month, every month, is a decision somebody is making by not making it.

Reading "Other" and the Mis-filed

"Other", or "General", or "Miscellaneous", is the row that tells you how well the rest of the sheet is working. A healthy "Other" is small, a few in a hundred, and flat. A growing "Other" means the category tree no longer describes the work: a new system without a row, a kind of request nobody anticipated, or a triager who has given up choosing. A large "Other" means the sheet is lying by omission, because the tickets that would have made some row rise are sitting in the row that means nothing. Sample ten "Other" tickets every month and write down what they were actually about; if three of them are the same thing, that thing needs a row.

Mis-filing moves counts between rows without anything in the world changing, and the sheet cannot tell the difference between a category that fell and a category that was re-filed. The signal is a pair: one row falls and a neighbouring row rises by about the same amount in the same month. A new triager who files remote-access problems under "Network" will produce exactly this pair, and the sheet will show remote access improving and the network degrading, both falsely. When you see a pair, read ten tickets from each row for that month. This is the category version of the rule in Counting Tickets Properly: when the definition moves, the count moves, and a line should be drawn on the chart to say so.

From the Sheet to Three Lists

The sheet is read once a month, on the first working day, and the reading produces three short lists, which are the whole output. The rising list holds every row that has risen for three months or is up by more than about a third on the same month last year. Each has a one-line note on the likely cause and a name against it to read twenty tickets. The fallen list holds every row that has fallen sharply. Each has a check that no neighbour rose and that the users are still there. Each also has a date a year ahead on which the row may be retired if it stays dead. The new list: every row created this quarter, with its launch count, its latest count, and the settle level once it has one. Three lists, usually under ten lines in total, and the sheet has done its work.

The table below is the same reading as a reference, one row per shape, with the first check and the usual action. It is deliberately short. A category line can only do a few things, and each of them has an ordinary explanation that twenty minutes with the tickets will confirm or rule out. The time to worry is not when a line does something; it is when a line does something and nobody has read twenty tickets to find out why.

A Short War Story: The Row That Fell Off the Sheet at Acme

At Acme, a desk lead called Jonas replaced the remote-access gateway in the spring. He watched the "VPN" row fall from about fifty tickets a month to two. After three months of near-zero he retired the category to tidy the sheet, re-filing the stragglers under "Network". Nine months later the new gateway's certificate expired on a Sunday night. On Monday morning a hundred and forty tickets arrived from the sales team. Three triagers filed them under "Network", "Laptop", "Login" and "Other" according to how each requester had described it. No single row jumped far enough to alarm anyone. The tickets were worked one at a time as separate laptop and login problems for most of the day. The pattern was only seen on Tuesday morning when a technician noticed that every "Login" ticket since Sunday mentioned working from home. The sales team lost about a day and a half of remote working, something over two hundred person-hours. A single row at zero would have shown the fault as a spike of a hundred and forty by nine o'clock on Monday. A line at zero costs nothing to keep; a line that has been deleted costs a day to rediscover.

The Category Trend Sheet

The sheet below is the grid as a form, with thirteen month columns compressed to fit a page and the reading columns on the right. In the spreadsheet it is wider and the months are real; on paper, the point is the shape of the thing. Fill the rows from the export on the first working day of each month and write the direction in one word. Move anything that needs attention onto one of the three lists at the bottom. Keep every month's version; the sheet from a year ago is the one that proves the fix worked and the one that shows what the new system cost.

CATEGORY TREND SHEET — THIRTEEN MONTHS, ONE PAGE
Desk: ______________________   As at: ____________   Date used: [ ] created  [ ] closed

Category            M-12  M-11  M-10  M-9   M-8   M-7   M-6   M-5   M-4   M-3   M-2   M-1   This   Direction   Note
                                                                                               month  (up/down/
                                                                                                      flat/new)
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
__________________  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
Other               ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____  _________   __________
TOTAL               ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____
Working days        ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____
Per working day     ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  ____  _____

CATEGORY CHANGES THIS YEAR (mark the month on the affected rows)
   Month ______  merged ______________ into ______________   history added together?  [ ] yes
   Month ______  split ______________ into ______________    new rows start this month
   Month ______  created ______________ for new system ______________

THE THREE LISTS (this month)
   RISING   row ______________  likely cause ______________________  who reads 20 tickets ________
            row ______________  likely cause ______________________  who reads 20 tickets ________
   FALLEN   row ______________  neighbour rose? ____  users still there? ____  retire no earlier than ______
   NEW      row ______________  launch ____  latest ____  settle ____  owner told? ____

"OTHER" SAMPLE: ten tickets read on ________; three or more about one thing?  [ ] yes: new row ______________

Hana rebuilt her monthly chart as this sheet on the Wednesday, which took an afternoon because she had to add two merged categories back together through a year of history. The operations director's question the following month was not "what are the tickets about?" but "why is remote access still climbing?" That was the right question, and it had an answer with a headcount in it. The pie chart was retired. Nobody has asked for it back.

Frequently Asked Questions