6. Organizing your data: Airtable basics

Airtable is a low-code, “spreadsheet-database hybrid” platform with a freemium model. It can be used for standalone datasets or to collect information, like a spreadsheet, but also has more robust features like a database that allow you to link your different spreadsheets (called tables) and records contained in them to each other.

It has many different uses, but you see it used most often in journalism for internal organization, like for editorial calendars and planning, impact tracking, membership directories and DIY customer relationship management systems. You can also easily publish embeddable Airtable databases and forms online, so it can also be used for collecting information through engagement and audience surveying or sharing out information. Some data journalism projects have used Airtable as part of their workflows for collecting and analyzing data.

The development tracker includes two of those uses, collecting data internally and collecting reader-sourced tips through externally published forms. But we use Airtable in a third, more foundational way – as the content management system that powers the public-facing development tracker site, or news app. If you work in journalism, you almost certainly use a content management system to post to your outlet’s site – Wordpress is a common one, and there are plenty of others like Arc Publishing, Ghost and custom-built CMSes.

In the development tracker, you don’t draft and publish posts as you would in a classic publishing CMS. Airtable records, or rows of data, about real estate developments are instead each essentially a separate page on the site. The way the template site is coded allows those pages to be created, as well as main category pages that are coded separately. See the publishing your site section for more on how you go about “publishing” your Airtable records to the site.

We chose to use Airtable this way because when we started this project, we were familiar with Airtable; it is really strong for capturing and entering the large quantities of data we wanted to compile, with a low barrier to entry for a first-timer; and it was relatively simple, if you’re a developer, to structure for and connect to a public site. With the template site, we’ve also made it easier for someone with less coding experience to take on the latter piece.

However, there are downsides to using Airtable. It is an atypical use for the platform, so there aren’t many public resources out there if you want to do this on your own. And Airtable is not really designed to be used as a CMS, meaning all of the hookups to the site must be custom built and settings can’t easily be adjusted without also changing the code. We are currently exploring other CMS options that we may use to host the Detroit Development Tracker in the future – but everything we’ve looked at so far will include more custom work on the developer side, and we still think this Airtable setup will work well for newsrooms that don’t have the developer capacity to build their own backend.

If you are new to Airtable, we recommend you read their getting started guides to familiarize yourself with the platform and how to use it. Below is a glossary of some essential Airtable terms we’ll use as we walk through setting up your data.

A base is a database where you store all the information for one project. The development tracker uses one base.

Each base can have multiple tables with different content or data sets. The development tracker uses three tables.

Your Airtable homepage shows all your bases, housed in one or more workspaces. Workspaces help you organize your projects if you have many bases; you don’t need to adjust these yet if this is your first Airtable project.

Each table in your base is made up of rows, called records. Each record corresponds to an item. In the development tracker’s main “Projects” table, each record corresponds to a real estate development project, and a published page on the public app. The tracker template projects base includes 10 sample records; you’ll have as many as your own dataset warrants.

Each record has multiple columns, or fields, where you store individual pieces of data for the record. You use fields to structure your records; you can use them externally, like an address that appears on a development's page on the tracker, or internally, like to track which staffer is assigned to complete or edit a record.

Fields can be configured to allow different types of data, manually added or automatically generated. Airtable allows more than 20 field types, like single-line text, single-select tags (that you can add manually) and attachments.

Airtable allows you to see your tables in different formats, called views, which include grids (the default view, which looks like a spreadsheet), galleries (shows each record as a card), calendars and several others. You can use views to display your table in a more useful way or to only show select information, like a view that only shows records that need to be reviewed. Editing records in a view will automatically update the entire dataset.

The development tracker template includes several named views on the left bar you can use or adjust.

Paired with views, filters allow you to show only certain information from your entire database, by adding one or multiple conditions based on fields (like only show records if a certain field is empty, or contains a certain value).

You can also group or sort records by certain fields for easier review. You can change those at any time, or set up different views that are sorted in particular ways.

Interfaces are a bit like views – they take more setup but can be a more user-friendly way for collaborators to work with a portion of a base. You can configure them to display dashboards or certain records with a more visually appealing layout (that is easier to use if you’re doing extensive writing or editing).

The development tracker template includes several interfaces that work well for adding or editing data and seeing what’s included in your base at a glance.

You can add collaborators with different levels of access. You can share an entire workspace or base with members of your organization. “Creators” have permission to make any changes to a base and “editors” are able to edit records and views but not reconfigure tables or fields.

You can also share a base as read only or comment only, and share particular views for editing or read only.

Once you have collaborators in your base, you can mention them in comments or use the collaborator field type to assign them records or action items.

Airtable automations allow you to easily set up certain automated actions in other platforms, with a trigger you determine, like when a record is added or matches certain conditions. It works with a range of apps, including Slack, Twitter, Google Suite and others.

In the Detroit Development Tracker, we use automations to send messages to a channel in our organization’s Slack whenever a new tip is submitted or a record is marked ready for review. Automations must be set up with your own accounts on other platforms; read more about how to add an automation here.

Airtable’s free tier is pretty robust, and we were able to build the Detroit Development Tracker without paying for premium.

Here are some of the restrictions to keep in mind if you want to avoid paying for premium (see pricing and feature comparison here):

• Records: Your base can have max 1,200 records and 2GB of attachments.
• Collaborators: You can have five collaborators with editor permissions or higher.
• Revisions: You can see two weeks of revisions on records.
• Apps: Airtable’s in-house and third-party apps give you more functionality on bases. You can have one for free per base. (The development tracker does not use any apps, but you could configure one.)
• Automations: You can set up as many automations as you want, but there is a limit of 100 runs (i.e., every time it is triggered to send a Slack message) per month on the free plan. If you use automations extensively for record review, submissions and other workflows, you may hit that limit easily.

In the next section, you will create an Airtable account and the base where you will store your data that powers the public tracker.