Getting started
Picking your platform
If you manage a significant volume of applications — whether for courses, grants, fellowship, workshops, events, or jobs — you’ve probably wondered which software platform best suits your needs.
Google Forms & Sheets is a classic starting point. It’s free or inexpensive (depending on your plan), and the spreadsheet is a familiar system for many of us. However, as the complexity of your application grows, you’re likely to bump into its limitations. For example, Google Sheets offers:
- just a handful of data types, like text, numbers, and dates
- a traditional spreadsheet view for presenting data with limited options for customization
- a relatively steep learning curve for automating common actions.
These limitations aren’t insurmountable. However, as your application and/or evaluation processes grow in complexity, things may begin to feel restrictive and/or precarious. Even more so if there are multiple people working with the system, whether other administrators, external collaborators, or other stakeholders.
You can improve your day-to-day management of applications with a more purpose-built system, such as:
- a dedicated applicant tracking system (ATS) like Greenhouse
- a multipurpose customer relationship management (CRM) system, like HubSpot
- a customizable database system, like Airtable.
If you have a technical background, you might even consider more deeply customizable systems like NocoDB or Retool. Or, for more comprehensive business solutions (and budget permitting!) Zoho or Salesforce; however, these systems can be especially technical and might warrant a dedicated and experienced administrator.
For application management, we find that Airtable provides a good balance of out-of-the-box useability with automation, flexibility and extensibility. It also benefits from frequent updates to its feature set and has a responsive support team.
We aren’t affiliated with Airtable — we’ve just seen organizations use it to great effect and we’ve come to appreciate its strengths as an ATS. In particular, we’ve been very impressed with BlueDot Impact’s use of Airtable for managing its courses on AI safety and biosecurity, and we’re very grateful to them for giving us an inside look!
Picking your plan
Airtable comes in four tiers: Free, Team ($20 per user per month), Business ($45 per user per month), and Enterprise Scale (custom pricing). The Team plan is a good place to start. It introduces extensions, sync integrations, and increases the number of records per base from 1,000 to 50,000 — and offers a 14-day free trial.
For sophisticated systems, the Enterprise Scale plan is good value. You’re unlikely to run into record count limits with this plan, and it supports two-way sync between tables (more on that later!). The ballpark cost may be ~$10,000 per year (though it varies by customer) but it’s worth noting: nonprofits can apply for a 50% discount.
The basics of Airtable
Before we dive into configuration, let’s cover the basics.
A workspace is a collection of bases shared between a group of collaborators:
A base is a collection of tables. A table is a collection of records (rows) and fields (columns). A table may also have multiple views for presenting a filtered set of records and/or fields:
A record is an entry in a table, and has values for each field within the table:
Those are the basic elements of Airtable. So, how can we use them to create powerful application management systems?
Figure out your system’s structure
The way you set up your system will be determined by the complexity of your needs.
A good rule-of-thumb is to think in terms of functional “data containers”. A data container might be a table or a base. For simple systems, we’d use tables as our primary data containers. But more complex setups might require entire bases as the primary data containers. So, for now, we’ll use the term “base” to denote a container.
Each base should have a clear, and relatively narrow, purpose. Accordingly, each base should only house records of a single type:
- The primary function of our ATS is to receive applications. So, we’ll get started with a base that does just this. In this base, each record will represent a single application.
- The secondary function of our ATS is to evaluate applications. So this will be the purpose of our second base — in which each record will represent an evaluated application.
- We may also want our ATS to serve as a database of applicants (in addition to their applications). So, we’ll likely want a third base to serve as an address book, where each record represents a contact.
Let’s explore these in turn.
A base for applications
As we’ve established, the primary purpose of our ATS is to receive applications — and we’ll need a base that serves this clear and narrow purpose. (As a reminder, we’re using “base” as a generic term to describe either a base or table here — simpler systems can get away with tables, whereas more complex systems might warrant bases.)
Once we’ve created our base, we’ll need an application form to begin receiving applications. We can do this from within any table. Just select Create… > Form and populate it with some questions. Anyone with a link to the form can submit an application, and each application will be represented as a record in the original table.
Airtable automatically converts the questions in the form into fields (columns) in the original table. So, we can view responses in-line and begin to evaluate them.
We’d recommend a second base for evaluations, but we can explore how we’d handle this in our original applications base first. (And a single-base setup can be sufficient for simpler systems.)
The most straightforward way to evaluate applications is to create new fields in the table, alongside the fields relating to the form we just created. We can use these new fields to assign scores and comments to particular answers, or applications as a whole. One tip: be very intentional when it comes to adding fields. The more data points you have, the more complex your system becomes — and the harder it gets to find the most insightful nuggets. Tracking data for the sake of it is tempting, but if that data isn’t clearly connected to decisions or analysis, your system can quickly devolve into unnecessary complexity. And, while you can always hide unwanted fields from view, don’t be afraid to delete fields that no longer serve your purposes. You can always restore bases to an earlier version if absolutely necessary.)
A base for evaluations
Now that we have a table with a mixture of fields, the value of a second base dedicated to evaluations becomes clearer.
Some of our fields directly relate to the application form (and their values are determined by the applicant); others relate to evaluation (and their values are determined by us as evaluators). It’s worth clearly delineating the “external” from the “internal” fields here, to make it clear to evaluators where they should leave their scores and comments – and to avoid accidentally editing a response from an applicant!
It is possible to do this within the original base. For example, you could make use of multiple tables within the same base. You could further distinguish external from internal data by including simple prefixes in your field names to indicate where the data originates e.g. “[a]” for applicant vs. “[e]” for evaluator. Or, for a more visually striking separation, you might consider using simple emoji (“🟨”) to highlight fields that the evaluator should be editing. (We don’t recommend using emoji if you intend to make use of scripts in your build, as emoji are trickier to refer to with code.)
But evaluation can be a complex process, and you may have dozens of “internal” fields tracking comments and scores — including calculated fields which can take a sum or average of other fields. All this makes it worth delegating evaluation to a second base. To do this, you’ll need to back out to the main workspace view and create a new base from scratch. You can then use Airtable sync to pull in the relevant data from the first base.
A base for applicants
Finally, if we want our system to also function as a CRM, we’ll want to build a base for applicants.
It’s worth appreciating the difference between applications and applicants here: one applicant may submit several applications. (We can use automations and linked records to connect our applicants to their respective applications across other bases — more on that later.)
We’ve likened a CRM to an address book, but a robust CRM will include not just a list of contacts, but a record of interactions with those contacts. This sort of CRM is particularly useful if you want to monitor the impact of your project over time. We can log these interactions as fields on the applicant records.
Further bases
There are all sorts of other functions you might try to squeeze out of your ATS.
For example, you may want to subject successful applications to further processing — perhaps assigning successful candidates to specific cohorts in your course/event. You can get away with using different views or tables within either of the first two bases for this purpose, but this will require more diligence and maintenance to prevent things becoming cluttered.
The main source of clutter is that cohort assignment will likely require dozens more internal fields, and these will only be relevant to the subset of applications which are successful. Having so many fields in view can quickly lead to things becoming chaotic, especially as users can (and often do) continue to add fields as the system evolves.
Perhaps more importantly, you may prefer that the individual or team responsible for cohort assignment is only able to view or interact with successful applications (rather than all original applications). Plus, you’ll be safe in the knowledge that any automations relating to successful applications (e.g. email confirmations) will be air-gapped from other applications.
So, in line with our recommendation for clear, narrowly-purposeful data containers, we’d recommend conducting cohort assignment in a third base — and creating further bases for any other post-application processing.
Linking & syncing
You can sync data between bases natively; however, two-way sync requires the Business plan. Two-way sync allows a record to be edited from either of the two bases (i.e. the applications base or the evaluations base).
Linked records
Besides syncing the same record across multiple containers, you may also want to link different records together. One use case for linking records is when you’re dealing with records of a different type that have some relationship with each other. For example, in addition to the containers housing applications, and applicants, that we’ve discussed above, you may decide to create a container for housing evaluators who assess the applications.
Suppose we have an applications table and an evaluators table:
Our goal is to link a suitable evaluator to the application that they’ll be reviewing. You can create a linked record field in the applications table by selecting Add field > Link to another record:
Lookup fields
Lookup fields expand the possibilities of linked records. A lookup field is a type of field which lets you pick, and link, specific contents from the linked record. For example, in addition to pulling in our evaluator record above, we might also want to pull in specific pieces of data from that record — like the evaluator’s area of expertise or email address:
You choose the relevant data from the evaluator record to link via a pop-up overlay, which is searchable. However, the search function is restricted to the source table’s primary field — the column furthest to the left. It’s therefore important that the primary field in any table is unique; an applicant name won’t do if you allow multiple applications from the same person.
There are two ways to ensure that the primary field is unique. One option is to create a unique record ID for each row. You can do this by creating a formula field and using the formula RECORD_ID(). Airtable will auto-populate each record with a unique string, like “recLgGyN46l9lpFhr”. However, this is hardly more searchable, unless you’ve got a good memory for strings or an appetite for copy and paste.
A better option for creating unique primary fields is to use formula fields to concatenate various values, like the applicant’s name and the time of their application. The time offers uniqueness and the name offers searchability. To do this, create a formula field and use the formula CONCATENATE(), inserting the desired field names in the brackets and separating them with commas.
Automations
A smooth experience for the applicant requires frequent communication from you — to let them know when the application has been received, when it moves from stage to stage, and when further information is required. Automating this communication can free up a huge amount of your time.
Another use case for automation is linking records. We’ve outlined the manual process above, but ideally this linking step would happen silently in the background.
There are a number of ways you can automate these sorts of workflows in Airtable, such as through native automations, extensions, its API, or scripts.
Let’s kick off with emailing applicants.
Emailing applicants
You can use Airtable’s native automations tab to automatically email an applicant (the action) when their application moves to a second stage of evaluation (the trigger). To build an automation, select the Automations tab at the top of the Airtable interface. You’ll be prompted to select the trigger and action, name your automation, and then enable it.
Email automations support mergefields, so you can personalize each message by pulling in data from the application itself (like the applicant’s name). But the email automation’s text editor is otherwise a little limited, and it’s difficult to pull in data from linked records in Airtable’s native automations. Also, if you’re sending a lot of emails, you’ll need to upgrade to a paid plan — Airtable’s free plan permits just 100 automation runs, which you’ll burn through fairly quickly.
You may therefore want to experiment with extensions, like SendGrid. Alternatively, for a more feature-rich communication platform, you could check out customer.io. It’s designed for marketing, and it’s much better at collating and presenting your outbound messages. (This is especially important when an automation doesn’t run as expected. Sometimes Airtable’s native automations fail to notify you when automated emails don’t go out, which can leave applicants in the dark until you discover what’s happened.)
Airtable also has an API, so you connect it to other apps in your stack using something like Zapier. However, the API isn’t especially rich (for example, while you can connect to Google Calendar, you can’t delete calendar events through the API). You can build more robust connections with a more flexible tool like Make, albeit with a steeper learning curve.
Finally, you can get around most automation obstacles by coding directly with Airtable scripts. This can be a powerful addition but does require understanding the basics of JavaScript.
Linking records
Another use case for automation is linking records, as we’ve mentioned above.
Suppose you’re reviewing an application, and want to know whether the applicant has previously submitted an application for your program. To do this, you’ll want to make sure that records from the same applicant are linked. We’ve seen how the manual process works, but we can use native automations to the same effect with much less effort.
As we’ve seen, every automation is built from a trigger and one or more actions. The key to automating linked records is to use two actions: one to find relevant records, and the other to connect them.
First, you’ll want to figure out which piece of data determines that two applications are from the same applicant. The applicant’s name or email address are both good options (although not foolproof: the applicant may write their name differently across different submissions, or submit from multiple email addresses).
You can then create an automation whose first action is, for any given record, to look for relevant records. The Find records action type does just this. If we want to link records on the basis of the associated email address, we can set conditions on this action to specify its remit.
In this case, we’ll want to create a condition that looks for records whose value in the “Email” field matches that of the original given record. (We’ll likely also want to add a condition that ignores records with an identical record ID. This avoids associating a record with itself, since it’ll necessarily match the email condition!).
For our second action, we’ll want to update the original record to display this list of related records i.e. those with a matching email address. It’s no use linking records if we can’t see which are linked! To achieve this, we want to make sure we’ve already created a field in our applications table to display these other records — we can call it “Related applications”. Then, in our automation, we can use the Update record action to update this field. We can configure it to pull whichever pieces of data from the related records are most useful — such as the record ID, application type, status, and so on.
Data visualization
Using Interfaces
Data visualization is another strength of more purpose-built software for managing applications. Airtable offers a pretty handy tool for visualizing your application data: Interfaces.
Think of Interfaces as the “front door” to your data. We can access Interfaces from its tab in the top menu of any base. Then we select a layout, pick a data source, and choose and customize interactive components to build dashboards for internal and external collaborators alike.
For example, here’s a simple dashboard for a grants management system, showing the proportion of applications in each stream, the application type, and the location of the applicants:
The drop-down element in the top-left lets us narrow the scope of the data — for example, to show only applications with a particular status (like “Under consideration”).
Interfaces are great tools for tracking the impact of your courses and events over time. If you’re running a fellowship, for example, you may want to build dashboards presenting how many sessions people have attended; how many of the resources they’ve completed; post-fellowship survey scores; and – if you’re tracking it – where they’ve ended up 6-12 months after the course.
That said, it’s a common misunderstanding to relegate Interfaces to view-only dashboards. Interfaces are actually a great tool for day-to-day management of applications, too. We can enable data editing from within an interface, and use record pickers for accessing specific sets of records. A record picker is a configurable element that lets you filter the contents of an interface to a specific set of records — like the drop-down we mentioned above.
In fact, we can add multiple record pickers to an interface, and configure each record picker to show records from a separate base. In this way, we can replace lookup fields (in tables) with record pickers (in Interfaces) for a much simpler system architecture. Accordingly, many teams could benefit from primarily interacting with their systems through Interfaces, with just 1-2 back-end engineers living in the deeper table views.
Putting it all together
Once you’ve set up your ideal system, it’s also worth taking a moment to jot down how it works. This will help us down the line once we’ve forgotten some of the details, and can be an invaluable onboarding tool to new members of our team (or external collaborators, like evaluators, who may not have a deep understanding of the system).
The first step in the process is making sure to write descriptions for all elements in the system. We can add descriptions to fields, tables, and bases — and recommend doing all three.
We also recommend creating a key for any prefixes you use in field names. We discussed the example “[a]” and “[e]” prefixes to indicate fields that relate to applicants and evaluators, respectively. You might also want to use a prefix like “[!]” in the names of any fields that trigger an automation when their value is changed.
Next, we suggest documenting the connections between records and bases with a schema. In Airtable, the Base Schema extension can show us how various aspects of the system tie together. (It also reveals how much complexity lookup fields add!) If we want more flexibility, draw.io is a handy free tool for flowcharts; for a more comprehensive and collaborative approach, consider Miro.
Maintenance
Even the most well-designed setup requires maintenance. We may introduce additional cohorts or application streams, decide to add or remove questions from our application form to improve the evaluation process, or find ourselves with a growing list of duplicate records.
Adding & removing fields
Suppose, for example, you’re running a series of annual workshops. Your application form asks applicants to select which workshop they’d like to attend from a single- or multi-select field (call it the “Workshops” field). At the end of the year, you decide to replace the options, from last year’s workshops (e.g. “Biotech 2023”) to the coming year’s workshops (“Biotech 2024”).
Removing options from this field will unfortunately affect historic records in your base. Any fields with the value “Biotech 2023” will become blank when this option is removed. An inelegant but tempting solution is to preserve all original options in the application form. You might do this by renaming “Biotech 2023” to “[unavailable] Biotech 2023”; however, the user experience will quickly deteriorate as the number of unavailable options grows. Alternatively, you might rename “Biotech 2023” to “Biotech 2024”; however, this will erase the fact that the application in question was for last year’s event (and only works if the workshop’s name stays the same year after year).
A more elegant solution is to use automations to preserve historic data. We can do this by adding a new text field in your table — call it “Historic workshop” — to house the original data. We can then create an automation that runs once and sets the value of this text field to match the value of the original (and unchanged) “Workshops” field. If the value of the “Workshops” field is subsequently changed, or deleted, the text in the “Historic workshop” field will be unaffected.
Besides changes to the application form, you may also make changes to the evaluation process, base structure, or anything else. Before deleting any fields, views, or tables, make sure to check your list of existing automations and see whether these elements are being relied on for some system function. If they are, you can edit the automation’s trigger or action(s) as appropriate. And revisit your schema to make sure the updates are reflected there!
Duplicate records
Duplicate records can be an issue with any database. An applicant may accidentally or otherwise submit an application form multiple times, or you may have multiple entries for an applicant in a CRM base (perhaps because they used different email addresses for different submissions).
There’s no substitute for designing your system in such a way as to mitigate or eliminate this possibility, with appropriately automated linked records. However, in the event that duplicates creep in — which becomes more likely as your applicant pool grows — we recommend using the Dedupe extension to merge individual fields from different records.
Getting support
At Impact Ops, we’ve helped several organizations establish systems for applicant tracking. That said, we’ve been inspired by the particularly thorough Airtable setup that BlueDot Impact has pioneered, and we’re grateful for the time and effort they’ve taken to show us how it works behind the scenes.
Our mission is to enable high-impact projects to grow and thrive, and we’re excited about creating bespoke, easy-to-use systems for managing applications — so teams can spend more time on their mission, strategy, and execution.
If you’d like support with application management, please get in touch at hello@impact-ops.org.