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? Part 2: VLE, Lecture Capture, and Social Media

By Ned Potter, Academic Liaison Librarian 

This is the second half of a two part post on how students like to hear from the central University. It's based on an audit conducted centrally at York, the results of which the Internal Communications Manager has kindly allowed me to share here. I'd recommend having a read of Part 1, focused on email as there's a lot of useful insights there.

This part will focus on the VLE, the popularity of lecture capture, use of social media and the University webpages. As with Part 1, I'll be using phrases like 'students prefer...' but of course the proviso is that I actually mean 'students at York prefer...' (and even more specifically, students who are prepared to sign up for a series of focus groups on internal comms in the first place). 

The surprising popularity of the VLE and the unsurprisingly popularity of Lecture Capture 

I must admit I was a little surprised to read that Blackboard was popular with the students. In actual fact they say it is difficult to navigate, but once mastered and if used well by their tutors, they are generally very positive about the VLE.

In particular the students liked the discussion forums where the lecturer takes the time to get involved. The opportunity to ask questions and clarify parts of the lecture they didn't understand is very much appreciated, and they highlighted the public availability of all the questions and answers - as opposed to a private conversation between student and lecturer which is seen as less fair and transparent.

The other things noted as positives were the email notifications when new content is added, and the posting of lecture materials and supporting information.

The most popular part of the VLE, however, was Replay, the lecture capture system that allows students to re-watch lectures (or catch-up if they were ill - lecture capture has been shown time and time again not to negatively impact on attendance, so it's not used as a way to avoid having to actually go to lectures...). To quote the report:

"At degree level they find it difficult to take in the level of detail and complexity in one sitting and so the opportunity to re-visit the lecture to listen and learn again, to take better notes and to revise is something they really, really value"

It is particularly valuable in conjunction with the discussion forums mentioned above, and reduces the need to seek out the tutor for extra guidance.

Not all students in the focus groups are on courses which use lecture capture extensively - when those that weren't heard from those that are, they made it clear they'd very much like this facility on their modules too.

You can read more about Replay on the E-Learning Development Team blog.

Students, social media and the University 

As mentioned in Part 1, the students would expect anything essential to be communicated by email. Social media can be used as well, but shouldn't ever be used exclusively for key info such as timetable changes and so on.

They're happy for Facebook to be used for 'fun stuff' but not serious stuff - they use it more than any other medium between themselves, but there's a mixed reaction to the University joining in. WhatsApp, Messenger and Snapchat are used a lot for peer-to-peer communication, and they really don't associate this kind of platform with the University and its communication channels at the moment. YikYak is known primarily as a place for cruelty and harm - students don't tend to use it unless there is a particular scandal they want to hear the gossip about.

Interestingly to me, Twitter is was reported as not being used abundantly and is considered as a tool for 'old people'. The main downside noted was about control, or the lack of control, over who sees what. At the Library we actually find Twitter to have quite high levels of engagement, the most of any social media platform we use, and the comms audit contradicting this chimes with other anecdotal evidence I've heard online of students being reluctant to really admitting they use Twitter but nevertheless using it anyway. It's also a lesson in trusting the stats, but interrogating the stats to make sure the engagement is actually coming from your users rather than your peers (and in our case, it is our actual users who interact with us and benefit from our Twitter account, predominantly).

Webpages and Google 

Students prefer to use Google to find information, even if it's info they know exists on the University website. I do this too - I Google my query plus the word 'York' even for stuff on the library website because it's quicker and more reliable. The students don't always trust the University's search function... They also don't expect news via the website - they feel they'll get any updates they need via email and social media. 

Perhaps the most interesting theme for me which came up in this section was one of relevance - students feel a lot of University comms are aimed at potential students, rather than at them, the current incumbents. There's an opportunity here for libraries: we are predominantly focused on existing users, and we can pull in other content for an internal audience, for example via Twitter, and share this with the students too.