pexels-viktor-lundberg-754595-1200x800 (1)

Post: From Excel to Qlik Sense: Our Analytics Journey in the Insurance Industry

In this special guest blog post, Chirag Shukla, a CITO in the insurance industry, will be our guide for all of the adventures, discoveries, and milestones his company encountered on their analytics journey.

We’ll start with Excel and end at our final destination, Qlik Sense.

Risk Administration Services, Inc. (RAS) is a Managing General Agent (MGA) for two insurance companies in South Dakota offering Workers’ Compensation Insurance.

Believe it or not, there is never a dull moment in insurance! And, analytics is at the center of it all, which encompasses pricing, claims prediction, loss management and – most importantly – caring for injured workers when they need help the most.

Excel was a dominant analytics platform until we outgrew its use for advanced analytics, and thus we began our quest to find the best analytics tool that would fulfill our analytics vision. Many look for a mobile analytics platform for this purpose, especially once Excel is unmanageable, so it is a useful search to demonstrate.

Along the way, we’ll explore how we significantly improved our ability to serve customers and partners in the following areas:

  • Realization of data existence
  • Greater access to meaningful data across a wider audience
  • Uncovering the stories behind the data
  • Understanding the why behind the stories that data tells
  • Exceptional customer experiences
  • Materializing strategic long-term decisions for the company

Excel, Our Analytics Starting Point

Excel is often called, ”the original BI tool,” or an “intro to business intelligence.” Excel, OpenOffice and other spreadsheet programs are ubiquitous. The beauty of these tools is they are easily available on most corporate systems and are quick to learn. It’s easy to do visualization, create slicing/dicing capabilities, and connect with various data sources with Excel. This makes it an ideal analytics tool for small and mid-sized datasets.

At RAS, Excel was our avenue to analytics for several years. Our data source was a data warehouse on SQL Server.

Each morning, our administrative staff would refresh the Excel spreadsheets, and email them out to our executives to be reviewed. This was fine and with some useful Tools for conjoint analysis in Excel we saw reasonable productivity with small to medium levels of data. But when large sets of data needed to be analyzed, the performance of these spreadsheets reduced. This caused data distribution to become cumbersome. Spreadsheets took longer to open. Visualizations took longer to render. Users with commodity hardware found it too difficult to consume data.

Additionally, our staff was not aware about all the datasets in RAS’s data warehouse. This resulted in our staff reviewing only a portion of the story. Putting together a cohesive story using various datasets became harder. There was no evidence that the spreadsheet attachments were opened. There was no evidence of decisions being made based on those spreadsheets, either.

The bottom-line was that RAS was on a journey to delight customers with excellent service. Such excellence can only be achieved when the executive branch and the front-line staff have access to the same dataset, a clear understanding of what data means, and a common understanding of what to do with such data to delight customers.

RAS was plodding along with and outgrowing Excel. It was time to find a Business Intelligence (BI) tool that would propel us to best serve our customers.

Why We Chose the Qlik Sense Route

At RAS, we performed discovery on Tableau, Sisense, Qlik Sense and TIBCO Spotfire in Q1 of 2018. The goal was to find a tool that would allow users to understand data quickly. Speed of development and fast visualization rendering was a top priority for us.

In order for RAS to become a more data-driven organization and make data available to a wider audience, the BI tool that would get us there had to meet several requirements:

  • Ease of data distribution
  • Ability for customers to access their own data
  • Intuitive for customers to use with minimal training

All the BI tools we evaluated were designed to tell us “What is happening?” but it was very important for us to have a tool that made it easy to search for and answer “Why is something happening?”. And, the IT team preferred a tool that would allow for iterative and fearless development with version control.

Each of the tools we analyzed fit generic requirements…the devil is in the details. We scored tools on 25 different attributes. Sisense and Qlik Sense stood out from the others on our detailed set of requirements. Both tools were fast at developing dashboards and the interaction with their dashboards was very quick as well. Qlik Sense ultimately won a close tiebreaker because of two reasons:

  1. Clean rendering on mobile phones/tablets.
  2. Our preferred perpetual licensing model.

Lightning Fast Data Access

In today’s age, information can be a key to success for businesses. Qlik dashboards allowed RAS to show data regarding policies, claims, loss history, etc. in just a handful of clicks. Previously, it took opening up several spreadsheets and piecing together the information. It was like hopping around the backroads of spreadsheets and transitioning to a streamlined interstate where all the information was right there and easy to access.

This easy to access and widely varied data created awareness and excitement among staff members. Curiosity began building. Demands to view information from various angles increased. Staff members were converging on methods to assist customers faster than ever!

Qlik does not come with a built-in version control system. RAS invested in Motio’s Soterre version control product which allowed IT to build dashboards fearlessly.

Prior to Qlik, IT staff used to set up discovery meetings, curate data, run analysis in Excel and then set up a follow-up meeting where edits to Excel were typically suggested. Now, IT staff pulls up Qlik during meetings and edits dashboards with visualization far exceeding the audience’s expectations. By the end of the meeting, not only are dashboards created but also data is analyzed.

