From a six-month spreadsheet to a ten-minute question

The problem

Leadership wanted to know how much of our IT teams' finished work was Run, Improve or Transform. Sounds simple. It wasn't, because the work type lived on the parent initiative and not on each story. Every story had to be matched back to its parent.

My manager did that by hand every period. Export the parents, export every finished item for every team, then pivot tables to pull it together. I'm pretty sure the workbook took about six months to build, and it still took a few hours every time someone ran it. The automation someone had tried before didn't work well, and its search skipped about three teams because it used a project name that doesn't exist.

What I did

I built a Rovo agent in Jira you can just talk to. I fed it the real search queries and the real workbook, since they showed what a right answer looks like, and kept tightening the instructions. It was on version eight before my manager first tried it.

I didn't want one report. I wanted something he could drop into an executive page every period and then keep asking questions of.

What went wrong

• One team showed over 99% of its work as missing a work type. My manager figured the agent wasn't reading the parent. I talked it through with a colleague, and he said every other team looks fine, so why is this one different? Turns out that team's board had no parents linked at all.
• It didn't act the same way twice. Telling it to read the parent worked once, then got ignored. So I stopped putting that in the prompt and put it in the agent's permanent instructions.
• An old date filter was hiding finished work. It dropped 66 items finished this year because the items were created earlier. That hit five of ten teams.
• Big questions got a worse answer. Past about 500 items, early versions switched to a shortcut. Now it uses the same method at any size, and if a request is too big for one turn it asks you to reply "continue."

My manager ran his own report next to the agent's before he trusted it. He also caught tasks showing up in a report that should have been stories and bugs. I fixed that too.

How I tested it

I wrote 55 questions with answers I'd already checked and ran them after every change. That's how I knew fixing one team hadn't broken another.

What changed

• Time. By my manager's estimate, a period took at least two to four hours to run and pull together. Now it's about ten minutes. The first working version took me roughly one to two hours to build. That one's my own guess.
• Visibility. It doesn't just say data is missing. Ask it to explain the missing reasons and it tells you which items have no parent and which parents have a blank work type, so people can fix it at the source.
• Follow-up questions. Any period, year, month or team. Breakdowns by initiative. Story points. You keep chatting instead of building another export, and it works for whatever conversation you're having about the percentages.
• Always current. Fix something in Jira and the next run of a past period uses the fix.
• It spread. My manager shared it with the person who wrote the original search. A colleague saw that metrics he'd given up on, because JQL felt too stiff, might be possible now. I update it when a new team shows up.

What I took from it

• If one result is way off from the rest, check the data before you blame the tool.
• Rules go in the agent's instructions, not in everybody's prompts.
• Build the test set early. It's what lets you change an agent without being nervous about it.
• Keep the old report next to the new one until the person who owns the number is comfortable.