Four Jira Queries Every Agency CFO Should Run, Weekly

A common pattern at agencies in the 50–200 person range: the project work runs in Jira; the project finances run in a spreadsheet (or in Tempo Cost Tracker, or in Productive, or in the closing pack a CFO produces fortnightly); and the two systems are reconciled monthly when finance sits down to ask why a particular project landed differently from how it was estimated.

By the time finance is reconciling, Jira already had the data that would have answered the question. It was just split across the wrong queries to be useful.

This piece lays out four JQL queries that, if run weekly, will surface most of the events that historically only appeared on the monthly margin report. They were the ones that, in our own first year on this discipline, repaid the time it took to set them up by a wide margin within a month. The fourth one — stalled worklogs — caught two of the four mid-flight cancellations we made in our first year on Saldo.

Query 1: hours-without-cost

The first query catches a quiet but persistent leak: work that was logged in Jira against a project, but where the named individual who logged it doesn't exist in the cost system, or where the rate hasn't been entered, or where the cost number is zero for some reason.

Cost-side data hygiene is rarely perfect at agencies with frequent hiring and salary changes. New hires get added to Jira before their hourly cost gets entered into the finance system. Salary reviews happen but the cost numbers aren't propagated. Contractors land on a project and their day rate is in someone's email, not in the finance system. In each of these cases, hours are accumulating against a project but the cost report shows £0 for them, which inflates the apparent margin.

The JQL piece is straightforward — it's the join with the cost system that does the work:

project in (XXX, YYY) AND timespent > 0 AND status != Closed
ORDER BY worklogAuthor, updated DESC

For each result row, look up worklogAuthor against your cost table. Any author with no row, a zero rate, or a rate that hasn't been refreshed in the last twelve months is a problem.

What we see: this query fires for roughly 4–7 named individuals per quarter across the agency I was CTO of, almost always for new hires whose onboarding included Jira access on day one but whose finance setup took two to three weeks. The cost being missed during the gap, summed across all such individuals, has run between £8k and £14k in a typical quarter for that setup. Catching it weekly, the gap is sub-£1k.

The action: a Slack message to the operations team to add the missing rates. Most cases are closed within a working day.

Query 2: role drift

The second query catches the leak we wrote about in detail in the senior-doing-junior-work piece: named individuals at a more expensive role than the ticket's estimated role.

This requires a small bit of structure in your tickets — specifically, an estimated_role custom field on each ticket (Lead, Senior, Mid, Junior, QA, etc.). It's a one-time setup. Once it's there, the query is:

project in (XXX, YYY) AND status != Closed AND timespent > 0
AND "Estimated Role" is not EMPTY
ORDER BY "Estimated Role"

For each result, compare worklogAuthor's actual role (from your HR or cost table) against the ticket's Estimated Role. Any time someone more expensive than the estimated role logs more than two hours against the ticket, the ticket is in role drift.

What we see: this fires on roughly 12–18 tickets a week across forty active projects. About a third are intentional (a senior coaching a junior through a hard problem, costed at lead rate and worth it). About half are correctable mid-flight (the senior steps off, a mid picks up). The remaining 20% trigger a real conversation: a re-pricing with the client, a re-staffing decision, or a structural change to how the project is being run.

The action: a daily Slack digest to the project lead with the list. The project lead either explains, re-staffs, or escalates. Most are explained or re-staffed within the same day.

Query 3: sub-project drift

The third query catches a leak that's specific to agencies that model their work as Development, Support and Internal sub-projects (which we strongly recommend; see the project-types piece).

The leak: a worklog logged against a Development sub-project for work that's actually Support (post-launch warranty, ad-hoc fixes, retraining the client team), or vice versa. The economics of the two sub-project types are different — Development is priced as a fixed-scope build, Support is priced as a recurring monthly budget — so a worklog landing in the wrong sub-project distorts margin reporting at both ends.

The JQL piece, for an agency using component-tagging to mark sub-project membership:

project in (XXX, YYY) AND component in ("Development")
AND created < startOfDay(-30) AND updated > startOfDay(-7)

The interpretation: if a Development ticket created more than 30 days ago is being updated this week, with new worklogs on it, it's almost certainly support work that should have gone into the Support sub-project. The build was supposed to have closed.

