CRM Analytics Tool: The Pareto Picker Tool
A basic introduction to the CRM4UNI project can be found here. We are an Open Source Higher Education project working on the creation of Analytics tools for University Business and Administrative Departments.
This post is intended to help further specify the process undertaken to develop the Pareto Analytics tool. We hope this information will help inform other projects creating customised analytics tools for Universities for sharing of these tools can occur openly and freely. We feel the Pareto tool is the first of seven requisite tools that need to be created to focus Higher Education in quality engagement with the business community: which include the histogram, check sheet, control chart, cause-and-effect diagram, flowchart, and scatter diagram.
User Case for Pareto Analytics Tool:
Glossary of Terms:
- BRD= Business Relations Department
- User = Team of Business Relations Manager in BRD
- End User = Student
- HE = Higher Education
- Organisation = Business
- Institution = Universities
User Description: Within the University is the Business Relations Department (BRD) made up of ten individuals (1 team manager, 6 business relation managers and 4 part time staff). It is the core Business Relations team that will be using this tool, however it will also be used by senior executives in the University to help visualise the overall effect of the department with the business community. The primary user team (business relationship managers) are on average 30-50 years of age, they are use to communicating with clients via email (outlook) and analog telephone. They have used a CRM system before and understand the value it can bring to their team, however individual client relationships are where they place the most value. This tool will ideally help team members realise the benefits of entering data into the system so that more fruitful business relationship strategies can be informed.
The primary aim of this tool is to help manifest discussion within the University -via the BRD- on creating bespoke learning packages for the surrounding business community. To accomplish this the BRD will be required to put data within the CRM on the following:
- List of organisation and personal contact details within those organisations
- Amount of money spent by organisation and individuals for organisations with the local HE institution
- Further data on the kind of human relationship that the HE institution has with individual business organisations and their employees
The above types of data are up to the Business Team to find, input and organise.
Overall context of process for using the Pareto Tool:
- The BRD collects data from other departments within the University (usually a manual process of requesting data in the form of Excel Spreadsheets). The team hopes to move an automatic process once templates are set up declaring mapped elements.
- Data is processed into SugarCRM by BRD CRM Manager: CRM template for data input is created declaring mapped data fields for organistation name and associated individual contacts, special attention is paid to mapping any fields with associated financial numbers relating to the organisational spendings in relation to the institution.
- Once requisite data is within the CRM, analytics tool GETs data from SugarCRM via ReSTful API. Data retrieved include organisational and individual contact titles/names and all associated financial spending numbers to each of those contacts/organisations.
- The Pareto tool renders this data as color coded bar graph (SugarCRM dashlet), with each bar representing an organisation and the colors within the bar representing the different individuals or cost descriptions (see interface 1 below).
- The Pareto Tool then analyses the total organisational spending (“bars”) displaying the top ~10 organisations within a visual bar chart interface, with Pareto “curve” drawn through top 20% organisational bars.
- The user is then able to click on anyone of the color coded segements of the bar to view more detailed view of the organisation and the relations of the institution to that organisation (see interface 2 below):
- The BRD team meet on a regular basis to look at this chart and click on various colors within the bars to see if there is further data that could be collected to help inform a business engagement strategy, e.g. BRD is able to identify organisation who regularly send employees on training courses and so BRD brings the appropriate college staff together to offer a bespoke certificate aimed at that business organisation.