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.

How do students really prefer to communicate, and what should we do about it? Part 1: Email

By Ned Potter, Academic Liaison Librarian 

Tweet 

At the University of York there are audits every few years around student communication. They're conducted centrally by the University, rather than being library-specific. The most recent one was shared with the Library Marketing and Comms Group by York's Internal Communications Manager, and she's kindly given me permission to share the findings here because I think they're absolutely fascinating. They challenge some conventional wisdom, and reveal a lot.

There was a lot about email so this post is devoted to that area; there'll be a part 2 later in the week which covers social media, lecture capture, the VLE and other types of comms.

It's important to note the information was gleaned through focus groups rather than surveys in order to properly capture nuance, so it's not a giant sample size (under 100 people) and inevitably the views reflected will be representative of students engaged enough to turn up for a focus group... But personally I find the findings more useful than generic articles in the Higher Ed press about students of today.

Throughout this article I'll be using phrases like "Students prefer to do X" - the obvious caveat is that I mean "Students at York do X" but I'm not going to write that every time...

How do students communicate? The main findings around email 

1) Email remains the primary and preferred channel of communication with the University

I like this one because it confirms something I've thought for a while - that email is NOT dead. It gets a bad press and it's definitely far less cool than social media, but it still has a function. It's not that students especially love email, it's that they want US - the University and its key services - to communicate key info this way. 

2) Email mainly gets checked on phones, and this happens very frequently

Students check email primarily on their phones, sometimes moving on to a PC / laptop later (see point 3 below). 

Students check their phones for emails first thing when they wake up, last thing at night, and several times in between - many students have 'new message' alerts set up to go to their lock-screens, and will check new emails as they come in even whilst doing other things such as attending lectures.

3) Students triage messages according to 5 criteria 

Students make quick decisions on whether an email gets read there and then, binned, or deferred. They consider (in order of importance):

Relevance - title, sender, and the opening part of the message visible on their phone before they press to open the message; 

Familiarity - do they know and trust the person sending the email? Trusted senders include tutors, supervisors, departmental administrators, the Library, Careers Service, and Timetabling; 

Urgency - does it relate to something important that day or a pending deadline; 

Action - do they need to do something?  (Notice this is 4th on the list of importance...) Interestingly if they do need to do something they'll star the email and find a PC or laptop to log onto and action the email;

Time - if it looks like it can be dealt with quickly they'll read it right away and then delete, file or just mark as read. 

4) The high volume of email they receive is okay, on one condition... it MUST be targeted

Students get a huge volume of emails but they don't mind this as long as the emails are targeted. They object to irrelevant emails and perhaps more so to emails that appear to be relevant but turn out not to be - one example given was an invitation to an employers' event for the wrong subject area or year / status. The sender of that email lost the trust of the students and future emails were deleted upon arrival, unread. 

Any sense of emails happening automatically or without proper thought as to their relevance was met with dissatisfaction. A particular type of email came at the same time each day, suggesting it was automated - this too became one to delete automatically. 

Newsletters and digest emails were read, but often only the first part (too much scrolling and the email was abandoned) and these are the first to go - to be deleted unread - when there's a day with an overly high volume of emails. 

What can libraries change about the way we email students? 

The first thing is don't give up on email. Students expect communication from us to be via this medium, and it was strongly expressed that important information should come this way - key info can be shared via social media but must ALSO be shared via email because it's the one channel everyone checks. The reports of email's death have been greatly exaggerated. 

The second thing is, small details - like titles - really matter. The Library appears to be on the list of trusted senders, but in order to get read you need a decent subject line. (This didn't come up in the audit but I'd argue time of day is important too - if students get a truck load of emails between 9am and 10am, it may be better to join a shorter queue for their attention later in the day at 11am.) Also, because students primarily read email on their phone, you need a very strong opening line. Open your email client on your phone right now - how much can you read without opening a specific email? The way my phone is set-up I get to see about 40 words. So your first few words need to go straight to the heart of the matter - no long intros.

This is obvious, right? We all check emails ourselves on mobiles, we know what it's like. But how many times do we craft emails specifically with the receiver on their phone in mind? I can't speak for anyone else but in my case the answer is: not nearly often enough. 

Thirdly, segment your audience and target them with relevant emails - never include a group in a mass email unless they are directly relevant and would benefit from the info. If an email isn't essential to anyone, does it even need to be sent at all? There are too many emails that are sent out not just to the relevant people but to a smattering of less relevant people too. Every time we do that we diminish our value as communicators - our currency - and get closer to joining the dreaded auto-delete list. 

And related to that, reduce automation because it suggests we're not trying hard enough to avoid wasting their time. It's very hard to think carefully whether or not to send an email if it's automated. I've always said we shouldn't send newsletters out at the same time each week or month - it should be because we have a critical mass of useful things to tell our audience, not because 'it's the time we send out the newsletter'. So anything automated should at least be reviewed to make sure it's still serving a worthwhile purpose and not alienating our users. 

In Part 2 of this post we'll look at the (surprising?) popularity of the VLE, the love-affair with lecture capture, and where social media fits in with formal communication.