Does Jira really suck?
Recently I went to a Product Manager Meetup and I couldn’t help but notice that throughout the conversations almost everyone present complained about how much Jira sucks at least once — including myself. Yet again, everyone is using it, including myself and it’s definitely not for a lack of alternatives available on the market. So I was reflecting on the tools that I work with on an everyday basis for my work at Educatly and noticed that Jira is invaluable to us — because we learned to use it for its strength which is allowing engineers to collaborate in an agile project — and for everything else we integrated Jira with Google Sheets!
Educatly is one of the hottest early-stage startups for the MENA region. I joined them last year to serve on the board as VP Product to support their mission to provide an educational opportunity to every education seeker, as it deeply resonates with my mission to reduce borders in the world. I’m here to make it easier for students to find educational opportunities abroad.
As an early-stage start-up that has yet to receive major funding, we started with minimal investment from its founders. A major portion of this investment of our platform which is currently developed and maintained with a fixed scope contract by our trusted development partner Commutatus, a start-up based in Chennai, India.
As of now, our platform development might not be the fastest (for a lack of resources), yet there’s a lot to do. Much more than what could fit in a single sprint and with our priorities sometimes changing very quickly, it was imperative for me to always keep an overview of each sprint’s progress as well as the high-level roadmap. As I also wanted to provide visibility and transparency to my other stakeholders in Educatly, I couldn’t rely on tools such as Confluence or Jira. It wouldn’t offer me the tools, visualizations or sharability that I was looking for on a daily basis, so I went and created my own.
Cue in, my own Sprint Tracker.
At first glance, the sprint planning tracker is not an immense feat of engineering. It was born out of the need to know whether we’re putting our effort into the right initiatives (by giving visibility of % effort spent on component and ticket type), whether our vendor is consistently performing (% sprint completion) and how much money we’re spending (Time Spent). What I’m proud of is that it is always updated, even as the sprint progresses. If I wouldn’t be able to update it so swiftly, there’d always be a pain in using it which would lead to me not using it and thus not updating it and thus not serving transparency for my stakeholders. I myself, I simply have to open this report and see a birds-eye view on the first glance. As we’re paying our developers based on the Time Spent and not Story Points, Jira is not able to give me any such reports.
Now, where does the information come from? This is where the Sprint Planning Sheet comes in.
For every sprint, there is a sheet just like this. It is where I plan my sprint, communicate the priorities to our vendor and where all calculations for the Sprint Tracker are being made. Whenever a new sprint starts, I simply have to create a copy of this sheet, put the tickets that I want the developers to focus on and let the magic unfold.
So let’s see how we can build it on our own. Don’t want to go through the trouble of building it on your own? You can get it here directly.
The idea is quite simple. The main source of information is the Jira Cloud For Sheets Add-on with which you can predefine the fields you want to import, automatically on a recurring basis. The sprint sheets automatically pull the right tickets to build reports on each individual sprint and then combine it to the overall report.
For this, we create a new tab called Jira import and open the Jira Cloud for Sheets add-on.
It gives you the ability to either use a JQL query or an existing Filter that you created in Jira to pull your tickets. A suggestion is not to pull all tickets from your project but only the ones you would want to be tracking. Then you can select the Fields that you want to pull. This is important. You would want to pull only the most important fields, in order not to reach the Google Sheet limit of rows too fast. In my case, I am pulling only the information I am actually going to need to create my reports: Issue Type, Key, Priority, Summary, Status, Created, Components, Time Spent, Sprint. Sprint needs to be the last one. Do not forget to set up a recurring automated import, by selecting Refresh Data Sheet. After your first successful import, it will give you the option to Scheduled update.
This is because properly analyzing Sprints is where the major difficulty lies: in Jira, a ticket can be part of multiple sprints (if it doesn’t get finished in a sprint it moves to the next). Importing this leads to multiple values concatenated with ; in the Sprint column. So if we are going to import your sprints dynamically into the sheets for individual sprints, how would we reference the specific sprint, when there are multiple? The same is also true for Components. At Educatly, there are 4 components. You might have less or more. Sprints, however, can have indefinite. Let’s dig into it.
First, we’re going to create an additional sheet called Import that we’re going to use to manipulate our imported data. We’re going to reserve columns A and B for later use. Starting from C1 you are going to simply reference back to the Jira Import sheet to ensure the same information is present there.
This means in cell C2 you are going to put ='Jira import'!A2, in C3 it will be ='Jira import'!A2 and in D2 it will be ='Jira import'!B2, respectively.
You will do this across your entire sheet until there is an exact copy of the Jira Import sheet in your Import sheet — and then we’ll deal with the Components issue. In my case, as I have 4 components, I will make sure that I have 4 columns called Components. You will have tickets with 0 components (see the first row in my case), with 1 component and with multiple components (highlighted row).
To deal with this, we’re going to take use of the SPLIT() function. It accepts 2 parameters; the field that we want to split and by what we want to split it. So instead of =’Jira import’!G2, we will change it to =SPLIT(‘Jira import’!G2,”;”). If you did everything right, it should start separating the multiple components into the extra columns you provided.
For Sprints, we do not have to name the remaining columns, but we do have to split them in the same way as we split Components.
Then, we’ll want to identify which is the latest sprint a ticket is in so we can properly import it into the respective Sprint Planning sheet. For this, we need a list of all the sprints a ticket with this specific combination of sprints has been in, ordered by the highest (= latest) sprint number. This is what we’ll use columns A and B for. Into B, we’ll simply concatenate all sprints again, separated by a space. We’ll do so by using the formula =concatenate(N2:AA2," "). This ensures that the values from all cells for sprints get fused together into one cell.
In A2, we’ll put the magic, which ensures that we always select the latest sprint.
=if(isnumber(hlookup(B2,'Import Transpose'!B$1:BW$1000,2,false))=FALSE,"",concat("EDU Sprint ",hlookup(B2,'Import Transpose'!B$1:BW$1000,2,false)))
This is a big mash-up of multiple formulas, so let’s break them down one by one. At the center of the whole piece is hlookup(B2,'Import Transpose'!B$1:BW$1000,2,false), which references a new sheet called Import Transpose. Let’s see what that is.
In cell A1, we have the formula which reads =UNIQUE(Import!B:B). This ensures that we get a list of all unique combinations of multiple sprints a ticket has been in, among all of the tickets. The reason we’re doing this is because we will have to transpose this list over our columns and the limit for number of columns is much less than the limit for the number of cells, so we need to be frugal with the number of entries we have here, hence the UNIQUE function.
In cell B1, we simply =TRANSPOSE(A:A), changing the values from being in all the rows in column A to being in all the columns in row 1. And below that, in cell B2 to BW2 (basically the end of the sheet) we have the formula =SORT(TRANSPOSE(SPLIT(B1,"EDU Sprint ")),1,false), which would split our concatenated Sprint values by the designation Jira gives to them, leaving only the numbers. We’ve learned before that SPLIT would populate the cells to the right of the cell in which the SPLIT is being performed, hence we wrap our SPLIT function with TRANSPOSE, making it a list and then SORTing it from highest to lowest. Et voilà, we get a list of all the sprints a ticket with this specific combination of sprints has been in ordered by the highest (= latest) sprint number. Our work in this sheet is done and we’ll go back to our sheet Import sheet and the formula we had looked at earlier.
HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false)
This HLOOKUP function would take value stored in B2 (which is the combination of sprints this ticket has been in), and look for it in the first row of the Import Transpose sheet, where we store the list of all unique combinations of sprints our tickets have been in. Once found, it will select the value of the second row from the column that our HLOOKUP search matched, which happens to be the first of the list of the sprint numbers we had just created earlier. So the output of this function would be a number, i.e. 1. Now let’s look at:
concat("EDU Sprint ",HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false))This simply takes the number and fuses EDU Sprint in the front. So the output of this function would be for example EDU Sprint 1. Now let’s look at:
=if(isnumber(HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false))=FALSE,"",concat("EDU Sprint ",HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false)))The remainder of the function simply acts as error handling. With IF(ISNUMBER(..., we are simply checking on whether the output of HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false) is a number (which it should be). If it is not (=FALSE), we will display nothing (""), but if it is, we will simply execute the function concat("EDU Sprint ",HLOOKUP(B2,'Import Transpose'!B$1:BW$1000,2,false)) giving us the final output we were looking for.
With this, we’re done with our Data Import!
Was this already too much work for you? You can get the complete file here directly.
Next, we’ll build our Sprint Planning sheet. Let’s start by utilizing our newly created function to pull in the tickets for a sprint.
For every sprint, you will have to list the full name of the sprint, such as here in the cell B2. This is how you can tell your sheet which sprint it is for to pull in the right data.
The function for pulling in the data is simply =QUERY(Import!2:1000,"select B,C,D,E,F,G,H,I,J,K,M where A = '"&B2&"'").
The query function is receiving two parameters. The first parameter is the data source for where the function will get its information from. In our case, it is all the information in the Import sheet. The second parameter is the actual query. If you’re familiar with SQL, you will feel at home here. Since in Parameter 1, we specified that we’re selecting data across column A to Z, we can reference each of these columns and you can even specify the order in which you want them to be returned. In this example, we are selecting the columnsB,C,D,E,F,G,H,I,J,K,M, which will give us Sprint, Issue Key, Issue Type, Priority, Status, Component 1, Component 2, Component 3, Component 4 and Time Spent. However, we’re not just selecting all the data, but as discussed earlier, we want to pull the tickets for this Sprint 10 only. Hence we’re adding the where parameter, saying that it should only select from rows where column A from the Import sheet is what is in B2 . Column A is the latest sprint, which we painstakingly pulled out and B2 references the value EDU Sprint 10. Once you got that, all the information should automatically be displayed.
Next, we’ll add in the Sprint Focus, which is used during the planning phase, before the sprint has been started. This means the list of tickets that we just pulled in automatically is most likely going to be empty. The columns Feature, ID, Type, Priority and Comment are added by me manually and do not necessarily follow Jira. The formula in the column for Issue Key utilizes SPLIT again to ensure just the key is being displayed.
The formula is =IFERROR(SPLIT(G4,"https://commutatus.atlassian.net/browse/"),""). The column Done? would look for the Issue Key in the list and check the status. As the sprint would progress and tickets would be completed, this check would turn from No to Yes. This is achieved with the following formula:
=IF(N4="","", IFERROR(IF(OR(VLOOKUP(N4,D$33:M$236,4,false) = "Production",VLOOKUP(N4,D$33:M$236,4,false) = "Done",VLOOKUP(N4,D$33:M$236,4,false) = "Completed",VLOOKUP(N4,D$33:M$236,4,false) = "Staging",VLOOKUP(N4,D$33:M$236,4,false) = "Approved"),"Yes","No"),"No"))N4 refers to the column Issue Key, meaning if that is empty, Done? will also be empty. The rest of the function will check whether the value in Status for the ticket with the respective Issue Key is Production, Done, Completed, Staging or Approved, for which the ticket will be considered Done. These statuses might change for you depending on how your Jira is set up. Let’s look closer at the formula:
VLOOKUP(N4,D$33:M$236,4,false)VLOOKUP works very similar to HLOOKUP. It will look for the value in N4 (Issue Key) in the given range (our list of tickets) and take the value from the 4th column (Status) from where it had found the value. Using OR(), we can chain together multiple IF statements, checking whether the status is Production, Done, Completed, Staging or Approved. IF what is in the OR function results in true, We display Yes, otherwise No. With IFERROR, we handle the case that neither of the statuses we’re looking for are found, in which case it will also be No.
The information gained from this is used to caluclate the Sprint Completion. For Committed, we’re counting the number of tickets in the Sprint Focus. This list is negotiated with the vendor, so this is what they would commit to. It’s calculated using the COUNTA function, which simply counts the number of cells with a value over the range of the column Feature.
Done is calculated by counting the number of tickets in the statuses Production, Done, Completed, Staging and Approved. This is done using the COUNTIFS function.
=COUNTIFS(G33:G1031,"Staging")+COUNTIFS(G33:G1031,"Approved")+COUNTIFS(G33:G1031,"Production")+COUNTIFS(G33:G1031,"Completed")+COUNTIFS(G33:G1031,"Done")Completion is calculated as the quotient of these two and Priorities counts how many tickets from the Sprint Focus are Done using =countif(O4:O,"Yes")/(countif(O4:O,"Yes")+countif(O4:O,"No")).
Wow, we’re almost done with the Sprint Planning sheet. Lastly, we’re building Pivot Tables (you find them under Data > Pivot table), which analyze how much effort (Time Spent) went into each Type and Component.
A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report.
Components
Taking the range of our imported tickets for this sprint, we will be able to select Components as Rows. This is pretty straight forward.
However, it does not show us any information. Instead of selecting Time Spent from the dropdown after pressing Add, we’ll select Calculated Field. This is because we need to divide Time Spent by 3600 (by putting ='Time Spent'/3600) to turn the Time Spent which Jira saves in seconds into hours. Don’t forget to select Summarize by SUM.
The principle for Type is the same. We’ll just select that as Row and use the same Calculated Field for Values.
Now we can summarize this information in the overall Sprint Tracker. Skip ahead and get the complete file here directly.
Let’s go one by one of how the columns Sprint, Started, Tickets, Completed, Completion, Time Spent, Component % and Type % are filled.
Sprint: This is filled by hand whenever a new sprint is created.
Started: =IF(COUNTA(indirect("Sprint "&A3&"!B53:B1000"))=1,"✘","✓")
This function will count how many rows there are in the list of tickets imported for the respective sprint. It will automatically look in the right sheet by taking the value of the sprint number from the column Sprint (A). For this, we use the indirect function. We’re using indirect, because we are dynamically getting the name of the sheet from the cell A3. That value in this case is 1. Without a dynamic reference in the query, the parameter would simply be "Sprint 1!B53:B1000". We’re using & to fuse together the different parts to build the parameter. For some reason (don’t ask me why), the formula will return 1 if no sheet by that name exists. So we can simply check whether a sprint for this has been created already (which in my case means it has started or is about to start) by checking whether the COUNTA function would be =1. If it is, we display ✘, if it is not, we display ✓.
Tickets: Here, we want to refer the number of tickets that we designated in Sprint Focus, not the number of tickets pulled from Jira. Hence, we count how many tickets have Yes and No for Done: =IF(B3="✘","",COUNTIF(indirect("Sprint "&A3&"!O:O"),"No")+countif(indirect("Sprint "&A3&"!O:O"),"Yes")). If B3="✘", we will display nothing.
Completed: We can simply reference Done from the Sprint Summary we created earlier: =IF(B3="✘","",indirect("Sprint "&A3&"!C4")).
Completion: Here we will reference the Priorities Completion we had set up earlier: =IF(B3="✘","",indirect("Sprint "&A3&"!C6")).
Time Spent: We’ll take the SUM of the column Time Spent from the imported tickets for a sprint. Do not forget to divide it by 3600: =IF(B3="✘","",SUM(indirect("Sprint "&A3&"!L2:L1000"))/3600)
Components: The function is the same for each of our components, it just needs some adjustment. It uses the VLOOKUP function to look for the respective component (EI, Student, API, Consultant) in the pivot table we created for this purpose earlier and takes the 2nd value (which is Time Spent). This is then divided by the Grand Total Time Spent.
=IFERROR(IF(B3="✘","",VLOOKUP("EI",indirect("Sprint "&A3&"!Q3:T9"),2,false)/VLOOKUP("Grand Total",indirect("Sprint "&A3&"!Q3:T9"),2,false)),0)This is almost the same for the Type:
=iferror((IF(B3="✘","",VLOOKUP(K$2,indirect("Sprint "&$A3&"!T5:U1000"),2,false)/VLOOKUP("Grand Total",indirect("Sprint "&$A3&"!T5:U1000"),2,false))),0)
Once you have that, you can just select your first row and drag it down as much as you want, already pre-filling all the fields. You can then pimp it a little using Conditional Formatting. For each new Sprint that you start, you simply duplicate your existing Sprint sheet and rename it respectively. Everything is automated with very little effort. Congrats! Let’s put the cherry on the top by creating the chart to easily compare sprints to each other.
much as you want, already pre-filling all the fields. You can then pimp it a little using Conditional Formatting. For each new Sprint that you start, you simply duplicate your existing Sprint sheet and rename it respectively. Everything is automated with very little effort. Congrats! Let’s put the cherry on the top by creating the chart to easily compare sprints to each other.
You’re done! Want to see how I did it? Just get my version here directly.
You’re done! Want to see how I did it? Just get my version here directly.
