Case Study: Leading investment firm dramatically accelerates analysis and unlocks previously inaccessible insights

Our client, one of the world's leading investment firms, faced a significant hurdle. They had invested in S&P Capital IQ Pro, a comprehensive third-party financial dataset with millions of data points on public and private company financials, estimates, ownership, and transactions. They aimed to gain a competitive edge by running proprietary analyses on this data, to augment and improve existing signal from other common sources like Bloomberg.

The complex state of the data made it challenging for regular usage, let alone the bespoke analysis needed to unlock value. Cleaning, modeling, and querying the data was a time-intensive, repetitive task that sunk precious analyst time. For urgent reports, using Capital IQ data at all was prohibitive.

In extreme cases, highly paid analysts would spend hours writing SQL code to reproduce reports readily available in Bloomberg. In many cases, they would abandon using Capital IQ data to meet deadlines. For the firm, this ultimately meant an underutilized expensive data asset, and considerable analyst time lost handling low-return tasks.

The firm turned to BigLittleRobots to reduce the time needed to work with Capital IQ, and help them leverage the dataset to gain a competitive edge and reach positive ROI on their purchase.

We stepped in to prepare the raw Capital IQ data for the firm’s specific analysis needs. Using modern data tools such as dbt and Snowflake, we designed and implemented an automated data pipeline that ran transformations across the billions of raw data points on a daily basis. This granted analysts access to fresh data that was initially cumbersome to reach – all in a simple, intuitive, and familiar form that matches their requirements and expectations.

For example, as part of our initial user requirements gathering phase, we identified financial estimate metrics shared across analysts at the firm. We carefully studied where and how these metrics were provided in the raw Capital IQ dataset. This allowed us to formally define a firm-level metrics layer, uncover discrepancies in methodology with Capital IQ, optimize SQL algorithms to apply the defined metrics formulas to the raw data, and finally design a table schema with end-users to serve the information in an optimal format.

The final solution was an efficient, automated, daily pipeline that reshaped the dataset in a form that best lends itself to our client’s analysis requirements. We complemented this pipeline with exhaustive documentation and SQL query templates, helping the analysts make quick, easy use of the tables in Snowflake.

As a result, we saved analysts >90% of their Capital IQ analysis prep time, making it possible to receive specific data in seconds instead of hours. Long 100+ line SQL queries were replaced with simple and intuitive 5-liners. More importantly, our work ensured the 3rd party data was used in their analysis, giving them a significant edge in the market from insights that were previously inaccessible.