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)

Update August 2020: the trick that enabled easy data import has been disabled. you can still use this build, but it will be more manual to set up

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 update: Facebook made this data un-extractable
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

there used to be a way to get every Facebook connection's birthday onscreen at once (via facebook.com/events/birthdays)

the most recent UI update has limited the data to the next 7 days, making it mostly useless. I have not found a workaround on desktop, mobile app, or mobile web.

their data export tools also do not include birthday data

if you think you have a workaround that re-enables this hack, email me: alec@contextify.io

#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:

¹ 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