From Manual Excel to Automated Data Integration

Consolidating CRM platforms, financial systems and multiple Excel files for more efficient, accurate data processes.

When two leading investment banking firms joined forces, it not only created one of the largest independent investment banks in the United States, but acted as a catalyst for them to completely overhaul their data management and CRM (Customer Relationship Management) capabilities. 

“Having two separate CRMs for this one company sounds like a nightmare, and it was” explains Kellen Blackman, Associate of Data Analytics for Capstone Headwaters.

Not only did they have two separate CRMs, but other information on closed deals was housed in separate Excel files. A big part of Blackmans’s job was, as he describes it “...getting all of the data that was in different Excel spreadsheets into a consolidated location.”

It was a time-consuming process that gave little visibility into historical data - something that was often needed by the financial firm’s clients. 

Blackman explains “If I pulled a report for a client and then we wanted to see what the data looked like last week, I’d have to see if I happened to download that data anywhere, or alternatively do a lot of calculations to get to the data where we were a week ago.”

Documenting changes to the data was a process that the team had to do externally, and in a manual way, meaning it wasn’t always as accurate as they would want (but was always more time-consuming than they needed).

To manage their data processes they were hand-coding in Python - which worked to a certain extent, but was limiting the data analytics team’s capability to collaborate with their co-workers. They weren’t able to share any of the process outside the team, and weren’t able to pass off the management of specific data integration points to someone else. To do that, they realized, they needed “a better tool with more visibility”

A new central database to improve processes

Following the merger which created the new Capstone Headwaters firm, the company decided it was time to radically overhaul their CRM capabilities and improve their data processes.

The firm decided to move from their two Microsoft Dynamics CRMs into one new DealCloud instance. They saw the opportunity to create, in Blackman’s words, “a central database for all our users, all of our employees, to access our data. And since it’s in the cloud, we can work on data integration with our financial system too. We can capture changes in the data, and archive everything.” He explains that essentially, that’s the role of CloverDX: “Taking the data that’s in our CRM, integrating it with different systems and archiving it. That’s where Clover comes in.”

The advantages of a cloud data warehouse

“Since we made such a big investment in this new CRM initiative, we wanted to make sure the data in it was accurate and that we were presenting it in the best way. CloverDX is a big part of that” says Blackman. 

Going from manually consolidating Excel spreadsheets to using an ETL tool for managing data processing was a vast improvement. With CloverDX they can take in data from all the different tables that are in the CRM, process it, write it to a CSV and push it to a Snowflake data warehouse. Which for the company means that it’s far easier to work with their data more effectively, and it’s also possible to then push the data to Tableau so they can visualize it.

Saving time and increasing accuracy

From the pain of integrating data manually and trying to piece together historical information, Capstone Headwaters is now saving significant time in both manual work and in getting the data into their CRM system, and they also now have the reassurance of knowing their data is accurate. Blackman explains how CloverDX has helped this change:

“The biggest pain point its relieved is bringing our financial data into the CRM. Previously it was once a month, I’d get the information from our VP Finance, and then I would do my prep, import that into the CRM, give it back to him, and he’d go through the financial system and make sure it was correct. The problem was if anything changed from a month or two months prior, then our data would be off and we’d have to try and figure out where that was. 

But now since we integrated it with CloverDX, if anything changes in the financial system it’s going to reflect in the CRM. It reduces the time it takes to get in there and it definitely increases the accuracy.” 

The end of manual, error-prone data integration

Now that the data team at Capstone Headwaters are working with CloverDX, they are discovering there’s more and more they can use Clover for. The team are looking at automating data quality testing to identify issues, correct them and push back into the system in a seamless way, as well as planning on integrating more sources into their data warehouse.

With CloverDX as a key part of the firm’s systems, the small data team has been able to free up their time from manual, error-prone data integration to something that’s accurate, transparent, and scalable. The central CRM database is now integrated with all their systems, and enabling better analysis, better historical data tracking, and a better service to Capstone Headwaters clients.