Dataset: Issues & Pull Requests
Entity: Issues
Field ID: workflow_timeline
Type: List
Description: The list of status events associated with the workflow_status field. This field has a complex structure (list of objects). A series of operators is provided to help extract useful information from this field:
TIMELINE_DURATION: Returns the total duration, in seconds, spent in a given (list of) timeline event(s).
TIMELINE_FIRST_START_AT: Returns the first start timestamp (entry timestamp) for a (list of) timeline event(s).
TIMELINE_FIRST_END_AT: Returns the first end timestamp (exit timestamp) for a (list of) timeline event(s).
TIMELINE_LAST_START_AT: Returns the last start timestamp (entry timestamp) for a (list of) timeline event(s).
TIMELINE_LAST_END_AT: Returns the last end timestamp (exit timestamp) for a (list of) timeline event(s).
Source: Calculated
Transformation logic:
Pull requests: this field is not applicable and will always return an empty array
[].Issues: Use the historical events/changelog/audit log on issues to create one event per change of
workflow_status. See the section Structure of the Workflow Timeline field for a more detailed explanation of the data model.
From: |
|
Github (PRs, Issues) | Repositories: N/A Projects: Status transition events from the timeline API |
Gitlab (PRs, Issues) | N/A |
Bitbucket (PRs | N/A |
Azure DevOps (PRs, Issues) | N/A |
JIRA (Issues) | Status transition events from the changelog API |
ClickUp (Issues) | N/A |
Trello (Issues) | N/A |
Structure of the Workflow Timeline field
The workflow_timeline field is an array of timeline events. Each timeline event represents time spent in a given workflow_status (e.g. the To Do or In Progress workflow statuses).
Each event entry has the following fields:
name: The name of the workflow status
name_id: The id of the workflow status in the source application
start_at: The datetime on which the issue entered the workflow status
end_at: The datetime on which the issue left the workflow status
Timeline events can be open or closed. An event is open when the item is currently in that status (e.g. the issue is currently in the workflow status In Progress). Open events have a start_at but no end_at. On the other side, closed events describe states that happened in the past and have both a start_at and an end_at.
The workflow_timeline field can be displayed in a custom formula and looks like this:
[
{
"name": "To Do",
"end_at": "2024-01-15T14:04:40Z",
"name_id": "10002",
"start_at": "2023-10-07T13:39:19Z"
},
{
"name": "In Progress",
"end_at": "2024-01-25T14:10:06Z",
"name_id": "10003",
"start_at": "2024-01-15T14:04:40Z"
},
{
"name": "In Review",
"end_at": "2024-01-30T14:10:10Z",
"name_id": "10004",
"start_at": "2024-01-25T14:10:06Z"
},
{
"name": "In Progress",
"end_at": "2024-02-05T14:10:06Z",
"name_id": "10003",
"start_at": "2024-01-30T14:10:10Z"
},
{
"name": "In Review",
"end_at": "2024-02-10T14:10:10Z",
"name_id": "10004",
"start_at": "2024-02-05T14:10:06Z"
},
{
"name": "Staging",
"end_at": null,
"name_id": "10005",
"start_at": "2024-02-10T14:10:10Z"
}
]
Using this example above, here is what each TIMELINE_* operator will return:
TIMELINE_DURATION(workflow_timeline, "In Progress")
// 2024-01-25T14:10:06Z - 2024-01-15T14:04:40Z
// + 2024-02-05T14:10:06Z - 2024-01-30T14:10:10Z
=> 1382722 (seconds)
TIMELINE_FIRST_START_AT(workflow_timeline, "In Progress")
=> 2023-01-15T14:04:40Z
TIMELINE_FIRST_END_AT(workflow_timeline, "In Progress")
=> 2024-01-25T14:10:06Z
TIMELINE_LAST_START_AT(workflow_timeline, "In Progress")
=> 2024-01-30T14:10:10Z
TIMELINE_LAST_END_AT(workflow_timeline, "In Progress")
=> 2024-02-05T14:10:06Z
Reporting Use Cases
The Workflow Timeline field is one of the most powerful attributes for process analysis, providing a complete historical log of every status an issue has passed through, including the time spent in each one. Unlike the Workflow Status field, which only shows the current state, this field unlocks the ability to perform in-depth cycle and lead time analysis based on your project management workflow.
Calculating Time in Status (Cycle Time Analysis): This is the primary and most critical use of the workflow_timeline field. It allows you to precisely measure how long work is spent in any stage of your process.
Measure Bottlenecks: You can calculate the average time issues spend in any column of your board. For example, a custom formula metric like
AVG(TIMELINE_DURATION(workflow_timeline, "In Review")) / DAY()will tell you the average time your issues are waiting for review. By creating metrics for each of your key statuses, you can build a comprehensive cycle time chart that highlights exactly where your process is slowing down.Combine Stages: You can also measure the duration across multiple statuses by providing a list. The formula
AVG(TIMELINE_DURATION(workflow_timeline, ["In Review", "In QA"])) / HOUR()would calculate the total time spent in your entire validation process.
Measuring Transition and Queue Times: You can use the timeline-specific functions to get the exact timestamps when an issue entered or exited a status, allowing for even more granular analysis.
Time to Start Work: You can calculate the "triage" or "pickup" time by measuring the duration from issue creation until it first enters an "In Progress" state with the formula
(TIMELINE_FIRST_START_AT(workflow_timeline, "In Progress") - created_at) / DAY().Time from Code Complete to Done: You can measure the efficiency of your release process by calculating the time from when an issue leaves the "In Progress" status until it is finally closed:
(closed_at - TIMELINE_LAST_END_AT(workflow_timeline, "In Progress")) / DAY().