It may be surprising to know that U.S. natural catastrophe economic losses totaled $119 billion in 2020, and 75% (or $89.4B) of those economic losses were caused by severe storms and cyclones. In the insurance industry, data is everything. Insurers use data to influence underwriting, rating, pricing, forms, marketing, and even claims handling. When fueled by good data, risk assessments become more accurate and produce better business results. To make this possible, the industry is increasingly turning to predictive analytics, which uses data, statistical algorithms, and machine learning (ML) techniques to predict future outcomes based on historical data. Insurance firms also integrate external data sources with their own existing data to generate more insight into claimants and damages. Google Cloud Public Datasets offers more than 100 high-demand public datasets through BigQuery that helps insurers in these sorts of data “mashups.” 

One particular dataset that insurers find very useful is Severe Storm Event Details from the U.S. National Oceanic and Atmospheric Administration (NOAA). As part of the Google Cloud Public Datasets program and NOAA’s Public Data Program, this severe storm data contains various types of storm reports by state, county, and event type—from 1950 to the present—with regular updates. Similar NOAA datasets within the Google Cloud Public Datasets program include the Significant Earthquake Database, Global Hurricane Tracks, and the Global Historical Tsunami Database.  

In this post, we’ll explore how to apply storm event data for insurance pricing purposes using a few common data science tools—Python Notebook and BigQuery—to drive better insights for insurers.

Predicting outcomes with severe storm datasets

For property insurers, common determinants of insurance pricing include home condition, assessor and neighborhood data, and cost-to-replace. But macro forces such as natural disasters—like regional hurricanes, flash floods, and thunderstorms—can also significantly contribute to the risk profile of the insured. Insurance companies can leverage severe weather data for dynamic pricing of premiums by analyzing the severity of those events in terms of past damage done to property and crops, for example. 

It’s important to set the premium correctly, however, considering the risks involved. Insurance companies now run sophisticated statistical models, taking into account various factors—many of which can change over time. After all, without accurate data, poor predictions can lead to business losses, particularly at scale.  

The Severe Storm Event Details database includes information about a storm event’s location, azimuth (an angle measurement used in celestial coordination), distance, impact, and severity, including the cost of damages to property and crops. It documents:

• The occurrence of storms and other significant weather events of sufficient intensity to cause loss of life, injuries, significant property damage, and/or disruption to commerce.
• Rare, unusual weather events that generate media attention, such as snow flurries in South Florida or the San Diego coastal area.
• Other significant weather events, such as record maximum or minimum temperatures or precipitation that occur in connection with another event.

Data about a specific event is added to the dataset within 120 days to allow time for damage assessments and other analysis.

Driving business insights with BigQuery and notebooks

Google Cloud’s BigQuery provides easy access to this data in multiple ways. For example, you can query directly within BigQuery and perform analysis using SQL. 

Another popular option in the data science and analyst community is to access BigQuery from within the Notebook environment to intersperse Python code and SQL text, and then perform ad hoc experimentation. This uses the powerful BigQuery compute to query and process huge amounts of data without having to perform the complex transformations within the memory in Pandas, for example.

In this Python notebook, we have shown how the severe storm data can be used to generate risk profiles of various zip codes based on the severity of those events as measured by the damage incurred. The severe storm dataset is queried to retrieve a smaller dataset into the notebook, which is then explored and visualized using Python. Here’s a look at the risk profiles of the zip codes:

Another Google Cloud resource for insurers is BigQuery ML, which allows them to create and execute machine learning models on their data using standard SQL queries. In this notebook, with a K-Means Clustering algorithm, we have used BigQuery ML to generate different clusters of zip codes in the top five states impacted by severe storms. These clusters show different levels of impact by the storms, indicating different risk groups. 

The example notebook is a reference guide to enable analysts to easily incorporate and leverage public datasets to augment their analysis and streamline the journey to business insights. Instead of having to figure out how to access and use this data yourself, the public datasets, coupled with BigQuery and other solutions, provide a well-lit path to insights, leaving you more time to focus on your own business solutions.

Making an impact with big data

