Google Data Studio

tip

Since writing this playbook, we've rolled out SQL writes for Airtable! This playbook demonstrates using our older HTTP API Proxy for writes instead of our new Postgres Proxy.

In the steps where we mention an HTTP API proxy, you should opt to just mutate records in your database instead. Read about writes to learn more.

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 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:

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

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

3. 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:

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

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

3. 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, Database, User, and Password into GDS. You can leave the Enable SSL checkbox empty. Then click Authenticate.

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

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

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

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

4. 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 Sequin Postgres database. To do this, you would follow the steps you went through to connect to your Sequin 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?).