Sunday, June 24, 2012

Build your CRM in an afternoon with Google Scripts

We're expanding our private beta at, 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:
 -  for an estimate of monthly actives users
 - an internal api  for most recent login date, current events/day,...
 - ... etc ...

Another tool worth mentioning is 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:
  sets the B1 cell to have text 'Toasters'.

2. Fetching external data:
   var response = UrlFetchApp.fetch('');
    allows us to retrieve info from our internal APIs,, and our beta tester's websites.

Now whenever someone signs up with an, 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, create a new spreadsheet, and click Tools : Script editor. Later you can run your script using Tools : Script manager. The documentation is also useful.


  1. It's really powerful indeed. We use Google Apps Scripts to pull calendar events from all employees into a spreadsheet and group by customer (determined by subject naming convention) and calculate / sum hours spent. No-friction time tracker :-)

  2. Peter, I love the idea of using scripts to personalize messages to your users. It's along the same theme of lifecycle marketing where your message is changed based on the usage behaviour of your customer.

    I also like the idea of automation because that's exactly what my company, Ecquire, does - we automatically capture contacts to your CRM with 1-click.

    One thing that you could do to improve how you manage leads is to introduce a sort of bucketing system. Define different tags for different types of contacts. In my case, I use: lead, warm lead, signup, paying customer, and evangelist. Have a predefined rule in how you would evaluate contacts. By applying these tags, you will be able to focus more of your efforts on the leads who have the most potential and relevance for your service.

    Look forward to hearing more about your CRM.