Investing in Artificial Intelligence (AI) can bring a competitive advantage to your organization. If you’re in charge of an AI or Data Science team, you’ll want to measure and maximize the value that you’re providing. Here is some advice from our years of experience in the field. 

A checklist to embark on a project: 

As you embark on projects we’ve found it’s good to have the following areas covered: 

• Have a customer. It’s important to have a customer for your work, and that they  agree with what you’re trying to achieve. Be sure to know what value you’re delivering to them. 
• Have a business case.  This will rely on estimates and assumptions, and may take no more than a few minute’s work.  You should revise this, but always know what justifies your team’s effort, and what you (and your customer) expect to get in return. 
• Know what process you will change or create. You’ll want to put your work in production, so you have to be clear about what business operations are changing or created around your work and who needs to be involved to make it happen
• Have a measurement plan. You’ll want to show that ongoing work is impacting some relevant business indicator. Measure and show incremental value. The goal of these measurements is to establish what has changed because of your project that would otherwise not have changed. Be sure to account for other factors like seasonality or other business changes that may affect your measurements.
• Use all the above to get your organization’s support for your team and your work. 

What measures to use?

As you start the work, what measures and indicators can you use to show that your team’s work is useful for your organization?

How many decisions you make. A major function of ML is to automate and optimize decisions: which product to recommend, which route to follow, etc. Use logs to track how many decisions your systems are making. 

Changes to revenue or costs. Better and quicker decisions often lead to increased revenue or savings. If possible, measure it directly, otherwise estimate it (for example fuel costs saved from less distance traveled, or increased purchases from personalized offers). 

As an example, the Illinois Department of Employment Security is using Contact Center AI to rapidly deploy virtual agents to help more than 1 million citizens file unemployment claims. To measure success the team tracked the two outcomes:  (1) the number of web inquiries and voice calls they were able to handle, and (2) the overall cost of the call center after the implementation. Post implementation, they were able to observe more than 140,000 phone and web inquiries per day and over 40,000 after-hours calls per night. They  also anticipate an estimated annual cost savings of $100M based on an initial analysis of IDES’s virtual agent data (see more in the link to case study).

Implementation costs. The other side of increased revenue or savings, is to put your achievements in the context of how much they cost. Show the technology costs that your team incurs and, ideally, how you can deliver more value, more efficiently. 

How much time was saved.  If the team built a routing system then it saved travel time, if it built an email classifier then it saved reading time, etc. Quantify how many hours were given back to the organization thanks to the efficiency of your system. 

In the medical field, quicker diagnostics matter. Johns Hopkins University’s Brain Injury Outcomes (BIOS) Division has focused on studying brain hemorrhage aiming to improve medical outcomes. The team identified the time to insights as a key metric in measuring business success. They experimented with a range of cloud computing solutions like Dataflow, Cloud Healthcare API, Compute Engine, and AI Platform for distributed training to accelerate iterations. As a result, in their recent work they were able to accelerate insights from scans from approximately 500 patients from 2,500 hours to 90 minutes.

How many applications your team supports. Some of your organization’s operations don’t use ML (say reconciling financial ledgers) but others do. Know how many parts of your organization benefit from the optimization and automation your team builds.

User experience. You may be able to measure your customer’s experience: fewer complaints, better reviews, reduced latency, more interactions, etc. This is valid both for internal and external stakeholders. At Google we measure usage and regularly ask for feedback on any internal system or process.

One of our customers, The City of Memphis, is using VisionAI and ML to tackle a common but very challenging issue: identifying and addressing potholes.  The implementation team identified the percentage increase of potholes identified as one of the key metrics along with accuracy and cost savings. The solution captures video footage from it’s public vehicles and leverages Google Cloud capabilities like Compute Engine, AI Platform, and BigQuery to automate the review of videos.  The project increased  pothole detection by 75% with over 90% accuracy. By measuring and demonstrating these outcomes, the team proved the viability of a cost-effective, cloud-based machine learning model and is looking into new applications of AI and ML that will further improve city services and help it build a better future for its 652,000 residents. 

Acknowledgements

Filipe and Payam would like to thank our colleague and co-author Mona Mona (AI/ML Customer Engineer, Healthcare and lifesciences) who contributed equally to the writing.

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:

1. Build a vocabulary list using the review column
2. Convert the review column into sparse tensors
3. Train a classification model using the sparse tensors to predict the label (“positive” or “negative”)
4. 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.