<jupyter_start><jupyter_text>Deploy a BQML customer churn propensity model to Vertex AI for online predictions  Learning objectives * Train a BQML classifier to predict customer churn.* Evaluate the performance of a BQML classifier.* Explain your model with BQML Explainable AI.* Export a BQML model to Vertex AI Endpoint for online predictions.  Introduction: customer propensity modeling in the mobile gaming industry  Setup  Define constants<jupyter_code># Retrieve and set PROJECT_ID and REGION environment variables.
PROJECT_ID = !(gcloud config get-value core/project)
PROJECT_ID = PROJECT_ID[0]
BQ_LOCATION = 'US'
REGION = 'us-central1'<jupyter_output><empty_output><jupyter_text>Import libraries<jupyter_code>from google.cloud import bigquery
from google.cloud import aiplatform
import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>Create a GCS bucket for artifact storage<jupyter_code># Create a globally unique Google Cloud Storage bucket for artifact storage.
GCS_BUCKET = f"{PROJECT_ID}-bucket"
!gsutil mb -l $REGION gs://$GCS_BUCKET<jupyter_output>Creating gs://dougkelly-vertex-demos-bucket/...
ServiceException: 409 A Cloud Storage bucket named 'dougkelly-vertex-demos-bucket' already exists. Try another name. Bucket names must be globally unique across all Google Cloud projects, including those outside of your organization.<jupyter_text>Create a BigQuery dataset<jupyter_code>BQ_DATASET = f"{PROJECT_ID}:bqmlga4"
!bq mk --location={BQ_LOCATION} --dataset {BQ_DATASET}<jupyter_output>BigQuery error in mk operation: Dataset 'dougkelly-vertex-demos:bqmlga4' already
exists.<jupyter_text>Initialize the Vertex Python SDK client Import the Vertex SDK for Python into your Python environment and initialize it.<jupyter_code>aiplatform.init(project=PROJECT_ID, location=REGION, staging_bucket=f"gs://{GCS_BUCKET}")<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis (EDA) in BigQuery This lab uses a [public BigQuery dataset]() that contains raw event data from a real mobile gaming app called **Flood it!** ([Android app](), [iOS app]()).The data schema originates from Google Analytics for Firebase but is the same schema as Google Analytics 4.<jupyter_code># %%bigquery --project $PROJECT_ID

# SELECT 
#     *
# FROM
#   `firebase-public-project.analytics_153293282.events_*`
    
# TABLESAMPLE SYSTEM (1 PERCENT)
%%bigquery --project $PROJECT_ID

SELECT 
    COUNT(DISTINCT user_pseudo_id) as count_distinct_users,
    COUNT(event_timestamp) as count_events
FROM
  `firebase-public-project.analytics_153293282.events_*`<jupyter_output>Query complete after 0.00s: 100%|██████████| 4/4 [00:00<00:00, 1871.83query/s]                        
Downloading: 100%|██████████| 1/1 [00:01<00:00,  1.87s/rows]<jupyter_text>Dataset preparation in BigQuery  Defining churn for each user<jupyter_code>%%bigquery --project $PROJECT_ID

CREATE OR REPLACE VIEW bqmlga4.returningusers AS (
  WITH firstlasttouch AS (
    SELECT
      user_pseudo_id,
      MIN(event_timestamp) AS user_first_engagement,
      MAX(event_timestamp) AS user_last_engagement
    FROM
      `firebase-public-project.analytics_153293282.events_*`
    WHERE event_name="user_engagement"
    GROUP BY
      user_pseudo_id

  )
  SELECT
    user_pseudo_id,
    user_first_engagement,
    user_last_engagement,
    EXTRACT(MONTH from TIMESTAMP_MICROS(user_first_engagement)) as month,
    EXTRACT(DAYOFYEAR from TIMESTAMP_MICROS(user_first_engagement)) as julianday,
    EXTRACT(DAYOFWEEK from TIMESTAMP_MICROS(user_first_engagement)) as dayofweek,

    #add 24 hr to user's first touch
    (user_first_engagement + 86400000000) AS ts_24hr_after_first_engagement,

#churned = 1 if last_touch within 24 hr of app installation, else 0
IF (user_last_engagement < (user_first_engagement + 86400000000),
    1,
    0 ) AS churned,

#bounced = 1 if last_touch within 10 min, else 0
IF (user_last_engagement <= (user_first_engagement + 600000000),
    1,
    0 ) AS bounced,
  FROM
    firstlasttouch
  GROUP BY
    1,2,3
    );

SELECT 
  * 
FROM 
  bqmlga4.returningusers 
LIMIT 100;
%%bigquery --project $PROJECT_ID

SELECT
    bounced,
    churned, 
    COUNT(churned) as count_users
