How to visualize Airtable data with Chartbrew
In this tutorial, we are going to create an Airtable connection in Chartbrew and visualize the data. We are going to use an Airtable template already created for this tutorial. The data contains pageviews and visitor data from Chartbrew's Simple Analytics dashboard.
If you are not using Chartbrew currently, you can get started in two ways:
Get the tutorial Airtable data
To get started, click the link below to access the data we will use for this tutorial:
Once there, you'll have to duplicate the data in one of your Airtable spaces. Click on the title of the document and then select "Duplicate base". Select where you want to duplicate it and make sure you check the box to include the records too.
Awesome! Now that we have a base, we can go ahead and connect Airtable to Chartbrew.
Create an Airtable connection in Chartbrew
Before we create the connection in Chartbrew, we have to generate a new Airtable Personal Token to authenticate the data requests from Chartbrew. You can generate yours from the account page here.
- Name: Chartbrew (can be anything)
- Scopes: data.records:read
- Access: Chartbrew Airtable tutorial (your workspace)
After clicking the Create token button, you will receive the token we will need for the Chartbrew authentication. Copy this code as we will use it in the next steps.
Next, create a new project or use an existing one in Chartbrew. Head over to the Connections screen and create a new API connection.
The bearer token field has to be filled with your Airtable Personal access token generated earlier.
Copy the token in the Bearer token field and save the connection. If you test the connection you should get a "404, not found" error, but that is to be expected since we are trying to get data from the root API URL.
- Name: Airtable (can be anything)
- Host: https://api.airtable.com/v0/
- Authentication: Bearer token
Create a time series chart with Airtable data
Next, we are going to create a time series chart that looks like this:
Click on the "Create a new chart" button in the sidebar menu and let's get started with building our first Airtable chart!
Our Airtable data contains two tables. One with the number of visitors on different days and the other one with the number of page views. Our goal is to plot this data in a line chart with two datasets. Create a new dataset called "Visitors"
Select the newly created Airtable connection, and click on the request button.
To know how to complete the request, we have to get the right API endpoint from Airtable. Head over to the Airtable API docs and select your new base.
Once there, you will see both "visitors" and "pageviews" tables in the side menu. To start, select the Visitors Table and click on "List records". The left view will show you how to request data. Copy the bit after the root of the API endpoint as shown in the picture below.
We can now paste this part of the URL in Chartbrew to match the URL shown in the Airtable docs. You can delete the "maxRecords=3" bit since we want to get all the data. After you do that, press the "Run the request" button and you should get the data in a JSON format in the left section.
Another important step in making sure you get all the required data is to paginate requests. Airtable limits the number of records returned per request, but Chartbrew can automatically make multiple requests to get all the data.
For the Chartbrew pagination to work well, you'll have to set:
- Enable the pagination
- Pagination Type: Cursor-based
- Cursor query parameter: offset
- Next cursor field name: offset
- Max items: set to any value you want or "0" for all
You can check the example below.
Now that we have the data coming in, we can go ahead and visualize it.
1. Click on the "Build the chart" button and prepare the X-Axis and Y-Axis so that Chartbrew knows how to plot the data.
2. Our goal is to show the number of visitors per day.
3. We need the "date" field on the X-Axis and the "visitors" number on the Y-Axis. Also, we don't need to apply any operation on the Y-Axis because the number of visitors is already aggregated.
Great! We have a nice time series chart showing the number of visitors per day. Now let's add the number of page views on this chart as well. We have to do the same steps, but instead of the Visitors Table, we need to get the right URL for the Pageviews Table.
- In Chartbrew, create a new dataset called "Pageviews"
- Head over to the Airtable API docs to get the right URL, but click on the "Pageviews table" instead
- Configure the X-Axis and Y-Axis in the same way as we did for the visitors
And here we go, we have a time series chart plotted with data from Airtable! Feel free to connect any of your tables to Chartbrew and you can have live visualizations of your data. It works with dynamic data since Chartbrew can be configured to keep your visualizations up-to-date.
Next steps
Charts and dashboards in Chartbrew have extra features that you can play around with. Some things you can do moving forward:
- Set your charts to auto-update so you get fresh data from your Airtable source
- Embed your charts on external sites like Notion, Blogs, Personal websites, etc
- Create a public dashboard and share your unique link with your audience
- Connect other data sources to your dashboard. See the tutorials section for inspiration
