May 13 2024 36 mins 4
I wasn’t sure if this topic should be it’s own episode but it’s been on my mind ever since I came back from Atlassian Team ’24 (Atlassian’s annual conference). At the conference, I had the opportunity to meet with a few people who are just as interested in spreadsheets as I am. We talked specifically how Jira can best work with spreadsheets (Excel or Google Sheets) and different workflows that result from the combination of these two tools. It was fascinating to hear how company culture and old ingrained way of doing things leads to the usage of spreadsheets when Jira and its add-ons can accomplish 80-90% of what the business need is. This episode highlights some of the things we discussed at the conference and implications for the future for teams using Jira and spreadsheets.
What is Jira?
Since most people following the newsletter are data analysts, I thought it would be relevant to first share what Jira is. Most would say Jira is issue-tracking software used by engineering and product teams to track software projects. The software aims to mirror agile and scrum methodologies for accomplishing tasks versus traditional waterfall techniques. The rituals behind agile and scrum are codified in Jira’s features, so that’s why the software is loved by thousands of engineering teams around the world. This is a good video from Atlassian on what a scrum project in Jira looks like. Near the end, you’ll see a backlog of tasks. The backlog is one of the most foundational principles of the scrum methodology and will serve as the launching pad for this discussion on Jira and spreadsheets.
Why do teams export Jira issues to Excel spreadsheets?
One theme for why teams would want to export Jira issues into spreadsheets is reporting. We also talked about using other tools like Power BI for reporting purposes, but the intermediary step between Jira and Power BI is still a CSV export.
There are built-in reporting and charting capabilities in Jira. There are also a plethora of add-ons in the Atlassian marketplace for custom charts. The issue with the add-ons is they can get quite costly since you are paying on a per-seat basis. So even if the Jira admin is the one creating the charts, you still have to pay for the other Jira users who are simply viewing the charts. This charting add-on below is one of the most popular add-ons for Jira with 10,000+ downloads. Looks a bit like Excel, no?
Digging a little deeper, we also discussed how the Jira backlog is kind of like a datastore for what the product and eng teams are working on. You can think of this almost like another table of data in your data warehouse. What does this mean for a regular business user who doesn’t work on the eng or product team and still needs the data? Traditionally, they would write a SQL query to get the data they need, do their analysis, and call it a day. With Jira, they would need the Jira admin to export the backlog to a CSV and then they can go off into Excel and do their custom reporting, PivotTables, and dashboarding to show how the product and eng team’s work aligns with the rest of the work of the company.
Story points, finance, and HRIS systems
Expounding on the above point, being able to merge your Jira backlog with other business data is why teams are exporting from Jira into spreadsheets. During the conference, I brought up the point that other business data might just be other worksheets in your Excel workbook. Perhaps one tab has data from your customer support team and another tab has data from your sales team. Through a series of VLOOKUPs and INDEX/MATCHes, a product owner may be able to get a full P&L for their area of work. Perhaps ERP software can do this but can it get to the level of fidelity that a Jira backlog has? This is why it’s easier to just export all your data (not just Jira) into one Excel file and do the custom analysis in that workbook.
Relating to this topic, one use case our group discussed was figuring out how much work was actually completed by the engineering team. To get an accurate picture of this, story points are included in the export. For those new to agile, story points are a unit of measurement for estimating the effort required to complete an item in the backlog.
The CSV export now contains the entire backlog, the engineer assigned to each item, and the story point estimate for the task. You can then combine this Jira data with data from an HRIS system like Workday to understand the output for each engineer taking into account PTO, holidays, etc. Furthermore, engineers might self-report how much time or capacity they are spending each project. Perhaps 50% of time is spent on Project A and 50% on Project B. These ratios (probably also tracked in a spreadsheet somewhere), can then be applied to the story points to get an accurate picture of how much effort was actually spent on the project as a whole.
You can take this one step even further by combining your Jira backlog data with costs and salaries from your finance system. Then you can start seeing the actual dollar costs for different software projects. This might be important for accounting teams as they may be interested in software capitalization and being able to compare software projects with other assets in the company.
The key takeaway is that these questions and answers start with exporting data from Jira into spreadsheets.
Benefits of exporting Jira backlog into spreadsheets
If you’re a follower of this newsletter and podcast, you already know why spreadsheets are the preferred tool for business users. Stepping outside of Jira for a second, Excel is still one of the best analysis and prototyping tools for businesses of all sizes. Our group talked about why Excel and Google Sheets is still used within companies and why it’s the first thing you even think about exporting to. We all already have practice doing this in our personal lives. Think of the first time you were able download transactions from your bank statement into a spreadsheet. What did that moment feel like? Is magical a stretch?
There are other benefits for exporting your Jira backlog into spreadsheets beyond reporting. If other team members don’t have a Jira license, they can still “see” the data in a spreadsheet format (assuming the organization is a Microsoft Office or Google Suite shop). It’s not ideal, but emailing that spreadsheet around or storing it on Sharepoint makes that Jira backlog collaborative. Now others beyond the engineering team can get visibility into what the engineering team is doing.
Jira add-ons for niche use cases
I mentioned the plethora of add-ons for custom reports in Jira. It’s amazing to me how many add-ons exist for very niche use cases in Jira.
One topic that came up during our discussion is how to calculate the time a backlog items spends in different statuses. When the item moves from “Not Started” to “In Progress,” you may want to know how much time has elapsed. This cycle time is important to understand how long it takes to complete tasks once they’ve started. There are add-ons for this in Jira but there are times when you may want to calculate the time in status according to your business rules. This means–surprise surprise–exporting to Excel and writing a formula to calculate the time it takes for items to move through statuses.
The issue is that this granularity of data doesn’t exist in the native export in Jira. To get this granular level of data, you would need another export that would have duplicate entries of a task and the timestamp for when that task moved to a certain status. This data is available through the API, but that would require additional work beyond doing a simple export from Jira.
Importing a spreadsheet into Jira
I didn’t consider the “other” direction of importing a spreadsheet into Jira until I met with people at the conference. To be precise, you can only import a CSV into Jira. The reason for importing from a CSV to Jira is when you want to make bulk changes or additions to your Jira backlog or perhaps you’re migrating from another issue tracking platform.
Another edge case I had not considered is that data entry into Jira is not straightforward for non-engineering teams. If you’re trying to crowdsource ideas for new projects to tackle for next quarter, do you ask your key stakeholders to simply create projects in Jira? What are the proper settings for the project? From the stakeholder’s point of view, all they care about is being able to add a new project quickly and perhaps a description of the project.
To make the data entry easier, you could use a Google Form to collect ideas from various business stakeholders. The form has standard fields like project name, project description, type of project (using a dropdown of pre-filled values), team, etc. Now you’ll have a Google Sheet of all the projects sourced from various parts of your organization in a standard format that works for submitting to your Jira workspace. Even after people submit projects, however, the Jira admin or DRI would have to clean up the submissions to make sure that only valid projects get imported into Jira.
Maintaining workflows with spreadsheets and Jira
Once your Jira backlog is exported out of Jira into a spreadsheet or the data is prepared in a spreadsheet to be imported into Jira, there is a whole separate set of issues that arise. We know that the spreadsheet is the easiest interface for non-engineering teams to use when it comes to Jira data, but someone still has to maintain that spreadsheet. Usually it’s the person who has the domain or business knowledge for why they need the spreadsheet in the first place and they happen to know enough formulas to get the analysis done.
In the moment, the business user gets the job done and goes on with their day. Inevitably, someone will ask the question: can we get the data for next month? Or next week? Now that business user has to think about a long-term solution for maintaining this spreadsheet or push it off to IT. This is where the spreadsheet can really hinder the progress of non-engineering teams. At the conference, we talked about a few different directions this could go.
Just do it manually each time
Not the most ideal scenario, but it gets the job done. After you’ve exported from Jira and your other tools, you brute force to get the job done. Some parts of the file might be automated or you set it up so that all you need to do is just paste in the raw data from the Jira export. Once you get good at doing the manual work, it becomes muscle memory. Thinking through an automated solution or moving off of spreadsheets entirely is an opportunity cost because you have become so fast at doing it manually. We talked about how this method is usually the preferred method when the analysis is ad-hoc or doesn’t need to be done frequently (e.g. quarterly).
Scripting with VBA or Google Apps Script
You decide you have some time to come up with an automated solution or you’re tired of people asking you for the analysis. So you decide to learn a little VBA for Excel or Google Apps Script for Google Sheets. I recently spoke with someone who figured out a way to write a Google Apps Script that pulls from Jira and dumps the backlog into a Google Sheets directly (using the Jira API).
While this solution does remove the manual work from the exporting from Jira and downstream analysis, the question remains: who will maintain this script? What happens when there are new custom fields in Jira that need to be pulled into the spreadsheet? If you are the only person that knows how the script works, you may find yourself scripting and gathering requirements from stakeholders on what data from Jira they want to pull.
Isn’t there an add-on for this?
For Jira Cloud and Google Sheets, yes. It’s just called Jira Cloud for Google Sheets. In the Atlassian marketplace, it only has a few reviews but in the Google Workspace Marketplace, it has 2M+ downloads and over 100 reviews. It’s actually supported by Atlassian and it’s free. But according to the Atlassian Marketplace listing, the add-on was released in 2022 and hasn’t been updated since then.
The reviews are mixed and for those who gave 1-2 stars, the add-on just stopped working altogether. So try at your own risk and I wouldn’t implement it in any business-critical workflows. This review shows what happens when you depend on the add-on and it stops working all of a sudden:
This review speaks to the point I made about embedding a spreadsheet or an add-on in your workflow and then having that spreadsheet or add-on go down. You (in this case, Atlassian) is in charge of fixing the issue and other downstream stakeholders have to come up with workarounds. This Jira user now needs to do a regular CSV export from Jira and get it in the format that he had set up with the Google Sheets add-on.
Build vs. buy: manage the spreadsheet yourself or let Atlassian do it for you?
The classic build vs. buy decision can be applied to the stance your organization takes on the usage of spreadsheets and Jira. You could rely on your TPMs and data analysts to pull data out of Jira and manage the scripts and spreadsheets themselves. This is the “build” scenario.
Atlassian knows that spreadsheets are a problem within organizations because they are brittle, prone to human error, and most importantly, no one typically takes ownership of the maintenance of that spreadsheet. So Atlassian gives you the opportunity to let them replace that spreadsheet at your organization with Jira Align. The product marketer for Jira Align did a great job of framing the problem on this landing page:
Saying Atlassian is giving you the “opportunity” to let them solve the spreadsheet problem for you is a bit of a euphemism. Atlassian sees a huge revenue opportunity with Jira Align which is why the pricing is on the higher side. It’s meant for large enterprises with 500+ Jira users. For 100 seats, Jira Align costs $155,000 annually (or around $130/mo/user). This includes “integrated” users who are still doing work in Jira Cloud and don’t need access to all Jira Align features.
Is Atlassian’s Jira Align worth it?
With an enterprise license, your organization will also most likely get all kinds of human support. Maybe a few account managers, CSMs, etc. to make sure you’re feeling happy and getting the most out of the tool. The main question is what happens when you want to customize something about a chart or metric that is outside of Align’s current feature set? Do you also get an on-call developer who build that feature for you or prioritize your feature requests on Align’s own product backlog?
I think you know where I’m going with this. Instead of not only exporting from Jira Cloud, some analyst may need to now export from Jira Align and merge that with other business data. I don’t necessarily agree with this paragraph on the Jira Align landing page:
Spreadsheet solutions are not designed for the rapid collaboration and information sharing that an innovative enterprise needs. A connected enterprise can quickly and easily answer questions like: Will we deliver on time? What are my team’s dependencies? Are we building the right things? How do we know?
I think the spreadsheet is exactly the “rapid collaboration” tool that most enterprises use because it’s not cost-prohibitive and knowledge workers know how to navigate a spreadsheet. However, if no team maintains the spreadsheet or the various workflows surrounding it, then Jira Align is probably worth it given it’s a managed service by Atlassian. Jira Align was actually an Atlassian acquisition back in 2020 and was formerly called AgileCraft. So there may still be some legacy integration issues between Jira Cloud and Align. But if you’re tired of exporting out of Jira into spreadsheets and would rather have this be someone else’s problem, Jira Align might be worth exploring.
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
The post Dear Analyst #127: Spreadsheets vs. Jira: Which one is better for your team? appeared first on .
