Weekly service desk scorecard with a running trend history

Every Monday morning at 8:00am, build my service desk scorecard for the week that just ended and keep a running history of it. This is deterministic reporting: fixed schedule, fixed metric set, counting and formatting over structured fields, no judgement calls. Every number should be computed in code rather than inferred.

Report on the seven day window that ends the Sunday immediately before the run, meaning the previous Monday at 00:00:00 through that Sunday at 23:59:59 in the service desk's local timezone. Derive the window from the run date each time so it stays correct every week.

Use List Customer Requests in Jira Service Management to pull the requests for the service desk I am reporting on. Include resolved and closed requests, not just open ones, and expand the request type and status so the breakdown fields come back on each record. Page through the results until the last page rather than stopping at the first page. From the full set, keep every request that was either created inside the window or resolved inside the window. A request can fall into both groups, so count it in each without double counting the totals.

For each request in scope, call Get Request SLA Information to retrieve its SLA cycles. Treat a request as meeting its SLA when its resolution cycle completed without breaching, and as a breach when any completed or ongoing cycle reports that it breached. Use the time to resolution metric for the headline figure. Skip requests that carry no SLA rather than counting them as either met or breached. Respect the rate limits: if a call comes back rate limited, wait for the interval the response asks for and retry.

Tally the totals deterministically: requests created in the window, requests resolved in the window, requests still open at the end of the window, SLA met, SLA breached, and SLA attainment as a percentage of resolved requests. Add a breakdown by request type and by current status. Our Jira Service Management catalog exposes no queue operation, so build the breakdown from the request type and status on each request and do not attempt to group by queue.

Before writing anything, use the Google Sheets Get Values action to read the existing rows in the tracking tab and take the last row as the previous week's figures. Read first, because once the new row is appended the last row would be this week's own numbers.

Then use the Google Sheets Append Values action to add exactly one new row for this week. Append only. Never update or overwrite an earlier row, because the value of this sheet is the accumulating week over week trend. Keep the column order fixed on every run: week start, week end, created, resolved, still open, SLA met, SLA breached, SLA attainment percent, and the request type breakdown. If the tab is empty on the first run, write a header row once before the first data row. If the last row already covers the same window, skip the append so a re run does not double count the week.

Finally, use the Slack Send a Message action to post the headline numbers to the service desk management channel. Include created, resolved, still open, and SLA breaches for the week, each with the change against the previous week's row shown as a difference and a direction, for example "Resolved: 58 (up 7)". Add the request type breakdown as a single short line. If there was no previous row, say this is the first week of tracking instead of showing a change. Keep the message short enough to read in the channel without opening the sheet, and point to the tracking sheet for the full history.