Google Cloud’s Public Datasets is just one resource within the broader Google Cloud ecosystem that provides data science teams within the financial services with flexible tools to gather deeper insights for growth. The severe storm dataset is a part of our environmental, social, and governance (ESG) efforts to organize information about our planet and make it actionable through technology, helping people make a positive impact together. 

To learn more about this public dataset collaboration between Google Cloud and NOAA, attend the Dynamic Pricing in Insurance: Leveraging Datasets To Predict Risk and Price session at the Google Cloud Financial Services Summit on May 27. You can also check out our recent blog and explore more about BigQuery and BigQuery ML.

So, you want to store objects on the cloud? But you’re really new to Google Cloud or Cloud Storage and would like someone to walk you through the process step by step?

Today is your lucky day! 

I will help you understand the big steps involved in setting up and using Cloud Storage. For a walkthrough of all the steps involved that even includes highlighting of things to select so you don’t spend all your time playing “Where’s Waldo” on the page looking for that one button, click try the tutorial!

Creating buckets, storing objects in them, and adjusting permissions may feel challenging at first, but after you’ve walked through these steps, you’ll be able to create and use the Cloud Storage your project needs.

Resource Hierarchy

Projects contain all of the related parts of your application. Inside projects, you may have buckets, the top level containers in Cloud Storage. Files and folders are stored in buckets. You can grant access to a bucket, folder, or file using Identity and Access Management.

You can do all of that in this interactive tutorial. So let’s get started. I suggest you follow along in the tutorial so you can see the specific keys and screens as I describe them.

Creating a project

Everything in your application will be in a project, even if the only service you are using is storage. Projects need to be associated with a billing account, so if you don’t have one, consider signing up for the Free Trial. You’ll start in the Cloud Console.

Creating a bucket

Once you have a project, you can use storage by navigating to the Cloud Storage page by using either the Navigation menu at the top left of the console or by searching for “Cloud Storage” using the search box at the top of the console.

At the Buckets page of Cloud Storage, you’ll be able to create a bucket. The name of your bucket needs to be globally unique; that is no other bucket in Google Cloud Storage can have the same name. This is because if you or your organization allow your bucket to be accessible on the Internet, it will be at the URL https://storage.googleapis.com/<bucket name>. You can also consider implementing a security feature to prevent data exfiltration, organization restriction headers when you’re finished creating your bucket.. Pick a region close to you for your bucket. In some cases it may make sense to use multi-region or dual-region, but the details are more than I want to get into here.  Don’t worry; you can find lots more information on them! Additionally, I won’t go into the details about selecting the appropriate storage class based on access frequency and longevity, but more information can be found here as well our recently introduced Autoclass tiering feature..

Since buckets can be accessed from the Internet, you need to be careful to only make the things you want to be public, public. The default value is to keep your data off of the Internet, so when you create the bucket, there’s an option to “Enforce public access prevention on this bucket” that is already selected. Since we’ll be making items in this bucket public later on, clear the option before confirming the bucket information. Don’t worry; you’ll see how to set that option back later.

Take a look at the Bucket detail page. There are tabs to see the objects in the bucket, the permissions on the bucket, and much more. There are commands to create folders and to upload files and folders.  We’ll do some of this in the next section. You can even find out much more from the Learn option in the upper right.

Adding items to the bucket

Find a file you’d like to upload to this bucket, remembering it will be made public to the Internet later. If you don’t have anything available, there’s a picture of a cat (of course, it’s a cat picture, this is the Internet!) in the interactive tutorial.

Go to the bucket details for your bucket. Using the “Upload files” button, you’ll just upload the file to the bucket by selecting the file from your computer.   We won’t get into service classes here, but when you’re ready, there are links in the references.

Of course, if you have a lot of files in a bucket, you probably will want some sort of organization. You can do this by creating folders in your bucket with the “Create Folder” option. Once you’ve done that, you can go to the overflow menu (three vertical dots at the end line with the object information) and select Move. Remember, if you need help finding this, the interactive tutorial will point it out. 

Making a bucket public 

No surprise, the Bucket details hold lots of information about your bucket. You’ll change the bucket’s permissions in the Permission tab. (I hope that’s not a surprise.) You can then give access to individuals, groups, or all users. For this tutorial, follow the steps to grant access to allUsers. There are a variety of different levels of access you can grant to a user. In this case, just grant the ability to view the object using Storage Object Viewer.

