If you’d prefer following this tutorial in video format, here is it:

What is Metabase and who is this article for

Metabase is a popular Business Intelligence tool. Particularly, many startups use it, the main advantages of Metabase are: 

• You can use it without knowing code or SQL, most business users can learn how to use it
• It’s open source, if you’re looking for a cheap option, you can install Metabase on your own servers and it will be free (minus of course the time spent installing it and maintaining it)
• You can easily share dashboard that you build over email or Slack at a regular interval

If you’re in one of the following categories, this article was made for you: 

• You’re a startup founder, or exec shopping for a new Business Intelligence tool. You’re trying out different solutions and want to understand how each works
• You’re working at a company that uses Metabase and you want to learn how to make the most of it

In this article, we’ll go through the initial setup and how to use Metabase to set up automated interactive dashboards and answer key business questions. Here are the various sections we’ll go through: 

• Connecting your database or data warehouse link
• How is Metabase structured: Questions, Dashboards, Collections and Datasets link
• How to add a question in Metabase link
• How to use custom columns in Metabase link
• How to join multiple tables in Metabase link
• How to Segment your data in Metabase link
• How to use SQL in Metabase link
• How to add build interactive dashboards in Metabase: Dashboard filters link
• Bonus tip: how to make your charts and dashboards clickable link
• How to send your dashboards to your colleagues on Slack and email: Metabase pulses link

We will not go through how to install Metabase on your own servers, we’ll use the hosted version. 

Let’s get into it! 🚀 

What we’ll be building

We’ll be using the database that Metabase connects by default to every new account. So if you want to follow along, you can create a Metabase account (you have a 14 days free trial, but you’ll have to put your credit card in) and do the steps as we do them. 

This database has 4 tables: 

• “Orders”: with columns such as “User ID”, “Product ID”, “Subtotal”, “Tax”, “Created At”, etc
• “People” (it could have been called Customers): with columns such as “Address”, “Email”, “Name”, “City”, etc
• “Products”: “Title”, “Category”, “Vendor”, “Price” etc
• “Reviews”: “:Product ID”, “Reviewer”, “Rating”, etc

If you’re not too familiar with how a relational database works, check out this very short (1min reading) article that goes through the basics.

We’ll see how to build the following dashboard: 

• Net revenue per month
• Number of orders per product category per month
• Number of orders per price segment

You can also see that this dashboard is interactive, at the top left, you can choose the category and all the charts and tables will update accordingly. 

Connecting your database or data warehouse

If you want to connect your own database to Metabase, it’s pretty simple, you’ll need the following credentials: 

• Host
• Port
• Database name
• Username
• Password

You’ll want to whitelist Metabase IPs so that they can access your database. Weirdly enough, Metabase doesn’t tell you that when you sign up, it took me time and some googling to find the IPs to whitelist. In order to save you time, here they are: 

• 18.207.81.126
• 3.211.20.157
• 50.17.234.169

‍

How is Metabase structured: Questions, Dashboards, and Collections

To me, understanding the various concepts Metabase uses was the most counter intuitive thing, so here’s a rundown of the various “objects” that you’ll find in Metabase. 

• “Questions” are an important concept in Metabase. As the name suggests, it’s a way to answer key business questions. The output is usually a table with data or a chart / graph.
• “Dashboards” is another key concept in Metabase. They’re a set of questions organized on a blank canvas, it looks like this:

• “Collections” are basically folders in which you can organize both your Questions and Dashboards

To see all your collections, questions and dashboards, head to the URL https://{your_workspace_name}.metabaseapp.com/collection/root (for us for example, it’s https://actiondesk.metabaseapp.com/collection/root). You’ll see something like this: 

Honestly, this is not super intuitive, hence the schema which I hope will help you understand how this works faster than I did.

‍

‍

How to add a question in Metabase

All right, now, let’s get to the meat of it. How do we answer a business question using Metabase? Let’s consider the following question: 

