Step by Step Guide to Building a Personal CRM from Scratch

Founders and VP’s inevitably miss birthdays and can forget to touch base with friends, investors, and referral partners. 

We’re human, after all.

In the below article, we’ll walk through setting up your own ‘personal CRM’ to help stay on top of important events and friendships. It will include the following features:

1. Identifies all people with birthdays in the upcoming 14 days
2. Highlights friends you haven’t seen in a while
3. Generates a raw data stream of your events that you can analyze if you want
4. Finds friends who work at particular companies (for example, for job hunting, networking, or prospecting).

Heads up: this article is a bit on the technical side. If you’re not technical, you can manually track a lot of this data as well. It will just take a bit longer. 

#1 – Get birthday data

#1.1 Head on over to facebook.com/events/birthdays, which shows upcoming birthdays.

If you scroll all the way to the bottom, it will load the next month. Keep scrolling until you have every month loaded on the page.

#1.2 – Right click and select Inspect Element

#1.3 – Right click the top most element html lang=en and select Copy Outer HTML

#1.4 – Paste that into a text editor (on Mac you have TextEdit) and save as fb_birthdays.html

#1.5 – Now let’s process the HTML and extract the birthdays

Open a new terminal session in the folder you just saved that HTML in.

#1.6 – Download the parsing script

(Before you start, let’s get you set up with the dependencies. if you don’t have pip, get it here)

pip3 install beautifulsoup4 icalendar

You can use my script – download it with:

git clone [email protected]:alecbw/Extract-Birthdays.git && mv fb_birthdays.html Extract-Birthdays && cd Extract-Birthdays

Then execute it with:

python3 parse_fb_birthday_html.py

You should have birthday data on all your Facebook friends (who include their birthdays). The upcoming 7 days will be represented literally (i.e. instead of month/day/years, it’s Saturday) so make sure to go into the CSV and change that.

(Now that you’ve finished doing this, you may ask, “Why not just use Facebook’s data export tools?” They’re intentionally unhelpful and don’t include birthday data)

#2 – Get employer info

#2.1 – Go to LinkedIn and export that too.

Go to their member data portal, select Connections, and hit Request Archive

#2.2 – There’s a 10-minute wait because LinkedIn is also intentionally unhelpful. Go make a coffee or feel free to start the next section. Eventually, they will email you a link, it will take you back to the original page, and you’ll click Download Archive.

#3 – Get historical calendar data

#3.0 – If you have multiple personal calendars (e.g. on one iCal and one on Google Calendars), I recommend consolidating them to one Google Calendar first.

#3.1 – Great, let’s go to Calendar Settings

Select Import & Export on the sidebar, and select the Export button

#3.2 – Now we have a .ics file – let’s convert it to a useful .csv

You can use my script – download it with:

git clone [email protected]:alecbw/Google-Calendar-to-CSV.git  && cd Google-Calendar-to-CSV

Then execute it with:

python3 convert_ics_to_csv.py

The script will convert the timestamps to your local timezone. it does not account for daylight savings or days you were traveling, because those things are hard.

Now you should have the two code-parsed CSVs plus the one downloaded from LinkedIn. Next, we’ll combine them into our personal CRM

#4 – CSVs, assemble!

The final product lives in Google Sheets – I’ve created a template you can use here

An example of what the rows look like populated is provided below. Once you’ve set it up, feel free to rearrange the columns and/or add additional functionality.

You’ll want to copy and paste the three CSVs into the respectively named tabs, aligning them with the existing headers

(you can CMD+A and CMD+C select the entirety of the CSV and paste into the cell labeled Paste Into This Cell)

#5 – Add some friends

In the main tab, add some Full Names and watch the rest of the columns populate! Past the first two rows, you’ll need to copy and paste (or drag down) the formulas once you get past the green/orange cells. The VLOOKUPs are mostly populated by Full Name; the Nicknames column is used for VLOOKUP’ing the Saw Last / Events data.

#6 – Let’s make this a recurring thing

Now you’ve got your sweet looking personal CRM all set up. You can stop here if you don’t keep a personal calendar. To ensure the Saw Last data continues being relevant, let’s make sure future calendar events are added.

#6.1 Setup the Zapier Google Calendar -> PRM connection

(it fits in the free tier if you have <100 events per months after filtering)

Set the trigger to Event Ended in Google Calendar.

Optionally: add a filter if you want to ignore recurring events (e.g. daily workout times). Events that don’t trigger the filter won’t count toward your Zap total.

#6.2 – Add field values and formulas

Below I’ve included the values to put in each field for the write to Zapier. Some require formulas to be pasted in; they will run automatically after the Zap is set up.

You can copy and paste the below Field Values:

That’s it! You’ve now built your own personal CRM.

I recommend pairing the personal CRM with a tool that reopens it on a schedule called TabSnooze. That way, you’ll have a nice little reminder to look through once a week or so. Feel free to add any other data sources and connections you like – you can reuse the VLOOKUP logic in the Main tab.

Requisite disclaimer: Please be sure to check the terms of service for each of these vendors, as they evolve and change over time. This article is strictly intended for entertainment purposes only. I’m not a lawyer and I don’t play one on TV.

About the author: Alec is a Growth Engineering Consultant specialized in automating startups’ user acquisition.  You can read more on his site, and follow him on Twitter.