The analytics landscape at Captive Resources was marked by many manual and time-consuming reporting processes, leaving no time for data analysis. MS Excel was the primary and preferred tool for reporting and analysis, and even when incorporating more advanced features such as macros and linked sheets, CRI reached the limit of the capabilities of the tool. Reports were largely in tabular form, with little focus on data visualization.
A large part of the time associated with these manual processes was due to CRI’s data residing in several siloed source systems. Any report which required data from multiple systems required complex Excel formulas and v-lookups and additional layers of auditing to mitigate the risks of manually combining this data for every request or refresh.
As they grew, CRI knew the time required for the existing manual reporting processes would begin to impact the business, such as making quick decisions related to finances and pricing. Without a more systematic way of understanding and predicting company growth, staffing considerations would also become more complex. In general, manual processes with ever-increasing amounts of data put data quality at risk.
Furthermore, there was little standardization between captives, so analysis across captives was already difficult and would only become worse as CRI expanded. This lack of standardization not only impacted the performance of CRI, but also their business partners. Pinnacle, the actuarial firm engaged by CRI, estimated that 30% of their time was spent on transforming, massaging, and validating the inconsistent data they receive from CRI.
CRI needed the ability to automate their reporting and see data across captives so they could perform unified analysis of premiums, claims/loss history, pricing factors, member counts, and more.
CRI engaged Analytics8 to build a solution that would automate reporting, leverage data to support the planned growth, and refocus the effort of resources toward analysis of data. We started by conducting a strategic assessment of the company’s current state of technology and business processes. We interviewed business users to identify their daily processes and understand how they were currently utilizing their data. During these discussions, Analytics8 identified the key metrics that CRI leadership wanted to measure and helped users prioritize their report requests and use cases.
We also conducted meetings with data source experts and SMEs and profiled multiple source systems. This allowed us to begin plans for a data warehouse and gain an understanding of the feasibility of implementing each data source.
Combining the knowledge gained on the business and data sides, we created a use case prioritization matrix which weighed business value against feasibility to identify the optimal implementation schedule which would deliver value in short iterations.
The assessment resulted in an overarching long-term roadmap and more detailed short-term implementation plan with artifacts that can be used immediately to begin implementing the recommended solution. We provided a detailed plan for a robust data warehouse and centralized reporting platform and provided suggestions on data storage, BI and ETL tools, and staffing needs to support their ideal solution.
Data Warehouse Implementation:
Moving forward with the analytics solution outlined in the assessment, we worked in short iterations to allow CRI to quickly start using parts of the solution. First, we designed and implemented an enterprise data warehouse that integrates Captive’s many data sources, including Sugar CRM, Pega, Pulpstream, Third-party administrator claims data, LotusNotes, and Excel; and we standardized the data collection process between captives to enable cross-captive analysis. We then implemented a modern BI platform (Qlik) on top of the data warehouse which aggregates data for 4,500+ member companies and provides self-service capabilities, powerful dashboards and visualizations, and a single point of access for all their analytics needs. The analytics solution offers insight into many business functions, including policy pricing, member history, claims, risk control, budgeting, brokers, and HR.
The roadmap and other deliverables from the strategic assessment allow CRI to proceed confidently with a solid plan to address growth concerns and implement an even more powerful and robust business intelligence solution. CRI now has a better understanding of data needs for business users across departments and the tools, data organization, and methods to effectively address those needs. They are utilizing the roadmap to plan for future phases, and the documentation within can serve as blueprints to speed up the development process.
With the implementation of the data warehouse and analytics solution, CRI now has a 360 degree view into their business and unified analysis of premiums, claims/loss history, pricing factors, member counts, and more.
The analytics solution allows Captive to:
Captive has also experienced huge time savings from automating data collection and blending tasks. Now employees can spend time analyzing their data and making data-driven decisions instead of manually creating reports. Additionally, the solution has helped create a culture where stakeholders take ownership of their data and work to improve accuracy and completeness.
Sign up for a data strategy session, and one of our analytics experts will consult with your company about your data and analytics strategies and processes.
To thrive with your data, your people, processes, and technology must all be data-focused. This may sound daunting, but we can help you get there. Sign up to meet with one of our analytics experts who will review your data struggles and help map out steps to achieve data-driven decision making.