Airtable: Creating a CRM (Part 1) – Base Data Model

This is part of a multi-part series on creating a CRM in Airtable.

Let’s go through a process of creating a CRM system together. One that would be suitable for a small business and will be able to scale while that business becomes bigger and serves more people. Creating an app like this is a process, so I am going to work on it and break down my work into several blog posts.

One great thing about Airtable is that you can create nearly any type of system. They provide the platform and automation tools to accomplish a lot. One bad thing about Airtable is that it can be difficult to get started. Airtable does not provide their own solutions that they manage & support (yet?), but there are a number of templates to get you started. Searching for CRM will provide you with a number of examples. The Airtable community provides templates as well via the Airtable Universe. Again, searching for CRM will provide you with a number of examples from the community.

While starting with a template is great, this series is about creating one ourselves, so let’s get started by creating a new base in Airtable and choosing to create one from scratch.

We will call this base “CRM Template”, but you can name it anything. Also, choose a color, icon, and add some text in the Base Guide.

We are now in the base and will be creating our base data model. For the purposes of this post, will be creating the core tables and will expand on those as this blog series continues. We are going to create the following tables at this time:

  • Organizations
  • Contacts
  • Interactions

Organizations

Organizations represent businesses and organizations like vendors, clients, prospects, companies, governmental entities, etc that we wish to keep track of in our system. This is a core table that many/most of our other tables will have a relationship with. In this new base, we are going to rename the default “Table 1” to “Organizations”.

This table can have many attributes about an Organization, but at this point we will keep it simple. The following fields will be configured.

Field Name Data Type Description
Name Single Line Text This is the default “Name” field in the table.
Location Single Line Text A simple field to capturing the Location of the record. This field may expand into a proper set of address fields, but for now it’s just a single line text field for Location.
Phone Phone Number The primary phone number for the organization
Website URL The website domain of the organization
Organization Size Single Select Populate this with the values LinkedIn uses for Company Size
Industry Single Select Populate this with the values LinkedIn uses for Industry
Attachments Attachment Keep the default field
Notes Long Text Keep the default field. You can edit the configuration to allow for Rich Text formatting if you like
Created Created Time A system field that will document when the record was created
Created By   A system field that will document who created the record

Contacts

Contacts represent the People that we will do business with. Since this is a B2B CRM we are building, Contacts will belong to one or many Organizations. This table can have many attributes about a Contact, but at this point we will keep it simple. The following fields will be configured.

Field Name Data Type Description
Name Single Line Text This is the default “Name” field in the table.
Organizations Link to Organizations The Organization(s) that this Contact is related to. When setting this up, be sure to turn on the “Allow linking to multiple records” option.
Work Phone Phone Number The primary work phone number for this Contact
Mobile Phone Phone Number Contact’s Mobile number
Email Email Primary email address
Alt Email Email A secondary or alternative email address
LinkedIn URL URL A link to their LinkedIn Profile
Attachments Attachment Keep the default field
Notes Long Text Keep the default field. You can edit the configuration to allow for Rich Text formatting if you like
Created Created Time A system field that will document when the record was created
Created By   A system field that will document who created the record

Interactions

An Interaction represents some interaction that took place with an Organization and/or Contact such as an email, meeting, phone call, document, etc. Interactions are not Tasks / To Dos. We will work on To Dos in a future post.

Field Name Data Type Description
Subject Single Line Text This is the default “Name” field in the table.
Type Single Select Values are “Email”, “Meeting”, “Document”, “SMS”, “Call”, “LinkedIn Message”, “Article”
Date & Time Date The Date and Time that the Interaction took place
Organizations Link to Organizations The Organization(s) that this Interaction is related to. When setting this up, be sure to turn on the “Allow linking to multiple records” option.
Contacts Link to Contacts The Contact(s) that this Interaction is related to. When setting this up, be sure to turn on the “Allow linking to multiple records” option.
Attachments Attachment Keep the default field
Notes Long Text Keep the default field. You can edit the configuration to allow for Rich Text formatting if you like. This houses the interaction details (e.g. an email body, meeting notes, etc)
Created Created Time A system field that will document when the record was created
Created By   A system field that will document who created the record

We are now finished setting up the system’s core data model. Below is a representation of the schema we have so far. This image was created using the Base Schema App from Airtable.

crmbaseschema-Jul-22-2022-07-01-15-24-PM

In order to have some data in the system for the rest of this blog post series, I went to Mockaroo and used it to create some fake data. The system has 42 Organizations and 240 Contacts at this time. You would obviously seed the system with your own sample data.

In the next series, we will setup a Web Leads table and a form to obtain new leads from a website.

Scott Hemmeter

Comments

Related posts

Search Starting my No Code Journey
Airtable: Creating a CRM (Part 2) – Web Leads Search