This post is aimed at a fairly niche audience so if you aren’t trying to make sense of poor data out of some ticketing system then you might want to skip this one.
This is based on my experiences pulling data out of Jira but it’s going to be applicable to almost any other ticketing system out there.
There are lots of reasons why we might want to pull cycle time data out of a system like Jira and unfortunately, Jira doesn’t make it easy. I’m going to assume that you’ve already figured out which API’s you need to call to get a full history of all the tickets in the time period you care about. There’s enough complexity in just making sense of the data without getting into the API’s.
Cycle time (or lead time if you prefer) is the time between some defined start point and the corresponding end point in the system. The complexity is in determining exactly when the ticket moved across the start point and then when it crossed the end point.
We could say that the moment a ticket is created in the system, it’s started. While this is very simple to implement, it rarely gives us metrics that are really useful. Starting the clock on creation may make sense for a help desk where tickets are being done immediately but for new feature development, tickets can be in a system for months or years before anyone decides that they’re worth doing. If we’re starting the clock on creation and then letting the work sit there idle for months then the cycle time numbers will be considerably less useful.
If the columns on the board are guaranteed to be in a specific order then we could key off the name of the column and start the clock when the ticket enters that column. The obvious problem is that sometimes a ticket will skip a column or columns will get reordered or renamed. This also has the problem that we are required to know the names of the columns and if we want to build some generic algorithm then we can’t assume that any board will have columns in any specific order.
The next thing we could try is to look for the first status change after creation and this is the approach that I often fall into. Whenever the status changes (to anything), we consider the item started. One problem here is that sometimes work will go directly from created to done with no intermediate steps and then we have no idea what the start date was. We could assume that it started the same day it finished, which will be right fairly often but it’s still a guess.
Now assuming that we’ve picked a way to determine the start time, what if we have two of them? What if the work moves from the created state into a different started state and then back to created? Has the clock still started or did we stop it? Either way you pick, that will be right in some cases and wrong in others. I generally start the clock on the first transition and keep it going, even if we move it back.
Now we move on to the end time. I find this is generally less ambiguous as most boards have fairly consistently named “done” columns. Hard coding the name of the column is less dangerous although obviously, still not perfect.
What if we have different flavours of done? What if there is a distinction between done and complete? What about some variation of cancelled where the work stopped but wasn’t actually completed? I’ve seen all of these cases and more.
What if it hits done multiple times? If it moves into done and then back into an in progress state (or even created) and then back to done. Do we use the first or the last? I typically keep the last done but there’s no clear right answer here.
On the surface, determining when an item started and then ended seems like a simple problem to solve and yet you can see that there’s significant ambiguity here.
We have to make a number of choices about how we choose to interpret the data and there is no single right answer. I’ve given the choices I typically make although even these are contextual and sometimes I do things in other ways.