A powerful new feature in Metabase version 0.33 allows you to join data together from your datasources to answer questions and create visualisations. This comes along with a complete refresh of the query builder within Metabase, which more than ever provides an easy way of exploring your data without having to rely on SQL. In this post, I’ll show you a quick example of how you can use these new features to join, aggregate and visualise some product usage data.
Our sample data
For this exercise, we’re working with some sample usage data collected from a web app. We’ve got two tables, one for
users and one for
users table has data for each distinct user of our web app:
In this table, we have:
- user_id: a unique identifier for each user in the app
- country: ISO 3166 alpha-2 country code for the country the user lives in
sessions has information about each session the users have had in the app over a period of time.
|0||d0c08af8-fe15-11e9-b6d6-a14dd2b3fa3a||user99009||2019-05-29 18:36:12||2019-06-19 11:15:45|
|1||d0c08af9-fe15-11e9-b6d6-a14dd2b3fa3a||user56055||2019-02-16 19:50:15||2019-06-19 11:12:39|
|2||d0c08afa-fe15-11e9-b6d6-a14dd2b3fa3a||user42868||2019-06-08 17:19:24||2019-06-19 11:12:08|
|3||d0c08afb-fe15-11e9-b6d6-a14dd2b3fa3a||user58711||2019-07-23 13:22:04||2019-06-19 11:07:44|
|4||d0c08afc-fe15-11e9-b6d6-a14dd2b3fa3a||user42024||2019-10-07 19:32:51||2019-06-19 11:04:18|
In this table we have:
- id: a unique identifier for each session that has occured in the app
- user_id: the user_id for the user that logged in
- start_date_time: the date/time the session started (YYYY-MM-DD HH:MM:SS)
- end_date_time: the date/time the session ended (YYYY-MM-DD HH:MM:SS)
You can see there is data specific to each table - for example, the country of the user is only recorded agains the
users table. But, importantly there is some common data, the
user_id which we can use to join the data.
Joining data in Metabase
Let’s look at answering a quick question - where in the world are we getting the most usage from?
To do this, we start by hitting “Ask a question” at the top of the screen. For joining data, use the “Custom question” feature as it gives you a little more control over the query without having to go full SQL!
We start from the
users table, and then choose “Join data” to join onto the sessions.
In the “Where” section, we’re describing how the two tables relate to each other. So we want to specify that we want to join where the user_id column on the users table matches the user_id in the sessions table.
Now, for what we want to show, we want to aggregate and look at the total number of sessions by country. So we want to summarise the number of distinct values of the session id which is unique for each session. And then, we want to group by the users country.
Once we’re done with that the question should look like this:
And now we’re good to visualise! Metabase has builtin support for maps and geographic visualisations, so out of the box you’re presented with a world map, highlighted based on the number of sessions in each country:
As with the other visualisations in Metabase, you can tweak the colour scheme and a number of other options.
And, if you like you can always just see the raw data:
Hopefully that’s a helpful primer on how to join data and quickly create cool visualisations using Metabase. If you’ve got any questions then please leave a comment!
As you can see, the query builder made that whole process pretty straighforward. If we were replicating that query with SQL then we would have had something along these lines:
select u.country, count(distinct s.id) as total_sessions from users u join sessions s on u.user_id = s.user_id group by u.country
But, the query builder meant we achieved that in just a few clicks.
Check out other posts about Metabase
Here’s a few other things I’ve written about Metabase:
And if you need more help using Metabase, the Discussion board is very active and ful of helpful people.