FROM
    bqmlga4.returningusers
GROUP BY 1,2
ORDER BY bounced
%%bigquery --project $PROJECT_ID

SELECT
    COUNTIF(churned=1)/COUNT(churned) as churn_rate
FROM
    bqmlga4.returningusers
WHERE bounced = 0<jupyter_output>Query complete after 0.00s: 100%|██████████| 4/4 [00:00<00:00, 1757.70query/s]                        
Downloading: 100%|██████████| 1/1 [00:01<00:00,  1.40s/rows]<jupyter_text>Extract user demographic features<jupyter_code>%%bigquery --project $PROJECT_ID

CREATE OR REPLACE VIEW bqmlga4.user_demographics AS (

  WITH first_values AS (
      SELECT
          user_pseudo_id,
          geo.country as country,
          device.operating_system as operating_system,
          device.language as language,
          ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num
      FROM `firebase-public-project.analytics_153293282.events_*`
      WHERE event_name="user_engagement"
      )
  SELECT * EXCEPT (row_num)
  FROM first_values
  WHERE row_num = 1
  );

SELECT
  *
FROM
  bqmlga4.user_demographics
LIMIT 10<jupyter_output>Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 828.59query/s] 
Downloading: 100%|██████████| 10/10 [00:01<00:00,  6.17rows/s]<jupyter_text>Aggregate user behavioral features<jupyter_code>%%bigquery --project $PROJECT_ID

SELECT
    event_name,
    COUNT(event_name) as event_count
FROM
    `firebase-public-project.analytics_153293282.events_*`
GROUP BY 1
ORDER BY
   event_count DESC
%%bigquery --project $PROJECT_ID

CREATE OR REPLACE VIEW bqmlga4.user_aggregate_behavior AS (
WITH
  events_first24hr AS (
    #select user data only from first 24 hr of using the app
    SELECT
      e.*
    FROM
      `firebase-public-project.analytics_153293282.events_*` e
    JOIN
      bqmlga4.returningusers r
    ON
      e.user_pseudo_id = r.user_pseudo_id
    WHERE
      e.event_timestamp <= r.ts_24hr_after_first_engagement
    )
SELECT
  user_pseudo_id,
  SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,
  SUM(IF(event_name = 'level_start_quickplay', 1, 0)) AS cnt_level_start_quickplay,
  SUM(IF(event_name = 'level_end_quickplay', 1, 0)) AS cnt_level_end_quickplay,
  SUM(IF(event_name = 'level_complete_quickplay', 1, 0)) AS cnt_level_complete_quickplay,
  SUM(IF(event_name = 'level_reset_quickplay', 1, 0)) AS cnt_level_reset_quickplay,
  SUM(IF(event_name = 'post_score', 1, 0)) AS cnt_post_score,
  SUM(IF(event_name = 'spend_virtual_currency', 1, 0)) AS cnt_spend_virtual_currency,
  SUM(IF(event_name = 'ad_reward', 1, 0)) AS cnt_ad_reward,
  SUM(IF(event_name = 'challenge_a_friend', 1, 0)) AS cnt_challenge_a_friend,
  SUM(IF(event_name = 'completed_5_levels', 1, 0)) AS cnt_completed_5_levels,
  SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,
FROM
  events_first24hr
GROUP BY
  1
  );

SELECT
  *
FROM
  bqmlga4.user_aggregate_behavior
LIMIT 10
%%bigquery --project $PROJECT_ID

CREATE OR REPLACE VIEW bqmlga4.ml_features AS (
    
  SELECT
    dem.user_pseudo_id,
    dem.country,
    dem.operating_system,
    dem.language,
    IFNULL(beh.cnt_user_engagement, 0) AS cnt_user_engagement,
    IFNULL(beh.cnt_level_start_quickplay, 0) AS cnt_level_start_quickplay,
    IFNULL(beh.cnt_level_end_quickplay, 0) AS cnt_level_end_quickplay,
    IFNULL(beh.cnt_level_complete_quickplay, 0) AS cnt_level_complete_quickplay,
    IFNULL(beh.cnt_level_reset_quickplay, 0) AS cnt_level_reset_quickplay,
    IFNULL(beh.cnt_post_score, 0) AS cnt_post_score,
    IFNULL(beh.cnt_spend_virtual_currency, 0) AS cnt_spend_virtual_currency,
    IFNULL(beh.cnt_ad_reward, 0) AS cnt_ad_reward,
    IFNULL(beh.cnt_challenge_a_friend, 0) AS cnt_challenge_a_friend,
    IFNULL(beh.cnt_completed_5_levels, 0) AS cnt_completed_5_levels,
    IFNULL(beh.cnt_use_extra_steps, 0) AS cnt_use_extra_steps,
    ret.user_first_engagement,
    ret.month,
    ret.julianday,
    ret.dayofweek,
    ret.churned,
    # 80% train (false), 20% evaluation (true)
    IF(ABS(MOD(FARM_FINGERPRINT(dem.user_pseudo_id), 10)) < 8, False, True) AS data_split
  FROM
    bqmlga4.returningusers ret
  LEFT OUTER JOIN
    bqmlga4.user_demographics dem
  ON 
    ret.user_pseudo_id = dem.user_pseudo_id
  LEFT OUTER JOIN 
    bqmlga4.user_aggregate_behavior beh
  ON
    ret.user_pseudo_id = beh.user_pseudo_id
  WHERE ret.bounced = 0
  );

