make your own personal CRM (for free in 20 minutes)

tl;dr: don't miss important personal moments with your own personal CRM

cost: $0

build time: 20 minutes (MVP)

Hey friends,

If you came here from tech twitter, you're almost certainly aware of the ever present request for startup/meme about personal CRMs. If you came here from outside that bubble, the short version is you could use software to be a better friend¹.

Today we'll be building a personal CRM. You don't need to know how to code to do so - though we will be using a couple snippets I've written

Once set up, it will have 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. Finds friends who work at particular companies (e.g. for job hunting)
  4. 0 manual data entry (after initial 20m setup)

#2 relies on you keeping a relatively up-to-date calendar. If that doesn't sound like you, you can choose to not implement that portion (or skip this entire exercise).


#1 - go get birthdays

#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's. 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 etc etc 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 run the script, let's get you set up with the dependencies.

if you don't have pip (a code library manager), start by running this:

curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py && python get-pip.py

then use pip to install the HTML parsing and calendar libraries:

pip3 install beautifulsoup4 icalendar

then, you can use my script - set it up with:

git clone git://github.com/alecbw/Extract-Birthdays && mv fb_birthdays.html Extract-Birthdays && cd Extract-Birthdays

then execute it with:

python3 parse_fb_birthday_html.py

cool cool cool you should have birthday data on all your Facebook friends (for those 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 work info

#2.1 - let's 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 goofy .ics file. let's convert it to a useful .csv

you can use my script. download it with:

git clone git://github.com/alecbw/Google-Calendar-to-CSV  && cd Google-Calendar-to-CSV

move the .ics into that folder and 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.

nice nice nice now you should have the two code-parsed CSVs plus the one downloaded from LinkedIn. let's smoosh them all together.


#4 - CSVs, assemble!

If the article tag wasn't a giveaway, the final product lives in Google Sheets. I've created a template you can use here

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

ok so you've got your sweet looking personal CRM all set up. but wait. you're not done. let's make sure future calendar events are added.

#6.1 - let's 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 - 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:

Zapier Field Name Zapier Field Value
Spreadsheet [you’ll have to manually select]
Worksheet [you’ll have to manually select]
# =(offset(indirect(address(row(),column())),-1,0))+1
Event Name {{37058374__summary}}
Description {{37058374__description}}
Type =JOIN("",ARRAYFORMULA(IFERROR(REGEXEXTRACT(PROPER(offset(indirect(address(row(),column())),0,-2)), {"Call";"Drink";"Coffee";"Brunch";"Lunch";"Dinner";"Gym";"Shift";"Work";"Hold";"Birthday";"Flight"}))))
With
Location {{37058374__location}}
Duration (min) {{37058374__duration_minutes}}
Start Time {{37058374__start__dateTime_pretty}}
Finish Time {{37058374__end__dateTime_pretty}}


¹ There are plenty of valid reasons why using software to manage your friendships is non-ideal (or, as described by others, unempathetic). I am of the opinion that it is worthwhile to have something to counterbalance my forgetfulness. To each, their own.

Thanks for reading. Questions or comments? 👉🏻 alec@contextify.io