These past few monthes we've made a brand new iPhone application for Diwi (dating app in French only, but do take a look!), and as we did a cool start with more than 2K active users in two weeks, we needed insights about who our users are and how they use the app: basically we needed pie charts.
When asked for a statistic dashboard, I'm used to do everything myself in a custom backend, but it can actually be a lot of work for just printing several charts:
The only real value of your stats is the data, so all the extra work in order to show it is wasted. Don't get me wrong, I love doing custom dashboards from scratch, but time is money and when @maxime told me about Metabase, although I was very skeptical at first it only took me an afternoon of testing to know this was the solution for us, and after using it for some weeks I've totally fallen in love with it ❤️ (I swear this post is not sponsored and they did not give me any money).
Metabase is just a solution to display your data in various charts, but it does it really well. It can be used as a client or centralized as an hosted application on your server. Here is an example of a dashboard you can make, done in under 15 minutes (these are not real numbers, I'm using a test dataset):
You have two ways of adding "Questions" to Metabase, first using the GUI:
Or for more complex queries, writing SQL (this means you can do pratically anything given you have the SQL skills):
Questions must not necessarily appear on a dashboard, you can add more specific questions to look at later, search them, tag them (with emojis 🙌) etc.
There's a bit more things you can do, like excluding some sensitive information from the mapping and also sending reports at a specific frenquence via e-mail or slack, which can be really convenient. For example you could send a report to your slack channel every morning to inform your team what happened on your website the day before.
It's free and open source. It's also very polished for an open source project.
It's easy to try: download the mac client or use the
jar, setup it in two minutes linking your database (MySQL, Postgres, Mongo, SQL Server, AWS Redshift, Google BigQuery, Druid, H2 or SQLite), sync it and start making diagrams in literraly 10 minutes. You can even try their demo dataset if you don't have any data at hand.
You can host it on your server: plug it to your production database (it's SELECT operations only), add some users, and everyone in your team can view your dashboard or even create their own questions (no SQL knowledge is required).
Dear Metabase team/contributors, if you're reading this, thanks for the awesome works you're doing! Metabase is already mature enough to use on a production project.
Still, here is a couple of little things that bugged me out while using Metabase, they might have already been fixed in 0.18 or might be know issues, but here is some things you probably want to know if you want to see if Metabase is for you.
Customization could be improved, for example it would be great to be able to pick individual colours for each data in your charts, even if they were made in SQL. In some projects colours have meanings and I believe it's a great way to improve readability.
It's too bad that in the GUI interface for adding a question, we can only go down to one relation level (For example : User -> Product, not User -> Product -> Category -> etc.), for Diwi's dataset it means I'm forced to write almost every question in SQL, where I could do everything in the GUI if I had an unlimited depth of relation. This also mean that my coworkers cannot easily use the questions I wrote in SQL if they don't have the knowledge.
While using the hosted version of Metabase, it looks like I can't directly add a question I just made to a dashboard, I have a
Cannot read property 'display' of undefined error message and I must go to the question list, hard refresh it before being able to add it in a dashboard, like there is some local cache issue in the browser. Same odd behaviour appears when you make a change to a dashboard, you are forced to hard refresh the page to see the changes you made. This works fine in the Mac client though.
Edit: this issue was not at all related to metabase but was because of too much caching by the server!
But nonetheless, the team is doing a really great job, and are adding new features often. For example Metabase 0.18 adds a way to add global filters to a dashboard (like a date picker). That feature is reeeeally gold and I can't wait to try it with our data.
Dashboard filters example, from Metabase blog
To end this post with a bit of LOL, here is an absolutely not useful query that I made in Metabase.
Here is the deal: remember that Metabase does not have any scripting language, so all you got is your good old SQL. What if you want a graph based based on a date, for example you want to display every new registrations per hour you had in the last 24 hours, you could write something like this:
SELECT DATE_FORMAT(registration_date, '%Y-%m-%d %H') AS "Time", COUNT(*) AS "New subscriptions" FROM user WHERE DATEDIFF(NOW(), registration_date) <= 1 GROUP BY DATE_FORMAT(registration_date, '%Y-%m-%d %H') ORDER BY registration_date ASC
But what if you don't have any new registrations bewteen 3 and 4 am ? No lines returned means that your graph will not show anything for 3 am, where you want a flat "0 registrations" point in your graph there. We want to add "0" when there is no data during a specific hour.
The key is to have a way to
SELECT numbers somewhere, then use a date function to substract time from the current date to create our hour range. The thing is there is no range function in SQL, so we have to rely on dirty tricks to generate numbers.
One way would be to add a
numbers table with sequential numbers in each row, or to use an incremental index value on one of our existing table (but there should not be any gap in the sequence, and the required amount of rows).
One other trick I found in a stackoverflow thread is this really cool way of generating a sequence of numbers using powers of two and
SELECT TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue AS number FROM (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
This will generate a sequence of numbers from 0 to 31 (enough for our 24h), we only have to integrate it into our query (I also added two other metrics along):
SELECT @date:= DATE_FORMAT( DATE_SUB( NOW(), INTERVAL ( TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue ) HOUR ), '%Y-%m-%d %H' ) as date, ( SELECT COUNT(id) FROM user WHERE DATE_FORMAT(registration_date, '%Y-%m-%d %H') = @date ) AS "New users", ( SELECT COUNT(id) FROM offer WHERE DATE_FORMAT(creation_date, '%Y-%m-%d %H') = @date ) AS "New rendez-vous", ( SELECT COUNT(id) FROM diwi WHERE DATE_FORMAT(date, '%Y-%m-%d %H') = @date ) AS "New diwi" FROM (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8 CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16 GROUP BY date ORDER BY date ASC
Voilà! Hope you enjoyed this useless, unreadable, fun SQL query. It looks like this in Metabase:
Thanks a lot for your attention, do leave me a message if you tried Metabase after reading this post. I hope I'll find the time to write more frequently here!
If you scrolled this far, that means you enjoyed this post.