SELECT
  *
FROM
  bqmlga4.ml_features
LIMIT 10
%%bigquery

SELECT data_split, COUNT(*)
FROM bqmlga4.ml_features
GROUP BY data_split<jupyter_output>Query complete after 0.00s: 100%|██████████| 14/14 [00:00<00:00, 6755.67query/s]                       
Downloading: 100%|██████████| 2/2 [00:01<00:00,  1.29rows/s]<jupyter_text>Train a BQML propensity model to predict customer churn<jupyter_code>MODEL_NAME='churn_xgb'
%%bigquery --project $PROJECT_ID

CREATE OR REPLACE MODEL bqmlga4.churn_xgb

OPTIONS(
  MODEL_TYPE="BOOSTED_TREE_CLASSIFIER",
  INPUT_LABEL_COLS=["churned"],
  DATA_SPLIT_METHOD="CUSTOM",
  DATA_SPLIT_COL="data_split",
  ENABLE_GLOBAL_EXPLAIN=True
) AS

SELECT
  * EXCEPT(user_pseudo_id)
FROM
  bqmlga4.ml_features
!pip list | grep 'google-cloud-bigquery'
%%bigquery --project $PROJECT_ID

CREATE OR REPLACE MODEL bqmlga4.churn_dnn

OPTIONS(
  MODEL_TYPE="DNN_CLASSIFIER",
  INPUT_LABEL_COLS=["churned"],
  DATA_SPLIT_METHOD="CUSTOM",
  DATA_SPLIT_COL="data_split",
#   ENABLE_GLOBAL_EXPLAIN=True,
#   NUM_INTEGRAL_STEPS=50,
  LEARN_RATE=0.01,
  BATCH_SIZE=32,
  HIDDEN_UNITS=[64,32],
  DROPOUT=0.1,
  ACTIVATION_FN="RELU"
) AS

SELECT
  * EXCEPT(user_pseudo_id)
FROM
  bqmlga4.ml_features<jupyter_output>Query complete after 0.00s: 100%|██████████| 3/3 [00:00<00:00, 1383.04query/s]<jupyter_text>Evaluate BQML model performance<jupyter_code>%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlga4.churn_xgb);
%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlga4.churn_dnn);
%%bigquery --project $PROJECT_ID

SELECT
  expected_label,
  _0 AS predicted_0,
  _1 AS predicted_1
FROM
  ML.CONFUSION_MATRIX(MODEL bqmlga4.churn_xgb)
%%bigquery df_roc --project $PROJECT_ID
SELECT * FROM ML.ROC_CURVE(MODEL bqmlga4.churn_xgb)
df_roc.plot(x="false_positive_rate", y="recall", title="AUC-ROC curve")<jupyter_output><empty_output><jupyter_text>Generate batch predict and explanations<jupyter_code>%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.PREDICT(MODEL bqmlga4.churn_dnn,
  (SELECT * FROM bqmlga4.ml_features WHERE data_split=True)) 
%%bigquery --project $PROJECT_ID

SELECT
  user_pseudo_id,
  churned,
  predicted_churned,
  predicted_churned_probs[OFFSET(0)].prob as probability_churned
  
FROM
  ML.PREDICT(MODEL bqmlga4.churn_dnn,
  (SELECT * FROM bqmlga4.ml_features WHERE data_split=True))
%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.GLOBAL_EXPLAIN(MODEL bqmlga4.churn_xgb)
ORDER BY
  attribution DESC<jupyter_output>Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 921.22query/s] 
