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.

Processing and ACTING ON 100+ hours of ethnography: a 5 stage approach

By Ned Potter, Academic Liaison Librarian 

Understanding Academics, introduced in the last blog post, is far and away the biggest UX project we’ve attempted at York, and the processing and analysis of the data has been very different to our previous ethnographic studies. This is due to a number of factors: primarily the sheer size of the study (over 100 hours’ worth of interviews), the subject matter (in depth and open ended conversations with academics with far ranging implications for our library services), and actually the results themselves (we suspected they’d be interesting, but initial analysis showed they were SO insightful we needed to absolutely make the most of the opportunity).  

Whereas for example the first UX project we ran conformed almost exactly to the expected 4:1 ratio of processing to study – in other words for every 1 hour of ethnography it took four hours to analyse and process – the total time spent on Understanding Academics will comfortably be in excess of 400 hours, and in fact has probably exceeded that already.

UX is an umbrella term which has come to mean a multi-stage process – first the ethnography to understand the users, then the design to change how the library works based on what you learned. In order to ensure we don’t drown in the ethnographic data from this project and never get as far as turning it into ‘proper’ UX with recommendations and changes, Michelle Blake and Vanya Gallimore came up with a 5 stage method of delivering the project.

Two particular aspects of this I think are really useful, and not things we’ve done in our two previous UX projects: one is assigning themes to specific teams or individuals to create recommendations from, and the other is producing and publicising recommendations as soon as possible rather than waiting until the end of the whole project.

As you can imagine the 5 stage method is very detailed but here’s a summary:

Coloured pens used in cognitive mapping
(in this case with the interviewer's reminder about the order
in which to use them)

      1)  Conduct and write up the ethnography. Academic Liaison Librarians (ALLs) spoke to around 4 academics from each of ‘their’ Departments, usually asking the subject to draw a cognitive map relating to their working practice,
and then conducting a semi-structured interview based on the results.

The ALLs then wrote up their notes from the interviews, if necessary referring to the audio (all interviews were recorded) to transcribe sections where the notes written during the process didn’t adequately capture what was said. The interviews happened over a 2 month period, with a further month to complete the writing up.

      2)   Initial coding and analysis. A member of the Teaching and Learning Team (also based in the library) who has a PhD and experience of large research projects then conducted initial analysis of the entire body of 100+ interviews, using NVIVO software. The idea here was to look for trends and themes within the interviews. The theming was done based on the data, rather than pre-existing categories – a template was refined based on an initial body of analysis. In the end, 23 over-arching themes emerged – for example Teaching, Digital Tools and Social Media Use, Collaborations, Research, Working Spaces. This process took around 2 months.

      3)   Assigning of themes for further analysis and recommendations. Vanya then took all of the themes and assigned them (and their related data) to members of the Relationship Management Team – this consists of the Academic Liaison and Teaching and Learning teams already mentioned, and the Research Support team. This is the stage we are at now with the project – each of us in the team have been assigned one or more theme and will be doing further analysis at various times over the next 8 to 10 months based on our other commitments. A Gantt chart has been produced of who is analysing what, and when. The preparation and assigning of themes took around 2 weeks.

      4)   Outcomes and recommendations. There are three primary aims here. To come up with a set of practical recommendations for each of the themes of the project, which are then taken forward and implemented across the library. To come up with an evidence-base synthesis of what it means to be an academic at the University of York: a summary of how academics go about research and teaching, and what their key motivations, frustrations and aspirations are. (From this we’ll also aim to create personas to help articulate life for academics at York.) And finally to provide Information Services staff with access to data and comments on several areas in order to help inform their work – for example members of the Research Support team will have access to wealth of views on how academics think about Open Access or the repository.

These aims will be achieved with a combination of devolved analysis assigned to different groups, and top-down analysis of the everything by one individual. Due to other projects happening with the teams involved, this stage will take up to 7 months, although results will emerge sooner than that, which leads us neatly to...

      5)  Distribution and Dissemination. Although this is last on the list, we’re aiming to do it as swiftly as possible and where appropriate we’ll publicise results before the end of the project, so stages 4 and 5 will run simultaneously at times. The total duration from the first interview to the final report will be around 18 months, but we don’t want to wait that long to start making changes and to start telling people what we’ve learned. So, once an evidence-based recommendation has been fully realised, we’ll attempt to design the change and make it happen, and tell people what we’re doing - and in fact the hope is to have a lot of this work completed by Christmas (half a year or so before the Summer 2017 intended end date for the final report). 

The full methods of dissemination are yet to decided, because it’s such a massive project and has (at a minimum) three interested audiences: York’s academic community, the rest of Information Services here, and the UX Community in Libraries more widely. We know there will be a final report of some sort, but are trying to ensure people aren’t left wading through a giant tome in order to learn about what we’ve changed. We do know that we want to use face to face briefings where possible (for example to the central University Learning and Teaching Forum), and that we’ll feedback to the 100 or so academics involved in the study before we feedback to the community more widely.

As we go through this project we’ll try and keep writing on this blog to update on where we are, what’s working, and what the challenges are. Above all, Understanding Academics has been one of the most exciting and insightful projects any of us have ever attempted in a library context.