No matter what system I’m using to track agile initiatives (Asana, Jira, etc.), I always wind up exporting the data into a spreadsheet to get customized metrics. Here’s an example of such a spreadsheet, and in this post, I’ll describe the steps to modify it to analyze agile metrics for your own purposes.

Data Prep

Prefix your user store subjects with one of the following prefixes.

Categorize your stories into objectives or epics by prefixing the subjects with something like “ObjectiveA: ” or “EpicZ: ” (e.g. ObjectiveB: SSO from SystemA to SystemB). Note that in the example spreadsheet, I didn’t use the formal user story format of “As a so-and-so, I want to…”
Use prefixes like the following to filter out unwanted stories:
“DUPE: ”
“MERGED: ”
“CANCELED: ”
“UNPLANNED: ”

Export

Export your user stories to a CSV file or the like. The required fields are simply “Date/Time Opened”, “Date/Time Closed”, and “Subject”. Just make sure that “Date/Time Opened” is in the second column, “Date/Time Closed” is in the third column, and “Subject” is in the fifth.

Import

Make a copy of this spreadsheet, and delete the contents of the “Imported Data“ sheet, but keep the headers. Copy and paste the exported data into the “Imported Data“ sheet. The data should look like the following. Modify the column headings as you see fit.

The “Velocity” Sheet

You can use the “Velocity” sheet to evaluate your team’s velocity. Just enter the “Eval Weeks” that you would like to evaluate and enter the “End” date. You might also want to tweak the FILTER formula in the “Ticket Number“ cell. The FILTER formula in the example spreadsheet filters out stories prefixed with things like “UNPLANNED“, “DUPE”, “CANCELED”, etc. The filtered data should look like the following. Note that the light blue color indicates that the spreadsheet automatically calculates or generates the data.

The “Objectives Only” Sheet

The “Objectives Only” sheet filters the imported data down to only the objectives that you care about. Modify the FILTER formula in the “Ticket Number” cell, and change the “ObjectiveX“ references to match your data set. The filtered data should look like the following. Note that the “Category” column uses a formula to capture the story prefix, and the “New This Wk” column uses a formula to flag potential scope creep. If such a record exists, the conditional formatting will highlight it in pink.

The “Burndown” Sheet

The “Burndown” sheet sums it all up. It uses data from the “Objectives Only” sheet to summarize and graph the data into burndown charts. To use the “Burndown” sheet, enter the first few consecutive dates starting in cell A2. Autofill the remaining dates. For columns B, C & D, change the column headings to match your objective naming convention. Similarly, change the formulas in row 2 of those columns to match your naming convention. Autofill the remaining rows of those columns, but only fill up to the current date (or as far as your data set goes). Leave the rest of the fields blank. The sheet uses the blank fields to extend the charts’ horizontal axes so that you can view the trendlines as projections. You can edit the Data Range of each chart to extend or shorten the projection.

You might be wondering why the sheet contains so many burndown charts for the same data. That’s because simple linear regression on burndown data doesn’t produce an adequate R-squared result. Therefore, for comparison, I added two other regression charts both of which use polynomial regression of different degrees. Note that while the “Polynomial 4” regression yields a high R-squared, its projected completion date (the x-intercept) might be too optimistic to share with stakeholders. The chart that I most often share with stakeholders is the “Polynomial 2” (see the following example), as that chart has a decent R-squared and realistic projection.

Total Burndown - Remaining Stories for 2019 - Polynomial 2 Regression.png

The “Burndown” sheet also includes a “Remaining Stories by Category“ chart, which plots each objective on a separate line so that you can see which objectives are moving along and which have stalled. In the following example, you can see that “ObjectiveB” did not get off to a good start as its initial burndown actually burned up.