Downloading: 100%|██████████| 18/18 [00:01<00:00, 11.37rows/s]<jupyter_text>Export a BQML model to Vertex AI for online predictions  Export BQML model to GCS<jupyter_code>!bq --location=$BQ_LOCATION extract \
--destination_format ML_XGBOOST_BOOSTER \
--model 'dougkelly-vertex-demos:bqmlga4.churn_xgb' \
'gs://dougkelly-vertex-demos-bucket/churn_xgb'<jupyter_output>Waiting on bqjob_r5b004b498bafea48_0000017b33a3d8b5_1 ... (2s) Current status: DONE<jupyter_text>Upload BQML model to Vertex AI from GCS<jupyter_code>IMAGE_URI='us-docker.pkg.dev/vertex-ai/prediction/xgboost-cpu.1-4:latest'
model = aiplatform.Model.upload(
    display_name=MODEL_NAME,
    artifact_uri='gs://dougkelly-vertex-demos-bucket/churn_dnn',
    serving_container_image_uri=IMAGE_URI,
)<jupyter_output>INFO:google.cloud.aiplatform.models:Creating Model
INFO:google.cloud.aiplatform.models:Create Model backing LRO: projects/617979904441/locations/us-central1/models/7813208791813455872/operations/3164169339733540864
INFO:google.cloud.aiplatform.models:Model created. Resource name: projects/617979904441/locations/us-central1/models/7813208791813455872
INFO:google.cloud.aiplatform.models:To use this Model in another session:
INFO:google.cloud.aiplatform.models:model = aiplatform.Model('projects/617979904441/locations/us-central1/models/7813208791813455872')<jupyter_text>Deploy a Vertex `Endpoint` for online predictions<jupyter_code>endpoint = model.deploy(
    traffic_split={"0": 100},
    machine_type="n1-standard-2",
)<jupyter_output>INFO:google.cloud.aiplatform.models:Creating Endpoint
INFO:google.cloud.aiplatform.models:Create Endpoint backing LRO: projects/617979904441/locations/us-central1/endpoints/6780292384308592640/operations/4445443433720446976
INFO:google.cloud.aiplatform.models:Endpoint created. Resource name: projects/617979904441/locations/us-central1/endpoints/6780292384308592640
INFO:google.cloud.aiplatform.models:To use this Endpoint in another session:
INFO:google.cloud.aiplatform.models:endpoint = aiplatform.Endpoint('projects/617979904441/locations/us-central1/endpoints/6780292384308592640')
INFO:google.cloud.aiplatform.models:Deploying model to Endpoint : projects/617979904441/locations/us-central1/endpoints/6780292384308592640
INFO:google.cloud.aiplatform.models:Deploy Endpoint model backing LRO: projects/617979904441/locations/us-central1/endpoints/6780292384308592640/operations/245836781197459456
INFO:google.cloud.aiplatform.models:Endpoint model deployed. Resource name: projects/617979904441[...]<jupyter_text>Query model for online predictions<jupyter_code>CATEGORICAL_FEATURES = ['country',
                        'operating_system',
                        'language']
from sklearn.preprocessing import OrdinalEncoder
def _build_cat_feature_encoders(cat_feature_list, gcs_bucket, model_name, na_value='Unknown'):
    """
    Args:
      cat_feature_list (list):
      gcs_bucket (str):
      model_name (str):
      na_value (str): default: 'Unknown'
    Returns:
      feature_encoders (dict):
    """
    
    feature_encoders = {}
    
    for idx, feature in enumerate(CATEGORICAL_FEATURES):
        feature_encoder = OrdinalEncoder(handle_unknown="use_encoded_value", unknown_value=-1)
        feature_vocab_file = f"gs://{GCS_BUCKET}/{MODEL_NAME}/assets/{idx}_categorical_label.txt"
        feature_vocab_df = pd.read_csv(feature_vocab_file, delimiter = "\t", header=None).fillna(na_value)
        feature_encoder.fit(feature_vocab_df.values)
        feature_encoders[feature] = feature_encoder
    
    return feature_encoders


def preprocess_xgboost(cat_feature_list):
    """
    """
    

    feature_int = feature_encoders[feature].transform([[test_instance_dict[feature]]]).item()
    test_instance_dict[feature] = feature_int
%%bigquery test_df --project $PROJECT_ID 

SELECT* EXCEPT (user_pseudo_id, churned, data_split)
FROM bqmlga4.ml_features LIMIT 1;
test_instance_dict = test_df.iloc[0].astype(str).to_dict()
test_instance_dict
endpoint.predict(instances=[list(test_instance_dict.values())])<jupyter_output><empty_output><jupyter_text>License<jupyter_code># Copyright 2021 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.100656731497853, 'fraction_numerical': 0.051338721899469565, 'mean_word_length': 5.109953703703703, 'pattern_counts': {'":': 1, '<': 73, '<?xml version=': 0, '>': 58, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '274910', 'license': 'Apache-2.0', 'n_tokens_mistral': 6384, 'n_tokens_neox': 5730, 'n_words': 1170, 'path': 'self-paced-labs/vertex-ai/vertex-bqml/lab_exercise.ipynb', 'repo_name': 'j143/training-data-analyst', 'size': '15836'}