Improved business processes for automative industry
Orchestra is a specialist management company responsible for the management of the UK Division of one of the worlds leading car manufacturers.
Summary of project:
Orchestra laid down by far the most complex problem Cocoonfxmedia has had to deal with to date. The project involved multiple data sets on old legacy systems. The brief was to take 9 access databases and 1 Oracle database plus a CRM systems data and convert this all into a system where the users can use one system to manage the whole business process. Cocoonfxmedia also had to come up with a solution to take out the need for Physical paper warranties and convert them to a digital format that could be emailed and managed through the system. The project was split into 4 phases with the priority of the Digital Warranty Packs (DWP) to be built first.
What we had to work with:
- 9 Access 97 databases
- 1 Oracle Database
- 1 SQL Database
- Over 10 Million records of data
- Over 700,000 images
- Removing the need to send out 12,000 + welcome packs by post.
The outcome:
Merged and integrated 11 databases into a cloud-based system which digitalised the whole business process. Allowing for fast and more accurate data being provided to the client. The entire system is managed on our carbon-neutral hosting service.
The brief was to take 9 access databases and 1 Oracle database plus a CRM systems data and convert this all into a system where the users can use one system to manage the whole business process. Cocoonfxmedia also had to come up with a solution to take out the need for Physical paper warranties and convert them to a digital format that could be emailed and managed through the system. The project was split into 4 phases with the priority of the Digital Warranty Packs (DWP) to be built first.
The Journey
Phase 1 - Audit Data
The first piece of work was to map out the complete data flow with the client and create a Glossary of Terms to allow both Cocoonfxmedia and Orchestra to talk the same language.
We then had to audit the data to see how many records we were going to manage and what level of Server we would need to handle the records and also the future growth. We also need to have a full over option in case the system crashed which we then spec out the server’s infrastructure and software requirements.
Once we established this, we then need to work out which version of Access and Oracle was being used and get the appropriate licenses for the out data system.
The DWP basically was using data directly from the dealerships and communicated with the main database. A mass mailing system was created that could monitor receiving mailboxes, so we knew if the email was correct. The system keeps track of the number of opens and also reasons for failure so Orchestra could contact the clients and get the correct data.
As part of the DWP project, we had to follow the strict branding guidelines set by the car manufacturer. We also found that the design provided didn’t work correctly for Mobile devices, so we had to redesign the delivery process of the HTML emails and also make changes to the physical design to maintain the brand guidelines and also make it nice to view on a mobile device.
Once a dealer entered the warranty onto the system the DWP email would link through to a digital version of the warranty which would be supplied in a print-ready PDF. It would also show the privacy options the client had chosen.
Currently, the system is sending out between 100 to 1000 emails a day with 99% accuracy and delivery rate.
Phase 2 - Back Office
This part of the project is complex which meant we need to manage and tie up over 10,000,000 records and all the associated data for the vehicles. This includes HPI Checks, checks with AA, Scanning of V5 logbooks and the whole process of managing the vehicles.
The car manufacture supplied a daily feed of all the new vehicles available per day this had a one shot per day option which meant if you didn’t get the connection right first time you will lose the data for that data. We had to develop a method not only to grab this feed but to keep a backup securely so that if there was ever problem with the system we can retrieve and restore data extremely quickly in which the old system couldn’t do.
This data then is cross referenced with the existing data and also a stock control report which the manufacturer would send daily. This would basically tell Orchestra which vehicles were allowed to be sold and allocated to Dealerships. (may need to double check this).
The vehicles on this list would then be crossed reference to see that if they’re needed to be checked by the HPI service and also to check that the relevant V5 was available or needed to be requested from the DVLA.
The whole process starts with Orchestra having to scan every hardcopy V5 they have and any new ones received. We then developed an OCR (Optical Character Recognition) algorithm to pick up the Vehicle Reg, VIN Number and Document number which are all the key information for a V5. The scanned image then would be cross referenced with the database. We need to get 100% accuracy and we found that through tuning the Algorithm we could get this to 99% accuracy which left 1 % where the image was either blurry or the characters couldn’t be clearly read. We then had to develop a tool which allowed a manual allocation if a re-scan didn’t solve the issue. This also required a complex algorithm to assign the manual entry to the vehicle record.
Once this process was built, we then had to find within the 9 databases all the relevant customer details as they’re split over multiple tables we built various rules and algorithms to tie up all the data. This includes the full vehicle ownership details and a complete timeline. We also had to create functionality to import there CRM and map this into the vehicle databases.
Phase 3 - Dealer Portal
This was a complete overhaul of the old website building it from the ground up. Removing all known bugs and making it easier to view and navigate round. The site is used by every dealer in the UK to register Digital Warranties which was automatically connected to the Back-office Phase 2. The new system checks to make sure dummy email addresses are not entered to bypass the system and also a postcode look up stores previous address searches to save costs on postcode look ups via an API. The design was changed slightly with clear typography and the new build used as little code as possible to make it run quickly and smoothly and easy to maintain.
Phase 4 - Merging of DWP with Back-office
The final phase was to remove the need for the DWP system phase 1 this meant us moving all the logic into the single system. We had already provisioned the code correctly to allow a very quick process of mirroring the data so the temporary URL then would be merged into all one system.