Week-long back-and-forth meetings with data consumers now takes just minutes to hours (at the most) as staff members have started to become aware of the wide variety of data at their disposal. With a blazing-fast product that can assimilate information into decision-inducing dashboards, RAS could now bring up these dashboards in front of customers and show them visualizations they could take action on quickly. Interactions between RAS and customers started to become memorable, and the need to frequently re-research information started to disappear.

Yet another benefit came from Qlik’s fast-paced filtering capabilities. Data quality issues were hard to track. With Qlik, cities with incorrect spelling – for example – were hard to miss. Cities with incorrect zip codes were caught faster. This brought to light a better awareness about data quality and we were quick to create a process and execution of data quality improvement.

Qlik Fosters a “Data for Everyone” Culture at RAS

While Excel was installed on every user’s system, access to data analytics in Excel was only available to a small set of users. As data started becoming larger, so did Excel analytics files. Most users were using lean terminals with just enough horsepower to run applications at a basic level. Large Excel analytics would not render fast enough on a significant amount of systems. This limitation was not clearly identified until Qlik was put in place. Qlik Sense offered a browser experience and every user in the company now had the ability to access data.

The manner of accessing data mattered as well. Excel analytics was distributed by email prior to Qlik Sense. After Qlik Sense, these extra administrative steps of refreshing Excel spreadsheets or uploading data were eliminated. Qlik Sense accumulated data directly from source systems periodically throughout the day and updated visualizations. It was so easy to change habits. We saw an increase in users voluntarily visiting Qlik Sense. And, with usage metrics available in Qlik Sense, we were able to educate the right audience to visit the right dashboard for the right reasons.

In the first month of commissioning Qlik Sense, patterns in business started to emerge that were otherwise intrinsically hidden in Excel analytics. Researching information was quick with Qlik Sense, which allowed staff to examine business metrics faster. The bookmarking function within Qlik Sense also allowed users to share their findings quickly. A new culture of data analytics was forming within the company and with customers at the epicenter.

With Data, There is Always a Story to Tell

Insurance has its fair share of complexity but customers like simplicity. Customers like to hear a story about their account’s performance. Terms like “subrogation,” “reinsurance,” “claims reserve,” etc. can be confusing. Qlik Sense allowed RAS to create dashboards with large KPIs and a storyline, sometimes a punchline even, to help customers understand how we improve the safety of their employees and help them heal after an injury.

Qlik Sense made it easy for RAS to show customers where their employees were slipping and injuring themselves. With just a click of a button, customers can see where training is essential in reducing injuries. Intuitive visual design allowed customers to come to a conclusion even before we could describe a story. Customers started to correlate various aspects of employee health and well-being. Various dashboards resulted in the “Aha!” moments as staff and customers began to realize that the reduction of injuries correlated with the training and safety programs implemented over time. Such stories always existed, but they were hidden across multiple large spreadsheets. As one staff member put it, “Qlik Sense talks to us in a way we understand.” That sentiment has grown across a wider population within RAS and its customer base.

Uncovering the “Why” Behind the Data

Once you hear the story the data is telling, curiosity kicks in. This naturally led the staff to search for the “Why.”

For example, claims expenses reported on Mondays for Company-A are higher than Company-B. Why is that? While Qlik Sense plays less of a role in explaining the “Why,” it sure eases the discovery process. Staff members were able to slice and dice information to find correlations. Qlik allowed them to quickly see that Company-A had no manufacturing shifts over the weekends but Company-B had weekend shifts. Why does that matter? Some injuries from Friday didn’t get treated and reported until Monday for Company-A. The longer an injured worker waited for treatment, the higher the chances were of complexity to develop resulting in higher costs to treat such complexities. Company-B, on the other hand, sought treatment and reported injuries over the weekend allowing for health care providers to administer treatment quickly. The overall cost for Company-B was, therefore, lower. Staff members discovered this correlation within minutes using the power of Qlik Sense.

Customer Experience Takes Flight

Injured workers turn to health care providers and insurance companies to begin their healing process. At RAS, we take pride in helping injured workers get back on their feet. While injured workers heal, employers are looking to insurance carriers to provide guidance so that they can create safety practices, examine the effectiveness of workplace safety, and make improvements where necessary to ensure workers are performing at their peak. RAS is able to do all of this along with providing employers the supporting analytics behind our guidance. This provides a whole new level of trust in our relationships with our clients which is very important in the insurance industry.

Qlik has significantly improved how we deliver information to customers. Prior to Qlik, hours of preparation went into creating stewardship reports, claim review documents, and arranging meetings to discuss such reports. With Qlik in place, customers can get up to the day data at their fingertips and find the What and Why of their claims. Majority of our customers fall in this self-service model approach. Customers needing quarterly touch points equally benefit from Qlik since visualization of quarter-over-quarter data requires just a click of a button rather than compiling and comparing data from Excel sheets from two quarters. Customers can clearly see a significant stride in the data analytics capabilities offered by Qlik and they appreciate the clearer understanding of the reasons impacting claims.