• How much revenue did our company make per month since it started?

From wherever you are in Metabase, you’ll always have the button “Ask a question”, let’s click on it and choose “Simple question”

It will ask us to pick our data, since we want to calculate revenue, we’ll need the “orders” table from our sample dataset. You’ll get this: 

Then click on Summarize, it will open a right side menu: 

Let’s: 

• Summarize by Sum of Total, which will give us the revenue
• And Group by “Created At”. you’ll see that you can decide how the “Created At” field (which is a date field) can be grouped (month, week, year, etc). By default, it’s month so it’s perfect for us since this is what we want.

Once you’ve configured these two things, Metabase will automatically choose a visualization that they deem the most relevant.

In this case, I’d like to have an area chart instead of a simple line chart: 

• Click on Visualization (bottom left)
• Choose Area

I would also like to have labels, numbers on each data points: 

• Click on “Display”
• Toggle “Show values on data points”
• Also (because we can), let’s turn off “Show dots on lines”

You’ll have this: 

Awesome, let’s save this

• Click on Save
• Give it a name
• Save it under one collection

Once you saved it, you’ll be prompted to add it to a dashboard

• Let’s click on Yes
• And let’s create a new dashboard

It’ll give you something like this: 

Don’t forget to save this! In Metabase, you have to save often. None of what you do is auto saved which I find pretty frustrating.

‍

How to use custom columns in Metabase

Okay, that’s pretty cool. Good job so far. 

Now, I’d actually like to make a small modification to my question. We calculated the total revenue, but I’d actually like to calculate the revenue net of discounts. 

Let’s see how to do this. 

From your dashboard, click on the question title (in my case “Revenue per month”) which will bring you back to the question. 

Click on the table icon to get back to the table view: 

You’ll see this: 

So here, this is something I quite dislike about Metabase, once you’ve built a “visualization” on top of a raw table, it’s not that easy to go back and see the raw table you started with.

You can click on the orders (top left on previous screenshot) but if you start doing stuff on it, you’ll lose the visualization. 

Anyway, in this case, let’s do this, let’s click on “Orders”: 

We’re going to do something that’s not going to work, but I think it’s worth knowing. If we want the Net revenue, then it looks like we could add a column to this table that would be “Total” - “Discount($)”. 

You have a feature in Metabase to do exactly that: 

• Click on the “Show editor” button:

You’ll get this: 

Click on “Custom column”

We’ll create a column called “Net total” which will be equal to “Total” - “Discount($)”

Then click on “Visualize”. You’ll get this:

This is what I meant earlier when I said it wouldn’t work. For some reason, when the discount is null, the Net total is null too. 

I wish I could use an IF formula in the “custom column” which would let me work around this. 

I still wanted to show you the “custom column” feature as it can be pretty convenient to transform your raw data and add custom business logic. 

I’ll plug our product Actiondesk quickly here. Actiondesk has a similar feature called “Calculated column” that supports all usual spreadsheet formulas, and let you easily concatenate columns, calculate segments, transform date columns, etc

Anyway, back to our problem of calculating the net revenue. Let’s do it another way: 

Let’s again summarize the data: 

• This time, let’s add two metrics in the “Summarize by” section

• “Sum of Total”
• “Sum of Discount”

• Let’s still group by “Created At” / Month

Click on the table icon: 

What we’re going to do is adding a “Custom column” to this aggregated table: 

• Click on “Show Editor”
• And then on “Custom columns”
• Create a “Net Revenue” column equal to “Sum of Total” - “Sum of Discount”

Then: 

• Click on “Visualize”
• Click on “Visualization”
• Choose “Area”

Since we want only the Net Revenue, remove the “Sum of Total” and “Sum of discount” from the left menu

I’ll add data labels again and remove the dots as we did previously, save the question and add it to the dashboard, I can now see my Revenue and my Net Revenue side by side

‍

‍

How to join multiple tables in Metabase

