Build your CRM in an afternoon with Google Scripts

We're expanding our private beta at segment.io, and in my dual frontend dev/account manager role I need a time-saving, flexible CRM. I've tried Salesforce and Highrise in the past, but neither one was a pleasure to use - I burnt out on manual data entry pretty quick. Our new solution was quick to build and solves all of my current problems.

As a startup that's still figuring things out, there are two main things we need from a CRM:
1. Flexibility - I need to choose (and constantly revise) which user info is stored and displayed. On a day-to-day basis we want to know when beta testers last logged in. But next week we might decide to send our beta testers fruit baskets, and suddenly physical mailing addresses will be a very important field.
2. Automation - Most of the data we need is gathered from various web endpoints:
 - compete.com  for an estimate of monthly actives users
 - an internal api  for most recent login date, current events/day,...
 - ... etc ...

Another tool worth mentioning is Intercom.io. It goes a step further than Salesforce and Highrise because it collects a bunch of data for you, but honestly I'm not interested in our signups' profiles on vimeo, Google+ or Foursquare... it seems a bit creepy. If you sign up, I want to know (a) what is your business about and (b) what analytics solutions are you using right now, so that we can better address your analytics needs.

So what to do?

A few weeks ago my friends at IronSpread got me thinking about a new approach: keep a list of leads in a spreadsheet, and use scripts to automate data collection about those leads.

Flexibility? ✓ It's a spreadsheet.
Automation? ✓ You can do almost anything with Google Apps Scripts.

Google Apps Script for Google Docs is like Visual Basic macros for Excel. Except you get all kinds of magic. The most useful components for us are:

1. Cell manipulation:
        sheet.getRange(2,1).setValue('Toasters');
  sets the B1 cell to have text 'Toasters'.

2. Fetching external data:
   var response = UrlFetchApp.fetch('https://segment.io/secret');
   Utilities.jsonParse(response);
    allows us to retrieve info from our internal APIs, compete.com, and our beta tester's websites.

Now whenever someone signs up with an email@interesting-domain.com, the spreadsheet CRM goes ahead and reads
 - the business's website title meta-tag (so we know what their business does)
 - estimated monthly active users
 - what analytics solutions they currently have in place
This helps us write well-suited introductions to each customer, and gives us a great feel for who's interested in beta testing.

Google Apps Script also gives you access to Gmail, which seems promising for a CRM, though I haven't tried using it yet.

If you want to get started and build your own, go to docs.google.com, create a new spreadsheet, and click Tools : Script editor. Later you can run your script using Tools : Script manager. The documentation is also useful.

Follow @reinpk