A Comprehensive Guide to Using Airtable as a Personal CRM

Update: I published a copy of this base on the Airtable Universe, check it out.

People are annoying (I will add at this point that this is a recurring theme on this blog). They are constantly changing jobs (thanks Gen X!), changing locations (thanks millennials!), and changing messenger apps (thanks Gen Z!). Like many of my friends, I have struggled to just keep up-to-date with people while maintaining my sanity as a knowledge economy worker.

Over the last decade, I have tried many solutions to this problem, from paper and spreadsheets to software such as Rapportive, Contactually, Trello, among many, many others.

None of these solutions has worked out. The reasons often overlap, from being a jumbled mess to being just too hard to update (especially on mobile). But frankly, I have not made any progress in tracking people better today than I was doing several years ago.

Recently, I moved my entire CRM database over to Airtable. It’s been a long learning process, but my current setup is the best I have ever had, and so I thought I would share what has worked for me, and how I set up my database.

Before we begin, an Important Disclosure: I am an investor in Airtable through my former VC role at CRV. I am writing this because I think it is helpful for others, but feel free to discount everything I say (minus my complaints about millennials — that’s completely legitimate no matter how much I am paid by Baby Boomers to say it).

Thesis on Personal CRM

Before I dive into the how-to, I want to talk a bit about how I think about networking and why I track people. The reality is that I communicate with roughly 2000-4000 people every year — just professionally. One week last month, I had 37 coffee meetings in 5 days (I single-handedly underwrite the third wave coffee café expansion in Manhattan). My job as a VC up until a few weeks ago was to talk to as many founders as possible, and make sure they have the connections and resources to succeed. Keeping track of what people are doing, what they’re looking for, and when I last talked with them is the linchpin to doing this job the best I can.

Building a high-quality network is really about taking advantage of organic growth — friends always want to connect you to other friends, and so being authentic, being helpful, being friendly, has been the key to meeting great people I have found.

With so many people and meetings though, I struggled to remember who I had talked to recently. In some cases like close friends, I really don’t need to track it, but in others, I need to be far more proactive in keeping a relationship going. That’s human nature, and completely okay. But I knew I needed a system to be able to keep track of all of these details.

This might be controversial, but I don’t believe there can ever be a truly automated system to networking (if you disagree, feel free to leave a comment below). I think we all have our own idiosyncrasies and unique views on what we want out of networking and what we need to track. I found that automated systems would always just screw up — a simple text exchange would count as a “contact”, when I really only wanted to count in-depth conversations. I needed a system that worked out of the box, but was also completely customizable for my needs. That’s where Airtable comes in.

Airtable Setup and Structure

Airtable is best described as “RDBMS meets Spreadsheets in a UX-friendly way” (I know what you are thinking: yes, I have a long career ahead of me as a brand marketer). The software organizes data into “bases”, which can have a series of customized “views” to look at that data. Airtable’s big strength is the ability to connect data records together through links – very similar to JOINs in SQL.

Ok, that might not make intuitive sense, so let’s just dive in and learn.

First, create an account on Airtable, and create a new base from scratch (you might also download one of the native apps if you prefer that to the browser). I called my base Relationships, but you can call it whatever you want.

To jump ahead a bit, here is the final destination (with people’s individual names and companies blurred):

I currently track 819 people according to Airtable, so obviously every person I meet doesn’t go into the database, and I do clear it out once a year to focus on the people I really have a relationship with. This is an arbitrary, imprecise process. C’est la vie.

Airtable’s intelligence comes from being able to define the kinds of data that exist in each column (which affects the UI you can use to update, so for instance, a date entry will get a date picker, etc.) In this “People” table, I have created the following columns:

1. Name (Single line text and also the “id” column of the table)

2. Contact Frequency (Single select)
   

3. To Chat? (Checkbox)

4. Last Contact (Date)

5. Chat? (Formula - described below)

6. Company (Link to Companies table, also described below)

7. Occupation (Multiple Select)
   

8. Location (Single Select)
   

9. Notes (Long Text)

10. Contact Every? (Formula - described below)

(Some other columns are created as part of the links that will be described later)

The options in the Occupation and Location columns are entirely customizable – just pick what options you want. Airtable also automatically adds new options if they are not already available in the list when you type them in. This way, the structure of the data can evolve organically without any sort of SQL migrations required. I color a handful of options since I spend the most time in a few cities and a few types of people. As always, feel free to color all of them, some of them, or just turn colored options off.

Daily Activity

At the end of each day, I go into Airtable and update the Last Contact field with today’s date for any person I have talked to that day (this is sort of like end-of-day journaling). Airtable has a shortcut for today’s date (Command-; on Mac) that makes it super easy to update this quickly. I also sometimes do this on mobile while at a coffee meeting – Airtable’s native app is particularly appealing here.

From there, the real magic comes from the “Chat?” column and a hidden column called “Contact Every?”. This is a formula that takes into account the last time I chatted with someone and how often I want to connect with them (the Contact Frequency).

The formula for the “Contact Every?” column is:

IF({Contact Frequency} = "Weekly", 7,
IF({Contact Frequency} = "Lead", 0,
IF({Contact Frequency} = "Monthly", 30,
IF({Contact Frequency} = "Quarterly", 90,
IF({Contact Frequency} = "Annually", 365,730)))))

And the formula for the “Chat?” column is:

IF({Last Contact} = "", DATETIME_DIFF(TODAY(), CREATED_TIME(), 'days') - {Contact Every?},
DATETIME_DIFF(TODAY(), {Last Contact}, 'days') - {Contact Every?})

