A case study of Brighton and Hove City Council’s work to create a single Customer Index using the UPRN (Unique Property Reference Number) as the unifying data key. This case study outlines the proactive work of the ICT Consultant Project Manager and IS Operations Manager in identifying and overcoming the specific set of challenges presented by the need to combine data from multiple disparate sources comprising multiple formats and record types into a single verified customer view. The case study examines the particular example of linking location data (the UPRN) to personal data to facilitate the data cleansing and matching processes, creating a Customer Index Record Number (CIRN). A key lesson has been the value of data matching from a location point of view but the case study also demonstrates the value of harmonising information within a council.
Background to the project and organisations and who was involved
Brighton and Hove City Council (BHCC) is a unitary authority in the South of England. The council has approximately 273,000 residents, according to the 2011 census. The council currently offers access to or information on around 250 services via its website. For the purposes of local government, Brighton and Hove is within a non-metropolitan area of England. As a unitary authority, Brighton and Hove City Council have the powers and functions of both a non-metropolitan county and district council combined. In its capacity as a district council, it is a billing authority collecting council tax and business rates, it processes local planning applications, it is responsible for housing, waste collection and environmental health. In its capacity as a county council it is a local education authority, responsible for social services, libraries and waste disposal.
BHCC’s requirement to implement a Customer Index strategy forms part of the wider ICT strategy which calls for the introduction of “technologies which combine to give a single view of the customer”. The Customer Index is a key component in the delivery of that goal through the provision of a “golden customer record” which will be used to deliver efficiency and effectiveness improvements as well as increase capacity to adapt to changing demands for services. A fundamental component of the introduction of the Customer Index is the Local Land and Property Gazetteer (LLPG), a service provided by GeoPlace.
“Our experience has shown that significant time and effort is often spent by system data owners on business-critical processes such as needing to verify a customer's identity. From an operational viewpoint the development of a customer index is transformational by providing an authoritative view of the customer across different business sources where previously such a view was absent. GeoPlace address data is a key component in our process model as the Customer Index grows in value to incorporate further sources from a wide range of customer transactional data.” Aleks Polanski, IS Operations Manager, BHCC BHCC identified 34 separate departments all using different systems, some of which used common data, others operating on a standalone basis. BHCC customers could exist in several separate customer records with no means of identifying which records were common to one individual. A single person can live at one address, own another address, work at another address etc. which made data record population a technical challenge.
Apart from the added administrative cost of duplicated data capture and entry, the existing fragmented record structure created a less than ideal customer experience requiring individuals to provide detailed personal data on multiple occasions and where data assisting a customer to access one service would not be visible to other services. In addition, this did little to hinder opportunities for fraudulent access to council services.
There were 3 main drivers behind the required creation of the Customer Index
1) Reduce the staff resource required to maintain data records by improving the data quality, reducing the amount of staff time spent on data processing and reducing the number of and amount of time dealing with customer complaints
2) Improve the customer experience by reducing the number of complaints and making access to services easier, specifically with a view to creating a new online customer portal with an enhanced My Account facility displaying personalised local services and information based on UPRN data from the unique CIRN
3) Create data records capable of aiding fraud analytics and fraud detection, realising significant annual cost savings
BHCC invested significantly in the future success of the project by ensuring that the Project Manager, Customer Index Lead and Master Data Steward had clear definitions of roles and responsibilities and were supported from inception to completion by a project board and by technical design, database analysis, quality assurance and information governance teams
The project team identified a subset of systems and datasets that would be suitable for a first cut at the Customer Index, excluding anything at this stage that could be classified as highly sensitive personal data (for example adult social care records).
The project team selected ClearCore by InfoShare as the key component to their data cleansing operation and set about delivery against a clearly defined methodology as follows:
- Identify disparities between data fields.
- Data cleansing.
- Customer Index loading.
Data disparities were subjected to two different resolution methods - completely disparate data was split via customised scripts before any cleansing or matching operations whilst data that had been split but separated into the wrong or differently named fields was cleansed within ClearCore based on predefined matching rules.
· Extract data from source systems in the best possible format.
· Import data into a SQL staging area within ClearCore system
· Pre-process the data using ClearCore cleaning and validation
· Progress data to a matching phase
· Data was loaded into a pre-staging area within the Customer Index
· The CI then identified if a person's record already existed and added them to a person’s table
· This process was repeated for organisations, roles, locations and systems with links to the LLPG URPN
Outcomes and impact
The project team has so far successfully extracted data from 10 internal systems and incorporated them into the Customer Index. The push to process data from the remaining corporate systems continues. As a result of the work already done the team has been able to identify additional challenges awaiting the next stage of delivery.
These include a variety of technical challenges around data access including, but not limited to, firewalls, security, sensitivity, different database systems, locations and server access. There have also been some challenges based on how data can be used across all systems whilst remaining within the terms of the Data Protection Act. This has resulted in a change to all DPA statements within the Council across the board including paper-based, online and SMS communications channels.
The Customer Index needed to be populated using a very specific process in order to maintain data integrity. There were also a number of issues relating to data quality that could not be identified until loaded into the Customer Index. It was recognised at an early stage that there were processes and systems that needed to have changes applied in order to start improving data quality.
“We are effectively using the ClearCore system and the Customer Index to highlight the data quality in order to identify to each department what they need to do to make improvements. This also highlights process and system changes that need to be implemented to improve data quality.” Gary Duckworth, Customer Index Project Manager, BHCC
Essentially, BHCC is well on the way to achieving its required objectives as highlighted in the priority outcomes statement below:
1. Improved data quality leading to a reduced cost / resource requirement
2. Auto‐population of input methods enhancing the quality of data capture
3. Automation of tasks leading to seamless data cleansing processing
4. Reduced staff processing time realising annual cost savings of £300‐400k
5. New online customer portal delivering an improved customer experience
6. Improved customer experience bringing a reduction in complaints
7. Cross departmental data access delivering improved inter departmental communication
8. Documentation facilitating complete/concise training and system documentation
9. Cross departmental system data flows leading to improved fraud detection
10. Fraud detection and prevention aiming to realise annual cost savings of approx £10m
BHCC has identified a number of next steps in the secondary phase of this project whilst the council continue to bring across data from other business systems to populate the Customer Index.
· BHCC has not yet resolved its approach to handling sensitive data from systems dealing with, for example, adult social care records and will need to consider additional issues over and above its successful approach to date to satisfy more rigorous data management requirements.
· Using the Customer Index capability as a launchpad, BHCC will work with Firmstep to improve its customer experience. Next steps for this include creation of a SSO My Account portal and the addition of mapping components to show services such as schools, parking and planning applications specific to customer location.
· BHCC will look to resolve any potential issues around multiple individual accounts to open a new and enhanced scope for service provision
· Enhancements to the existing feedback loop proposed by BHCC will help to improve data quality as well as assist with fraud analytics and detection.
“The council’s ‘My Account’ is a mobile-friendly website where customers will be able to update their details, make reports, and request and pay for services. Sitting behind My Account is the Customer Index, a database of customer records drawn from systems including council tax, parking and housing. Records are matched and compared by a system called ‘ClearCore’ to produce a set of ‘golden records’ of customer data.
This gives us a single view of the customer, rather than having multiple, unrelated, separate records, so we can provide a better service. By logging into ‘My Account’, customers will be able to see joined-up information about their transactions with the council, eg council tax balance, housing rent payments and previous service requests. We hope this will allow citizens to find the information they need and request the services they want quickly and efficiently, avoiding unnecessary contact and reducing frustration. All this should lead to better customer satisfaction through a much improved customer experience.” Richard Clarke, Programme Manager, BHCC
Key lessons learned from the project
“The work carried out by Brighton and Hove is a brilliant example of how good quality address data can be utilised to link disparate data sources together. In particular, the power of the Unique Property Reference Number can be used to link people to place, create efficiencies and improve services. This type of project, while exemplary in its execution, is achievable by all councils as every authority across the country has access to rich local address data.” Steve Brandwood, GeoPlace
“The most significant challenge faced was the selection of data sources from systems. BHCC has a large number of disparate sources (Oracle, MySQL, SQL, Access, PostGre) and a number of bespoke application solutions. There were issues with accessibility, usage, formats, matching, security, sensitivity, data protection and information governance all requiring careful identification and consideration. We also needed to identify a selection of data that was proportionally large enough to provide acceptable results, could be relatively easily interrogated and be matched against other data sources to produce sufficiently cleansed, accurate and useable data resolving the lack of links between the disparate siloed systems.” Gary Duckworth, Customer Index Project Manager, BHCC
The open and inclusive approach to project discussions from the outset, including representation from across all service delivery user groups, information governance professionals, enterprise architects and database analysts has created a collaborative environment within which to deliver this project successfully. It is clearly imperative in a project of this nature to engage a project manager who really understands data. The project team also highlighted the need to be completely aware of resource issues that may not be easy to identify at project outset. As an example, the feedback loop for automatic updates has provided resource challenges throughout, in that departments buy into the principle of providing this but often have resource challenges when it comes to actually supplying. They caution against making the assumption that departments will automatically be able to provide an adequate resource to cater for this upgrade to data quality.
The team is also extremely pleased with the early decision made to prioritise high data quality over high quantity. Whilst slowing the initial pace of the project, the more stringent criteria applied to initial cleansing and matching processes have paid dividends. While time-consuming, the process accelerates with refinement and maturity.
It is clear that the UPRN has been both the glue and the key to the success for this project.