Live More new Guides on the way! Get notified by signing up to CAST's newsletter..

Home > Guides

Tracking a monitoring and evaluation system using Airtable

Contributed by

Citizens UK is a people powered alliance dedicated to challenging injustice and building stronger communities. The Living Wage Foundation, a Citizens UK initiative, believes in a society where in-work poverty no longer exists. Their mission is to encourage employers to play their part in tackling in-work and post-work poverty

This Guide focuses on how to use some of Airtable’s key features. It talks about using them to make it easy for teams to share data and for project leads to use that data. It will be most useful to people who already understand monitoring and evaluation.

Jump to steps
Key software:
Airtable
How they set it up:
Used free support from the software company or internet research
Setup costs:
Staff time
Ongoing costs:
Airtable's 'business plan' subscription - approx £20 per user/month with 50% non-profit discount. You could also set up a less sophisticated system using Airtable's free plan.
Guide in use since:
July 2023

Steps to using Airtable to make monitoring and evaluation smoother

Setting up a new monitoring and evaluation system takes time and effort. It will be worth it, if you start from the right place.

A system can be helpful if:

  • Your organisation wants to track a complex piece of work in an accessible way

  • Different people in your organisation are tracking and storing things in different ways and this makes pulling reports together difficult

  • You have a CRM (Customer Relationship Management) system which is outdated, expensive or difficult to use or that has a tight limit on who can use it - but you don’t want to take on a whole “replace the CRM” project.

  • You’re about to scale up your projects - and are worried that your old methods of handling data won’t work as you grow.

Airtable is a multipurpose piece of software that can do many things. When it comes to handling monitoring and evaluation data it offers:

  • data collection methods (public forms and private records)

  • a simple spreadsheet like feel

  • ability to link records (in a similar way to other relational databases)

  • the ability to add multiple views (rather like queries - ways to generate filtered information for different reports)

  • the ability to create dashboards for key data.

The key advantages are:

  • You do not need to be a database programmer to set it up. You can do it following on screen instructions or videos. (Although it will be helpful if you are used to generating reports from databases or spreadsheets. This will help you decide what you want Airtable to do).

  • It is very flexible, you can change things easily, and add new ways of using it as you go.

Airtable has several versions, and a complicated pricing structure. Take care to work out which version will meet your needs and budget.

Citizens UK were attracted to Airtable because it was cost effective and flexible. They were managing a large funding stream, and their Learning and Network Manager wanted to set up a system that could evolve as their monitoring and evaluation framework evolved. She was confident that she could configure Airtable herself to achieve this.

It gave them an opportunity to pull together information that had previously been held separately by different teams, and to streamline some elements of their monitoring approach.

You need to know what you are aiming to measure before - or at least at the same time as - you create data storage and collection systems.

You should consider best practice in monitoring and evaluation. Look at what you want to prove in connection with your theory of change, your organisation strategy or your funding outcomes. If you're completely new to Monitoring and Evaluation - have a look at NCVO’s resources before you start working out what system to build.

Once you have a sense of what types of data you are going to collect you will be able to decide how to use some of Airtable's key features.

You need to:

  • Create a Base in Airtable (it looks like a database table or a spreadsheet).

  • Decide if you want all your data in one tab or if you need separate tabs (like worksheets or tables) in that Base.

  • Each tab is made up of columns that contain Fields. Each Field relates to a type of data you decide to collect. Fields will be a mixture of data types that help you measure your outputs and outcomes (for example, number of attendees at an event, scale rating of whether people have increased confidence) and things you want to filter your data by (like location or date).

Living Wage Foundation’s Learning and Network Manager worked with her teams and realised that their data fell into 5 categories:

  1. People
  2. Actions (often these are events)
  3. Groups
  4. Organisations
  5. Training and Learning

Each team wanted to track different outputs and outcomes. So they created an initial Base with 5 category tabs. Then, within each tab they set up different Field types that team members could add data to. Team members often had to judge if data was relevant or appropriate to add. So they created descriptions for each Field (an Airtable feature). That way the team would be able to see and discuss exactly what data was needed for each Field.

Some data is really clear and straightforward - for example, the date an event took place. Other data is messier.

Always work with your teams that will be gathering the data as you create your reporting database. This will help you create the right Field types and descriptions that mean everyone understands what they are inputting.

Understanding data is more important than understanding Airtable to get this right. For example, you might have to choose between creating a field that has 4 drop down options to choose from, versus a free text box for people to write into. Both are easy to create but knowing which will be most useful depends on your data knowledge. As a rule drop down box data is easier to analyse but some people may find it reductive or less meaningful.

