BigQuery ML for Sentiment Analysis: How to Make the Most of Your Data
1826
Of your peers have already read this article.
4:30 Minutes
The most insightful time you'll spend today!
Introduction
We recently announced BigQuery support for sparse features which help users to store and process the sparse features efficiently while working with them. That functionality enables users to represent sparse tensors and train machine learning models directly in the BigQuery environment. Being able to represent sparse tensors is a useful feature because sparse tensors are used extensively in encoding schemes like TF-IDF as part of data pre-processing in NLP applications and for pre-processing images with a lot of dark pixels in computer vision applications.
There are numerous applications of sparse features such as text generation and sentiment analysis. In this blog, we’ll demonstrate how to perform sentiment analysis with the space features in BigQuery ML by training and inferencing machine learning models using a public dataset. This blog also highlights how easy it is to work with unstructured text data on BigQuery, an environment traditionally used for structured data.
Using sample IMDb dataset
Let’s say you want to conduct a sentiment analysis on movie reviews from the IMDb website. For the benefit of readers who want to follow along, we will be using the IMDb reviews dataset from BigQuery public datasets. Let’s look at the top 2 rows of the dataset.
Although the reviews table has 7 columns, we only use reviews and label columns to perform sentiment analysis for this case. Also, we are only considering negative and positive values in the label columns. The following query can be used to select only the required information from the dataset.
SELECT
review,
label,
FROM
`bigquery-public-data.imdb.reviews`
WHERE
label IN ('Negative', 'Positive')The top 2 rows of the result is as follows:
Methodology
Based on the dataset that we have, the following steps will be carried out:
- Build a vocabulary list using the review column
- Convert the review column into sparse tensors
- Train a classification model using the sparse tensors to predict the label (“positive” or “negative”)
- Make predictions on new test data to classify reviews as positive or negative.
Feature engineering
In this section, we will convert the text from the reviews column to numerical features so that we can feed them into a machine learning model. One of the ways is the bag-of-words approach where we build a vocabulary using the words from the reviews and select the most common words to build numerical features for model training. But first, we must extract the words from each review. The following code creates a dataset and a table with row numbers and extracted words from reviews.
-- Create a dataset named `sparse_features_demo` if doesn’t exist
CREATE SCHEMA IF NOT EXISTS sparse_features_demo;
-- Select unique reviews with only negative and positive labels
CREATE OR REPLACE TABLE sparse_features_demo.processed_reviews AS (
SELECT
ROW_NUMBER() OVER () AS review_number,
review,
REGEXP_EXTRACT_ALL(LOWER(review), '[a-z]{2,}') AS words,
label,
split
FROM (
SELECT
DISTINCT review,
label,
split
FROM
`bigquery-public-data.imdb.reviews`
WHERE
label IN ('Negative', 'Positive')
)
);The output table from the query above should look like this:
The next step is to build a vocabulary using the extracted words. The following code creates a vocabulary including word frequency and word index from reviews. For this case, we are going to select only the top 20,000 words to reduce the computation time.
-- Create a vocabulary using train dataset and select only top 20,000 words based on frequency
CREATE OR REPLACE TABLE sparse_features_demo.vocabulary AS (
SELECT
word,
word_frequency,
word_index
FROM (
SELECT
word,
word_frequency,
ROW_NUMBER() OVER (ORDER BY word_frequency DESC) - 1 AS word_index
FROM (
SELECT
word,
COUNT(word) AS word_frequency
FROM
sparse_features_demo.processed_reviews,
UNNEST(words) AS word
WHERE
split = "train"
GROUP BY
word
)
)
WHERE
word_index < 20000 # Select top 20,000 words based on word count
);The following shows the top 10 words based on frequency and their respective index from the resulting table of the query above.
Creating a sparse feature
Now we will use the newly added feature to create a sparse feature in BigQuery. For this case, we aggregate word_index and word_frequency in each review, which generates a column as ARRAY[STRUCT] type. Now, each review is represented as ARRAY[(word_index, word_frequency)].
-- Generate a sparse feature by aggregating word_index and word_frequency in each review.
CREATE OR REPLACE TABLE sparse_features_demo.sparse_feature AS (
SELECT
review_number,
review,
ARRAY_AGG(STRUCT(word_index, word_frequency)) AS feature,
label,
split
FROM (
SELECT
DISTINCT review_number,
review,
word,
label,
split
FROM
sparse_features_demo.processed_reviews,
UNNEST(words) AS word
WHERE
word IN (SELECT word FROM sparse_features_demo.vocabulary)
) AS word_list
LEFT JOIN
sparse_features_demo.vocabulary AS topk_words
ON
word_list.word = topk_words.word
GROUP BY
review_number,
review,
label,
split
);Once the query is executed, a sparse feature named `feature` will be created. That `feature` column is an `ARRAY of STRUCT` column which is made of `word_index` and `word_frequency` columns. The picture below displays the resulting table at a glance.
Training a BigQuery ML model
We just created a dataset with a sparse feature in BigQuery. Let’s see how we can use that dataset to train with a machine learning model with BigQuery ML. In the following query, we will train a logistic regression model using the review_number, review, and feature to predict the label:
-- Train a logistic regression classifier using the data with sparse feature
CREATE OR REPLACE MODEL sparse_features_demo.logistic_reg_classifier
TRANSFORM (
* EXCEPT (
review_number,
review
)
)
OPTIONS(
MODEL_TYPE='LOGISTIC_REG',
INPUT_LABEL_COLS = ['label']
) AS
SELECT
review_number,
review,
feature,
label
FROM
sparse_features_demo.sparse_feature
WHERE
split = "train"
;Now that we have trained a BigQuery ML Model using a sparse feature, we evaluate the model and tune it as needed.
-- Evaluate the trained logistic regression classifier
SELECT * FROM ML.EVALUATE(MODEL sparse_features_demo.logistic_reg_classifier);The score looks like a decent starting point, so let’s go ahead and test the model with the test dataset.
-- Evaluate the trained logistic regression classifier using test data
SELECT * FROM ML.EVALUATE(MODEL sparse_features_demo.logistic_reg_classifier,
(
SELECT
review_number,
review,
feature,
label
FROM
sparse_features_demo.sparse_feature
WHERE
split = "test"
)
);
The model performance for the test dataset looks satisfactory and it can now be used for inference. One thing to note here is that since the model is trained on the numerical features, the model will only accept numeral features as input. Hence, the new reviews have to go through the same transformation steps before they can be used for inference. The next step shows how the transformation can be applied to a user-defined dataset.
Sentiment predictions from the BigQuery ML model
All we have left to do now is to create a user-defined dataset, apply the same transformations to the reviews, and use the user-defined sparse features to perform model inference. It can be achieved using a WITH statement as shown below.
WITH
-- Create a user defined reviews
user_defined_reviews AS (
SELECT
ROW_NUMBER() OVER () AS review_number,
review,
REGEXP_EXTRACT_ALL(LOWER(review), '[a-z]{2,}') AS words
FROM (
SELECT "What a boring movie" AS review UNION ALL
SELECT "I don't like this movie" AS review UNION ALL
SELECT "The best movie ever" AS review
)
),
-- Create a sparse feature from user defined reviews
user_defined_sparse_feature AS (
SELECT
review_number,
review,
ARRAY_AGG(STRUCT(word_index, word_frequency)) AS feature
FROM (
SELECT
DISTINCT review_number,
review,
word
FROM
user_defined_reviews,
UNNEST(words) as word
WHERE
word IN (SELECT word FROM sparse_features_demo.vocabulary)
) AS word_list
LEFT JOIN
sparse_features_demo.vocabulary AS topk_words
ON
word_list.word = topk_words.word
GROUP BY
review_number,
review
)
-- Evaluate the trained model using user defined data
SELECT review, predicted_label FROM ML.PREDICT(MODEL sparse_features_demo.logistic_reg_classifier,
(
SELECT
*
FROM
user_defined_sparse_feature
)
);Here is what you would get for executing the query above:
And that’s it! We just performed a sentiment analysis on the IMDb dataset from a BigQuery Public Dataset using only SQL statements and BigQuery ML. Now that we have demonstrated how sparse features can be used with BigQuery ML models, we can’t wait to see all the amazing projects that you would create by harnessing this functionality.
If you’re just getting started with BigQuery, check out our interactive tutorial to begin exploring.
A Recap on Google Cloud Databases and Storage Options- Part 2
3962
Of your peers have already read this article.
6:00 Minutes
The most insightful time you'll spend today!
Isaac Newton said, “If I have seen farther than others it is because I have stood on the shoulders of giants.” He meant that in order to explain the Law of Gravity, he used the work of major thinkers who came before him in order to make intellectual progress (as-in giving credit). In the 1640’s (yes, the time of the English Civil War and the start of the mini Ice Age), we can say the Age of Reason began and the word “data” was also (re)born in a way.
Why? Hard to assert a specific reason, it could be apropos of all those events of that decade, and somewhere amidst all that, the scientists and Churchmen (rather Churchman, Henry Hammond who really coined the term data) started to pen down their credit in books and there was a continued proliferation of data to reason their finding (and most times to reason why their work was better than that of the others). And thus comes to us the word datum from the Latin verb dare (it means “to give”, not the English dare).
Dare to Recap History?
Data is history captured through language (it has become the future as well, but that is for another day). Now we all like history (well, most of us). But it is highly likely the context gets lost in the complexity and style of definition. One way to mitigate that risk is to have a clear set of definitions (language), sustained hold of events (history), a clean process of capture (extract) and a scalable process for translation and aggregation (transform). If we want our data to be successful and rise to the occasion, then we need to keep these ways to mitigate the risk of complexity in mind.
And this is exactly what we discussed in the Part 1 of this blog series, Data Modeling Basics—the various business attributes, technical aspects, design questions, and considerations for designing your database model.
In this blog…
We will look into the different databases and storage options in Google Cloud, a brief note on each one of them, when to choose one over the other, interesting alternatives, exceptions and if you make it to the end of the blog, a fun challenge to make sure we put this little tech nugget to an ACID test (see what I did there?). If you are a cloud enthusiast, a database practitioner, a data geek, or a general wonderer of life with computing, you may find this engaging…
Google Cloud Storage Options
We at Google Cloud, have realized how hard it is to go through these laundry list assessment aspects and have made it simpler for you with a Decision Tree. (Of course, It ain’t Christmas if not for the tree):
If only the world was always “Structured”
In a structured world, you will know all the attributes on a first-name basis (I mean to say that you will have a well defined fixed set of attributes that can be modeled in a table of rows and columns), and the applications are transactional or analytical in orientation. Transactional Structured Data operate one row at a time generally and they need to adhere to ACID compliance. (Ah. Now you connect the dots, if not already.) ACID properties are Atomicity, Consistency, Isolation, and Durability. Cloud SQL and Cloud Spanner are our Google Cloud choices for Transactional Structured Data use cases.
Let’s look at the below aspects for each type and structure of data:
- Why that option? (highlights and key features)
- When to choose?
- When not to choose?
- Security aspects
Cloud SQL
- Fully Managed, cloud-native RDBMS (Relational DataBase Management System) that offers both MySQL, PostgreSQL, SQL Server engines
- Cloud SQL is accessible from apps running on App Engine, GKE, or Compute Engine
Note: A managed database is one that does not require as much administration and operational support (creating databases, performing backups, updating the operating system of database instances) as an unmanaged database.
When to use Cloud SQL?
- Typical online transaction processing (OLTP) workloads
- Lift and shift of on-premise SQL databases (or from anywhere else) to cloud
- Regional applications that do not need to store > 30 TB of data in a single instance
When not to use Cloud SQL?
Cloud SQL is not an appropriate storage system for online analytical processing (OLAP) workloads or data that requires dynamic schemas on a per-object basis.
Security
Data stored is encrypted both in transit and at rest. Have built-in support for access control, using network firewalls to manage database access.
Cloud Spanner
Relational, horizontally scalable, global database with strong consistency- Supports schemas, ACID transactions, and SQL queries (ANSI 2011)
- Scales horizontally in regions, but can also scale across regions for workloads that have more stringent availability requirements
When to use Cloud Spanner?
- For large amounts of data and when you require high transactional consistency
- When you require sharding for higher throughput, access and low latency
When not to use Cloud Spanner?
Cloud Spanner is not an appropriate storage system for online analytical processing (OLAP) workloads
Security
Security features in Spanner include data-layer encryption, audit logging, and Identity and Access Management (IAM) integration.
Analytical Structure is when we want the data to tell us an aggregated or enhanced story, for which we use limited columns and multiple rows and hence mostly use a Column-Oriented storage mechanism. Column-oriented storage is if we want to store the data in the tables by columns instead of by rows, and this column-oriented storage is done to efficiently access only a subset of columns for querying. BigQuery is the data warehouse option for analytics needs.
BigQuery
BigQuery is a fully managed Data Warehouse for analytics with built-in data transfer service- Peta-byte scale, low-cost warehouse that supports loading data through the web interface, command line tools, and REST API calls
- Incorporates features for machine learning, business intelligence, and geospatial analysis that are provided through BigQuery ML, BI Engine, and GIS.
Note: A data warehouse stores large quantities of data for query and analysis instead of transactional processing.
When to use BigQuery?
For use cases that cover process analytics and optimization, big data (Petabyte scale) processing and analytics, data warehouse modernization, machine learning-based behavioral analytics, and predictions
When not to use BigQuery?
BigQuery is not a Transactional database and is oriented on running analytical queries, not for simple CRUD operations and queries.
Security
BigQuery provides encryption at rest and in transit. Cloud Data Loss Prevention (Cloud DLP) can be used to scan the BigQuery tables and to protect sensitive data and meet compliance requirements. BigQuery supports access control of datasets and tables using Identity and Access Management (IAM).
And then we have the Semi-structured and the Unstructured world of data that we will address in the below sections.
Cloud Firestore (Cloud Datastore)
Firestore is the next major version of Datastore and a re-branding of the product. Taking the best of Datastore and the Firebase Realtime Database, Firestore is a NoSQL document database built for automatic scaling, high performance, and ease of application development.
- A fully managed, serverless NoSQL Google Cloud database designed for the development of serverless apps that stores JSON data
- Can be used to store, sync, and query data for web, mobile, and IoT applications
- Automatically handles sharding and replication making it highly available, durable, and scalable
- Provides ACID transactions, SQL-like queries, indexes, and more
- If a client does not have network connectivity, the Firestore API lets your app persist data to a local disk and synchronizes itself with the current server state once connectivity is reestablished
When to use?
For use cases of app development, live synchronization, offline support, multi-user collaborative applications, leader board, etc.
When not to use?
Not a relational database so not meant for relational structured data use cases.
Security
Firestore Security Rules support serverless authentication and authorization for the mobile and web client libraries. Identity and Access Management (IAM) manages database access.
Cloud Bigtable
- Bigtable is a wide-column, fully managed, high-performance NoSQL database service designed for terabyte- to petabyte-scale workloads
- Bigtable is battle tested on Google internal Bigtable database infrastructure that powers Google Search, Google Analytics, Google Maps, and Gmail
- Provides consistent, low-latency, and high-throughput storage for large-scale NoSQL data
When to use?
- For large amounts of single key data and is preferable for low-latency, high throughput workloads
- For real-time app serving workloads and large-scale analytical workloads
When not to use?
While Bigtable is considered an OLTP system, it doesn’t support multi-row transactions, SQL queries or joins. For those use cases, consider either Cloud SQL or Datastore.
Security
- All the data at rest in Cloud Bigtable is encrypted using Google’s default encryption, by default.
- Instead of Google managing the encryption keys that protect your data, your Bigtable instance can also be protected using a key that you manage (customer-managed encryption keys (CMEK)) in Cloud Key Management Service (Cloud KMS).
Cloud Storage
- Google Cloud Storage is an object storage system that is durable and highly available, persists unstructured data like images, videos, data files, videos, backup, and other data
- It is unstructured and so the files in the cloud storage are atomic that you read the entire file but you cannot access specific blocks in the files
- Cloud Storage is available in multiple classes, depending on the availability and performance required for apps and services
- Standard – Offers the highest levels of availability and is appropriate for storing data that requires low-latency access
Nearline – Low-cost, highly durable, fast-access storage service for storing data that you access less than once per month
Coldline – Very-low-cost, highly durable, fast-access storage service for storing data that you intend to access less than once per quarter
Archive – Lowest-cost, highly durable, fast-access storage service for storing data that you intend to access less than once per year
Security
Files in Cloud Storage are organized by project into individual buckets. These buckets can support either custom access control lists (ACLs) or centralized identity and access management (IAM) controls.
Firebase Realtime Database
- Firebase is a realtime, NoSQL, Google Cloud database that is a part of the Firebase platform that allows you to store and sync data in real-time and includes caching capabilities for offline use
- Data is stored as JSON and synchronized in real-time to every connected client and remains available when app goes offline
When to use?
For mobile and web app development, development of apps that work across devices
When not to use?
Not in relational dataset use cases. The Realtime Database is a NoSQL database and as such has different optimizations and functionality compared to a relational database. The Realtime Database API is designed to only allow operations that can be executed quickly.
Security
The Realtime Database provides a flexible, expression-based rules language, called Firebase Realtime Database Security Rules, to define how your data should be structured and when data can be read from or written to. When integrated with Firebase Authentication, developers can define who has access to what data, and how they can access it.
That’s a rather packed read. But I hope you find this useful to understand comprehensively the basics of data, storage options and databases in Google Cloud Platform.
Next Steps, before I go…
In the blog part 1 of the series, I ended with an action item – “How would you model a NoSQL solution for an application that needs to query the lineage between individual entities that are represented in pairs?”.
Well, my answer is Firestore. As part of this episode, why don’t you take some time to go over the options and key aspects that attribute to this.
Renaulution: Renault’s story of migrating to 70 applications in 2 years
3326
Of your peers have already read this article.
3:00 Minutes
The most insightful time you'll spend today!
Editor’s note: Renault, the French automaker, embarked on a wholesale migration of its information systems—moving 70 applications to Google Cloud. Here’s how they migrated from Oracle databases to Cloud SQL for PostgreSQL.
The Renault Group, known for its iconic French cars has grown to include four complementary brands, and sold nearly 3 million vehicles in 2020. Following our company-wide strategic plan, “Renaulution,” we’ve shifted our focus over the past year from a car company integrating tech, to a tech company integrating cars that will develop software for our business. For the information systems group, that meant modernizing our entire portfolio and migrating 70 in-house applications (our quality and customer information systems) to Google Cloud. It was an ambitious project, but it’s paid off. In two years we migrated our Quality and Customer Satisfaction information systems applications, optimized our code, and cut costs thanks to managed database services. Compared to our on-premises infrastructure, using Google Cloud services and open-source technologies comes to roughly one dollar per user per year, which is significantly cheaper.
An ambitious journey to Google Cloud
We began our cloud journey in 2016 with digital projects integrating a new way of working and new technologies. These new technologies included those for agility at scale, data capabilities and CI/CD toolchain. Google Cloud stood out as the clear choice for its data capabilities. Not only are we using BigQuery and Dataflow to improve scaling and costs, but we are also now using fully managed database services like Cloud SQL for PostgreSQL. Data is a key asset for a modern car maker because it connects the car maker to the user, allows car makers to better understand usage and better informs what decisions we should make about our products and services. After we migrated our data lake to Google Cloud, it was a natural next step to move our front-end applications to Google Cloud so they would be easier to maintain and we could benefit from faster response times. This project was no small undertaking. For those 70 in-house applications (e.g. vehicle quality evaluation, statistical process control in plants, product issue management, survey analysis), for our information systems landscape, we had a range of technologies—including Oracle, MySQL, Java, IBM MQ, and CFT—with some applications created 20 years ago.
Champions spearhead each migration
Before we started the migration, we did a global analysis of the landscape to understand each application and its complexity. Then we planned a progressive approach, focusing on the smallest applications first such as those with a limited number of screens or with simple SQL queries, and saving the largest for last. Initially we used some automatic tools for the migration, but we learned very quickly nothing can replace the development team’s institutional knowledge. They served as our migration champions.
The apps go marching one by one
When we migrated our first few Oracle databases to Cloud SQL for PostgreSQL we tracked our learnings in an internal wiki to share common SQL patterns, which helped us speed up the process. For some applications, we simplified the architecture and took the opportunity to analyze and optimize SQL queries during the rework. We also used monitoring tools like Dynatrace and JavaMelody to ensure we improved the user experience.
The approach we developed was very successful—where database migration was initially seen as insurmountable, the entire migration project was completed in two years.
With on-premises applications it was hard for our developers to separate code performance from infrastructure limitations. So as part of our migration to Google Cloud, we optimized our applications with monitoring services. With these insights our team has more control over resources, which has reduced our maintenance and operations activity and resulted in faster, more stable applications. Plus, migrating to Cloud SQL has made it much easier for us to change our infrastructure as needed, add more power when necessary or even reduce our infrastructure size.
A new regime on Cloud SQL
Now that we’re running on Cloud SQL, we’ve improved performance even on large databases with many connected users. Thanks to built-in tools in the Google Cloud environment, we can now easily understand performance issues and quickly solve them. For example, we were able to reduce the duration of a heavy batch processing by a factor of three from nine to three hours. And we don’t have to wait for the installation of a new server, so our team can move faster. Beyond speed, we’ve also been able to cut costs. We optimized our code based on insights from monitoring tools, which not only enabled a more responsive application for the user, but it also reduced our costs because we’re not overprovisioned.
Learn more about the Renault Group and try out Cloud SQL today.
Seven-Eleven Japan Leverages Google Cloud’s Performance and Speed for Real-time Business Insights
6849
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
With the rise of technologies like smartphones, retailers have felt the pressure to meet evolving consumer needs and expectations. Seven-Eleven Japan(“SEJ”) has long been on the forefront of this thanks to the way they develop and invest in IT. However, in recent years, Japan’s leading convenience store chain has struggled to maintain its complex legacy systems at the rate needed to keep up with today’s rapid digitization, spurred on by the increasing proliferation of smartphones and an IT vendor-dependent structure.
Legacy systems limiting real-time responsiveness and innovation
Since its early days, SEJ has been proactive in adopting information technology, mainly relying on technology solutions from Japan’s leading vendors. But as the systems have grown, key business issues have been resolved using a vendor-dependent structure rather than being driven by SEJ’s own needs.
Datasets and business logic were combined and built into legacy environments, gradually leading to data silos. As a result, data was distributed across multiple systems, causing a variety of problems, including the inability to efficiently retrieve data when needed, delays in accessing data collected in individual stores, and difficulties taking measurements at the right time in business operations that require real-time responsiveness.
Connecting different systems also takes time and money, and the lead time for introducing new services—from planning to development and launch—has been longer than expected.
To solve these problems, SEJ’s IT department built “Seven Central”—a new platform for practical data use launched in 2020 to support the company’s future IT strategies and digital transformation initiatives.
At its core, Seven Central’s ultimate purpose is to allow real-time data views. Versatile, real-time datasets—such as point-of-sale (POS) data from 7-Eleven stores—are consolidated into a centralized location in the cloud. They created a simple data mart that provides data via an API to enable them to respond more quickly to requests from individual departments.
“In such uncertain times, it’s vital to use data to make quick decisions,” says Izuru Nishimura, Executive Officer and Head of ICT Department. “Each department across the entire company will be able to gain an immediate understanding of the situation based on the most up-to-date data and respond accordingly. This is why we built Seven Central.”
Google Cloud selected to help SEJ build and grow their data cloud
Today’s rapidly changing business environment has also highlighted the risk of IT support becoming a bottleneck. The long-term strategy is to gradually expand the datasets managed and collected in Seven Central according to business needs.
In the first phase, SEJ collected POS data from all 21,000+ stores to enable real-time analysis. Moving forward, they would like to collect other relevant data—for example, unstructured data, such as images and videos, or master datasets that are currently stored externally.
Google Cloud was already a top contender when SEJ started developing Seven Central in 2019. They compared various public cloud services besides Google Cloud, focusing on three main capabilities.
“We placed particular emphasis on service scalability to drive future digital transformation; security when handling data, which is the lifeline of our company; and finally, openness,” says Nishimura. He emphasizes that openness was perhaps the most important factor for choosing Google Cloud. Breaking away from the negative aspects of an entirely vendor-dependent system enabled them to build an agile development system with multiple vendors.
Google Cloud technologies including BigQuery and API management platform, Apigee, play a vital role in Seven Central. BigQuery’s high-speed processing at petabyte scale and fully managed infrastructure helped keep costs low during development and verification.
“Data is stored in a way that allows you to share it easily across organizations, which helps solve the issue of data silos from the perspective of scalability. I also like the fact there are some interesting features that could be used in the future—like BigQuery ML, which enables machine learning on BigQuery,” says Nishimura.
Apigee allows SEJ to separate datasets and business logic, which is one of the key points of Seven Central. While the trend these days is to standardize interfaces using an API, the reality tends to involve many different APIs rather than the introduction of one unified API. With Apigee, SEJ provides a single unified API for all of its data cloud, and they can now understand what data is used thanks to Apigee’s API usage visualizations.
“Right now, we collect data from all 21,000+ stores,” says Nishimura. “But in anticipation of a future expansion in business operations, we have designed a system that can scale up and run without issue, even if we were to have 30,000 stores, with 1,000 customers per store per day, purchasing five items per person.”
Real-time insights with BigQuery and Cloud Spanner
Google Cloud partner Cloud Ace came on board early in the planning phases. Based on their recommendations, SEJ decided to continue making full use of BigQuery to analyze data collected from all 21,000+ stores throughout Japan, while also using Cloud Spanner’s availability, near-unlimited scalability and transactional consistency to help achieve the real-time results needed for the project.
“Given that both the data and the regularity with which it is accessed are expected to steadily increase in the future, we chose Cloud Spanner as backend storage for data delivery via API. We consider it a good choice,” says Shota Kikuchi, General Manager, Consulting Department, Technology Division, Cloud Ace Co., Ltd.
Finally, they chose to use Google Cloud’s Stream Analytics Solutions messaging service for collecting POS data in real time, which can then be put to immediate use with Cloud Spanner and BigQuery.
High-speed responses exceed targets and create new value
Seven Central went live in September 2020 with surprising results.
They initially set a target time of one hour from when a customer makes a purchase to the point when Seven Central can use that data. But when the final system was first tried—it took barely a minute. Moving forward they estimate that the latest inventory data from the service side will become available within a few minutes of being added to the system.
“This is real innovation, and I must admit that I am quite surprised. As well as being able to solve existing issues, we also hope it will lead to new improvements and services that have been unimaginable up until now,” says Nishimura.
The team hopes to roll out the Seven Central platform in all companies affiliated with Seven & i Holdings—not just SEJ. They also plan to explore Google Cloud AI and machine learning technologies to take on challenges in new areas. For example, they are investigating the idea of clustering individual stores using BigQuery ML.
Seven Central has already attracted attention from many departments and received a lot of requests. Nishimura and his team say they hope to continue to grow Seven Central while still observing their fundamental principles—not including business logic, maintaining real-time results, and staying true to the uniqueness of SEJ.
Learn more about Google Cloud smart analytics solutions.
How AI-powered ML Models Helps Run Unemployment Claims Verification at Scale
8409
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
With unemployment application submissions reaching record numbers over the past year, state and local agencies in the United States have faced the challenge of processing unprecedented numbers of claims per week. The digital infrastructure most agencies have in place is unable to handle this volume, resulting in constituents waiting longer, and bad actors taking advantage of vulnerable systems. The Department of Labor Inspector General estimates that $63 billion in claims distributed is either an improper payment or fraud.
Validating claims also requires secure data sharing with other agencies for document and identity verification. Government leaders need a way to allow case adjudicators to quickly and confidently release backlogged claims, integrate with existing systems, and segment legitimate claims from potentially fraudulent ones — all within limited government budgets — securely and at scale.
Implementing a fraud detection solution on Google Cloud
States were under pressure to release payments, while also filtering out potentially fraudulent claims. SpringML and Google Cloud developed a framework to give adjudicators a reliable verification process that quickly filters potentially fraudulent claims, while processing the remaining claims so benefits reach citizens in a timely manner. SpringML and Google Cloud, applied AI-powered machine learning models to detect anomalous patterns in large datasets. Using Google Cloud tools, SpringML implemented a solution to streamline workflows, improve efficiencies, automate processes and identify potentially fraudulent claims.
SpringML used a variety of Google Cloud products to deliver a fraud detection solution, including:
- Google Cloud Storage to store and manage data
- BigQuery to store tabular data and BigQuery Machine Learning (BQML) to conduct machine learning on that data
- AutoML solutions to build predictive models and risk scoring
- Visualization tools such as Looker and Data Studio to present data and help government leaders make informed decisions.
Implementing machine learning to detect improper payments allows agencies to classify claims as “fraud” or “not fraud” based on the number of flags, as well as prioritize the most urgent claims. Deploying intelligent virtual agents to handle frequently asked questions meant that live agents could focus their time on more challenging cases.
Even once the pandemic is behind us, there will be bad actors trying to take advantage of overwhelmed or legacy systems. We’ve identified a few best practices for agencies managing enormous case loads and looking to improve improper payment analytics:
- Move your systems to the cloud. Many on-premises legacy systems can’t update their applications and scale to meet the volume of claims. Moving to a cloud environment enables rapid solution deployment and ingestion of large amounts of data without fear of overloading the system. The cloud scales with you–cost-effectively and securely.
- Understand patterns in the data. The answer is always in the data — we used deep analysis to help uncover suspicious patterns in large data sets. We implemented unsupervised machine learning to learn behaviors and create configurable rules that adjust to new information that comes into the system. We can uncover patterns that are likely associated with fraud – ones that a human might have missed.
- Use AI/ML tools to automate your existing systems and teams. These tools enable humans to work smarter and more efficiently. We automate anomaly detection and create dashboards for adjudicators to rapidly process claims. We are enabling the Wisconsin Department of Workforce Development by implementing automatic calculations and processing of recharge amounts, resulting in faster processing times and fewer human errors. Proactive fraud detection and timely calculation of recharge payment allowed DWD to ensure the benefits reached the right individuals.
- Build flexibility into your systems. We discovered that fraud patterns change over time. For instance,flags for fraud during March-May 2020 were vastly different from those we found in June-July 2020. Google Cloud tools make it easy to continually update algorithms to detect patterns and integrate external data sources.
Using Google Cloud tools, we can update digital infrastructure and incorporate machine learning best practices to help organizations efficiently process large volumes of claims and identify high probability fraudulent ones. SpringML provides consulting and implementation services and industry-specific analytics solutions that deliver high-impact business value to accelerate data-driven digital transformation. Learn more about fraud detection and how to improve improper payments analytics by watching our webinar.
Ahead of the Curve: 5 Data and AI Trends Set to Shape 2023
2279
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
How will your organization manage this year’s data growth and business requirements? Your actions and strategies involving data and AI will improve or undermine your organization’s competitiveness in the months and years to come. Our teams at Google Cloud have an eye on the future as we evolve our strategies to protect technology choice, simplify data integration, increase AI adoption, deliver needed information on demand, and meet security requirements.
Google Cloud worked with* IDC on multiple studies involving global organizations across industries in order to explore how data leaders are successfully addressing key data and AI challenges. We compiled the results in our 2023 Data and AI Trends report. In it, you’ll find the metrics-rich research behind the top five data and AI trends, along with tips and customer examples for incorporating them into your plans.
1: Show data silos the door
Given the increasing volumes of data we’re all managing, it’s no surprise that siloed transactional databases and warehousing strategies can’t meet modern demands. Organizations want to improve how they store, manage, analyze, and govern all their data, while reducing costs. They also want to eliminate conflicting insights from replicated data and empower everyone with fresh data.
A unified data cloud enables the integration of data and insights into transformative digital experiences and better decision making.
Andi Gutmans, GM and VP of Engineering for Databases, Google Cloud
In the report, you can learn how to adopt a unified data cloud that supports every stage of the data lifecycle so that you can improve data usage, accessibility, and governance. Inform your strategy by drawing on organizations’ examples such as a data fabric that improves customer experiences by connecting more than 80 data silos, as well as other unified data clouds that save money and simplify growth.
2: Usher in the age of the open data ecosystem
Data is the key to unlocking AI, speeding up development cycles, and increasing ROI. To protect against data and technology lock-in, more organizations are adopting open source software and open APIs.
Tweet this quote
Understand how you can simplify data integration, facilitate multicloud analytics, and use the technologies you want with an open data ecosystem, as described in the report. Learn from metrics about global open source adoption and public dataset usage. And explore how global companies adopted open data ecosystems to improve patient outcomes, increase website traffic by 25%, and cut operating costs by 90%.
3: Embrace the AI tipping point
Pulling useful information out of data is easier with AI and ML. Not only can you identify patterns and answer questions faster but the technologies also make it easier to solve problems at scale.
We’ve reached the AI tipping point. Whether people realize it or not, we’re already using applications powered by AI—every day. Social media platforms, voice assistants, and driving services are easy examples.
June Yang, VP, Cloud AI and Industry Solutions, Google Cloud
Organizations share how they’re reaching their goals using AI and ML by empowering “citizen data scientists” and having them focus on small wins first. Gain tips from Yang and other experts for developing your AI strategy. And read how organizations achieve outcomes such as a reduction of 7,400 tons per year in carbon emissions and a more than 200% increase in ROI from ad spend by using pattern recognition and other AI capabilities.
4: Infuse insights everywhere
Yesterday’s BI solutions have led to outdated insights and user fatigue with the status quo, based on generic metrics and old information. Research shows that as new tools come online, expectations for BI are changing, with companies revising their strategies to improve decision making, speed up the development of new revenue streams, and increase customer acquisition and retention by providing individuals with needed information on demand.
Organizations are equipping business decision-makers with the tools they need to incorporate required insights into their everyday workflows.
Kate Wright, Senior Director, Product Management, Google Cloud
In the report, you’ll discover why and how data leaders are rethinking their BI analytics strategies and applications to improve users’ trust and use of data in automated workflows, customizable dashboards, and on-demand reports. Global companies also share how they improve decision making with self-service BI, customer experiences with IoT analysis, and threat mitigation with embedded analytics.
5: Get to know your unknown data
Increasing data volumes can make it harder to know where and what data they store, which may create risk. Case in point: If a customer unexpectedly shares personally identifiable information during a recorded customer support call or chat session, that data might require specialized governance, which the standardized storage process may not provide.
If you don’t know what data you have, you cannot know that it’s accurately secured. You also don’t know what security risks you are incurring, or what security measures you need to take.
Anton Chuvakin, Senior Staff Security Consultant, Google Cloud
Check out the report to learn about data security risks that are often overlooked and how to develop proactive governance strategies for your sensitive data. You can also read how global organizations have increased customer trust and productivity by improving how they discover, classify, and manage their structured and unstructured data.
Be ready for what’s next
What’s exciting about these trends is that they’re enabling organizations across industries to realize very different goals using their choice of technologies. And although all the trends depend on each other, research shows you can realize measurable benefits whether you adopt one or all five.
Review the report yourself and learn how you can refine your organization’s data and AI strategies by drawing on the collective insights, experiences, and successes of more than 800 global organizations.
More Relevant Stories for Your Company
Quick Tips to Get the Most Out of Cloud Spanner
Today, IT Admins and DBAs are inundated with thankless tasks. But they no longer have to deal with that. With Cloud Spanner, they can focus on value-add and innovation instead of maintenance. Creating or scaling a globally replicated database for mission-critical apps now takes a handful of clicks. Industry-leading high-availability
Home Depot’s Interconnected Retail Experience by Virtue of Google Cloud Migration for SAP Applications
With nearly 2,300 stores, The Home Depot is the world’s largest home-improvement chain — a brand that professional contractors and DIYers alike have come to depend on. The home improvement industry continues to experience unprecedented demand and dramatic increases in online ordering accompanied by expanding consumer expectations for things like
Four Key Takeaways from Google and Quantum Metric’s Back-to-School Retail Benchmark Study
Is it September yet? Hardly! School is barely out for the summer. But according to Google and Quantum Metric research, the back-to-school and off-to-college shopping season – which in the U.S. is second only to the holidays in terms of purchasing volume1 – has already begun. For retailers, that means
AirAsia Turns to Google Cloud to refine Pricing, Increase Revenue, and Improve Customer Experience
AirAsia needed a platform incorporating products that could capture, process, analyze, and report on data, while delivering value for money and meeting its speed and availability requirements. The airline also wanted to minimise infrastructure management and system administration demands on its technology team members. The airline conducted a proof of
