Measuring Team Performance: Calculate LeadTime metric using JIRA and GitHub data


By:   |   Updated: 2021-04-16   |   Comments   |   Related: More > DevOps


Problem

Do you want to visualize your RnD team performance to drive business value? Is there anything that is slowing down your development pipeline? How agile is your team? How long are your customers waiting for the features?

There are many things that can hold you back. Backlog management, code review delays, resources provisioning, manual testing and deployment automation efficiency. In this article I will show you my method of measuring one of the metrics described in this book called LeadTime.

Solution

You can learn how to accelerate your organization by reading this book that summarizes 4 years of research of how to measure software delivery performance. We will cover LeadTime in this article. LeadTime measures the delivery of the feature which is "the average amount of time it takes from the time code is checked in to the version control system to the point in time where it is deployed to production."

There are several important phases during the development process:

Feature development phases that require work that might not have been done before:

  1. Feature design, start of which can be a task creation in a backlog
  2. Feature development

Feature delivery phases, in an agile environment this must be performed continuously and as quickly as possible.

  1. Testing
  2. Deployment

In my solution below I will not take into consideration how long the feature has been waiting in the backlog and how long it took to write the code. I will calculate the LeadTime as a time difference between the end of feature development and deployment into production.

Data Sources

Our team is using GitHub and JIRA during the development cycle so we will use data from those systems to calculate the LeadTime metric.

GitHub (Git) is very popular collaboration development platform that is being used by many developers around the world. My team is using GitHub for the code version control, code reviews and deployment branching. I consider each Pull Request to be a development unit so I will calculate a LeadTime for each Pull Request as a time period starting from the PR merge time until the time when the feature was deployed to production.

JIRA is the framework that my team uses for agile project management and task tracking. I will pull Deployment/Releases tickets from JIRA to get exact deployment day and time and deployment status.

How to Join these Data Sources

In order to join data between JIRA and GitHub I ask developers to add to each deployment ticket the GitHub URL that compares deployed version and previous version and returns all PRs that have been deployed. We are using GitHub Release Tags to identify the deployment.

This is an example of comparison URL: https://github.com/yourcompany/yourrepository/compare/v0.0.9...v0.0.10

If we have such a URL inside the JIRA ticket, we can easily identify which GitHub Repository this deployment is connected with and the result of this API call is the list of PRs that were deployed. Additionally, we will pull the PR number of commits.

Here you can see an example how the GitHub and JIRA data can be joined and how we can visualize our LeadTime metric: every circle on the chart is a PR colored by the GitHub repository it belongs, the dot size is the feature "size" (number of commits in PR), each circle position is PR LeadTime (days) and the line shows overall average LeadTime for all Team repositories.

release lead time chart

I have used the AWS cloud service Glue to pull the data. I will provide an example of how to connect to each data source to pull the data from the above systems. You can adjust it for any Python environment that you prefer. I export the data to AWS S3 storage, but this can be done on any storage device or Database Management system like SQL Server or PostgreSQL. I am using Tableau to visualize the data.

Python script to pull data from JIRA

We will use a few standard Python libraries

import boto3
import json
import re

In order to use the JIRA API we will need the Python library jira.py.

This package is not a part of pre-installed packages in an AWS Glue job. We will need to install this package as an external library. The easiest way to install an external library is to use easy_install package.

import os
from setuptools.command import easy_install
import imp
import site install_path = os.environ['GLUE_INSTALLATION']
easy_install.main(["--install-dir", install_path, "jira"])
imp.reload(site)

from jira import JIRA

Now we can connect to JIRA. I have a list of JIRA projects in the file on S3 and I loop over each and pull tickets of type Deploy. The code is long, so you can download all the code at the bottom of this article. You will probably need to fix tabulation to be able to run the Python code.

apikey = '***'
user = '***'
server = 'https://jira.yourcompany.com'

options = {
    'server': server
}

s3 = boto3.resource('s3')

jira = JIRA(options, basic_auth=(user, apikey))
output_str = ''
for project in jira_projects["projects_list"]:
     jira_query = 'project = {} AND issuetype = Deploy'.format(project['jira_project'])
          deploy_tickets_in_project = jira.search_issues(jira_query, json_result=True, maxResults=5000)

    for ticket in deploy_tickets_in_project['issues']:
    			ticket_json = {"JIRATicket": ticket['key'],
              	"Project": project["jira_project"],
				"TicketSummary": ticket['fields']['summary'],
				"DeploymentEnvironment": ticket['fields']['customfield_14208']["value"],
				"DeploymentTicketResolved": ticket['fields']['resolutiondate'],
                "DeploymentStatus": ticket['fields']['status']['name'] ,
                "GitHubReleaseCompareUrl": ticket['fields']['customfield_25423'] }

                if ticket_json["GitHubReleaseCompareUrl"]:
                    mt = re.match(r'https://github.com/Vonage/(.*)/compare/(.*)\.\.\.(.*)',
                              ticket_json["GitHubReleaseCompareUrl"])
                    if mt:
                         ticket_json['GitHubComparisonStartReleaseTag'] = mt.group(2)
                         ticket_json['GitHubComparisonEndReleaseTag'] = mt.group(3)
						 ticket_json['DeploymentVersion'] = mt.group(3)
                         ticket_json['GitHubRepository'] = mt.group(1)

                         ticket_json = {k: "No value" if not v else v for k, v in ticket_json.items()}

                         output_str += '{}\r\n'.format(json.dumps(ticket_json, default=str))

s3.Object(s3_result_bucket_name, s3_export_file).put(Body=output_str) 

