My Homegrown, Personal CRM
A little over four years ago, I tried to get my address book mess figured out. Centralizing everything in FullContact (now known as Contacts+) was a marked improvement, but after years of living with the end result, I realized that storing contact data about a lot of people is a far cry from actually leveraging that information. I needed to do more than simply know how to contact a person; I needed to build a system that prompted me when to reach out to them.
What problem am I really trying to solve?
I didn’t start this project with a specific goal in mind — it was more of a nagging sense that I just wasn’t managing my network as well as I could. I’m a connector by nature, and I would have told you that I was generally pretty good about reaching out to folks and periodically checking in. But I wasn’t looking for “pretty good” results. To be good at my job, I should be better than pretty good at leveraging my network — for my own benefit, for the benefit of our portfolio companies, and for my friends’ benefit. (After a month of living with the system I’ve built, I can safely say: I was nowhere near “pretty good” when it came to staying in touch with folks. In many cases, it had been years since we last connected.)
After giving the problem some thought, I decided on a handful use cases I was trying to solve for:
- when traveling to a city, who should I reach out to?
- who would I recommend for product management / design / leadership / etc. if asked by someone in our portfolio?
- which women/URMs in my network are interested in serving on boards?
- who do I know who works at [Company X]? Who used to work there? Who’s on their board?
- who did I meet at that conference last year?
- who in my network is interested in [charitable topic]?
- when was the last time I touched base with [person]? Who am I overdue to reach out to?
Side note: there are some things I just can’t do
I work at GV; though not part of Google (we’re an Alphabet company), I still have to abide by Google’s security protocols. As a result, any tool that requires access to your inbox, contacts, calendar, etc. is strictly off-limits. (If this doesn’t apply to you, I’ve heard good things about Affinity and Streak.)
Recommendations:
I asked Twitter whether anyone had a good system they liked — either off the shelf or custom, and pretty quickly got consensus:
For homegrown solutions, several folks recommended Airtable (thanks Katie Jacobs Stanton and dick costolo, among others!) and Coda. I started with Airtable to see what was possible, and the results so far have been quite promising. Several people on Twitter asked for a write-up on the end result, so here we go:
Step 1: Who’s in?
I could have just imported all of my Contacts+ records; right now, my address book is about 3,500 records. But I’d noticed that over time the data had degraded — people changed jobs that I hadn’t updated. Or I’d accepted an ‘update’ to the contact record that proved to be old data. Or I’d accidentally merged two records into one. Whatever the reason, I didn’t think my Contacts+ data was pristine, and I wanted the CRM to be a current and accurate representation of who’s in my network and what I knew about them.
To get there, I created a Google Sheet for each contact source (Twitter, LinkedIn, Facebook, Contacts+, etc.), and added a column to flag which people I wanted to add to my CRM. SocialRank made it easy to export the people I follow on Twitter; LinkedIn, Facebook, and Contacts+ were all easy to export to CSV. Interestingly, each source had 3-4,000 records. One by one I reviewed and decided whether they were part of my current network or not. There were a ton of overlaps — people in my address book who I follow on Twitter and am connected to on LinkedIn, friends I know on Facebook who are in my address book, etc. When all was said and done, I had a couple thousand names. De-duped, it was just over 1,000.
Step 2: Construct the Airtable Schema
Before I could start entering records into Airtable, I had to have a rough idea of what information I wanted to capture. Automation would come later; right now, I just needed a shell.
(Briefly, a quick review of Airtable and why it was a good fit for what I wanted: Airtable files are known as Bases — short for “database”. Each base has one or more tables, tables contain records, and those records have fields. Fields can be related to each other in the same way that cells in a database can relate to each other. If you’d like to learn more, this overview of Airtable is a good starting point.)
To start with, I created some standard fields in a “People” Table in Airtable — name (first, last), email, title, company, phone, etc. — and in several cases, made the field a lookup against a separate table within the Airtable base. This means that when entering in “Sundar Pichai” into my CRM, as soon as I start typing “goo” in the Companies field, Airtable does a lookup against all entries in the Companies Table. If there’s a record that matches that string (“Google”) it prompts me to select it instead of manually typing it. When I go to the Companies Table, I can see all People who share that Company.
Similarly, I created fields that were lookups against other Tables:
- Expertise (what is this person known for? What would I recommend them for?)
- Cities (more like a region: when I’m in New York City, who’s in the area who I’d want to see?)
- Charitable Interests (is this person passionate about civil rights? diversity & inclusion? mass incarceration? gender equity?)
Step 3: In you go!
With the shell created, I added people one by one. Tedious? Yep. Powerful? Most definitely. That guy I last talked to 15 years ago who ran social media for a Presidential campaign? He’s now CTO at a professional sports franchise known for their tech acumen. I had no idea!
That woman I last interacted with 12 years ago on a non-profit we were both involved with? She just joined Google last spring. No clue. I should reach out and say hi!
On and on it went. I visited each LinkedIn profile, consolidated whatever contact info I had for them, and slowly filled in the CRM with raw data. From all of the various sources, I ended up with just over 1,000 records.
(A bonus? Thanks to LinkedIn spamming people telling them I was looking at their profile, I heard from several people before I could even reach out to say hi. Turns out, this was actually a nice, lightweight way of re-establishing contact with a number of folks.)
Step 4: Keep in touch
Once I had the people in the system, I wanted a way of capturing the frequency with which I wanted to be in touch with folks. Was this someone I wanted to ping once a year? Or someone who I should reach out to when I’m traveling to their town? A VIP I should touch base with more frequently?
To do this, I created a field called “Keep in touch” which has a few options (3 months, 6 months, 12 months). This let me sort contacts into various buckets, and then trigger specific actions based on how recently (or not) I was in touch with them.
Step 5: Interaction tracking
Knowing how often I want to reach out is fine; but it’s only really useful if Airtable knows when I last touched base. After all, knowing I want to reach out to someone every 180 days is useless unless Airtable knows that it’s been 200 days since the last time we chatted.
I tinkered with a few approaches to capturing this info; ultimately, I went with a separate table called Interactions. Records in the Interactions table have just a handful of fields:
- Person (who the interaction is with)
- Type (email, phone call, meeting, etc.)
- Date
- Notes
With this, I can log an interaction — in the background, Airtable looks to see who’s overdue (more on this below).
Step 6: Zap All The Things
I hadn’t used Zapier before. Now that I’ve started, I may never stop. Zapier is a powerful integration layer that lets you create interoperability between products; by connecting it to Airtable, I am now able to connect the CRM to outside systems and leverage existing workflow to add info to the CRM without manually entering that info.
My first “Zap” (a Zapier applet) looked for contacts in my CRM that were flagged as “Overdue” (more on that in a bit); if a contact was overdue, I wanted Zapier to create a task in Todoist, the app I use to manage all of my tasks.
The key here is to use an Airtable “View” — essentially, just another slice of data of the People table, filtered to only show contacts flagged as “overdue”. Zapier monitors that view — and each time there’s a new record in that view, it creates a new task in Todoist.
Here, Person X is set in Airtable to contact every 90 days; the last time I talked to her was 4 months ago. Airtable flags her as overdue, which causes her to show up in the “overdue” view, which creates a task in Todoist with her contact info in the task so I don’t have to look her up in the CRM:
This was cool, but what I quickly realized was that this wasn’t all that helpful unless Airtable knew when the task was completed. (Once I reached out to the person, they should no longer be considered overdue.) I tinkered a bit more with Zapier, and created a Zap to do the reverse of the first Zap: whenever I complete a CRM task, it logs a new interaction in Airtable, filling in the date of the interaction (i.e., the date the task was completed) and associating it with the person I contacted.
Just like that, I had leveraged my existing workflow (task management in Todoist) to track when I’d reached out to people in my network.
Warning: once you create a few Zaps, you find yourself looking for every other app you use to connect to Zapier. There are… a lot of integrations. Like, a lot.
Step 7: Other Interactions
Capturing completed tasks was nice, but email is where I do most of my interacting. As mentioned earlier, I can’t give Airtable or Zapier access to my work inbox; nor do I want to track every sent email — just some. I decided to use Zapier in a different way — as a bcc: alias.
I created a Zapier email alias, and set up a Zap to process incoming messages received at that address to write a new interaction, associating it with the person who’s in the To: field, setting it to the date the email was sent.
For both work and personal Gmail, I created a contact called “airtable”; now, when I send an email I want to capture in the CRM, I just bcc:airtable and Zapier writes a new record in the Interactions table in Airtable.
But what about calendar appointments? Phone calls? Texts? I don’t capture those automatically — nor do I really want to. But I did want to make it as easy as possible to record meaningful interactions…and discovered Zapier Push, a way to create Zaps tied to a Zapier Chrome extension. Now I can capture any interaction I want by filling in a few fields from any Chrome window.
As I thought through various approaches, I decided that in most cases, good enough was, in fact, good enough. Will I miss a few interactions? Probably. But if I capture most of them, I’ll have a better sense of how often I’m in touch with my network — and can more effectively decide how often to reach back out.
Step 8: A word on ‘overdue’
If you’ve made it this far, you might want to know exactly how Airtable knows when a contact is overdue. There’s a couple steps involved:
- I have a ‘keep in touch’ field in the People table that lets me choose how frequently I want to stay in touch with someone. It’s a picklist with a few options: Every 3 months, every 6 months, every 12 months.
- A hidden field (‘Keep in touch days’) converts each selection to the number of days associated with that period (3 months = 90, 6 months = 180, etc.). (Technically this is unnecessary, but I found the UI to be slightly more user-friendly to be text (“Every 3 months”) vs. simply capturing the corresponding number (“90”).)
- All interactions for a given contact are tracked via a lookup field to the Interactions table; a separate field called “Most recent interaction” is a Rollup field that looks for the most recent date from the Interactions field (see image at left).
- Finally, there’s a hidden formula field called ‘Overdue’. If ‘Keep in touch days’ is empty, do nothing. Otherwise, if the number of days between today and the most recent interaction is > the number of days in ‘Keep in touch days’, then set the ‘Overdue’ field to true. The formula in Airtable is expressed like this:
IF({Keep in touch days}=””,””,IF(DATETIME_DIFF(TODAY(),{Most recent interaction},’days’)>{Keep in touch days},“Overdue”))
As mentioned above, I have a separate View of the People table called “Overdue for catchup”; it’s a view that simply looks for any contact where the (hidden) “Overdue” field is not empty. Once a new record shows up in this view, the Zapier integration above is triggered, resulting in a new task showing up in Todoist.
Step 9: Updating contact info
I talked earlier about my reliance on Contacts+ as my contact manager. Among other features, Contacts+ looks for updated contact info for each contact, and makes it easy to accept those changes. A risk in building out this CRM system was the creation of an island of contact data which would inevitably be out of date as contacts get new titles, change companies, etc.
For now, I have Contacts+ updating Airtable contacts via Zapier. Each time I create a new record in Airtable, it’s copied into Contacts+. When the Contacts+ Assistant suggests that there’s new info for a contact, I can review it and accept it; if accepted, that info is copied over to Airtable and the record stays in sync.
This introduces a risk that information in Airtable gets silently updated with bad data — if I make a mistake in Contacts+, it’ll propagate to Airtable. I’m willing to live with that risk for now, given the convenience that this integration provides.
Further reading
As I worked on this project, a number of resources were super helpful:
- Airtable Universe, CRM templates
- Danny Crichton, A Comprehensive Guide to Using Airtable as a Personal CRM
- Tiny Blue Orange, How I use Airtable to manage clients
- Stefan Colovic, Building a ‘Personal CRM’ With Airtable
In addition, as I ramped up, I posted a few times on Airtable’s forums, and on a few occasions reached out to both Airtable and Zapier support to iron out a few wrinkles. I was impressed with response times and with how thorough each interaction was. Special shout-out to Jonathan Bowen on the Airtable forums, who helped me solve a Zapier problem. Talk about going above and beyond! He works at a webdev shop in the UK called Dragon Drop that does custom Airtable work (among other stuff); if you don’t think you’re up to the task of what I outlined above, but are interested in having it built, I’d bet he’d love to talk with you. (He has no idea I’m pointing to him; I’m just grateful for his early help, as it helped me learn more about what I could do with Airtable / Zapier.)
Cost
I chose to pay for both Airtable and Zapier; for the size of the CRM base I created, I could probably just get by with Airtable’s free plan. But I wanted some of the additional features included in the Pro plan ($20/month per user), and liked the extended snapshot / revision history available at that level.
With Zapier, I opted for the Professional plan ($50/month) — mostly to enable access to conditional logic and increased transaction quotas. I probably could have been fine with the Starter plan ($20/month) and made do without the conditional logic, but after a few complex Zaps, I can tell the additional flexibility will be worth it for me.
All in, I’ve spent less than $1,000 on a tool that’s already dramatically increased my connectivity with people who are important to me (professionally and personally). If I was doing this on a smaller budget, I could have easily reduced that to ~$240/year (free Airtable plan, Starter Zapier).
Final thoughts
Network Diversity
Having all of this information in a database makes analysis of the data much easier. Several years ago, I wrote about how biased my address book was. Everywhere I looked, my network was 80% men. After assembling my CRM, I was happy to see that in 4 years that ratio has shifted dramatically to 60% men, 40% women (OK, technically: 39.8%). Perfect? No. But much better. I use Airtable Blocks to visualize various data in the CRM, and keep this pie chart handy. It’s a good visual reminder to stay focused on improving overall representation in my network.
I have a similar Block that analyzes the % of my network that is under-represented minorities. In the past, I didn’t have a way to programmatically evaluate that, and consequently wasn’t measuring it. With this CRM project, I decided to start capturing that info manually so I could work on improving it. Today, less than 10% of my current network are under-represented minorities; that’s nowhere near where I want it to be. Now that I have a baseline (and a visual reminder in Airtable), I will be working on making progress similar to how I worked on gender balance over the last few years.
Ahh, LinkedIn. Throughout this exercise, I’m left wondering why LinkedIn doesn’t offer actual CRM functionality. It’s a directory of professionals; 94% of my network has a LinkedIn profile. (That’s quite a bit higher than Twitter, by the way: as far as I can tell, only 68% of my network is on Twitter. That surprised me.) LinkedIn could help me manage my network instead of simply capture my connections; it could let me organize those relationships, could encourage people to update their contact info so I have a current way of reaching out to them… this feels like an area absolutely ripe for LinkedIn to branch into. At a minimum, if Zapier and LinkedIn could talk to each other, I could get new job titles / companies into Airtable directly, and work that into my existing workflow. For now, LinkedIn is an island.
Was it worth it?
Each Monday morning, my admin and I sit down and discuss the next few weeks. Last week, Inga suggested that my Airtable project was working a little too well: she’d just had to schedule a lunch three months out.
I’m just a few weeks into this using the CRM, and the response rate on my proactive outreaches was much, much higher than I’d expected. I’m feeling more connected to my network — more up to date on what people are up to, more plugged into what’s going on. That feels like a worthwhile goal in and of itself; but it’s also led to some highly valuable next steps for the people I was talking to. I’ve long admired Tim O'Reilly’s mantra to “create more value than you capture”, and this project has already helped me do exactly that for people I care about. It’s a good start.
I also learned several new tools. I’d not used Airtable or Zapier before. I’m in love. And I learned about others, too: as part of this exercise, I got back in touch with former co-worker Lane Shackleton, who gave me a demo of his own approach to solving this problem with Coda, which was at least as impressive as what I ended up building in Airtable. (Speaking of which: Satya Patel recently shared his version of this concept — a “self-driving Rolodex” — using Coda here.) As I move past construction of the CRM to using it, I’m already starting to think about other problems I can tackle with these tools.
In publishing this, I’m looking forward to hearing other ideas of how this could be extended. I imagine there are a number of clever ideas that readers will have (or have already implemented) — let me know what you come up with!
A final thanks to several people whose input and feedback helped shape my thinking on this project, and helped me think through what I was really trying to solve for: Hillary Pitts, Travis Todd, Erik Heels, Karen Wickre, Dick Costolo, Cissy Hu, and Robin Klau.