The power of Qlik Sense shines when RAS acquires a new customer with strong beliefs about claims losses. Qlik Sense allows them to confirm or correct their notions effortlessly. For example, customers seeking RAS’s help in resolving back injuries may not recognize that the cause of back injury is slipping/tripping over uneven sidewalks. Focusing on the rework of a sidewalk may be more effective in keeping the workforce healthy than increasing the number of physical therapies for each back-injury case. Such valuable insights are commonplace with analysis through Qlik Sense.

Strategic Thinking Gets a Boost

Excel had a role to play in our strategic thought process. Long and short-term strategies were developed based on various datasets spread across several spreadsheets. However, developing strategies required significant data preparation time.

With Qlik Sense in place, strategic thinking became easier. Historic data analysis, trends, and predictions within the same dashboard allowed RAS to change scenarios to predict potential outcomes of strategic decisions. Qlik also helped RAS management to stay on track with tactics supporting strategic decisions.

Not Our Final Destination, but the Beginning of a Beautiful Friendship

RAS has made significant leaps and bounds in data analysis over the last few years of our journey and Qlik has been the change agent. We’ve witnessed a cultural shift with data in the organization thanks to Qlik. IT staff has seen a decrease in requests for multi-page reports and an increase in action-inducing dashboards. RAS staff is serving customers more effectively and faster than ever. Strategic decisions are powered by “if-then” scenarios within Qlik.

So where do we go from here? Well, the possibilities are endless.

Forward thinking, RAS plans to utilize Qlik Sense to analyze results of predictive analytics/AI models for pricing and assist with the calibration of models from time to time to meet industry demands. Customers and their insurance brokers can get near-real time feedback on pricing.

Qlik Sense will also be used to study the long-term impacts of COVID-19. This can be helped along by using Coronavirus Applications from companies such as Vantiq, who support businesses trying to mitigate and edge out COVID-19.

Another example is that customers will benefit from preparing better for weather conditions especially in areas where slick surfaces result in an increase in claims.

All of these types of analytics and more can be brought to the forefront by using Qlik Sense. This truly is an exciting time to be in the world of analytics! And I think a quote from Ralph Waldo Emerson nicely sums up our experience with data and Qlik, “It’s not the destination, it’s the journey.”

Scroll to Top
As the BI space evolves, organizations must take into account the bottom line of amassing analytics assets.
The more assets you have, the greater the cost to your business. There are the hard costs of keeping redundant assets, i.e., cloud or server capacity. Accumulating multiple versions of the same visualization not only takes up space, but BI vendors are moving to capacity pricing. Companies now pay more if you have more dashboards, apps, and reports. Earlier, we spoke about dependencies. Keeping redundant assets increases the number of dependencies and therefore the complexity. This comes with a price tag.
The implications of asset failures differ, and the business’s repercussions can be minimal or drastic.
Different industries have distinct regulatory requirements to meet. The impact may be minimal if a report for an end-of-year close has a mislabeled column that the sales or marketing department uses, On the other hand, if a healthcare or financial report does not meet the needs of a HIPPA or SOX compliance report, the company and its C-level suite may face severe penalties and reputational damage. Another example is a report that is shared externally. During an update of the report specs, the low-level security was incorrectly applied, which caused people to have access to personal information.
The complexity of assets influences their likelihood of encountering issues.
The last thing a business wants is for a report or app to fail at a crucial moment. If you know the report is complex and has a lot of dependencies, then the probability of failure caused by IT changes is high. That means a change request should be taken into account. Dependency graphs become important. If it is a straightforward sales report that tells notes by salesperson by account, any changes made do not have the same impact on the report, even if it fails. BI operations should treat these reports differently during change.
Not all reports and dashboards fail the same; some reports may lag, definitions might change, or data accuracy and relevance could wane. Understanding these variations aids in better risk anticipation.

Marketing uses several reports for its campaigns – standard analytic assets often delivered through marketing tools. Finance has very complex reports converted from Excel to BI tools while incorporating different consolidation rules. The marketing reports have a different failure mode than the financial reports. They, therefore, need to be managed differently.

It’s time for the company’s monthly business review. The marketing department proceeds to report on leads acquired per salesperson. Unfortunately, half the team has left the organization, and the data fails to load accurately. While this is an inconvenience for the marketing group, it isn’t detrimental to the business. However, a failure in financial reporting for a human resource consulting firm with 1000s contractors that contains critical and complex calculations about sickness, fees, hours, etc, has major implications and needs to be managed differently.

Acknowledging that assets transition through distinct phases allows for effective management decisions at each stage. As new visualizations are released, the information leads to broad use and adoption.
Think back to the start of the pandemic. COVID dashboards were quickly put together and released to the business, showing pertinent information: how the virus spreads, demographics affected the business and risks, etc. At the time, it was relevant and served its purpose. As we moved past the pandemic, COVID-specific information became obsolete, and reporting is integrated into regular HR reporting.
Reports and dashboards are crafted to deliver valuable insights for stakeholders. Over time, though, the worth of assets changes.
When a company opens its first store in a certain area, there are many elements it needs to understand – other stores in the area, traffic patterns, pricing of products, what products to sell, etc. Once the store is operational for some time, specifics are not as important, and it can adopt the standard reporting. The tailor-made analytic assets become irrelevant and no longer add value to the store manager.