Tech Blog

Delighting Consumers with Google Cloud CDP

July 17, 2023

Delighting Consumers with Google Cloud CDP

A CDP is a software platform that helps companies collect, store, and analyze customer data from all sources. This data can then be used to create personalized experiences for consumers, target relevant advertising, and measure the effectiveness of marketing campaigns.

Picture source

How to build CDP on Google Cloud

We can build CDP to do all of these things. Collect data from website, mobile app, social media, and point-of-sale systems. This data is then stored in BigQuery, Google’s cloud-based data warehouse. BigQuery is a powerful tool that allows Cymbal to quickly and easily analyze large amounts of data.

Then use Looker, Google’s data visualization platform, to create dashboards and reports that help them understand their customers. Looker makes it easy to visualize data in a variety of ways, so we can see how customers are interacting with our products and services.

We can also use Google Cloud’s machine learning capabilities to predict which customers are most likely to make a purchase. This information is then used to target those customers with personalized advertising.

Source : Demo: Tasting consumer success with a Customer Data Platform (CDP) built on Google Cloud

Google Cloud’s Consumer Centricity Platform helps businesses drive consumer centricity across their business by providing advanced personalization and marketing analytics, custom digital commerce experiences, and accelerated innovation. It is ready for the privacy-centric future of predictive customer engagement, and offers features such as breaking data silos, driving predictive engagements faster, augmenting consumer data, and taking advantage of Google’s secure-by-design infrastructure. It is used by leading global companies like Mondelez, P&G, and Reckitt.

For more details: Customer Data Platform on Google Cloud

Explainable AI in Looker and BigQuery ML

In today’s world, businesses are increasingly turning to artificial intelligence (AI) to improve their operations and make better decisions. However, one of the challenges with AI is that it can be difficult to understand how models make their predictions. This can be a problem for businesses that want to use AI to make decisions that affect people’s lives, such as in healthcare or finance.

Explainable AI is a field of AI that focuses on developing methods to make AI models more understandable to humans. This can be done by providing explanations of how a model makes its predictions, or by visualizing the model’s predictions.

Looker is a BI tool that helps stakeholders understand explainable AI from BQML. It does this by creating governed dashboards and data experiences using a unique semantic model. This allows end users to enter their own transaction details and view the prediction and model explanation without writing any code.

Building a Fraud Model in SQL Using BigQuery Machine Learning

BigQuery Machine Learning (BQML) is a tool that allows analysts to create machine learning models entirely in SQL. This can benefit organizations by democratizing data science capabilities, eliminating data governance and MLOps challenges, and allowing models to be trained and predictions made without moving any data.

In one example, a retail bank used BQML to create a model to predict fraud based on transaction amount, distance between a merchant and the customer’s home, and transaction time of day.

The sample dataset is publicly available to query. Make sure to create a dataset named retail_banking in your project to store the resulting ML datasets and models.

CREATE OR REPLACE TABLE retail_banking.training_data as (

SELECT

card_transactions.trans_id AS trans_id,

card_transactions.is_fraud AS is_fraud,

–amount for transaction: higher amounts are more likely to be fraud

cast(card_transactions.amount as FLOAT64) AS card_transactions_amount,

–distance from the customers home: further distances are more likely to be fraud

ST_DISTANCE((ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),

(cast(card_transactions.merchant_lat as FLOAT64)))), (ST_GeogPoint((cast(SPLIT(client.address,’|’)[OFFSET(4)] as float64)),

(cast(SPLIT(client.address,’|’)[OFFSET(3)] as float64))))) AS card_transactions_transaction_distance,

–hour that transaction occured: fraud occurs in middle of night (usually between midnight and 4 am)

EXTRACT(HOUR FROM TIMESTAMP(CONCAT(card_transactions.trans_date,’ ‘,card_transactions.trans_time)) ) AS card_transactions_transaction_hour_of_day

FROM `looker-private-demo.retail_banking.card_transactions` AS card_transactions

LEFT JOIN `looker-private-demo.retail_banking.card` AS card

ON card.card_number = card_transactions.cc_number

LEFT JOIN `looker-private-demo.retail_banking.disp` AS disp ON card.disp_id = disp.disp_id

LEFT JOIN `looker-private-demo.retail_banking.client`AS client ON disp.client_id = client.client_id );

BigQuery provides model fit metrics, training logs, and other model details.

CREATE OR REPLACE MODEL retail_banking.fraud_prediction

OPTIONS(model_type=’logistic_reg’, labels=[‘is_fraud’]) AS

SELECT * EXCEPT(trans_id)

FROM retail_banking.training_data

— Account for class imbalance. Alternatively, use AUTO_CLASS_WEIGHTS=True in the model options

WHERE (is_fraud IS TRUE) OR

(is_fraud IS NOT TRUE AND rand() <=

(SELECT COUNTIF(is_fraud)/COUNT(*) FROM retail_banking.training_data));

BigQuery ML supports a variety of models, including xgboost and deep neural networks. Explainability can be used with these models to understand how they make predictions.

Explainable AI in BQML

