Build a dashboard with Google Data Studio and Airtable

Connect Airtable to Google Data Studio so you can quickly visualize the insights hiding in your Airtable base.

Connect Airtable to Google Data Studio

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 Sync Inc to turn your Airtable base into a Postgres database that plugs right into Google Data Studio.

How this works

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

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

  1. Go to https://app.syncinc.so/signup and create an account.

Create Sync Inc account

  1. Connect your base to Sync Inc 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.

Connect your base

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

Get your credentials

Step 2: Connect to Google Data Studio

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

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

Sign up for GDS

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

Add resource

  1. 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 Sync Inc Postgres database including the `Host`, `Port`, `Databse`, `User`, and `Password` into GDS. You can leave the Enable SSL checkbox empty. Then click Authenticate.

Add credentials

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

Create

  1. Google Data Studio will load the data from Sunc Inc 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.

Save

  1. Repeat steps 1 - 5 for each table in your Airtable base.

Build your dashboard

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

  1. Click the Create button again, but this time select Report.
  2. 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.

Add data sources

  1. Now that you have one Airtable data source added to your report, add the rest by clicking the Add data icon and repeat step 2 until all your data is added to you report.

Add remaing data sources

  1. 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 they need 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 & 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 Sync Inc Postgres database. To do this, you would follow the steps you went through to connect to your Sync Inc database, but at step 4 you instead click to run an advanced SQL query. Then, you can write a SQL query to join tables and return the 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?).

Custom Client Portal
Metabase

Was this helpful?