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.

<figure class="wp-block-image size-large"><figcaption class="wp-element-caption">Source: Atlassian</figcaption></figure>

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.

<figure class="wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio"></figure>

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?

<figure class="wp-block-image size-large"><figcaption class="wp-element-caption">Source: eazyBI</figcaption></figure>

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.

<figure class="wp-block-image size-full"><figcaption class="wp-element-caption">How to export Jira backlog to CSV after writing a JQL query. Source: Quora</figcaption></figure>

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.

<figure class="wp-block-image size-full"><figcaption class="wp-element-caption">Source: Plaky</figcaption></figure>

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?

<figure class="wp-block-image size-full is-resized"><figcaption class="wp-element-caption">Source: Amazon</figcaption></figure>

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 t