Then go into the Objects tab to find the URL for the object you uploaded. Copy that URL and ensure you can indeed access the object from another tab or incognito window.

Now that you’ve done that, go back to the Permission tab for the bucket and select Prevent Public Access to secure the bucket again.

So, in a nutshell, to make all files in a bucket public, in the Permissions tab:

• Uncheck the option “Remove Public Access Prevention”
• Use Grant Access to give allUsers the Storage Object Viewer role in Cloud Storage.

To make all files in a bucket private, in the Permissions tab select Prevent Public Access.

Cleaning up

There is a charge to keep items in Cloud Storage, so you’ll probably want to delete the object you uploaded by selecting it and pressing Delete. You can delete the bucket in a similar way. The tutorial has details, of course.

While there are lots more details about Cloud Storage, you’ve got the big picture now. Go do something creative and useful with it!

So what now?

If you haven’t walked through the interactive tutorial, give it a try!

Business Problem
UKG Ready primarily operates in the Small and Medium Business (SMB) space, so inherently many customers are forced to operate and make key business decisions with less Workforce Management (WFM) / Human Capital Management (HCM) data. In addition to volume, SMB lacks the variety of data needed to create a dynamic and agile organization. This puts SMB at a major disadvantage compared to larger segments.

Project Goals
People Insights module is committed to surfacing insights to customers in the context of their day-to-day duties and aid in decision making. With the SMB customer data limitations mentioned above, the goal of this project was to create a global dataset that augments individual customer data to bring light to less obvious, yet important information.

Challenges
UKG Ready is a highly configurable application that gives customers the opportunity to build solutions on a platform that meets their specific business needs. High configurability gives high flexibility to customers in their usage of the software. However, it becomes nearly impossible to create a global dataset for machine learning and data insights. UKG Ready manages just under 4 million of the US workforce and some 30,000+ customers. Despite the large employee dataset size, machine learning models that are specific to customers are starved for data because the individual customers have a relatively small employee population. Does that mean we cannot support our SMB customers’ decision making with ML?

Result
Partnering with Google, we were able to develop an approach that allowed us to standardize various domain entities (pay categories, time off codes, job titles, etc.) so that we could build a global dataset to augment SMB customer data. Using machine learning we were able to build a common vocabulary across our customer base. This common vocabulary encapsulates the nuances of how our customers manage their business and yet is generalized and standardized such that the data can be aggregated over the variety of customer configurations. This allows us to serve up practical insights to customers through various use cases. Our partnership allowed us to leverage Google Cloud Services to meet the needs of our complex machine learning models, distributed data sets and CI/CD processes.

How
UKG Ready decided to partner with Google for an end-to-end solution for the analytics offering. This allowed us to focus on our core business logic without having to worry about the platform, environment configurations, performance and scalability of the entire solution. We make use of various Google Cloud services such as Cloud Triggers, Cloud Storage, Cloud Functions, Cloud Composer, Cloud Dataflow, Big Query, Vertex AI, Cloud Pub/Sub… to host our analytics solution. Jenkins manages the entire CI/CD pipelines and cloud environments are configured and deployed using Terraform.

The standardization of business entities problem was solved in three distinct steps:

Step 1: Collecting aggregated data
We needed an approach to collect aggregated data from our highly distributed, sharded, multi-tenant data sources. We developed a custom solution that allows us to extract data aggregated at source for PII and GDPR considerations and transfer to Google Cloud Storage in the fastest manner possible. Data is then transformed and stored in Big Query. Services used: GCS, Cloud Functions, DataFlow, Cloud Composer and Big Query. All processes are orchestrated using Cloud Composer and detailed logging is available in Cloud Logging (Stackdriver).

Step 2: Applying NLP (Natural Language Processing)
Once we had the variety of customer configurations or the business entities available, we then applied NLP algorithms to categorize and standardize these in buckets. This approach assumes that customers use natural language for configurations like job titles, pay codes etc.

String Preparation
The input data for string preparation process is an entity string or several strings, that describe one entity object (like name-description pair or code-name pair). The output represents set of tokens that may be used to run a classification/clustering model. The process of string preparation tokenizes strings, replaces shortcuts, handles abbreviations, translates tokens, handles grammatical errors and mistypes

