How to create a custom dashboard for your startup’s KPIs with Google Spreadsheet and a Raspberry Pi
Introduction
This article is intended for managers that want to automate stuff in a MVP kinda way without the need of a technical team. Every startup needs a way to report some data or KPIs so I will show how I built a free custom dashboard in a few steps.
I am Alex, one of the founders at Make it Group. We build startups in Technology and/or IoT. Learn more here.
Context
Tipaw, one of Make it Studio’s startups, aims to reduce the number of dogs in shelters. How? By transforming mentalities about dog adoption (it’s not a new phone or TV you let in your living room), and by promoting responsible dog breeders that really care about their puppies’ welfare.
A custom dashboard ?
“What Gets Measured Gets Improved” — cfr. interesting article by Burak Bilgin
When we started Tipaw, we tested a bunch of different stuff for a few months, trying to reach our product/market fit. We were always attracted by defining our KPIs, but honestly, we just didn’t have that maturity yet and we would just generate “metrics porn” (numbers that are “cool” but doesn’t really help us on a daily basis).
Lately, we did reach that maturity, and we hired a couple of passionate people. We spent a lot of time brainstorming with the team. Meetings would last for hours. Discussions were based on intuitions rather than on numbers.
It was time.
We needed those KPIs to help the team align itself with facts. Having numbers in front of us at all times would also help us have a better gut feeling about how the company was doing.
Let’s dig in
How to define your KPIs ?
KPI’s are metrics obviously useful for your startup. Choose them wisely and make sure, they are the numbers you can have a direct impact on. I won’t go further on that, as the topic of this article is about how to build a quick dashboard.
In our case at Tipaw, we needed a few KPIs : the number of new breeders that subscribed, the number of buyers that subscribed, the number of litters the breeders are posting, the number of matching (buyers contacting breeders through our platform), the number of potential matchings (buyers that are interested in a particular breed but haven’t contacted a breeder yet).
Where to find them ?
In our case we just created a simple API that extracts those numbers from our database. We run this API through a cronjob every hour to “grab a screenshot” of the numbers at that time so we can see our efforts over time. This API would save the results into a CSV accessible on our servers.
You can use the sample data here. It looks like regular CSV content.
Making this data available to our team
Now the beauty of Google Spreadsheet is that I don’t have to spend time building my own frontend to plot my charts. And I certainly didn’t want the developers to deviate from their sprint to build the dashboard. It might have been more beautiful and sexy with their talent. However: “Better a good today, than a perfect tomorrow”. Also, if the dashboard evolves, which it will for sure, I don’t want to be dependent on other people, I’d rather make it evolve myself as soon as I need it.
Long story short: just add this line in the first cell of your Google Spreadsheet.
=importData("https://dev.test-it.io/tools/custom-dashboard/export.php?key=46330ed2c038989f6030ac7b539d80f92f9bc18f")
Google Spreadsheet will update the data automatically as soon as your cronjob runs. Find my example here.
I can then make use of any formulas of Google Spreadsheet to interpret my raw data. In my case I needed to change the date format and use a Pivot Table to have the data aggregated by day. I also added a “Daily” tab that would calculate the daily improvements.
Here’s the final result of the custom dashboard.
We just discovered datastudio.google.com where you can build reports and dashboards starting from sets of data (Google Analytics, Google Search Console, AdWords but also Google Spreadsheet, SQL databases etc). I haven’t tested it yet, but it might be something to consider for sure to build more complete reports/dashboards 😉
Display your custom dashboard on a screen with a Raspberry Pi.
The purpose of the Raspberry Pi is to :
- Autoboot when arriving in the office
- It needs to connect automatically to the internet
- Open the browser and open a tab to the Google Spreadsheet custom dashboard.
You can find the routine to achieve this in a tutorial written by Alex Bain:
In a nutshell, here are the steps:
First of all we need to enable internet. You can use an ethernet cable or configure the WIFI on the Raspberry Pi.
With the internet connection working properly you can now do the following upgrades:
sudo apt-get update
apt-get upgrade
We can now install chromium and a package to hide the mouse.
sudo apt-get install unclutter
sudo apt-get install chromium
For the next step we have to modify a file inside of the Raspberry Pi and add the line below. It allows us to remove the mouse, the low power icon and to avoid the RPi to pass in blank screen.
sudo nano ~/.config/lxsession/LXDE-pi/autostart
@chromium-browser -noerrdialogs -kiosk -incognito YOUR_URL
@xset s noblank
@xset s off
@xset -dpms
@unclutter -idle 0.1 -root
A last modification is required
sudo nano /etc/lightdm/lightdm.conf
Once in the file you can add the line just below in the [SeatDefaults] section.
xserver-command=X -s 0 dpms
You can now restart your raspberry and enjoy your brand new dashboard display.
Next steps
Obviously, a lot of improvements are possible. The first thing I did is to secure the API. In this example it’s totally public and anyone can access it (if they know the link at least) but you probably don’t want other people other than you team to have access to this data. You might therefore use the “Script Editor” from Google Spreadsheet to add some JS to query your server in a more secure way (with a token for instance). I am developing the use of the “Script Editor” in a future article called “How to use Google App Script to automate stuff” where secure API calls are possible ;-).
What is Make-It?
We build Startups. Since 2014, we’ve successfully developed what we describe as ‘a machine’ to test, launch and/or grow startups by working with over 200+ entrepreneurs and building our own. We strongly believe in the ‘Test It’ mentality: test fast, fail cheap, and try again.
Are you ready to Make it happen?
Let’s see how we can help!
