Students, meet your match: how ScholarMatch made Airtable work for them, part one

ScholarMatch’s drop-in center, at 849 Valencia.

The story in four sentences

ScholarMatch, a small nonprofit serving underprivileged students by crowdfunding college scholarships, started using an Airtable database as an inexpensive and flexible software solution for managing donors and donations. As the organization grew larger, and broadened its mission to include comprehensive college counseling services, ScholarMatch was able to easily adapt its database to fit its changing needs.

ScholarMatch keeps all the information related to their students, donors, donations, program schedules and attendance, volunteers, alumni, press mentions, and much, much more in a comprehensive database, which allows them to see quickly how these different categories are interconnected in meaningful ways.

Especially for a nonprofit without the budget to pay for an outside tech consultant to set up custom database software, Airtable provides a solution which is robust enough to handle complex problems and versatile enough to scale with a growing organization.

The origins of ScholarMatch

Dave Eggers has always been passionate about education. Though best known as the author of the widely acclaimed memoir A Heartbreaking Work of Staggering Genius, he also founded the nonprofit 826 Valencia in 2002, which provides writing tutoring for underserved students. As part of its services, 826 Valencia offers six college scholarships every year. But soon, they had hundreds of students applying for these six slots — it was heartbreaking to have to turn down hundreds of qualified candidates, each with their own compelling stories.

So in 2010, Eggers founded another nonprofit — ScholarMatch — dedicated to making scholarships possible for these driven but disadvantaged high schoolers. Kickstarter, launched the year prior, provided inspiration for ScholarMatch’s model: each student’s scholarship is crowdfunded by donors, and the donors receive a series of updates from the student, creating a tangible link between their support and a student’s success. “We want our scholars to know that someone from their community supports them, and cares about them,” says Diana Adamson, Executive Director of ScholarMatch.

In the beginning, ScholarMatch served 39 students, exclusively through its hands-off crowdfunding website. Today, ScholarMatch works with over 600 students per year, and provides active support and mentorship throughout the entire college application and enrollment process, all the way up to college graduation. From their drop-in center across the street from 826 Valencia, in San Francisco’s Mission District, they provide the financial aid, coaching, and career support needed to ensure a successful graduation.

Getting ScholarMatch to its current size was no easy feat, requiring not just determination, but organization. To manage a nonprofit with thousands of stakeholders — donors, students, volunteers, colleges, and more — ScholarMatch needed a reliable platform that could easily house and cross-reference many different types of information. Traditional database systems were out of the question: in addition to being inflexible and costly, they required tens of thousands of dollars in implementation. “We didn’t have the budget to pay for a tech person to come in and set up a system for us. We needed something that we could transform ourselves, and something that my staff could use without any computer programming expertise,” Adamson says.

In their search for a tool that would be powerful enough to handle all of their information, intuitive enough for all of the staff to learn, and flexible enough to grow with them as an organization, Adamson came across Airtable. ScholarMatch initially started using Airtable in December 2013 to track their donors and donations. But as their organizational mission broadened in scope and they began to offer more services, their Airtable database grew with them.

Today, ScholarMatch uses a comprehensive Airtable database for many different purposes: storing records of their donors and donations; keeping demographic information about their students and their home lives; planning their college coaching programs; making lists of different colleges’ fly-in events; tracking their mentions in the press, and more. And when it’s time to apply for grants, all the numbers are in one place. “Airtable tracks everything,” Adamson says. “It’s the home of all our organizational info.”

Donations are fund-amental

When Adamson first arrived at ScholarMatch, all of the information on donors, students, and donations was scattered across a handful of messy, shared word docs. This was adequate when the number of students and donors was still very small. “But by year 2 or 3,” she says, “it was beginning to be much more complex, and we needed a full CRM to get the full picture of what was going on with a student. What high school do they go to? What services do we provide them? Who has donated to their scholarship, and how much is their scholarship? We needed a dynamic system that could link those things together.”

In a traditional spreadsheet program, cross-referencing data between sheets is a laborious and fragile process, and requires making manual updates in many different locations whenever any information is changed. In ScholarMatch’s Airtable database, however, the table of donor records is linked to the table of donation records, which is then linked to a table of recipient student records, ensuring that any changes made to any of the tables causes the other tables to update in realtime.

ScholarMatch has integrated its Airtable database with Stripe so that whenever a new donation is added to the system, it is automatically inserted into Airtable. After the data from Stripe arrives in the database, a formula in the first field of the donations table concatenates the date of the donation and the amount of the donation to create a code for each donation record.

A formula for success

When ScholarMatch uses Airtable to log every individual donation, or every instance that they interact with one of their students, each record of those events needs to have a unique, descriptive name. Unlike a table of contacts or a table of charitable organizations, in which the name of each record is fairly straightforward, it’s not especially obvious how to assign brief and descriptive names to records of events. In these cases, it can be useful to set up a formula as the primary field.

For every row in ScholarMatch’s donations table, the formula which generates the donation code works by looking at the values in the “Donation Date” column and the “Donation Amount” column and concatenating them by using ampersands, along with some static text. The formula also uses the DATETIME_FORMAT function to ensure that the values in the “Donation Date” field are appropriately displayed in the desired M/D/YYYY format.

Then, ScholarMatch uses linked record fields to associate each donation with its appropriate donor and student. By selecting the name of the appropriate donor in the linked record type field “Donors,” and selecting the name of the appropriate student in the linked record type field “Students,” all three tables are linked together — meaning that any changes made in one table will be reflected in all tables.

The links created by the linked record field allow you to click on the name of a record from a different table and bring up all of that record’s associated information. Linked record fields also enable ScholarMatch to use other powerful field types, such as lookup and rollup fields.

Things are looking up

Using linked records allows ScholarMatch to arrange data from other tables in useful ways. Since the “Donations” table is linked to the “Contacts” table, ScholarMatch can use another field, called a lookup field. A lookup field uses a linked record field to automatically pull values from one table into another table.

ScholarMatch sends out thank-you letters to their donors for every donation. Instead of clicking through each linked donor’s individual record every time they need to get a donor’s address, or going to the contacts table, they have created a lookup field which automatically populates with each donor’s address in the donations table.

To set up this lookup field, ScholarMatch selected a linked record field in the “Donations” table that is linked to the “Contacts” table — in this case, the “Donor” field. Then after picking the “Donor” column, they selected the specific field they wished to look up from the target contacts table, the “Address” field.

Once configured, the lookup field brings up each donor’s address by searching through the information in the contacts table. This way, it’s easy to see each donor’s address without ever leaving the donations table or expanding any records.

Let the good times roll(up)

ScholarMatch tracks the total amount of donations a donor has contributed over time by using a rollup field. Like a lookup field, a rollup field draws values from a linked table, but it can also perform calculations or formulas based on the linked records.

For donors who have given to the organization multiple times, it’s important for ScholarMatch to track how much money they’ve contributed in total. Rather than going through each linked record separately and adding up all of the dollar amounts manually, ScholarMatch set up a rollup column that automatically sums up each donor’s lifetime contributions.

The “Total Donations” rollup field in ScholarMatch’s “Contacts” table is connected to a linked record field linked to the “Donations” table. Then, the rollup field takes the values from the “Donation Amount” column in the “Donations” table that are associated with a particular donor and adds up of all of those values.

For more on ScholarMatch, including how they adapted their Airtable database to scale to their growing organization, continue on to Part 2.