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 NameData TypeDescription
NameSingle Line TextThis is the default “Name” field in the table.
LocationSingle Line TextA 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.
PhonePhone NumberThe primary phone number for the organization
WebsiteURLThe website domain of the organization
Organization SizeSingle SelectPopulate this with the values LinkedIn uses for Company Size
IndustrySingle SelectPopulate this with the values LinkedIn uses for Industry
AttachmentsAttachmentKeep the default field
NotesLong TextKeep the default field. You can edit the configuration to allow for Rich Text formatting if you like
CreatedCreated TimeA system field that will document when the record was created
Created ByA 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 NameData TypeDescription
NameSingle Line TextThis is the default “Name” field in the table.
OrganizationsLink to OrganizationsThe 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 PhonePhone NumberThe primary work phone number for this Contact
Mobile PhonePhone NumberContact’s Mobile number
EmailEmailPrimary email address
Alt EmailEmailA secondary or alternative email address
LinkedIn URLURLA link to their LinkedIn Profile
AttachmentsAttachmentKeep the default field
NotesLong TextKeep the default field. You can edit the configuration to allow for Rich Text formatting if you like
CreatedCreated TimeA system field that will document when the record was created
Created ByA 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 NameData TypeDescription
SubjectSingle Line TextThis is the default “Name” field in the table.
TypeSingle SelectValues are “Email”, “Meeting”, “Document”, “SMS”, “Call”, “LinkedIn Message”, “Article”
Date & TimeDateThe Date and Time that the Interaction took place
OrganizationsLink to OrganizationsThe Organization(s) that this Interaction is related to. When setting this up, be sure to turn on the “Allow linking to multiple records” option.
ContactsLink to ContactsThe Contact(s) that this Interaction is related to. When setting this up, be sure to turn on the “Allow linking to multiple records” option.
AttachmentsAttachmentKeep the default field
NotesLong TextKeep 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)
CreatedCreated TimeA system field that will document when the record was created
Created ByA 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.

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.

4 thoughts on “Airtable: Creating a CRM (Part 1) – Base Data Model

  1. Pingback: Airtable: Creating a CRM (Part 2) – Web Leads | Scott Hemmeter

  2. Pingback: Airtable: Creating a CRM (Part 3) – Opportunities | Scott Hemmeter

  3. Pingback: Airtable: Creating a CRM (Part 4) – Convert the Web Lead | Scott Hemmeter

  4. Pingback: Airtable: Creating a CRM (Part 5) – Task Management | Scott Hemmeter

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s