Clubhouse have just released a new Google Sheets integration. If you’re not familiar with Clubhouse it’s a project management tool designed for software teams. We’ve been using it at Geckoboard since 2016 and have never looked back - you can read more about why we switched to Clubhouse from Trello here.
The new integration automatically exports all your Clubhouse tickets and accompanying metadata to a spreadsheet. Whereas you could get at the same data via their API before, this makes the whole process significantly easier and opens up a load of no-code possibilities for analysing your project management data.
The reason we’re excited about the feature is that combined with Geckoboard you can now easily show project KPIs and information from Clubhouse, like recent tickets completed, alongside your product analytics on a team dashboard. With our Slack feature you can set up an automatic daily screenshot of your dashboard - helping your team to monitor progress both in terms of ticket completion and impact. Right now you’re probably working remotely, but when you’re back in the office you can display the same dashboard on a TV by your desks.
Below we’ll go through the steps required to add Clubhouse data to your Geckoboard dashboard, but even if you don’t use Clubhouse or Geckoboard, this is also a great worked example of how to use Google Sheets’ IMPORTRANGE, FILTER and QUERY functions.
How to build a real-time Clubhouse dashboard for your project
1. Set up the Clubhouse Google Sheets integration
Step one is to set up the Clubhouse Google Sheets integration. Clubhouse have instructions for this here. Once set up, it automatically dumps all your tickets and all their metadata into a new Google Sheet - updating live.
2. Use IMPORTRANGE to create a second spreadsheet to process your Clubhouse data
The sheet created by Clubhouse should be thought of as read-only, so the second step is to export the data to a new sheet that you can shape to your needs.
In a new spreadsheet, in the topmost cell (A1), use the IMPORTRANGE function to extract the data you need from the original spreadsheet.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXxxXXXXxxxXXXxxxXxXxxxxxXxbx_Xx/", "Stories!A1:E")
The first part of the import range is just the URL of the source spreadsheet. You don’t need the "/edit.." part you get when you copy the URL from your browser address bar. (After you enter the function you’ll see a small popup asking you to "allow access" to the spreadsheet which you’ll need to click).
The second part of the import range defines the cells you’d like to import, and must include the name of the source worksheet, in our case Stories. Here you can select just a specific cell or multiple columns. You’ll hit an error if you try to import too many columns in one go though.
To keep things manageable, we recommend you just import the columns you need.
In our example we import the first 5 columns with Stories!A1:E.
We then add specific extra columns by using the same import range function again in different cells and changing the range e.g. Stories!Z1:Z. In our example cells F1, G1, H1, I1 and J1 all have different ranges to pull in the Project, Epic, Is_Archived and Completed_at, Started_at and state columns.
3. Using the FILTER function to produce a list of recently completed cards for a specific project.
Now it’s time to start processing our raw data. The simplest way to do this using the FILTER function.
FILTER expects a range of cells to filter, and then a set of criteria to filter them by. It returns rows that match the filter. In our example we’re going to use it to return a list of completed tickets for a certain project, “Expansion”.
In cell A2 we write:
filter('Raw data'!A:L,'Raw data'!F:F = "Expansion",'Raw data'!K:K = "Done")
The first part 'Raw data'!A:L defines the cells to filter. The rest of the formula is a list of the filters to apply. Raw data'!F:F = "Expansion" restricts the tickets in the Expansion project.
Raw data'!K:K = "Done" ensures the list only includes cards that are complete.
We’ve now got our list, but it needs sorting. To do this we wrap our filter function in a SORT function.
=sort(filter('Raw data'!A:L,'Raw data'!F:F = "Expansion",'Raw data'!K:K = "Done"),9,FALSE)
Here the 9 tells it to sort by the 9th column (completed_at) and FALSE tells it to sort in descending order so the most recent cards appear on top.
In Geckoboard, we can use this to add the table of recently completed cards.
We use a slightly adjusted version of this filter to also add a table of cards that are currently in progress.
There’s no real limit to what you can do here. You may want to show cards in a particular state, epic, or milestone.
You can also do all sorts of additional processing if you want. For instance you might want to include how long cards take to complete. To do this you’d include "Started_at" in your original IMPORTRANGE, then add a new column at this step and deduct "Stared_at" from "Completed_at" then multiply the value by 24 to convert the answer to hours.
One optimization we did for our board is change the card owner from the email addresses returned by Clubhouse to names.
To do this, in a new column, we used the following formula to remove the email domain and then used proper casing to make sure everyone’s name started with a capital letter.
=PROPER(SUBSTITUTE(E22,"@example.com",""))
4. Combining COUNT with FILTER to show key project stats.
The Filter function can be combined with COUNT, SUM, AVERAGE and other functions to produce all sorts of useful KPIs.
For example, if we wanted to show how many Tickets were completed today in a new cell on this worksheet we could write;
=count(filter(I:I,I:I>=today()))
Here I:I is the "Completed_at" column of the list of completed tickets we just produced. This list can then be filtered to just the rows that have a complete time of today using I:I>=today().
Wrapping all this in a COUNT returns the number of tickets that match this criteria.
To get tickets completed in the last 7 days we just modify the filter criteria to today()-6)
(If you want you can also skip the intermediary step of producing a table of filtered results and wrap the original function we wrote in step 3 in a count straight away.)
Again there’s no real limit to what you can do here with various filters. In our example dashboard we add a few project stats as number widgets in the top right corner.
5. Using the QUERY function to return grouped data
With enough patience you could probably do all the data manipulation you required with a mixture of FILTER, COUNTs and LOOKUPs and manual calculations.
However, it’s not an efficient route for showing data like tickets completed by day, burn down charts, tickets outstanding by epic or even who’s completed the most tickets.
Pivot tables can be a good approach when you need to group data like this - but using the QUERY function is more robust. It allows you to run simple SQL queries within your spreadsheet. If you’re not familiar with SQL it’s a bit of a learning curve, but if you use Google Sheets a lot it’s probably worth the investment. Or you can just copy and paste from this article!
Now our goal is to produce a chart of cards completed by day. Here’s the formula we use:=query('Raw data'!A:L,"select I, count(A) group by I order by I DESC")
'Raw data'!A:L selects the data to query, in our case the raw data we imported in step 2.
We know we want the first column of our results to be the date solved so we write select I. We want the second column to be the count of the different tickets solved on that day, so we add count(A). The comma tells the query it’s a second column. We use the ticket id for the count because it’s unique for each ticket. group by I buckets the results by column I, which is the completed_at date.
order by I DESC just tells the query to sort the data by column I in descending order.
We can enhance the query further by adding a where clause to restrict it to just returning data for this year and project by adding where I > date '2020-01-01' and F = 'Expansion' .
You’ll notice that the second column produced by our query is given the name "count id". We can give this a more appropriate name by adding LABEL count (A) 'total’ to our query.
Leaving us with the final query of:
=query('Raw data'!A:L,"select I, count(A) where I > date '2020-01-01' and F = 'Expansion' group by I order by I DESC LABEL count (A) 'total'")
(You’ll notice here instead of Raw data'!A:J we’ve written CH. That’s because we’ve used the Named Range feature in the Data tab to alias the selection to make it quicker to type the formula. This is entirely optional.)
You can use an identical approach to return a leaderboard of who’s completed the most tickets. This time grouping by owner instead of date:
=query(CH,"select L , count(A) where I > date '2020-11-01' and F = 'Expansion' group by L order by count (A) DESC LABEL count (A) 'total'")
6. Building a CH dashboard in Geckoboard
Now the easy part - hooking up Geckoboard! Using our Google Sheets Data source you just need to select the spreadsheet you’ve just made.
In our example we add:
- Table visualizations to show cards currently and those that have recently been completed.
- A leaderboard to show who’s completed the most cards (only as a bit of fun!)
- A column chart to show the number of cards completed by day
- Number visualizations to show cards in progress, left in the epic etc.
On our dev team dashboards we often include Mixpanel data too, so we can track the impact of what we’ve shipped. Here we’ve added no. of users completing a key action related to the project, but we also often track feature adoption, funnel completion rates or even A/B test results.
To set up your dashboard to automatically post to Slack you just need to hit the share button in Geckoboard.
Your team can also access their dashboard at any time in the browser either via a sharing link or logging in. In normal times we’d also recommend putting it up on a TV in your office!
There’s no real limit to what you can do using the techniques we’ve outlined above. As a Product Manager you might want a visible list of Epics left in a milestone and how many cards each have left or to list cards that match a specific label.
Creating your own Clubhouse project dashboard? We’d love to see what you come up with, and if there’s anything you need assistance with don’t hesitate to contact our support team or tweet us.
Not a Geckoboard user yet? Sign up for a Free Trial.