In the previous question, we only needed data from 1 table which was pretty convenient. In most cases, you’ll need to combine or “join” - to use the technical term - data from multiple tables. 

For example, it’d be great to see a breakdown of my revenue by product category for each month. 

To do that, I’ll have to use data from the “Orders” table and from the “Products” table (which contains the information about the category). 

Let’s see how to do that: 

Click on “Ask a question”, and this time choose “Custom question”. 

That will make you start from the editor: 

• Choose Sample Dataset / Orders
• Click on Join

Choose Products

Once you chose products, you’ll see that Metabase will automatically fill the part after the “where”

This is because in this sample database, the column “Product ID” in the table “Orders” is a foreign key associated to the column “ID” in the table “Products”, which is a primary key.

If you have no clue about what I just said, basically, it’s written somewhere in the database that these two fields are associated, and that’s how Metabase was able to automatically fill this part.

If you want to learn what that means, check out this short article about relational databases I’ve already mentioned.

In some cases, you won’t have a foreign key and a primary key and you’ll have to fill yourself this part, basically like in a vlookup in excel or google sheets.

Before moving forward, let’s unselect some columns from the table Products because, in this case, I only want the Category columns: 

Now that you’re pretty much Metabase masters, the rest if going to be very straightforward: 

• Click on Visualize

Then, click on Summarize: 

• Since we want the number of orders this time, a simple count is enough (every record is an order, so the count will give me the count of records hence the count of orders)
• We want to group by two columns: “Created At” like earlier but also by “Category”

I initially thought you couldn’t group by two columns in Metabse because if you select a second one, it will unselect the first one. 

After pulling my hair for 30min, I realized that to add a second (or third, etc) one you need to click on the +

(If you’d like to compliment me on my advanced meme skills, please tweet at me)

You’ll have this (in the table view)

From here, there are two things I’d like to show you: 

First, I’d like to show you how to have something like a pivot table in Metabase in Excel / Google Sheets: 

• Click on Visualization
• Choose Pivot table
• It’ll automatically give you this

I’d actually like it to be the other way around: Category in rows and months in columns. 

• I just have to drag “Category” to the “Rows” section and “Created At” to the “Columns” section

Instead of the absolute numbers, I’d like to see for each month the percentage of orders for each category. As far as I know, this is not possible in Metabase, if anybody knows how to do this, let me know! 

If this is the kind of thing you’d need to do, check out Actiondesk, very easy to do with Actiondesk.

That’s one way of visualizing the number of orders per month per category. I’ll save this to our dashboard. 

Another way would be through a stacked bar chart: 

Let’s start again from the step right before we started doing the pivot table. 

• Instead of choosing pivot table as a visualization, let’s choose “Bar” this time
• Click on “Display” and select “Stack” (check out also the “Stack - 100%” option which is super interesting)
• Let’s save to our dashboard

We now have this, it’s starting to be really cool! 

‍

‍

How to Segment your data in Metabase

One use case I’ve seen often is something like a breakdown of orders per price segment: 

• How many orders were for product worth less than $10
• What about between $10 and $50?
• Etc

Let’s see how to do that in Metabase. 

We’ll start by creating a custom question and joining “Orders” and “Products” like we did before, but this time, we’ll get the “Price” column from the “Products” table

You should have this:

Let’s click on Summarize and group by Price

As you can see, Metabase will automatically segment your price column. You can change how it does that by clicking on the “Auto binned” button. 

Unfortunately, it doesn’t let you customize your segment. If they had the “IF” function in custom columns, we could do it that way, but it seems the only way to do it is actually write SQL, which is a shame.

Again, I’ll plug Actiondesk here. That’d take 5 seconds to do in Actiondesk thanks to an simple IF function.

Anyway, let’s choose the option “10 bins” and instead of a chart, I’d love to see a table, so in visualization, let’s choose “Table”. 

Then, let’s save it to our dashboard. 

