CASE STUDY

Insurance Evaluation of Customer for Insurance Brokerage Firm

Industry

Insurance

Work Done

Business Intelligence,

Challenge

The client is a large insurance brokerage firm based in the USA that works with various general insurance and liability insurance companies. They have a clientele that buys insurance from them, but the challenge for them was to educate their clientele on proper coverage, like whether the insurance purchased by them is sufficient or not.

The diverse data for each type of insurance is stored in different databases, and the amount of insurance required is calculated dynamically, considering risk analysis and keeping in mind the data related to customers and industry standards.

The solution

Apache Kafka streamlines were deployed to fetch all user-relevant data from various systems, which was then transformed into cubes of data and stored in the Snowflake warehouse, where different dimensions and facts were created based on the insurance and risk factors associated with them.

The pipelines were managed and scheduled using Apache Airflow.

The transformed data is then presented in Power BI, complete with a filtering mechanism for administrators and embedded analytics for all users to view the insurance amounts they have for each of general insurance and liability, as well as what they should have to cover risk factors.

For better visualization, the risk factors were classified as red, green, and amber, with red representing an urgent need to purchase additional coverage, amber representing planning and meeting with a broker firm to learn more about it, and green defining adequate coverage. We integrated R programming with Power BI and built the code using the GGPlot library to get the exact same metric as requested by the client.

Technologies used

Results

Projects and pipelines were deployed in the AWS environment of the client using containerization. In September 2022, the project yielded significant growth in insurance coverage for clients, which resulted in 13% more sales YoY and 8% more sales QoQ.