Integrating disparate databases, and a new website to facilitate income generation.
Pay and Employment Rights Service (West Yorkshire)
The Pay and Employment Rights Service (PERS) offers employment services to employees and employers in the Voluntary Sector in the UK. This includes a telephone advice line; provision of training events; access to up to date HR documents and policies; support for home workers and small business start ups; and mediation services. Originally most of these services were free to the user, depending on grants obtained by PERS. However, the funding often restricted service provision to users within a particular geographic boundary, or who met other specific criterea.
PERS wanted to widen the provision of these services via a paid for subscription service to meet the demand of users who did not qualify for free services, and to develop an income generation strategy.
A secondary aim was to clean up and integrate around 12 disparate databases (MS Access and Excel) with tens of thousands of records into one combined solution that integrated with the website and could be used by all PERS staff..
This would enable the whole organisation to use the same root data to:
- Update service user records in a consistent way.
- Keep track of advice given via the telephone helpline.
- Link services provided with a particular funding stream, ensuring relevant criterea were met and that required information was recorded.
- Reduce conflict of interest issues (e.g. if advice over an issue was being given to an employee, to be able to quickly check whether advice on the same issue was being given to the employer and vice-versa).
- Provide reports for funders.
- Manage and implement postal and email lists.
- Check the subscription status of callers requesting services
- Create and publicise training events, and facilitate online booking, etc.
It would also enable visitors to the website to :
- View, book, and pay for, training events publicised on the website
- Take out an online subscription (either paid for or grant-funded) to access PERS services.
- Access HR documents and policies, either freely, or on a pay per document basis, depending on subscription status.
Background
CiviCRM provided an "out of the box" solution for most of this project:
- The phone call activity allowed for the recording and tracking of telephone advice given to a service user and for looking back over previous advice. Funders required additional information about each activity which was easily collected via custom profiles with certain fields that had to be filled in by the staff member.
- The CiviEvents module offered a quick and easy way to create and manage events, allowing staff to view attendee details and to populate the public website with event details and online booking facilities very easily.
- The CiviMember module facilitated the creation of a number of subscription services at tiered levels of pricing to enable organisations and individuals to be subscribed or to subscribe theselves to services provided by PERS.
- The ability to record Relationships allowed staff to view employees of an organisation they were dealing with and to check the employment history of individuals they were giving advice to - eliminating previous difficulties caused by the organisation potentially advising both employer and employee over the same issue.
It was decided to create a brand new website based on Joomla 2.5 and to use CiviCRM within that as the primary database to monitor and record all PERS service provision and contact information.
Challenges
Data cleansing and organisation prior to importing into CiviCRM was a big challenge. - There was no real distinction made in prevoius records between data referring to an individual or data referring to an organisation. There were also thousands of duplicate client records due to mis-spelling of names, different addresses, or staff simply not checking to see if a contact already existed in the database before creating a new one. (which the MS Access database allowed!). It was necessary to spend some considerable time creating and running a variety of scripts to merge a number of disparate sources of data and to re-organise and de-duplicate the data before it was in a fit state for importing into CiviCRM.
The project also required an accessible document library on the public website which restricted free access to subscribed members, and charged non - members. It was going to be necessary to utilise a third party Joomla component to facilitate the document library (JDownloads) and to customise its code to integrate with CiviCRM somehow!
It was also necessary for civic boundaries (Wards and Metropolitan Districts) to be recorded against addresses. - This to ensure that funded services only went to areas the funding was intended for. Not everybody knew the civic area they lived or worked in, and a lot of guesswork was used previously. - We worked with the organisation to provide an interface which hooked into the CiviCRM code and used the provided postcode to automatically populate the ward and district custom fields with accurate data provided from a reliable postcode lookup service.
Solution
CiviCRM provided:
- A system that could be owned by PERS itself, and not involve ongoing fees, contracts, or being bound to one service provider for ongoing development/support.
- Easily customisable to fit the requirements of the organisation.
- Control over cost - free software with opportunity to pay for customisation later if required.
- Access to the back end code and the ability to use hooks and custom extensions to modify/exted functionality (e.g. postcode area lookups, integration with other components)
- It meets the need for both an office database for recording staff activities and contact information, and a public website which provides training events, subscription signups, etc.
David Florence from Leeds11.com was employed at PERS for a number of days per week over approx 8 months to develop amd implement the soution. Graphic designers and other staff at PERS worked with him to achieve the final result. Additional support to help resolve a few unexpected issues we needed help with was given by http://www.gmcvo.org.uk