Python script to pull data from GitHub

We will use a few standard Python libraries.

import boto3
import json
import time

In order to use the GitHub API we will need a the Python library github3.py.

import os
from setuptools.command import easy_install
import imp
import site install_path = os.environ['GLUE_INSTALLATION']
easy_install.main(["--install-dir", install_path, " github3"])
imp.reload(site) from github3 import login, GitHub, pulls

Now we can connect to GitHub.

We can either use user and password or use a token for authentication. Here you can find more information on how to create a token.

scopes = ['user', 'repo']
apikey = '***'
gh = login(token=apikey)

s3 = boto3.resource('s3')

today = datetime.today()

deploy_commits = []
pull_requests = [] 

We will use the S3 file with a list of deployments that we have exported in the previous step.

for item in jira_deploy_tickets:
    if "GitHubRepository" in item and item["GitHubRepository"] != "No value":
        repository = gh.repository('Vonage', item['GitHubRepository'])

There is a limit on API calls in GitHub so we want to check that we aren’t close to the limit each time.

if gh.ratelimit_remaining < 1000:
     raise Exception('Cancelling the job. Ratelimit is low: {} , stopping the job'.format(gh.ratelimit_remaining)) 

if (item['GitHubComparisonStartReleaseTag'] != "No value" and item[
    'GitHubComparisonEndReleaseTag'] != "No value"):
    comparison = repository.compare_commits(item['GitHubComparisonStartReleaseTag'],
                                            item['GitHubComparisonEndReleaseTag'])
    for commit in comparison.commits:
        commit_dict_full = commit.as_dict()
        # print(commit_dict_full)
        commit_dict = {}
        commit_dict['repository'] = item['GitHubRepository']
        commit_dict['scan_date'] = today
        commit_dict['team'] = item['Team']
        commit_dict['JIRADeploymentTicket'] = item['JIRATicket']
        commit_dict['DeploymentVersion'] = item['DeploymentVersion']
        commit_dict['PrevDeploymentVersion'] = item['GitHubComparisonStartReleaseTag']
        if item['DeploymentEndTime'] == "No value":
            commit_dict['DeploymentEndTime'] = item['DeploymentTicketResolved']
        else:
            commit_dict['DeploymentEndTime'] = item['DeploymentEndTime']
        commit_dict['DeploymentEnvironment'] = item['DeploymentEnvironment']
        commit_dict['GitHubReleaseCompareUrl'] = item['GitHubReleaseCompareUrl']
        commit_dict['DeploymentStatus'] = item['DeploymentStatus']
        commit_dict['sha'] = commit_dict_full['sha']
        commit_dict['author'] = commit_dict_full['commit']['committer']['name']
        commit_dict['commit_date'] = commit_dict_full['commit']['committer']['date']
        commit_dict['html_url'] = commit_dict_full['html_url']

        deploy_commits.append(commit_dict)

To get more information on PR you can pull PR data.

pull_requests = []
for request in repository.pull_requests(state='closed', direction='desc'):
pr_dict_full = request.as_dict()
pr_dict = {key: pr_dict_full[key] for key in
['html_url', 'title', 'created_at', 'closed_at', 'merged_at', 'id', 'merged_at',
'merge_commit_sha', 'number_of_commits']} pull_requests.append(pr_dict)

Data Analysis using AWS Athena Query

Here is a query that I use in Tableau to create the above visualization.

with deployments as (
     select t.team,repository, c.deploymentversion,c.sha,
     TO_TIMESTAMP(case when c.deploymentenvironment = QAand c.deploymentstatus = 'Deployed' then REPLACE(t.DEPLOYMENTTICKETRESOLVED,'+0000','') else null end) as deployed_to_qa,
     TO_TIMESTAMP(case when c.deploymentenvironment = 'Production' and c.deploymentstatus = 'Deployed' then REPLACE(t.DEPLOYMENTTICKETRESOLVED,'+0000','') else null end) as deployed_to_prod
  from prs c
    join tickets t
      on c.jiraticket = t.jiraticket
  where t.DEPLOYMENTTICKETRESOLVED != 'No value'
  and deployed_to_prod is not NULL
),
Prs_data as 
( select TEAM,REPOSITORY,HTML_URL,MERGED_AT::timestamp as merged_at,NUMBER_OF_COMMITS,MERGE_COMMIT_SHA,FIRST_COMMIT_DATE
  from prs_data
where MERGED_AT IS NOT NULL
  )
  select p.TEAM,p.REPOSITORY,p.HTML_URL,p.merged_at,p.NUMBER_OF_COMMITS,p.MERGE_COMMIT_SHA,p.FIRST_COMMIT_DATE, 
  d.deploymentversion,d.GITHUBRELEASECOMPAREURL,
  deployed_to_qa,deployed_to_prod,
  datediff('days',p.merged_at,deployed_to_prod)
     as lead_time,
  datediff('days',p.merged_at,deployed_to_qa)
     as dev_to_qa_time, 
   datediff('days',deployed_to_qa,deployed_to_prod)
     as qa_time    
from prs_data p 
   join deployments d
     on d.SHA = p.MERGE_COMMIT_SHA
 

If I have touched something interesting to you, I would love to hear your feedback. How does your team’s LeadTime metric look? Which challenges has this article help you solve?

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Maria Zakourdaev Maria Zakourdaev has been working with SQL Server for more than 15 years. She is also managing other database technologies such as MySQL, Postgresql, Redis, RedShift, CouchBase and ElasticSearch.

View all my tips


Article Last Updated: 2021-04-16

Comments For This Article





download














get free sql tips
agree to terms