Hacker BI: Airtable + Metabase

You manage what you measure...right?

But at a startup, just measuring your business can be hard. Setting up analytics, cleaning up your data, structuring it properly.

Luckily, Airtable can make it really easy to pull together your data from all sorts of sources. Then Metabase can help you ask the hard questions of your data so you can become data driven.

A tool I helped build, Sequin makes it easy for you to connect Airtable to Metabase so you can standup an analytics stack in no time. In this tutorial, we'll walk through it step by step.

What is Metabase

Airtable needs no introduction, but you may be new to Metabase.

Metabase is a powerful platform for asking questions of data. Sequin allows you to connect all your Airtable data to Metabase.

While Metabase doesn’t have native support for Airtable, it does come with first-class support for Postgres. So we’re going to use Sequin to turn your Airtable base into a Postgres database that plugs right into Metabase.

Then, in the background, Sequin will do all the hard work to keep the data current so your metrics are always up to date.

Airtable Setup

For this tutorial, we'll use Airtable's inventory tracking template:

This base contains simple data around inventory, orders, and sales that almost any business selling products or services might work with. You'll use this data to build a dynamic dashboard in Metabase that shows how sales and gross profits are trending:

First, add the Airtable inventory tracking template to your Airtable workspace:

1. Log into your Airtable workspace and then open the inventory tracking template in a new tab.
2. Click the Use Template button to add the inventory tracking template to your workspace.

Sequin Setup

Now, use Sequin to provision a Postgres database that contains all the data in the inventory tracker base:

1. Go to https://app.sequin.io/signup and create a Sequin account:

1. Connect your base to Sequin using the tutorial or check out the Quickstart guide. It's as easy as copying and pasting your API Key into Sequin, selecting the inventory tracker base you just added to your workspace, and clicking Create:

1. Sequin will immediately provision you a Postgres database and begin syncing all the data in the inventory tracker base. You'll be provided with credentials for you new database. Keep these handy as you'll use them to connect your Sequin database to Metabase:

Metabase Setup

With an Open Source license, you can choose to install Metabase for free if you wish. Or, you can pay to use the hosted version of Metabase - known as Metabase cloud.

In this tutorial, we'll us Metabase cloud (which comes with a nice 14 day trial).

Simply go to https://www.metabase.com/ and create a Metabase Cloud account by clicking Get Metabase and selecting to start a free trial:

You'll go through several steps to create an account, select your cloud url, and add payment information (you can cancel at any time in the trial).

Metabase will then spin up your cloud instance and email you in a couple minutes when everything is ready.

Then, just login to your new Metabase account.

Connect Sequin to Metabase

You'll add your Sequin Postgres database to Metabase just as you would any other Postgres database:

1. Click the gear icon in the top right corner and select Admin:

1. On the Metabase Admin page, select the Databases tab and click the blue Add database button:

'

1. On the add database page, select PostgreSQL as the database type and give your new database a name - in this case, something like "Airtable - Inventory Manager." Then, enter the Host, Port, Database Name, Username, and Password for your Sequin database (in case you closed the tab, you can find all this information by clicking the Connect button next to the resource in the Sequin console). Lastly, toggle on SSL and click the blue Save button at the bottom of the page:

1. Metabase will confirm it can connect to your Sequin Postgres database and present you with a success modal. Click the I'm good thanks link to close the modal:

1. You'll see that your new database has been added! You can now exit the admin page by clicking the gear icon in the top right corner and selecting Exit admin:

Create a new dashboard

All your Airtable data is now accessible in Metabase. Now, you'll use Metabase to ask questions of your data and build a dashboard.

Before you start querying your Airtable data and building visualizations, set up your dashboard:

1. Click the plus icon in the top right and select New dashboard:

1. In the modal that appears, give your new dashboard a name - something like "Sequin Tutorial." Then click the blue Create button:

You'll now see an empty dashboard. Let's fill it with some helpful insights.

Create your first question

Metabase calls metrics and visualizations you derive from your data questions. While this nomenclature feels a little casual compared to other BI tools, I like the mental model it creates. It encourages you to consider, "what questions do I need to answer from my data?"

Since you are working with inventory data from a small business, the first question you might want to answer is "is the store profitable?" In business language, this metric is called the gross profit of the store. It would also be helpful to inspect the gross profit at different time periods to see how it changes.

To calculate gross profit you'll need to sum up all the sales of the store and then subtract any costs. To then inspect the gross profit over different time periods, you'll want to add a date filter that only includes sales and costs from specified time periods.

With your question defined, you can now ask it in Metabase.

To do so, click Ask question in the navigation bar and then select Native query to create a new question. On the new question page, select the Airtable - Inventory Manager database that you just added:

You might be wondering why you are using the Native query builder to create your question instead of the Simple question or Custom question options. You'll often want to use native queries to build questions on your Airtable data because, as you'll see, your Airtable data will contain more complex data structures like Postgres arrays and JSON. SQL is an easy way to work with these objects and format your results. You can learn more about how to query your Airtable data in SQL by reading the Sequin Cheat Sheet and Reference.

You can now use SQL to calculate the gross profit. To get started, calculate the gross profit without the ability to filter by date:

select
    sum(sales_orders.revenue::numeric - (product_inventory.manufacturer_price * sales_orders.quantity)) as "Gross Profit"
from sales_orders
join product_inventory on sales_orders.product[1] = product_inventory.id;

There are several flourishes in this query that are worth unpacking:

1. First, you are selecting the revenue column from the sales_orders table. Because revenue is a calculated field in Airtable, it appears in your Sequin Postgres database as type text. Since you can't add and subtract text values, you are then casting this to type numeric using ::numeric.

2. Next, you are calculating the Gross Profit by taking the sum of all the revenue and subtracting the costs (i.e. product_inventory.manufacturer_price * sales_orders.quantity)

3. Finally, you are joining the sales_orders table to the product_inventory table. In Airtable, sales_orders.product is a linked record to the product_inventory table. Linked records appear in your Sequin Postgres database as arrays, because linked records can contain multiple values. So to complete the join, you use sales_orders.product[1] to extract the first (and only) value of the array to match it to the corresponding record in the product_inventory table.

When you run the query by clicking the blue play button, you'll see that the small business is indeed making money:

Since gross profit is a dollar value, adjust the settings for this question to add a dollar sign as well as two decimal places:

That looks more like it! As a last step, recall that you want to be able to inspect the gross profit over different time periods. To do this in Metabase, you'll edit your underlying SQL query to add a WHERE clause with a variable:

select
    sum(sales_orders.revenue::numeric - (product_inventory.manufacturer_price * sales_orders.quantity)) as "Gross Profit"
from sales_orders
join product_inventory on sales_orders.product[1] = product_inventory.identity
[[where {{date}}]];

Breaking this down. The {{date}} syntax creates the variable. Then, you wrap the entire WHERE clause in brackets ([[where {{date}}]]) to make this statement optional in case no date is provided (you can learn more about variable in the Metabase documentation).

Metabase will detect the {{date}} variable in the query and open the variable settings to the right. Since the gross profit question you are building will live in a dashboard - you will configure the date variable to be a Field Filter.

To do so, you'll select Field Filter as the variable type. Since your date variable is actually filtering the date column of the sales_orders table, you'll then map the variable accordingly. Last, since you want to be able to select different time periods, you'll set the widget type to Date Range:

You'll now see a date selector in the question, and if you select a new date range the value of the gross profit will update:

Keep in mind that in the Airtable template we are using, the date range is from February 17, 2017 to February 27, 2017 :)

With everything looking good, save your question by clicking Save and naming your new question something like "Gross Profit." After saving, you'll return to the Metabase home page.

Add your question to the dashboard

Finally, add the gross profit question as well as a date range filter to your dashboard:

1. First, open up your dashboard by clicking Browse all items from the home screen and then selecting the Sequin Tutorial dashboard.
2. Edit the dashboard by clicking the pencil icon in the top right.
3. Add your Gross Profit question to the dashboard by clicking the plus icon and selecting it from the modal. Resize and position as you would like.
4. Then, add a date range filter to the dashboard by clicking the Add a filter icon, selecting Time, and then selecting Date Range.
5. Finally, map the date range filter to the date variable in your gross profit question and then click the Done button.
6. Last but not least, click Save.

You've now created a new dashboard with a Gross Profit question that you can filter by date. The foundations of your dashboard are now in place.

Add a graph

Now, add some graphs to your dashboard to reveal more about the small business.

Starting with a Metabase question, it would be nice to know how many sales happen each day and on which sales platform. A stacked bar chart might tell this story well.

To build the question, click the Ask a question button and then select Native query just as you did before. Select your Airtable - Inventory Manager database.

Now write the SQL that answers this question:

select
    sales_orders.date::DATE,
    sales_orders.revenue::NUMERIC,
    sales_orders.sale_platform
from
    sales_orders
[[where {{date}}]];

This query should look more familiar to you now:

• You'll notice that in the SELECT statement you are again using CAST (i.e. the ::) to ensure that the data returned is the right type.
• You are then adding an optional WHERE clause with the {{date}} variable so that your date range picker will also affect this data as well.

When you add the SQL to your question, you'll first configure the {{date}} variable just as you did with your gross profit question:

Finally, let's present this data as a stacked bar chart. Metabase makes this pretty easy:

1. Click the Visualize button in the lower right corner and select the Bar option.
2. The settings pane will open and Metabse will detect the data types to auto format the axis. All you need to do is toggle the chart to be a stacked bar chart by clicking the Display tab and selecting Stack.

The chart looks great. Save it by naming it something like "Sales by Date and Platform." Then repeat the steps you performed with the gross profit question to add it to your Sequin Tutorial dashboard.

As a last step, you'll need to map the Date Range filter to the Date Variable in your new graph by clicking the Date Range Filter and then selecting the Date variable in the "Sales by Date and Platform" graph:

When you're done, click the Save button and you'll see the beginnings of a dashboard that quickly tells the story of this small business:

Conclusion

In this tutorial you've learned how to build a dynamic dashboard in Metabase using Sequin and Airtable. From this starting point, you can use SQL to answer almost any question hiding in your Airtable base and present it in a clean dashboard that you can easily share.