‍

How to use SQL in Metabase

For those of you who know SQL, you can also directly write SQL queries in Metabase. While you can do a lot of things without having to write SQL in Metabase, it can definitely come in handy for some complex use cases. 

I won’t spend much time on this as it is pretty straightforward. When creating a question, you can choose the option “Native Query” and then write your query. 

‍

How to add build interactive dashboards in Metabase: Dashboard filters

So right now, our dashboard looks like this: 

It’d be awesome to be able to send this to colleagues and let them change some parameters to change the charts and tables. For example, they may want to look at these same charts and tables for a specific time period or for a specific category.

Let’s build this: 

• Click on Edit dashboard
• Click on Add a filter
• We’ll start with the filter on Category, select “Other categories”

You’ll have a screen looking like this: 

I was initially very confused because I expected to link the filter to a column on the right side menu and I was not looking at what is on the left. 

As you can see, you’ll select the column linked to the filter for each chart or table. For example, for the first chart, I’ll link the filter to the column “Category”:

On some charts and tables, you’ll see that there’s “No valid field”. This is the case in our second chart. It’s because for this chart, we had not included the “Category” column in the raw data. To fix it, we can edit the question and add that column.

I’ll select the column “Category” for every chart and table and then save the dashboard. Now, you can easily select one or several specific categories which will update your dashboard: 

‍

‍

With the same method, we could easily also add a filter on time periods.

‍

‍

Bonus tip: how to make your charts and dashboards clickable

On our dashboard “Number of orders per month per category”, it’d be pretty cool to be able to click on 1 specific category and have all the other charts and tables filtered on that specific category. 

Well that’s possible with Metabase. Let’s see how to make this happen:

Click on “Edit dashboard”

On each chart / table, you’ll have a button called “Click Behavior”, click on it:

You’ll have this screen:

• Choose “Update a dashboard filter”
• Choose the “Category” filter
• Save your dashboard

You can now click on 1 category and the filter will update accordingly: 

‍

‍

‍

How to send your dashboards to your colleagues on Slack and email: Metabase pulses

I won’t go into details about how to make this work because it’s very simple, but this feature is worth nothing as it seems to be one of the main reasons people love Metabase. 

Once you set up a dashboard, you can get it sent to you and / or your colleagues with a slack message or an email. 

To do this: 

• When on your dashboard, click on “Sharing” / “Dashboard subscriptions”
• You can then choose email or Slack
• Rest of the setup is straightforward. 

‍

‍

Conclusion

I hope this was helpful. Metabase is a good tool that has the following advantages: 

• Reasonably priced, even free if you install it on your own servers
• Reasonably easy for somebody non technical to build dashboards
• Easy to share dashboards regularly over email or Slack

From my point of view, this is where Metabase is not great: 

• It still requires a bit of a learning curve to get up to speed on it (hence the need for articles like this one). As a result, most people in a company or team won’t use it regularly. 
• It’s not easy to start from a chart or table and drill down to see the raw data behind. 

• Ex: if you see your revenue is decreasing, it will require some effort to understand why and look at the raw data behind the chart

• It’s not very flexible, and sometimes you just want to get the data out in a spreadsheet.

If you’re currently shopping for a BI tool to make the most of your data, you should check out our product Actiondesk. 

Actiondesk is the easiest way for startup teams to make better decisions with their data. It works like a spreadsheet, so everybody can easily get started. 

If you’re in operations, marketing, sales or customer support and have a question about your business, instead of asking your engineering or data team, just answer the question yourself with actiondesk and a few spreadsheet formulas.

With Actiondesk, you can answer in a few minutes questions like

• How much revenue did we have yesterday? 
• What’s our sales breakdown by region or supplier?
• How many deals entered a specific deal stage last week?
• How many customer requests take more than 48 to respond to?

You can create your account for free and try it for yourself here.

If you have any questions or comments on this article, let me know on twitter.