ML Models

Statistical
The idea of the model is to use defined target classes (clusters) and assign several tokens (anchors) to each of them an entity that has any of those tokens would be “attracted” to appropriate class. All other tokens are weighted according to frequencies of usage of theses tokens in the entities with anchor tokens:

Using anchor tokens, we are building kind-of Word2Vec - dimensionality of vector is equal to number of target classes. The higher the specific dimension (cluster) value, the higher the probability of entity to be included in appropriate cluster. Final prediction for entity tokens list for specific class is sum of weights of all the tokens included. Predicted cluster is a cluster that has maximal prediction score.

Lexical Model
We managed to generate reasonable amount of labeled data during statistical model implementation and testing. That opens a possibility to build “classical” NLP model that uses labeled data to train classification neural network using pretrained layers to produce token embeddings or even string embeddings. We started experimentation with pre-trained models like GloVe and got good results with single words and bi-grams but started getting issues in handling of n-grams. Our Google account team came to our rescue and recommended some white papers that helped formulate our strategy. We now use Tensorflow nnlm-en-dim128 model to produce string embeddings – it was trained on 200B records English Google News corpus and produces for each input string 128-dimensional vector. After that we use several Dense and Dropout layers to build a classification model.

Ensembling
To perform ensembling all the model results for each class are cast to probabilities using softmax transformation with scale normalization. Final predicted probability is maximal average score of both models among all the classes scores – appropriate class is predicted class.

The machine learning models are deployed on Vertex AI and are used in batch predictions. Model performance is captured at every prediction boundary and monitored for quality in production.

Step 3: Making available common vocabulary
Having the standardized vocabulary, we then needed a mechanism to have the results be available in UKG Ready reports and customer specific models like Flight Risk and Fatigue. For this we again used Google Services for orchestration, data transformation and data storage.

Once the modeling is complete, we made the customer specific models leveraging the above architecture be available in Reports. We utilized our proven existing technology choices in GCP for orchestration, data transformation and data storage

Results
We are able to build a common vocabulary of our customers’ business entities with good confidence. And be an expert advisor to our SMB customers in their decision-making using machine learning. With the advice of our Google account team and using Google services we can add value to our product in a relatively short amount of time. And we are not done! We continue to use this platform for new use cases, complex business problems and innovative machine learning solutions.

Sample result:

Special thanks to Kanchana Patlolla , AI Specialist, Google for the collaboration in bringing this to light

Over one third of organizations believe that data analytics and machine learning have the most potential to significantly alter the way they run business over the next 3 to 5 years. However, only 26% of organizations are data driven. One of the biggest reasons for this gap is that a major portion of the data generated today is unstructured, which includes images, documents, and videos. It is estimated to cover roughly up to 80% of all data, which has so far remained untapped by organizations.

One of the goals of Google’s data cloud is to help customers realize value from data of all types and formats. Earlier this year, we announced BigLake, which unifies data lakes and warehouses under a single management framework, enabling you to analyze, search, secure, govern and share unstructured data using BigQuery.

At Next ‘22, we announced the preview of object tables, a new table type in BigQuery that provides a structured record interface for unstructured data stored in Google Cloud Storage. This enables you to directly run analytics and machine learning on images, audio, documents and other file types using existing frameworks like SQL and remote functions natively in BigQuery itself. Object tables also extend our best practices of securing, sharing and governing structured data to unstructured, without needing to learn or deploy new tools.

Directly process unstructured data using BigQuery ML

Object tables contain metadata such as URI (Uniform Resource Identifier), content type, and size that can be queried just like other BigQuery tables. You can then derive inferences using machine learning models on unstructured data with BigQuery ML. As part of preview, you can import open source TensorFlow Hub image models, or your own custom models to annotate the images. Very soon, we plan to enable this for audio, video, text and many other formats, and pre-trained models to enable out-of-the box analysis. Check out this video to learn more and watch a demo.

Create an object table

CREATE EXTERNAL TABLE my_dataset.object_table
WITH CONNECTION us.my_connection
OPTIONS(uris=["gs://mybucket/images/*.jpg"],
object_metadata="SIMPLE", metadata_cache_mode="AUTOMATIC");
​
# Generate inferences with BQML
SELECT * FROM ML.PREDICT(
MODEL my_dataset.vision_model,
(SELECT ML.DECODE_IMAGE(data) AS img FROM my_dataset.object_table)
);

