icon_home Created with Sketch.

Personal CRM

Concept

I wanted a way to track how I was communicating with friends, and get a view into how long it had been for any particular individual.

Motivation

Life gets busy, things come up. They get busy. And suddenly months or years go by. Worse was I also wasn't paying attention to potential new friends. However at some point, I learned about how sales teams use customer relationship management software to keep track of clients and sales. I thought that I could make a facsimile for my needs.

Technical Conceit

I like to use conceptual projects to spur learning new technologies. This time I asked myself could I jerry-rig Airtable (a consumer-friendly version of a cloud database) with iOS shortcuts?

Why that tech stack?

I deconstructed this project into three parts.

  1. Data entry
  2. Data storage
  3. Data compilation and automation.

The first lives or dies with me keeping up with tracking. (I explored third-party apps to track automatically, but not comfortable opening up all of my communications to a company. Also, it would never be expansive enough to track all my platforms.) So I needed to manually enter the data, and if there’s one spot that would be best, it’d be my phone. Where most of my communication happens anyways. Shortcuts is cool because it’s more native than a webpage but kept me out of XCode.

Data storage could have been anything. But I was curious about the new fangled shiny thing people were starting to use. Also it’s API structure was surprisingly well-documented and built out. (Plus I found someone had already set up some Airtable based shortcuts for iOS.)

As for part three, I was hoping Airtable would be more robust. But they only had simple email triggers. But I realized Shortcuts could cover the gap. More on that later.

screenshot of the phone home screen with the shortcut available screenshot of entering in a name screenshot of entering in an interaction type

Data Entry

I didn’t need anything exotic. Name, type of interaction, and date stamp. So the first shortcut pulls up my contact book, asks for the platform type (e.g. email or video or in-person hang) and that’s it.

I added some quality-of-life features. Like by default it shows the last 12 people contacted. Day-to-day, I mostly talk to my inner circle, so it’s convenient.

map of all the shortcuts and how they relate to each other

Aside: Linking Shortcuts

I started with looking at the shortcuts community for what was already out there. I would discover they were doing some crazy shenanigans. A major surprise was that they were exploiting the ability of a shortcut to run other shortcuts, and passing data back and forth. Effectively turning shortcuts into discrete functions. It cleaned up the code considerably. There was even a “package” manager which was insane, and overkill for my needs. But this work acted as the foundation for interacting with Airtable.

screenshot of airtable table

Data Storage

Airtable seems to be trying to be consumer friendly. A burly cousin to say Google Sheets? Anyways, I mostly just needed a spreadsheet, and it had that in spades. The drawback to using a consumer-friendly database? The moment I needed mySQL’s “distinct” functionality. A key output is figuring out how long it has been since I contacted people. In a traditional setup, I would just query for the list of people and their newest date entry. But Airtable’s grouping was purely visual, the API call still pulled all the entries. And Shortcuts is way too fussy to run loops of objects. (It can, but it’ll fight you at every step.) So I decided to streamline the input side rather than the output side.

I split Airtable into two tables. One would keep every interaction entry while I would treat the other as a table with distinct names. Shortcuts does the heavy lifting here as it checks for existing entries and updates the distinct tables accordingly. It’s admittedly a little fragile as I don’t verify edge cases such as two friends with the exact same names. But should fit my needs well enough.

screenshot of code loop to create the email

Data Compilation and Automation

From there, I could do the time tracking. Airtable has a concept of views which effectively queries on the dataset. So using the distinct table, I put people into time windows. One week with no contact, one month, one quarter, and past a quarter.

The last set of shortcuts exploits Shortcut’s automations. Every Sunday at 7am, it triggers the email formatting work. It pulls data from those four views, formats it into an email, and sends it to my inbox.

screenshot of the email Shortcut sends

Closing Thoughts

Man, this was so satisfying to make. It helps me pay better attention to people I want to take care of. It fits my lifestyle. It’s right on the phone, and just takes a few taps. The email and tables act as a nice artifact. And can serve as a data source for future manipulations.

Weird, do you have anything app-like?

I got the gist now.

612-643-1555
phong@phonghtran.com

This reminds me of…

Back to Home