Refining data definitions was a core element of Living Wage Foundation UK’s development phase. 

For example, Living Wage Foundation UK believe their work is effective because of long term rather than short term relationship building. So they wanted to track their first contact with people and organisations. But what did 'first contact' actually mean and who should they track it with?

First they identified two criteria for deciding who to track:

  1. they have played or are likely to play a leadership role in their work

  2. they are currently a member of a Living Wage related campaign group.

Then they needed to work out what counted as meaningful first contact with with these people. The team discussed how to judge what counted and changed the Field description to match their decisio. 

Data is only useful if you are going to use it. As you set up your Fields, think ahead and check that you will be able to perform the analysis you want on it.

Once again, this relies on your understanding of monitoring and evaluation processes and the type of logic usually applied to analysis. Understanding which Airtable features to use is straightforward if you know what you want to achieve.

Citizens UK often use Airtable’s ability to segment and filter data by date. Sometimes these dates are automatically added to Fields (Airtable has a “record created” automation for example), and sometimes they need to be added by team members.

See more about analysis in Step 7.

Airtable can look like a spreadsheet to people who are used to them. But it has the power of a database.

This means that you don’t need to re-enter data into different tabs. Instead you can link it. This makes adding information to the Base easier, and creating sophisticated Views and Dashboards possible.

Data records are linked across Living Wage Foundation’s Airtable in many ways.

For example, when someone runs an event, they enter the event into the Actions (Event) tab and complete some related Fields.

Then they add a record of all the people that attended by selecting them from a field linked to the People tab. Those that already exist in the People tab can be added directly. For new attendees they create a record in People and then add them.

They have set the People records to automatically update from this, so you can see which Actions and Training and Learning events people have attended.

People can enter data into Airtable in 3 ways:

  1. They can work horizontally along a “row” (actually a record) in the Base and add the data directly there

  2. They can open the record itself (using an expander arrow on the row) and add the data for each record into a record view

  3. You can create a form for them (or a participant) to submit the data. Sometimes this is easier for people. But its also useful if you don’t want people to have access to all records in a table for privacy or security reasons.

To give the team confidence you will need to:

  • Make sure they have training in how to use the system

  • Make sure they understand and see the benefits of collecting good data for both their day-to-day work and for making strategic decisions

  • Give them opportunities to talk to each other about how they integrate data entry tasks with their other work

  • Listen to them when they have frustrations with the system and see if you can make improvements or simplify the process

  • Hold regular update sessions looking at how data input is going.

Living Wage Foundation spent 3 months piloting, understanding and improving their base with one core team. Then they trained other teams.

This meant they had a core of people who could share positive experiences of using the system. It also showed that the development team were considerate of and responsive to people's needs and challenges around data entry.

They now meet with everyone who uses the Base regularly and continue to make improvements.

Views are Airtable’s simplest reporting feature. You filter or segment the data using Fields you want to organise things by, then save it as a View. Airtable offers a summary of the data in each Field that you can use for analysis.

For example, you can filter data that focuses on a key area, between certain dates and save as a View. Selecting that View will always show you that data and a summary of the total number of attendees or percentage of records that have complete data.

You can end up with a lot of Views in your Base. Create a clear naming system for them to avoid confusion, and consider creating Sections (an Airtable feature) to categorise them. Also consider having a plan to review and remove Views you haven’t used in a period of time. You don’t lose any data, and can always rebuild them if needed.

Citizens UK use their Views for 3 main purposes:

  1. Gathering reporting information for funders

  2. Sharing during team meetings to look at progress (including how up to date people’s data is)

  3. Making strategic plans for the future of the project.

They tend to use the Views by taking some of the data they show and adding it to written reports for funders. Sometimes they’ll pull data out and do further manual analysis.

They often analyse data together - either via screen share, or by several users logging in to a view.

There’s no value in good data for its own sake. You need to be using it to inform decisions about your work as well as to provide information for funders.

Regularly review how you are using your data. Remove Fields and stop tracking things that aren't being used.

As your project grows and changes, try and find time to experiment with new Airtable features.

Citizens UK are currently working with a consultant to set up Airtable Dashboards. This pulls data from the main database into screens that are easy to read or present. Dashboards can show data from multiple tabs and views. They also combine different types of data display including graphs and charts and numbers with short explanations.

They’re also making use of the Salesforce integration, which is just one of many integrations Airtable offers.

Further information