By analyzing unstructured data natively in BigQuery, businesses can

• Eliminate manual effort as pre-processing steps such as tuning image sizes to model requirements are automated
• Leverage the simple and familiar SQL interface to quickly gain insights
• Save costs by utilizing existing BigQuery slots without needing to provision new forms of compute

Adswerve is a leading Google Marketing, Analytics and Cloud partner on a mission to humanize data. Twiddy & Co. is Adswerve’s client – a vacation rental company in North Carolina. By combining structured and unstructured data, Twiddy and Adswerve used BigQuery ML to analyze images of rental listings and predict the click-through rate, enabling data-driven photo editorial decisions.

“Twiddy now has the capability to use advanced image analysis to stay competitive in an ever changing landscape of vacation rental providers – and can do this using their in-house SQL skills.” said Pat Grady, Technology Evangelist, Adswerve

Process unstructured data using remote functions

Customers today use remote functions (UDFs) to process structured data for languages and libraries that are not supported in BigQuery. We are extending this capability to process unstructured data using object tables.

Object tables provide signed URLs to allow remote UDFs running on Cloud Functions or Cloud Run to process the object table content. This is particularly useful for running Google’s pre-trained AI models, including Vision AI, Speech-to-Text, Document AI, open source libraries such as Apache Tika, or deploying your own custom models where performance SLAs are important.

Here’s an example of an object table being created over PDF files that are parsed using an open source library running as a remote UDF.

SELECT uri, extract_title(samples.parse_tika(signed_url)) AS title<br>FROM EXTERNAL_OBJECT_TRANSFORM(TABLE pdf_files_object_table,<br>["SIGNED_URL"]);

Extending more BigQuery capabilities to unstructured data

Business intelligence – The results of analyzing unstructured data either directly in BigQuery ML or via UDFs can be combined with your structured data to build unified reports using Looker Studio (at no charge), Looker or any of your preferred BI solutions. This allows you to gain more comprehensive business insights. For example, online retailers can analyze product return rates by correlating them with the images of defective products. Similarly, digital advertisers can correlate ad performance with various attributes of ad creatives to make more informed decisions.

BigQuery search index – Customers are increasingly using the search functionality of BigQuery to power search use cases. These capabilities now extend to unstructured data analytics as well. Whether you use BigQueryML to produce inference on images or use remote UDFs with Doc AI to produce document extraction, the results can now be search indexed and used to support search access patterns.

Here’s an example of search index on data that is parsed from PDF files:

CREATE SEARCH INDEX my_index ON pdf_text_extract(ALL COLUMNS);
​
SELECT * FROM pdf_text_extract WHERE SEARCH(pdf_text, "Google");

Security and governance – We are extending BigQuery’s row-level security capabilities to help you secure objects in Google Cloud Storage. By securing specific rows in an object table, you can restrict the ability of end users to retrieve the signed URLs of corresponding URIs present in the table. This is a shared responsibility security model, for which administrators need to ensure that end users don’t have direct access to Google Cloud Storage, and use signed URLs from object tables as the only access mechanism.

Here’s an example of a policy for PII images that are secured to be first processed through a blur pipeline:

CREATE ROW ACCESS POLICY pii_data ON object_table_images
GRANT TO ("group:admin@example.com")
FILTER USING (ARRAY_LENGTH(metadata)=1 AND
metadata[OFFSET(0)].name="face_detected")

Soon, Dataplex will support object tables, allowing you to automatically create object tables in BigQuery and manage and govern unstructured data at scale.

Data sharing – You can now use Analytics Hub to share unstructured data with partners, customers and suppliers while not compromising on security and governance. Subscribers can consume the rows of object tables that are shared with them, and use signed URLs for unstructured data objects.

Getting Started

Submit this form to try these new capabilities that unlock the power of your unstructured data in BigQuery. Watch this demo to learn more about these new capabilities.

Special thanks to engineering leaders Amir Hormati, Justin Levandoski and Yuri Volobuev for contributing to this post.