Joining data using Metabase

Learn how to join data for powerful visualisations in Metabase

Joshua

4 minute read

Joining data using Metabase

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 sesssions.

The users table has data for each distinct user of our web app:

user_idcountry
0user95912RU
1user82483AU
2user22286US
3user70233RU
4user88353RU

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

The sessions has information about each session the users have had in the app over a period of time.

iduser_idstart_date_timeend_date_time
0d0c08af8-fe15-11e9-b6d6-a14dd2b3fa3auser990092019-05-29 18:36:122019-06-19 11:15:45
1d0c08af9-fe15-11e9-b6d6-a14dd2b3fa3auser560552019-02-16 19:50:152019-06-19 11:12:39
2d0c08afa-fe15-11e9-b6d6-a14dd2b3fa3auser428682019-06-08 17:19:242019-06-19 11:12:08
3d0c08afb-fe15-11e9-b6d6-a14dd2b3fa3auser587112019-07-23 13:22:042019-06-19 11:07:44
4d0c08afc-fe15-11e9-b6d6-a14dd2b3fa3auser420242019-10-07 19:32:512019-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.

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.



comments powered by Disqus