Essentially, you are saying that if you want to talk quarterly with someone, then you want to talk to them every 90 days. Then you compare the last time you chatted with someone to how often you want to chat with them to see whether you are over or under the number of days. So if I talked to someone 48 days ago and want to chat with them quarterly, I have 42 days to go until I next need to reach out.

The magic is that anyone who has a “Chat?” column value greater than 0 is someone who I am overdue to chat with. Airtable makes it easy to filter for values greater than 0, which I have moved into a new view I call Overtime.

(As an aside, you can create as many views as you want of your data. Each view is just a window onto the data — so you can have views that hide certain fields, or that are sorted differently (I.e. a pipeline view). Airtable has several types of views as well, from tabular to a Trello-like kanban view. Feel free to create as many views on your data as you find them useful.)

How do you determine the contact frequency? For me, I try to make it as organic as possible. Best intentions are your worst friends here frankly. It’s always going to be hard to connect with acquaintances in cities you rarely visit. So my suggestion would be to very clear-eyed about how often you can realistically connect with someone. In general, I find we all overestimate how easy it it to stay in sync with others.

One other note is around “Lead” and “TBD”. I track these with their own views, so for the most part, I don’t want them to be part of my flow of people. Leads are people that I have been introduced to, but have not met. TBD is for people I have met, but still trying to get a handle on how often we are going to chat in the future.

Once the data is in Airtable, we can do all kinds of classic SQL commands - we can order by different columns, we can filter based on certain criteria, etc. So if a founder asks me to recommend a designer living in San Francisco, I can filter for precisely that and get a list of people that match that criteria. You can also Group By different fields, so I can, for instance, group founders by geography sorted by when I last talked with them. Slick.

Now, I have had some pushback from friends around the time it takes to update this data. Searching and updating the date for 10-30 people per day seems like a lot. My view is that if you are taking an hour to meet with someone, it better be worth the 10 seconds it takes to update the record in Airtable (and maybe you shouldn’t take a meeting where a little overhead is a problem). I have found that updating the records takes no more than 10-15 minutes per week. Given that my job in many ways is tracking people, I don’t think that is a huge demand on time, but it does require persistence around processes.

Other Tables

I created a Companies table so that I can track all the startups I was seeing as a VC. That table looks like:

1. Name (Single Text)
2. Type (Single Select for VC Firm, Startup, non-profit, etc.)
3. People (Link to People, with multiple entries option checked)
4. Themes (Single Select, for matching companies to my personal investing themes)
5. Last Round (Single Select, for Series A, B, etc.)
6. Investors (Link to People, with multiple entries option checked)
7. Location (Single Select, similar to column from People table)
8. Notes (Long text)

I don’t keep company data that well up-to-date – I found it nice to track a bit, but I am relatively lenient here.

At this point, you can create more tables to track things you want. I track intros of people to me, so I have a table that tracks Intro From (link to People table) and Intro To (also link to People table). I have a table tracking offers that people have made to me and vice versa, and I track conferences that I am attending and which of my friends are also attending). Feel free to experiment and don’t be afraid to track more things.

The Zero Data Problem

Ok, let’s say you are trying to create a personal CRM for the first time. Getting the columns and tables setup is easy, but where do you get the initial data?

There is no easy answer here. I have been tracking for years, so it has accumulated over time. My general advice is to just start tracking today and worry about it moving forward. This puts a bit more administrative burden on the user upfront, but it has the best chance of actually succeeding. Within a few weeks, you will already have a pretty solid database of your contacts, since invariable the people you talk with most frequently are the ones you probably care about the most (or one would hope).

You could also look at your calendar for the last six months and sort of import the data manually, or maybe hire someone on Upwork to do this. Airtable’s interface is sufficiently fast that this whole process would take probably a good day to do, but obviously that’s pretty damn boring (like watching C-SPAN Friday evenings, not that I do anything like that personally of course). Which is why I recommend just updating going forward rather than spending that kind of time looking backwards.

For the more programming fluent, Airtable does offer a full API, so you can import data from other apps using code. For instance, you could use APIs to read through Gmail and copy names into Airtable. In general, I think the manual approach results in a better final dataset, but feel free to read up on the docs and see if there is a model that works for you.

Conclusion

It’s easy to put a huge amount of attention on software in any problem space. A personal CRM isn’t a magic box that suddenly makes networking effortless. The goal is to take your mind off a bunch of details, so that you can just be yourself when talking with people and worry about all of these details later.

Some people find this really cynical by the way. “If you are friends with someone, why is it so hard to stay in touch with them?” (Hmmmmmmm?!?). The reality is that we are all busy. I would rather stay in touch with people and be intentional about it, rather than just completely forget about people over time. In my mind, it is far more negative to just not care than to just use a piece of software as a memory aid.

This is also a reminder that the setup I have is what has worked for me, and the kinds of things you care about might be worth tracking for you. For instance, I don’t track people’s pets, but maybe you love pets, and this is a natural way for you to connect to people. In that case, maybe a column to describe the pet would be helpful. I don’t track people’s personal interests too much since I generally remember it, but if that was worthwhile, it might be worth a column as well. Experiment! Airtable makes it super easy to add and delete columns and data over time, so there is very little risk to trying new things.

I use this database as a personal CRM, but Airtable is designed for teams in mind. I can definitely see something like this working for a small firm, if you can agree on what to track and how to do it (and then people actually updating it!). Pipelines could all be based here, for instance.

That’s it - if you have any other feedback or ideas or thoughts, feel free to leave a comment below.

Photo by Orion Montoya used under Creative Commons