The CRM at York: a technical overview

By Stephanie Jesper, Teaching & Learning Advisor

Last month, Michelle Blake wrote about our Customer Relationship Management system which we use in the Relationship Management Team at the University of York. She explained our reasons for creating our own system, and the benefits that system has given us. What she left to me to explain was the mechanics that lurk beneath its shiny surface. If you're the sort to quake at the sight of spreadsheets, you may wish to look away now.

It's fair to say that I'm obsessed with spreadsheets, so it was inevitable that my proffered solution to our CRM Question involved one. But it's not all spreadsheets. It is, though, heavily reliant on the Google suite of applications that we use here at York. The image below shows the fundamental components:

Our CRM system, in bits.

Let's take each component one-by-one.

CRM Form

The front end of the system is based upon an old-version Google Form. The problem with Google Forms is that they are very vertical, especially when you have a list of options. We wanted something a little more compact, so I basically copied the script from the Google Form and tinkered with it. This was a lot easier to do with the old-style forms than it is with the new-style forms, so if you want to do something like this, you might be better finding an old-style form that you can copy and modify. 

Filling in a CRM Form

The first modification I made was to run the options in columns, thereby saving considerable vertical space (you can imagine how long the page would have been with every option on its own line). There are also a considerable number of hidden questions that only display if certain options are selected. For instance, there's an option in the "Department" dropdown for "Multiple departments" that reveals a checkbox list of all departments. What's more, if you select a department for which we have an action plan, a checkbox list of actions is revealed so that progress on those actions can be logged. In total there are forty questions on that form, but only eleven are displayed by default. 

This toggled content is controlled using jQuery and style sheets. We also have some jQuery controlled autocompletion in the freetext "Other department" field. This searches against the default department list to mitigate against alternate formulations of existing department names.

The problem with hacking and modifying a Google Form is that you then need to host it somewhere. Initially we just put the CRM Form on a local drive, but now we have some password-protected webspace which means we can access it wherever we have a web connection. 

CRM Form (Responses)

When a CRM Form entry is submitted, it is fired off as the Google Form entry that it is, and gets added to a Google Sheet (as is the traditional output of a Google Form). The imaginatively titled CRM Form (Responses) spreadsheet is the heart, lungs, liver, stomach, and brain of the CRM (it's basically a giant spreadsheet-y haggis). It's a messy thing, reflecting the evolution of the system, and I hope to go in and simplify things at some point next year. 

The first thing that happens in this spreadsheet is that the data from the form gets tidied up using various arrayformulae. Comma-separated multiple-choice fields get pulled apart, mixed in with values from elsewhere, and are reconstituted as appropriate. The data then gets transformed into a layout that can be read by an Awesome Table (we'll come to that later). A second sheet counts up the number of matches for each form item to give a quantitative overview of our engagement. This is then broken down by department.

CRM Query

More in-depth analysis is carried out in our CRM Query spreadsheet which uses a Google Sheets query function to interrogate the data held in the CRM Form (Responses) spreadsheet. The query output can then be used to populate visualisations of our engagement like some of those we showed in the previous post. For example, here's some of our transactions by type:

Transaction types visualised via the CRM Query spreadsheet

CRM Summary

I mentioned Awesome Tables earlier, and this is where they come in: the CRM Summary is a user-restricted Google Site with an embedded Awesome Table from which we can see the transactions that have taken place. It looks something like this:

A couple of recent transactions in the CRM Summary

Like the query function in CRM Query, the Awesome Table lets us interrogate the CRM Form (Responses) spreadsheet as a database. But it does it in a way which looks a lot prettier and which can be used by more than one person at once. In the above example, I've limited to only those transactions with which I've had some involvement. There's even an edit button which allows me to revise any of the information I submitted.

CRM Alert

Going to a website or a spreadsheet to see what people have been doing is all very well, but it's a bit of a chore. If only we could set the spreadsheet up to notify us by email when something interesting happens... Well with Google Sheets we can. Each member of the team has their own CRM Alert spreadsheet which queries the CRM Form (Responses) sheet. Individuals can tailor the query to match search terms such as their department or area of interest. A Google Script keeps an eye on the spreadsheet and if a new entry gets returned by the query, an email gets fired off containing all the relevant details. 

Here's the code we use - if you're using a device with a pointer you can hover for a few bonus annotations:

function runAlert() {

  SpreadsheetApp.flush();

  var sheet = SpreadsheetApp.getActiveSheet();

  var from = sheet.getRange("Database Query!I1").getValue();

  var tot = sheet.getRange("Database Query!J1").getValue();

  var to = sheet.getRange("Database Query!J1");

  if(from!=tot) sendEmail(from); 

  if(from!=tot) to.setValue(from); 

}

function sendEmail(from) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var line1 = sheet.getRange("Database Query!A"+from).getValue();

  var line2 = sheet.getRange("Database Query!B"+from).getValue();

  var line3 = sheet.getRange("Database Query!C"+from).getValue();

  var line4 = sheet.getRange("Database Query!D"+from).getValue();

  var line5 = sheet.getRange("Database Query!E"+from).getValue();

  var line6 = sheet.getRange("Database Query!F"+from).getValue();

  var line7 = sheet.getRange("Database Query!G"+from).getValue();

  var line8 = sheet.getRange("Database Query!H"+from).getValue();

  var line9 = sheet.getRange("Database Query!I"+from).getValue();

  

  var email = sheet.getRange("Database Query!B1").getValue()

  var subject = "CRM Alert";

  var body = "An entry matching your search terms has been added to the CRM:\n\nDATE:\n"+line1+"\n\nNAME:\n"+line2+"\n\nDEPT:\n"+line4+"\n\nCONTACTS:\n"+line5+"\n\nTRANSACTION:\n"+line6+"\n\nAIMS:\n"+line7+"\n\nCATEGORY:\n"+line8+"\n\nDETAILS:\n"+line9 ;

  

  MailApp.sendEmail(email,subject,body); 

}

The first function checks to see if there are any new transactions, and the second generates the email before firing it off. Obviously the cell references are rather tied to our specific setup, but the basics are there should you want to try something similar.

The result is an email that looks something like this:

An entry matching your search terms has been added to the CRM:

DATE:

Mon Dec 12 2016 00:00:00 GMT-0000 (GMT)

NAME:

Steph Jesper

DEPT:

External Relations

CONTACTS:

Father Christmas

TRANSACTION:

Other: Christmas List

AIMS:

CATEGORY:

Collections,Liaison and Relationships: General

DETAILS:
I sent Father Christmas my Christmas List. This year for Christmas I have asked for a shiny spreadsheet, a CRM system, and some gin.

If this transaction matched any of the search terms my colleagues have set up, they will be receiving an email now, just like this one. Perhaps they're writing a Christmas List right now too.

He knows when you are sleeping. He knows when you're awake. He knows if you've been bad or good, so be good for goodness sake...

Unlike Father Christmas, the CRM doesn't know any of this. It only knows what we put in it. And what we put in it may vary depending on the precise nature of our role. But hopefully what we get out of it is a good insight into what the rest of the team are up to and how it relates to our own activity.