What we see: this fires on 2–4 tickets a week. Each one is worth looking at because it represents post-launch work that's been silently absorbed into the build's cost line. Over a quarter, this query has caught between £6k and £15k of mis-allocated support time at the agency I was CTO of.

The action: the project lead either rights the ticket (moves it to the Support sub-project, where it'll be costed against the support budget), or — if the support budget is already spent for the month — has the conversation with the client about either expanding the support retainer or pricing the ticket as a change request.

Query 4: stalled worklogs

The fourth query is the most important one. It catches projects that are quietly accumulating cost without delivering output — the worst pattern in agency work, because it's invisible until it's expensive.

The signature is: a ticket that's been "in progress" for more than two weeks, accumulating worklogs against multiple senior people, with no movement in status.

The JQL:

project in (XXX, YYY) AND status = "In Progress"
AND statusCategoryChangedDate < -14d
AND timespent > 0
ORDER BY timespent DESC

The interpretation: tickets that have been in progress for more than two weeks, with hours logged against them. Sort by total time spent.

What we see: 1–3 tickets fire per week, but the financial impact per ticket is by far the highest of the four queries. A single ticket that's been in progress for a month with three seniors logging time against it can carry £8k–£20k of accumulated cost — easily a quarter to a half of a small project's total budget.

The two times this query has caught cancellable projects for us: in both cases, the ticket was a "we need to figure out what we're building" exploration that had quietly run from a 1-day spike into a 3-week investigation, accumulating £15k+ of cost while the rest of the project waited. Both times, the right action was to stop the investigation, escalate to the client about the underlying ambiguity, and either re-price or close the engagement. Both times, that conversation moved the project from a 6-week loss-making slide into a clean restart or a cancellation.

The action: a daily review by the project lead, plus a weekly review by the CFO. The CFO review is the one that matters — project leads often won't escalate themselves because they're closer to the work and tend to think the next day will be the breakthrough. The CFO has a different vantage and will pull the trigger on conversations the project lead is reluctant to start.

Why these four, and not "all the obvious ones"

Two related questions we get when we present this list: why these four, and why not other obvious ones (like "tickets without estimates", or "tickets without assignees", or "projects over their estimate")?

The four we run weekly are the ones with the highest financial impact per minute of CFO attention. We tested others. Most either fire too often (and produce alert fatigue), or fire too rarely (and don't justify the time), or fire on things that don't actually correlate with margin movement.

• "Tickets without estimates" fires on hundreds of tickets a week and 95% of them are correctly un-estimated (small bug fixes, exploratory tickets, support tickets where the estimate is the budget). It's noise.
• "Projects over their estimate" only fires after the project is over its estimate. By that point the conversation is post-mortem, not intervention.
• "Stalled worklogs" — query 4 — catches the same problem two weeks earlier, and at the ticket level rather than the project level, where the conversation is still a useful one to have.

The four queries above were chosen by elimination, after running about a dozen weekly queries for six months and tracking which ones produced actions that actually changed margin outcomes. These four were the ones that did. The others either produced no actions or produced actions that were post-hoc.

Setting them up: practical notes

A few practical notes on installing these in your own Jira instance.

• The cost-side data (real hourly cost per individual) needs to live somewhere queryable, and it needs to be kept current. We use a small internal table; some agencies use Tempo's cost field; some use a Google Sheet. The exact place doesn't matter; the data has to exist.
• The Estimated Role custom field is a one-time install per project type. Set up a sensible enum (Lead / Senior / Mid / Junior / QA / DevOps), make it required on tickets above a certain estimate threshold (we use 8 hours), and let smaller tickets default to "Mid".
• The component-tagging for sub-project drift requires you to actually structure your projects with Development / Support / Internal sub-projects. This is an Ops change worth making for many other reasons; see the project-types article for the case.
• The four queries can run as Jira saved filters with a daily/weekly subscription email. You don't need a separate reporting tool. The CFO can read them in their inbox.

If you want all four queries running automatically against your real Jira instance, with cost-side data wired in, role drift surfaced as alerts, and sub-project drift caught the same week — Saldo is the layer we built to do exactly this. The 15-minute demo runs against your real Jira data; we don't ask for card details up front, and we don't follow up if it doesn't land.

The shorter version of this article: Jira already has the data your CFO needs. The closing pack is a way of getting at it monthly. The four queries above are a way of getting at it weekly. The financial difference between weekly and monthly insight, on the average forty-project agency, is between £80k and £200k a year of catchable margin.