Executive Leadership Reporting: 3x Python Flow Metrics Scripts

**Describe the task**

Consider implementing:
https://www.scrum.org/resources/blog/flow-metrics-python-3-scripts-ensuring-transparency

**Acceptance Criteria**
- [ ] first
- [ ] second
- [ ] third

**Additional context**
- Script one: how long work waits in each state

Most work management tools record every state transition with a timestamp. Pull the recently completed items, reconstruct the transitions, and you get the median time each item spent in each state.

Here is the result from one team's last sixty completed items:

Read the shape rather than the numbers. This team is not slow at building. It is slow at confirming that what was built is correct. Roughly four-fifths of an item's life is spent after the code exists, waiting for a person to look at it.

There is a second observation sitting in that output, and it is the more uncomfortable one. A column called "Ready for QA" holding items for a median of fifty-four hours suggests that testing is not genuinely inside the Definition of Done, it is a downstream stage the Increment passes through afterwards. That is a Definition of Done conversation, and it is not one this team was having.

import requests, pandas as pd
from datetime import datetime

AUTH = (EMAIL, API_TOKEN)
JQL = 'project = ABC AND statusCategory = Done ORDER BY resolved DESC'

r = requests.get(
f"{BASE_URL}/rest/api/3/search",
params={"jql": JQL, "maxResults": 60, "expand": "changelog"},
auth=AUTH,
)

rows = []
for issue in r.json()["issues"]:
# The changelog records transitions only, so the opening state has no
# start event. Seed it from the item's creation timestamp.
created = datetime.fromisoformat(issue["fields"]["created"][:19])
events = [(created, "To Do")] + sorted(
(datetime.fromisoformat(h["created"][:19]), item["toString"])
for h in issue["changelog"]["histories"]
for item in h["items"] if item["field"] == "status"
)
for (t1, status), (t2, _) in zip(events, events[1:]):
rows.append({"key": issue["key"], "status": status,
"hours": (t2 - t1).total_seconds() / 3600})

df = pd.DataFrame(rows)
df = df[df.status != "Done"] # Done is terminal, not a queue
print(df.groupby("status")["hours"].median().sort_values(ascending=False).round(1))

Note what the script does not do. It does not tell you why. Reviewers may be overloaded, items may be too large, testing may sit with a separate group. Diagnosing that is the work of the Sprint Retrospective. The script only ensures the conversation starts from evidence rather than from whoever speaks first.

Script two: where the constraint moved

This is the one I think matters most at the moment.

When Developers adopt AI coding assistants, producing code becomes faster. Confirming that code is correct does not. The constraint relocates, and throughput measures do not notice, because they count completed items rather than where items queue.

Three numbers from the same team's pull request history make the shift visible:

The gap between the fiftieth and ninetieth percentile is the finding. Half of all pull requests get a first look inside a day. One in ten waits more than three days. Median change size over the same quarter grew from roughly 140 lines to 410, which is what you would expect when generating code becomes cheap, and which explains the latency, because nobody opens a four-hundred-line change at the end of the day.

import requests, pandas as pd
from datetime import datetime

HEADERS = {"Authorization": f"Bearer {GITHUB_TOKEN}"}
REPO = "your-org/your-repo"

prs = requests.get(
f"https://api.github.com/repos/{REPO}/pulls",
params={"state": "closed", "per_page": 100},
headers=HEADERS,
).json()

rows = []
for pr in prs:
if not pr["merged_at"]:
continue
reviews = requests.get(pr["url"] + "/reviews", headers=HEADERS).json()
opened = datetime.fromisoformat(pr["created_at"][:19])
merged = datetime.fromisoformat(pr["merged_at"][:19])
first = min((datetime.fromisoformat(rv["submitted_at"][:19])
for rv in reviews if rv.get("submitted_at")), default=None)
rows.append({
"pr": pr["number"],
"hrs_to_review": (first - opened).total_seconds()/3600 if first else None,
"hrs_to_merge": (merged - opened).total_seconds()/3600,
"size": pr["additions"],
})

df = pd.DataFrame(rows)
print(df.describe(percentiles=[.5, .9]))

What follows from this is a working agreement discussion, which sits squarely within the Scrum Master's accountability for the Scrum Team's effectiveness. Do the Developers cap change size? Reserve a review-first hour? Pair on generated code rather than reviewing it asynchronously? Those are the team's decisions. The data only establishes that there is something to decide.

One caution worth stating. If completed-item counts are rising while review latency also rises, the delivery system is accumulating a queue, not accelerating. Treating the first number as improvement while ignoring the second is how teams commit to a forecast they cannot meet.

Script three: opening the Sprint Retrospective with observations

Many Sprint Retrospectives open cold, and the agenda gets set by whoever is most confident. Arriving with three or four observations changes the first ten minutes.

Observations, not conclusions. You put them up and ask whether any of it matches what people experienced. Sometimes the answer is no, and that is a useful answer.

signals = []

reopened = df[df.status_sequence.apply(lambda s: "Done" in s and s[-1] != "Done")]
if len(reopened):
signals.append(f"{len(reopened)} items returned after being marked Done")

slow = pr_df[pr_df.hrs_to_review > pr_df.hrs_to_merge * 0.6]
if len(slow):
signals.append(f"{len(slow)} changes spent over 60% of their life awaiting first review")

print("\n".join(f"- {s}" for s in signals))

The issue describes three Python reporting scripts using requests and pandas, but names no repository files, tests, or entry points. Start by locating the project's script conventions and reviewing the Jira and GitHub API examples. Before implementation, clarify the placeholder acceptance criteria; done should mean the three requested flow-metric and retrospective outputs are defined and reproducibly generated.

Written by the indexing model from the issue text.