SELECT * FROM ML.EXPLAIN_PREDICT(MODEL retail_banking.fraud_prediction, (

SELECT ‘001’ as trans_id, 500.00 as card_transactions_amount, 600 as card_transactions_transaction_distance, 2 as card_transactions_transaction_hour_of_day

UNION ALL

SELECT ‘002’ as trans_id, 5.25 as card_transactions_amount, 2 as card_transactions_transaction_distance, 13 as card_transactions_transaction_hour_of_day

UNION ALL

SELECT ‘003’ as trans_id, 175.50 as card_transactions_amount, 45 as card_transactions_transaction_distance, 10 as card_transactions_transaction_hour_of_day

), STRUCT(0.55 AS threshold))

Creating a “What-If Scenario Dashboard” in Looker

BQML is a powerful tool for building machine learning models, but it can be difficult for non-technical stakeholders to understand and use. Looker provides a way to make BQML more accessible to these stakeholders by providing an interface that allows them to view model explanations and predictions without writing any code. This can help to improve trust, adoption, and overall success of AI initiatives.

BQML Predictions in Looker’s Semantic Model

The LookML pattern below creates a “What-If Scenario” dashboard. It defines a parameter for each hypothetical user input and builds a derived table using ML.EXPLAIN_PREDICT on a subquery with the user’s input parameters. This pattern should be modified based on your dataset, trained model, and desired user inputs. Alternatively, you can reference the existing BQML Looker blocks on the Looker marketplace for an end-to-end guide to using BigQuery Machine Learning with Looker.

view: model_user_predictions {

parameter: distance {type: number}

parameter: amount {type: number}

parameter: hour {type: number}

derived_table: {

sql:

SELECT * FROM ML.EXPLAIN_PREDICT(MODEL retail_banking.fraud_prediction, (

SELECT ‘001’ AS trans_id,

{% parameter amount %} AS card_transactions_amount,

{% parameter distance %} AS card_transactions_transaction_distance,

{% parameter hour %} AS card_transactions_transaction_hour_of_day

), STRUCT(0.50 AS threshold) );;

}

dimension: predicted_is_fraud {

type: string

sql: ${TABLE}.predicted_is_fraud ;;

}

Machine Learning Accelerator with Looker and BigQuery ML

Introduction:

Machine learning is a powerful tool that can be used to solve a variety of problems. However, it can be difficult and time-consuming to build and train machine learning models. The Machine Learning Accelerator with Looker and BigQuery ML makes it easy to build and deploy machine learning models without any coding or machine learning expertise.

How it works:

The Machine Learning Accelerator with Looker and BigQuery ML works by using Looker’s data visualization and exploration capabilities to create a machine learning model. Once the model is created, it can be deployed to BigQuery ML for production use.

Benefits:

There are many benefits to using the Machine Learning Accelerator with Looker and BigQuery ML. Some of the benefits include:

Easy to use: The Machine Learning Accelerator with Looker and BigQuery ML is easy to use, even for people with no machine learning experience.
Fast: The Machine Learning Accelerator with Looker and BigQuery ML can build and deploy machine learning models quickly.
Scalable: The Machine Learning Accelerator with Looker and BigQuery ML can scale to handle large amounts of data.
Accurate: The Machine Learning Accelerator with Looker and BigQuery ML can build accurate machine learning models.

The ML Accelerator can help businesses improve their decision-making by providing them with accurate and actionable insights. It can also help businesses save time and money by automating tasks that would otherwise be done manually.

We can follow the github app-ml-accelerator to get a comprehensive overview of how to start using ML Accelerator on Looker.

1. Choose a BigQuery Connection

You will need to select a BigQuery connection during installation. The application can only be used with a single connection to prevent data from moving between connections. The connection chosen will determine which Looker Explores will be accessible from within the application.

2. Adjust Service Account Roles

The service account used by the BigQuery connection chosen in Step 1 should have the following IAM predefined roles.

BigQuery Data Editor
BigQuery Job User
Vertex AI User

3. Create BigQuery Dataset for ML Models

Create a dataset (e.g., looker_bqml) in the BigQuery connection’s GCP project.

4. Install Application

The application can be installed directly from Looker Marketplace (recommended) or manually installed following the steps below.

Marketplace Install

Refer to the Looker Docs for installing a tool from Marketplace. Select the BigQuery connection name chosen in Step 1 during installation.

5. Configure Application with User Attributes

The application uses three Looker user attributes to store its configuration settings. The following user attributes are required for the application to work properly. Each user attribute needs to be named exactly as listed below with a data type of String. The recommended setting for user access is None.

Create the following user attributes and set their default values.

6. Create a Looker Role to Manage User Access

The application is designed for users with access to Explores and SQL Runner in Looker. Users will need the following permissions to use the application.

explore
use_sql_runner

Choose a the objective in ML Accelerator

7.1 Make sure you can query the data, then it is good to go

How to Debug?

Check the query information in the Admin->Database->Queries

Check the error message when you can’t query the data/

Conclusion:

The Machine Learning Accelerator with Looker and BigQuery ML is a powerful tool that can be used to solve a variety of problems. It is easy to use, fast, scalable, and accurate.

More information:Machine Learning Accelerator with Looker and BigQuery ML

Conclusion

Explainable AI is a powerful tool that can help businesses to understand and use AI more effectively. Looker and BigQuery ML provide a simple and easy way to create explainable AI models.

For more detailed instructions, please refer to the following resources:

Delighting Consumers with Google Cloud CDP