Many companies have faced the problem of issue prioritization, and we are no exception. Customers wanted new features, managers offered their ideas, and developers came up with their solutions. Our product backlog in Jira turned into an endless list of issues. The sprint planning meetings took hours and ended up with the sprint still not getting the most significant backlog issues.
Our solution was to create a Google Sheets spreadsheet to prioritize tasks and synchronize the remote team.
- We adopted a decision matrix or decision matrix analysis—a method that helps make weighted decisions based on different criteria.
- Came up with criteria from the RICE score and AARRR.
- Integrated the spreadsheets with Jira using Automate.io. Tried Zapier as well.
And so we got a prioritization tool for the whole team.
We decided to share our prioritization template and tell you about the tool we invented based on it.
How to prioritize with a weighted matrix template
We've left a few of our issues in the spreadsheet as an example. Delete them before configuring the prioritization matrix.
Rename the teams and criteria
- Rename the tabs with the teams that will evaluate the issues. We've divided into Product Managers and Devs.
Also, change the team names on the Factors tab.
2. Rename the evaluation criteria on the same tab or use the suggested ones. The spreadsheet has an example of our criteria. They may differ for different teams. For example, managers can evaluate the impact of a feature on sales, but they cannot determine the development time.
Our criteria examples:
Product Managers Team.
- Money—Influences the money income.
- Activation—Helps to understand how the product works.
- Retention—Increase user's motivation to use the product again.
- Service—Helps us spend on customer support less time without quality loss.
- Ads—Increase the amount of Facebook Ads a user launches via our product. Important for Facebook Marketing Partnership.
- Posting—Helps to create more templates or custom posts. Shows the advantage of posting and analysis via the tool. Customers post more via the service.
- Reach—How many customers or product units or how much money this feature will affect.
- Time—Time spent on development, complexity.
- Value—Importance for development and product.
Add criteria weight and description
1. Add each criterion a description using a comment on the cell in the Factors tab and team tabs so that users can see them immediately. Do not put descriptions on separate tabs—no one likes to switch between them.
2. Add each criterion weight in the Factors tab—a ratio that shows the importance of the criterion compared to others.
In our matrix, we used the Time criterion (dev time), which has a negative ratio. Set the required weight for each criterion from -3 to 3.
Add Jira issue links
Each issue has a link to Jira so that you can open it and read the description.
1. You must add the link to your Jira domain. Open any issue and copy a part of the link from it.
2. Add the link to the field on the Factors tab (Jira Domain for links).
Set up the Total tab
- The evaluation results will appear on the Total tab.
The data is taken from the Factors tab from columns A through O by default.
If there are more/fewer criteria, they must be added/removed from the formula.
2. Issues with Done, In Review, Test, Bug, or Epic statuses do not fall into Total by default either. If you need to change this setting, add/remove the required statuses from the formula.
How to integrate Jira with Google Sheets via Zapier
You can add issues to the table manually—long and painful, but free. Or you can use Zapier integration. That' s only three simple steps.
Step 1. Jira↔Zapier Integration
1. Create a new integration with Zapier. Connect Jira using the company domain. Jira Cloud, in our case. Choose the New issue trigger.
2. Select the Jira project from which you want to import the issues and check if the data is being loaded.
Step 2. Google Sheets↔Zapier Integration
1. Add an action that should happen in Google Sheets spreadsheet.
The first action is to search for the required row in the spreadsheet.
2. Fill in all fields: spreadsheet name, tabs, and where the data will be imported from.
3. Specify the Issue key column as the element to be searched. The value we are looking for is Key (unique issue key-number in Jira).
4. Specify search condition—search from the bottom of the spreadsheet.
5. Select the Add function if nothing is found. Then a new issue will be added to the decision matrix.
6. Add fields to be included.
7. Test & Continue.
8. Give a name for the automation.
Step 3. Google Sheets Automation Set Up
1. Fill out the next step. Add a new action (click plus)—Update Spreadsheet Row in Google Sheets, which will automatically update the data in the specific columns. Specify the Google Drive name, the spreadsheet name, and the tab where you want to apply the changes.
2. Specify the data that should be changed if it is found. Specify the row in which the information will be updated and the issue type—the Task in this case. If step 2 is filled out, the necessary items will appear in the drop-down list.
3. Fill out all fields in which the information should also be updated. Click Continue.
5. Launch the integration (Zap).
6. Check. Move the backlog issue in Jira. Wait for some time or force the trigger.
Change the issue status.
How to add existing issues to the decision matrix
1. Go to Jira filters. Create a filter with the project where the issues you want to upload to the decision matrix are. Select all issues and all statuses. Make sure that the columns in the filter match the Google Sheets spreadsheet columns. Note: set the columns in the Jira filter in the same sequence as in the spreadsheet. Save the filter and download it.
2. Import as a new tab. Copy all tasks to the Jira issues tab.
Issues evaluation in decision matrix template
We evaluated the issues once a week. We chose a specific day for the project prioritization to synchronize.
- To start the assessment, go to the tab with the team you belong to.
- Start evaluating each issue by existing criteria. In our weighted matrix template, the issues are scored from 0 to 3. Scores value:
Calculation and decision matrix analysis
After the evaluation, all scores are automatically calculated in the Calculations tab by the formula:
Total volume = Criterion 1*Factor 1 + Criterion 2*Factor 2 + Criterion 3*Factor 3 + Criterion N*Factor N.
The total value and the top priority issues will be calculated in the Total tab. Issues with a high number of scores will be at the top of the list. In the team's opinion, they have the highest priority. We have determined that we can take no more than 20 issues to the future sprint, so we highlight that number in the Total tab of the weighted decision matrix.
You can change the number of top issues using the conditional formatting function.
How to automate the weighted decision matrix
At first, the Google Sheets spreadsheet seemed perfect, but after a couple of weeks, we started having problems: Jira import was constantly crashing, lots of duplicated or lost data rows, spreadsheets were often freezing when several people were evaluating at once. We had a Google Sheets admin who was continually fixing the formulas. Anyway, the team sometimes forgot to assign the scores.
After two months of suffering, we took this weighted scoring model as a basis and made our prioritization tool—Ducalis. Issues were uploaded automatically, formulas were no longer crashing, and notifications reminded users when it was time to prioritize. We used the service inside our remote team for more than a year and now decided to show it to the world.
Give a try to Ducalis
- Ducalis works blazing fast, even if the whole team evaluates the issues at the same time.
- The full issue description is always at hand, and you don't need to switch to Jira.
- Ducalis doesn't lose or duplicate the tasks, which often happens in spreadsheets.
- Random clicks can't break the calculation formulas in Ducalis in the wrong place.
- Ducalis sends reminders to the team so that no one forgets about prioritization.
You can go a long way and prioritize product features in the weighted decision matrix template or save time on backlog grooming and try Ducalis right away.