As part of the Catalyst project, we are encouraged to share "recipes", things we've built that can be shared with others who may be looking to solve the same problem. I fully intended to do that with something I trialled for creating an automated "Idea Prioritisation Matrix", and while it wasn't a 100% success I think the process is still worth sharing and it may be something that can be refined in the future.
The Problem
The usual method for an Idea Prioritisation Matrix, is to let people put each idea on a sticky note (physically or digitally) and move them about the matrix depending on two criteria: the user value, and the feasibility (or effort). Like this:
The problem I had, was that in the session I was running, I didn't know how many ideas were going to be generated (though I assumed it would be quite a few) and thought that a number of people moving that many sticky notes around would get a bit messy and cluttered.
Also, for the project in hand (the redevelopment of our CABmoney website) I thought that the "effort by organisation" part might be difficult for people to decide upon. Without knowing what is easy or hard to develop for a website, this could have been skewed.
The Solution
Rather than attempting online sticky notes, I dropped back to a simpler method of scoring the ideas - Google Forms. Most people are familiar with Google Forms, so I set up a fairly basic one with a question for each idea and a ranking of 1-10.
Because most of the ideas were being formulated during the session, I was able to easily duplicate the questions and add the new ideas in as we went, meaning that the form was populated as soon as we needed it.
I wanted to be able to visualise this in a way that Google Forms wasn't able to (the matrix), so I then linked up Google Data Studio. After a bit of trial and error, it seemed that the data format directly from the form wasn't suitable for Data Studio, so I went back to the form responses spreadsheet to make some adjustments.
To save Google Data Studio having to do some of the "heavy lifting", I set up two new tabs in the form responses table to make it more manageable for Data Studio.
The first tab transposed the raw data, so that it was "long" rather than "wide".
In cell A1, I used the code
=transpose(Responses1!A1:AAN208)
which changed the data layout from this
to this
Then, in the other new tab, I set up cell B2 to again transpose the ideas with
=transpose(Responses1!B1:1)
Cell C2 as an average of the scores from the new "Transposed" tab. This formula would need to be manually copied down depending on how many ideas there were.
=average(Transposed!A2:2)
and an empty column for the "Ease of implementation" score that I would add manually once the results were in from the form.
The finished form (after the results were in) looked like this:
Over in Data Studio, I added this worksheet as a data source. I knew that I wanted the results to be displayed with the axes going through the center of the matrix, so as these were scores out of ten I simply added two new calculated fields that subtracted five from each score, so that the score range was now -5 to +5.
Plotting these new fields on a scatter graph would automatically create the matrix as soon as the Google Forms were filled in, this seemed much easier than getting people to move multiple sticky notes around.
For the visual effect, I added the image below (created by me, free to use) behind the graph so that the plotted points would fall into the various coloured bands. The image below is rectangular as that was the shape of the matrix, but it can be stretched to fit any other dimensions if needed.
Did it work?
Technically, yes.
Though not quite as well as I'd have liked.
One issue was that in asking everyone to score the ideas out of ten, most people thought most of the ideas were good, so there was a bottleneck of ideas up in the top half of the matrix. Nothing had scored lower than 6.4 for impact (or 1.4 on the adjusted matrix with minus 5 applied).
I was able to remedy this by changing the Y-axis so that it started at 0 instead of -5, which effectively stretched the results out from top to bottom.
Also, for the sake of clarity, I removed the data labels from the matrix as there was so much overlap of text. Within Data Studio this works well, as the points can be hovered over to view the text but if exported elsewhere would need a key (the labels could be numbered ideas with a key to show what they are).
