How A Premier Mortgage Company In The United States Migrated & Automated 200+ Excel Sheets

A case study detailing how Sparklore’s future-ready, data-fuelled insights automated a manual reporting system seamlessly.

Client profile:

The client is a premier mortgage company providing best-in-class residential mortgage lender services throughout the United States.

Once a small family-owned business, the company witnessed over 30-fold growth within a few years to become a leading mortgage player with a widespread footprint across the country.

Driven by their trio mission of simplicity, authenticity and transparency, the client is committed to simplifying the complexities of residential mortgage for their ever-expanding customer base.

Business Challenges

In the wake of their exponential growth, the mortgage company found it increasingly difficult to manage its reports manually using traditional excel sheets. Their high-end requirements necessitated a data-driven transition that automated their reporting system without disrupting the existing workflow. Predictably, the challenges were multifaceted.

  • An isolated reporting system that was difficult to manage
  • Limited data visibility
  • Tedious exchange of 200+ excel reports between the departments
  • No mechanism to ascertain the utility or necessity of these reports
  • The data was gathered from multiple sources, often with little or no correlation
  • The organization’s reluctance in transitioning from traditional reporting to modern technology
  • Long hours of error-prone manual reporting

Business Solution

To automate its reporting system and reduce manual intervention, the client realized that their requirements would be best met by specialized data visualization experts who were experienced in working with different verticals and would also be able to implement the transition in a simple, accessible manner.

In light of these requirements, Sparklore was seen as an ideal fit owing to its ability to work with different data sources, simplify data automation and design, and its ability to maintain and publish interactive dashboards.  

Approach and Strategy  

After understanding the client’s requirements through structured data collection, Sparklore’s highly-coordinated team of data engineers and visualization consultants decided to adopt a step-by-step approach to automate the client’s reporting system.

Data Collection & Storage

Firstly, Sparklore realized that the entire data had to be stored and collected in one place. To this end, they:

  • Undertook design thinking methodology to understand the specific requirements of the client
  • Examined their existing reporting process
  • Conducted onsite requirement sessions with different departments
  • Took complete ownership of the 200+ excel reports
  • Compiled an Oracle database containing the entirety of client’s data
  • Identified redundant reports
  • Consolidated different reports delivering the same insights

Report Preparation & Automation

The next logical step was to automate data using interactive dashboards that would save manual work hours, foster communication and facilitate 360° visibility. In this light, Sparklore’s data visualization experts:

  • Extracted data manually and created corresponding Oracle tables
  • Designed and developed the data in Tableau
  • Developed Tableau Server Sources with relevant refresh schedules
  • Collected data from Oracle and linked it seamlessly to Tableau based on specific requirements
  • Prepared Tableau dashboards that looked similar to Excel tables to simplify the transition
  • Prepared visually-attracting graphs & charts that allowed the client to drill down into
  • Performed audits on existing reports
  • Combined different reports on one dashboard to improve visibility and communication between business units

Implementation & Training

The biggest roadblock in implementing the solution was the client’s reluctance in making the transition, given their comfort level with Excel reporting. To overcome this challenge, Sparklore:

  • Examined their existing reporting process
  • Dispatched its own team to the client office to help smoothen the transition
  • Educated the client about the numerous benefits of data automation, and Tableau in particular
  • Undertook a rigorous QC process to ensure the accuracy of all reports
  • Smoothened the implementation by preparing the reports in Tableau and replacing Excel reports with interactive dashboards
  • Trained the client’s employees in the use of new tools
  • Conducted post-transition training sessions to increase user familiarity with Tableau

Business Outcomes

  • Reduced the manual work hours of the client team by 40%
  • Improved client productivity by 85%%
  • Increased data visibility by 80%
  • Facilitated seamless communication across all teams

The Road Ahead

After automating their system seamlessly, Sparklore is planning to consolidate and connect other departments within XYZ to Tableau.

Written by

Leave a Comment

Your email address will not be published. Required fields are marked *