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
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
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.
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
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
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 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&"'").
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
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 columns
B,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 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
IF what is in the
OR function results in
true, We display
IFERROR, we handle the case that neither of the statuses we’re looking for are found, in which case it will also be
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
Completion is calculated as the quotient of these two and Priorities counts how many tickets from the Sprint Focus are Done using
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.
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.
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:
Completion: Here we will reference the Priorities Completion we had set up earlier:
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:
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 literally 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.
Make sure to select the type Combo Chart. Then you just use the Sprint Numbers as X-Axis and make sure you do not only select your existing sprints but pull the range down. This will make sure your graph updates automatically when you add a new sprint. Then for Series you select what you want tracked in your chart. Under Series you select which Series (Tickets, Completion, Time Spent) you want to be on which axis and what style you want it to be (i.e. Columns, Line, Area, …)
You’re done! Want to see how I did it? Just get my version here directly.
And here comes the best thing. The information in this Google Sheet also ties into my overall high-level roadmap. What? You thought you’re done here? No… This is only the first part. In a future article, I’ll talk about Roadmapping!
In my previous job at AIESEC, I was taught a product roadmap needs to be a sophisticated, detailed, always up to date and most importantly really easy to read to anybody. Somehow that translates into a Gantt chart, which to be honest was detailed, sophisticated and easy to read. However, as it includes a time element, it was hardly ever reflecting the current state (whenever something was taking too long), it was time consuming to update so sometimes it wasn’t updated, and most importantly it invited to put competing priorities without symbolizing what is the highest. When everything is a priority, nothing is a priority.
So I turned to a simple table format with some key columns. Some columns are for me to fill my details and manage my priorities, some are for my stakeholders and some are for both.
Status: not only is it important to show what the current status is but also to translate it to layman’s terms. Your stakeholder might not know what ‘Staging’, ‘QA’, etc.. means. Thus it’s important to choose statuses that are easy to understand. Additionally it’s important to have some non Jira statuses such as Decision needed or Not doing (this one is very nice. It gives you the ability to put input you get from others in the roadmap, to give them the feeling of being heard, while still communicating effectively that it does not fit in your current plans).
Value proposition: you will notice that this one is printed in big letters. As your roadmap should be easy to read and your stakeholders should know what to focus on, this steals all the attention. Why are we building this? The most important question you need to answer for your stakeholders to get their buy in and align them on your product vision. It’s useful to properly craft this message as this will also be the message you would want Marketing, Sales and Operations to pick up and reiterate.
ETA: It’s good to stay vague, as it’s the estimated time. You don’t want people freaking out when something is not delivered on a specific date they were expecting it.
Priority vs Priority score: it’s always good to let simple math do the prioritization for you, but sometimes some common sense is needed. Use the additional Priority column to fill your final priorities starting from 1 and sort by this column to always visualize what is your top priority.
How useful was this post?
Click on a star to rate it!
Average rating / 5. Vote count:
No votes so far! Be the first to rate this post.