Image by Author | Ideogram
Introduction
AirTable is a cloud-based, user-friendly, and AI-driven platform for creating, managing, and sharing databases. It combines the best of Excel spreadsheets with relational database management systems. AirTable offers a freemium subscription model, whereby some limited features can be used for free, making it ideal for smaller projects or beginners, whereas the paid version provides advanced features and a larger amount of computing resources.
This article provides a starting point for anyone interested in AirTable and what it has to offer at a beginner’s level, specifically for data analysis. The article walks you through the process of creating a new AirTable app that incorporates some data and uses it for some basic analysis procedures.
Signing Up and Creating Your First Project
As a cloud-based tool, AirTable does not require downloading a desktop application, but simply accessing its website and signing up. If you have a Google account, for instance, you can use it for a quick sign-up; otherwise, there is the option to register using an email address.
Once signed up, we are ready to create our first project. In AirTable, the concept of base or app is analogous to a project or app — essentially, a container for all the data — so let’s create a new base. If you cannot see at first glance the “create blank app” button, you may have to check for the “Create” button on the bottom-left corner or, alternatively, if there is an “x” icon to be clicked on the top-right corner, click on it and you will be prompted to created a blank app.
You should then see a screen like this:
New base (project) in AirTable
Now it’s time to import some data. AirTable bases consist of one or multiple tables. By default, an empty table named “Table 1” appears. Next to it, there is a tab called “+ Add or import“, which we will click on. In AirTable, there are various options to add data to our project, for instance, from spreadsheets in Google Sheets or Excel, Salesforce, Google Drive, Trello, and many more. We will use one of the simplest approaches: uploading a CSV file, concretely from a URL. To do so, select “CSV file“, and on the left-hand side of the emerging window, choose “Link (URL)“, as shown below:
Uploading CSV data via URL
Copy the following URL to a dataset I made available for you in GitHub, and paste it into the text field that appears. Then click on the right-hand side blue button, and when asked to create a new table or use an existing one, make sure you create a new table. Do not be tempted to use the existing default table called “Table 1”, as that table schema is not compatible with that of the dataset we are importing.
That’s it! You now have a new table populated with the imported data, which contains records of customers in a shopping mall, with the following attributes:
- Customer ID: the numerical identifier of a customer.
- Gender: the customer’s gender, namely male or female.
- Age: the customer’s age expressed as an integer.
- Income: the customer’s annual income in thousands of US dollars ($).
- Spending score: a normalized score ranging between 1 and 100 of the customer’s spending level.
Beginning Data Analysis
In the imported table, all columns are of numerical type, except for “Gender”, which is categorical. In AirTable, a categorical column with one possible value per instance among a predefined set of them is called “Single Select”. You can check or modify the properties of “Gender” or any other field by hovering on the column header, clicking on the v-like icon that appears, and selecting “Edit field”. For this tutorial, we will leave are column types as they are, and proceed to perform some analysis.
Grouping customers by gender: Grouping data records by values of a categorical attribute is as easy as clicking on the “Group” button above the table. Select “Gender” and then “Collapse all” to see aggregated summaries of your data for each gender. By default, you will see the total (sum) of values per attribute and gender, but you can also choose to see the average (or median, min, max, etc.) values of columns like income, spending score, and so on. This can be done as shown in the following screenshot:
Analyzing grouped customers by gender
We can observe that males have, on average, higher income than females, but women spend more than men.
Average income and spending score by gender
To remove a grouping of the data, simply click on the “Group” icon again, then click on the bin icon next to the created grouping to remove it and see your full, ungrouped table again.
Filtering young customers: Next, let’s try to do a filtering. This is an easy and intuitive operation available at the “Filter” icon next to the previously used “Group” icon. In the pop-up dialog, select “+ Add condition”. A filtering condition consists of three elements: a field or column name, an operator, and a value. Examples of conditions are “Age >= 39”, “Spending Score = 10”, “Gender is not Male”, etc. To filter young customers, we will set the condition “Age < 30”. This should filter a total of 55 customers. One interesting thing to do at this point is to combine the filter made with (once more) a grouping by gender, to check whether the findings about income and spending score in males vs. females still apply for young customers. Once you have tried this, filters can be easily removed similarly to groupings.
Using formulae to define an “income class” field: AirTable allows the creation of new columns under many different approaches, formulae being one of them. Simply click the “+” button next to the right-most column in your table to add a new column, and choose “Formula” as the creation method or column type. For instance, we can use the following formula:
IF({Annual Income (k$)} < 40, "Low",
IF({Annual Income (k$)} < 70, "Medium", "High"))
To create a new column called “Income class” whose values (categories) will be defined depending on the value of the annual income column, by following the above formula consisting of two nested conditionals. If you are not familiar with spreadsheet-like formulae syntax, don’t panic, there is a “Create formula with AI” button whereby AirTable’s AI assistant can help build a formula based on your specifications or goal.
Using formulae to create a new column
Using interfaces to visualize your data: Airtable interfaces are used to generate data visualizations. This feature is limited in the free tier, but it is still possible to create simple dashboards with elements like bar charts and pivot tables — that is, cross-column tables that summarize and aggregate the data based on field combinations. To try creating an interface, click on “interfaces” at the top toolbar, and follow the assistant steps. You may end up getting something like this dashboard:
Interface dashboard in AirTable
Note that interfaces are designed to be shareable among teams, e.g., for driving business intelligence processes.
Wrapping Up
This article introduced AirTable, a versatile and user-friendly cloud-based platform for data management and analysis, combining features of spreadsheets and relational databases with AI-powered functions. The guide provided in this article is intended to introduce new users to AirTable and outline some basic functions for data analysis. Needless to say, while they have not been our main focus, AI features provided by the tool are arguably one of the recommended next steps to explore beyond this point.
Iván Palomares Carrascosa is a leader, writer, speaker, and adviser in AI, machine learning, deep learning & LLMs. He trains and guides others in harnessing AI in the real world.
