Use Airtable and Google Data Studio as your 2021 business OS

Your business can get lost in an Airtable base.

Do you know how sales are trending? How many customer interactions took place today? How big the support queue is?

The Airtable UI does not make it easy to go beyond surface-level analytics. You are especially constrained if you want to combine performance metrics in Airtable with other data sets.

While Airtable is limited in its reporting capabilities, there are plenty of tools purpose built to help you build and share insightful reports and dashboards. Google Data Studio is one of the better options, because it’s easy to learn, allows you to blend different data sources together, and comes loaded with convenient tools to make your reports interactive.

Until recently, you really only had two reliable options to get your data out of Airtable and into a tool like Google Data Studio: integrate with the Airtable API and work through pagination, rate limits, and filterBy. Manually export and import CSVs by hand, every time. While these work, neither are easy or efficient.

That’s why we’re building Sequin so you can unleash your Airtable data.

In this post, we’ll use Sequin to connect all your Airtable data to Google Data Studio in a matter of minutes.

Connect Airtable to Google Data Studio

You might be asking, "how is this going to work exactly?"

While Google Data Studio 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 Google Data Studio.

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

First, use Sequin to provision a Postgres database with all your Airtable data:

Go to https://app.sequin.io/signup and create an account.

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, base ID, and the names of your tables.

In a couple seconds, you’ll be provisioned a Postgres database that Sequin will begin filling it with all your Airtable data.

Step 2: Connect to Google Data Studio

Now, we’re going to use the credentials you just received from Sequin to connect your Postgres database to Google Data Studio:

Go to https://datastudio.google.com/ and sign in or create a free account.

Once you are logged in, click the Create button and select Data source from the drop down. A page of connection options will appear. Click the PostgreSQL option.

You’ll be asked to provide the credentials for the Postgres database you want to connect to Google Data Studio. Enter the credentials for your Sequin Postgres database including the Host, Port, Databse, User, and Password into GDS. You can leave the Enable SSL checkbox empty. Then click Authenticate.

Now, you will see a list of all the tables from your Airtable base. Select the table you want to import and click the blue Connect button.

Google Data Studio will load the data from Sequin and show you the columns in the table as well as the corresponding data type (text, boolean, etc). Name your table in the top right (I recommend the naming convention Airtable - Base Name - Table Name) and if you’d like, change the data freshness to 1 hour so your reports show the freshest possible data. Google Data Studio will auto save as you make changes. Once everything looks good, click the Google Data Studio logo in the top right corner to return to the home page.

Repeat the previous steps to connect each table in your Airtable base to Google Data Studio. You'll only need to do this once for each table.

Build your report

With all your Airtable data connected to Google Data Studio, all that is left to do is build your dashboard.

Click the Create button again, but this time select Report.

You’ll be presented with a screen to add data to the report. Click on My data sources and select one of the Airtable data sources you just added.

Now that you have one Airtable data source added to your report, add the rest by clicking the Add data icon.

Build! You can add scorecards, bar graphs, maps and more by just dragging and dropping.

Final Tips

Here are a couple ways to level up your reports.

Build customer portals

Google Data Studio is great for building internal reports. But you can also use it to build interactive reports and portals for your customers. You can easily create a GDS report that shows just the data a specific customer needs to see from across your base in one simple view. Then when you share it with your client you can require a Google login or password to keep their data confidential and secure. This is amazing for project tracking and more.

Controls

Add date pickers, search fields and other controls to your reports so you can easily slice and dice your insights.

Interactions

Just like controls, you can turn on chart interactions so that when a viewer clicks a row on a table, the rest of the data on the report filters to just the data corresponding to that row. It’s really nice.

Join and blend data

As I mentioned at the beginning, sometimes your business insights are hiding across tables. So you may need to pull together data from several Airtable tables into one data view in Google Data Studio to get the metric you need.

You can do this two ways:

1. You can use a Google Data Studio blended view to connect tables. Click the Resources menu and select Manage blended data. Then click to add a data view. You’ll then select the two or more tables you want to merge together and identify the join key that GDS can use to match up the data. Since the underlying data source is Airtable, the join key relating the two tables will often be a lookup field.
2. You can actually join the data from the two tables using SQL when you connect to your Sequin Postgres database. To do this, you would follow the steps you went through to connect to your Sequin database, but instead of selecting a table you'll click to run an advanced SQL query. Then, you can write a SQL query to join tables and return the exact data you need. For an easy reference on how to write this SQL query, you can check out this post I put together on querying Airtable data with SQL.

Start with questions

A dashboard or report is really only as useful as the questions it answers and the decisions it motivates. So before you get in the weeds, start by outlining what questions you want to answer from your Airtable data (i.e. are we running out of stock?) or what decisions you want to drive (i.e. can we respond to every customer within 24 hours?).