How to build an automatically updating Product Roadmap in Google Sheets

[#1]

When working on this post, I reviewed the Atlassian Marketplace for numerous product roadmap-related discussions. It came as no surprise that all of them expressed different views on what the roadmap actually is, what form it should have, what information it should contain, who it should be shared with, etc.

In the end, this research confirmed that there is indeed no one-size-fits-all solution.

However, after summing up the voiced requirements, I realized that our approach to creating a roadmap can benefit a large part of the Atlassian Community. One of the core reasons is that at my company we use Google Sheets, an extremely flexible system, to document the roadmap. So, by applying some spreadsheet magic even the most demanding user can adjust it to their needs.

Let’s start by reviewing what Jira users mentioned as crucial for a working roadmap, and then move on to the ‘how-to’ part, where I will share a step-by-step guide and a ready-made Google Sheets product roadmap template that you can apply for your project.

What do users expect from a product roadmap?

Below I’ve split all the answers into categories to make it easier for you to read:

• Level of details

Generally, a roadmap represents a high-level vision for the product or service. Usually, it is compiled for a period from 12 months to 5 years. Its main goal is to communicate the product strategy to different stakeholders, keeping the team synchronized in that direction, and explain the next steps. 

However, some organizations prefer to delve deeper and build roadmaps on the story or task level.

• Scope

The scope correlates directly with the level of detail a roadmap includes. 

Therefore, some users may build a product roadmap focusing on broad topics only. In this case, it is always beneficial to have an opportunity to map epics to those high-level items.

Others review and prioritize the topics related to the product and, based on these conversations, add corresponding epics to Jira. So, the product roadmap is built of epics, which may include multiple stories/tasks documented as Jira issues, and which are accessible from the general hierarchy. 

Each roadmap’s information block can be populated by additional details as well.

The view can include a separate list of epics that haven’t been planned yet for PMs to keep an eye on.

Having a responsible person mentioned next to the epic allows company leaders and personnel specialists to see who is managing it.

• System

One may create a product roadmap in the form of a presentation in PowerPoint, Google Slides, or other similar tools. Some specialists use Portfolio by Atlassian, Visio diagram, Excel, Aha tool, SharpCloud, or the in-built product roadmap functionality in the next-gen Jira. 

• Format

When it comes to a roadmap, the general expectations from the data format include the following:

• Simple, easy to read
• Effortless 
• Flexible, easy to adjust
• Self-explanatory

One of the most common roadmap formats is a visual that resembles a Gantt chart, which breaks up into the list of components on the left side, and the timeline on the right.

Many respondents stated that a legend is another component that may help a user navigate through the roadmap. Some users noted that adding links to sources where the data comes from, sharing roadmaps via a URL which does not require sign-in, as well as applying color coding are examples of features people would want to have at hand when managing roadmaps.

• Audience

Depending on the organization size and established internal processes, roadmaps may be shared with C-level executives, marketing and sales, PMs, developers, and other team members to keep them all aligned on where the product is heading. Sometimes companies even choose to create public versions of high-level roadmaps for customers to freely access and provide feedback. 

• Frequency of updates

When developing product priorities may likely change frequently. That’s why it's ideal when a roadmap can instantaneously reflect all changes and communicate them to stakeholders. 

Being able to see live data that comes directly from Jira is a big benefit since it eliminates the need for constant manual updates. 

• Communication of changes

All roadmap changes should be immediately communicated to all stakeholders and include comments explaining the details and the reasons why they were made. 

• Version control

Some users mentioned that they must keep the history of all product roadmap versions so that one can commit to its execution, and roll back to see the previous version at any time. 

• Timelines

Many users are reluctant to add timelines to a roadmap because of its broad nature. To be able to make accurate estimations, one needs to collect information, put together an implementation plan, allocate team members, and only then calculate the deadlines. That’s why users prefer seeing roadmaps which contain rough estimates rather than a strict schedule. These estimates are usually displayed in the form of a horizontal timeline view. 

• Assess the results

The team has to be able to use a roadmap to assess their results and make conclusions on what steps have to be amended to optimize their future work, as well as be able to reach the initial goals. 

Feel free to comment on this post and tell us which points you agree with, and which ones you disagree with. I think it would be interesting for all of us to hear different stories and learn from each other. 

Why is Google Sheets a good fit for building roadmaps? 

Let’s try to understand why we chose Google Sheets for creating a roadmap. First of all, because of its flexibility - its supernatural ability to adjust to a wide range of use cases, starting from a family expense tracker and going up to mastering budgets, performing payroll calculations, creating product stats dashboards, documenting roadmaps, and more.

Beyond question, the tool has lots of other advantages to boast of, but I would stress these four, which all contribute to its flexibility and offer added value for our use case:

• Easy to use and adjust - Google Sheets is indeed a very straightforward app. One can be at the beginner level and use it for basic calculations. For advanced users, Google Sheets opens new horizons and breaks down borders, since it can help you do almost anything you may need to do with data. 

• Advanced calculations - Google Sheets offers numerous in-built functions, and also lets you use Apps Script to build custom projects. In our case, it gives the ability to build timelines, calculate various project metrics, and assess the results, add links to epics and tasks, and more. 
• Rich visualization capabilities - be it a simple color-coding or a more complex graph, Google Sheets can do both. Since roadmaps are expected to be easy to read and self-explanatory, I am not going to apply enhanced visualization tricks to make our roadmap look impressive. On the other hand, I will make it clear and simple, with little color coding to emphasize the core values and add the timeline itself, and that’s it. 

• Detailed version history is here to enable effective version control, providing information about who made the changes, and when and what kind of amendments they were. One can also use comments to discuss the details of the roadmap with stakeholders, or add extra data to communicate the reasons why these adjustments were made. 

A step-by-step guide to creating a product roadmap in Google Sheets

Earlier in this article, I mentioned how important it is to have the data source linked directly to the tool that you are using for a roadmap, as well as have this information in perfect sync between the two systems. Ideally, have data automatically updated in real-time, or at least frequently enough to avoid the necessity of importing data manually. 

Coupler.io allows you to set up a schedule for automatic data reimport, ensuring that the information displayed in your roadmap is always up to date. That’s why I suggest you use this Google Sheet add-on to fetch data from your project management app.

Step 1

To start, upload your issues data from Jira Server or Jira Cloud to Google Sheets. Note, if you need to pull project-level data from Jira Cloud, you can use this guide.

Besides, one doesn’t need to be a developer to use Coupler.io. It will be enough to apply some JQL knowledge to filter out the data you need to pull from Jira. As shown below, I imported Epics and Tasks from a certain project.

Step 2

Once your data is linked, you may use this Google Sheets product roadmap template to start working on your roadmap.

This template includes the list of epics and tasks related to them, an issue key, description, status, assignee, link to the issue, and other useful information located on the left side of the document. All this information is uploaded directly from Jira to the raw data tab and gets automatically updated on an hourly basis.

I have used the Filter function to pull information from the raw data tab.

=IF(ISBLANK(B4:B),,FILTER('raw data'!AQ2:AQ,'raw data'!A2:A=B4:B))

To create a link, I have just merged the URL to my Atlassian environment and the Issue Key using this formula:

=REPLACE("https://coupler-demo.atlassian.net/browse/",43,6,B4)

The right side contains a one - year timeline with the current period marked in bright green. And I used the following formula to achieve this result:

=ARRAYFORMULA(TEXT(K1:W2, "MMMM yy") = TEXT(TODAY(), "MMMM yy"))

The cells colored in light green were added manually. I’ve typed “1” in every cell that I wanted to plan our activities using the same light green color and applied conditional formatting to this range.

The Duration column is calculable, showing the number of months required to complete a certain Epic or Task.

One can also apply Filter to the heading row to sort or filter values in the table.

That’s it! Not rocket science! :)

Conclusion

You are more than welcome to leave your feedback as to whether this approach would fit your organization or not. If not - please explain why, so my teammates and I can come up with a better solution and help your team by reviewing our approach, as well as sharing a new version of it in the comments below.