!pip install google-cloud-bigquery

# Automatically restart kernel after installs
import IPython

app = IPython.Application.instance()
app.kernel.do_shutdown(True)

"""
Explanation: <table align="left">
  <td>
    <a href="https://console.cloud.google.com/ai-platform/notebooks/deploy-notebook?name=Churn%20prediction%20for%20game%20developers%20using%20Google%20Analytics%204%20%28GA4%29%20and%20BigQuery%20ML%20Notebook&download_url=https%3A%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fanalytics-componentized-patterns%2Fmaster%2Fgaming%2Fpropensity-model%2Fbqml%2Fbqml_ga4_gaming_propensity_to_churn.ipynb">
      <img src="https://cloud.google.com/images/products/ai/ai-solutions-icon.svg" alt="AI Platform Notebooks">Run on AI Platform Notebooks</a>
  </td>
  <td>
    <a href="https://github.com/GoogleCloudPlatform/analytics-componentized-patterns/blob/master/gaming/propensity-model/bqml/bqml_ga4_gaming_propensity_to_churn.ipynb">
      <img src="https://cloud.google.com/ml-engine/images/github-logo-32px.png" alt="GitHub logo">
      View on GitHub
    </a>
  </td>
</table>

Overview
This notebook shows you how you can train, evaluate, and deploy a propensity model in BigQuery ML to predict user retention on a mobile game, based on app measurement data from Google Analytics 4.
Propensity modeling in the mobile gaming industry
According to a 2019 study on 100K mobile games by the Mobile Gaming Industry Analysis, most mobile games only see a 25% retention rate for users after the first 24 hours, and any game "below 30% retention generally needs improvement". In light of this, using machine learning -- to identify the propensity that a user churn after day 1 -- can allow app developers to incentivize users at higher risk of churning to return.
To predict the propensity (a.k.a. likelihood) that a user will return vs churn, you can use classification algorithms, like logistic regression, XGBoost, neural networks, or AutoML Tables, all of which are available with BigQuery ML.
Propensity modeling in BigQuery ML
With BigQuery ML, you can train, evaluate and deploy our models directly within BigQuery using SQL, which saves time from needing to manually configure ML infrastructure. You can train and deploy ML models directly where the data is already stored, which also helps to avoid potential issues around data governance.
Using classification models that you train and deploy in BigQuery ML, you can predict propensity using the output of the models. The models outputs provide a probability score between 0 and 1.0 -- how likely the model predicts that the user will churn (1) or not churn (0).
Using the probability (propensity) scores, you can then, for example, target users who may not return on their own, but could potentially return if they are provided with an incentive or notification.
Not just churn -- propensity modeling for any behavior
Propensity modeling is not limited to predicting churn. In fact, you can calculate a propensity score for any behavior you may want to predict. For example, you may want to predict the likelihood a user will spend money on in-app purchases. Or, perhaps you can predict the likelihood of a user performing "stickier" behaviors such as adding and playing with friends, which could lead to longer-term retention and organic user growth. Whichever the case, you can easily modify this notebook to suit your needs, as the overall workflow will still be the same.
Scope of this notebook
Dataset
This notebook uses this public BigQuery dataset, 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; this notebook applies to use cases that use either Google Analytics for Firebase or Google Analytics 4 data.
Google Analytics 4 (GA4) uses an event-based measurement model. Events provide insight on what is happening in an app or on a website, such as user actions, system events, or errors. Every row in the dataset is an event, with various characteristics relevant to that event stored in a nested format within the row. While Google Analytics logs many types of events already by default, developers can also customize the types of events they also wish to log.
Note that as you cannot simply use the raw event data to train a machine learning model, in this notebook, you will also learn the important steps of how to pre-process the raw data into an appropriate format to use as training data for classification models.
Using your own GA4 data?
If you are already using a Google Analytics 4 property, follow [this guide]((https://support.google.com/analytics/answer/9823238) to learn how to export your GA4 data to BigQuery. Once the GA4 data is in BigQuery, there will be two tables:

events_
events_intraday_

For this notebook, you can replace the table in the FROM clause in SQL queries with your events_ table that is updated daily. The events_intraday_ table contains streaming data for the current day.
Note that if you use your own GA4 data, you may need to slightly modify some of the scripts in this notebook to predict a different output behavior or the types events in the training data that are specific to your use case. 
Using data from other non-Google Analytics data collection tools?
While this notebook provides code based on a Google Analytics dataset, you can also use your own dataset from other non-Google Analytics data collection tools. The overall concepts and process of propensity modeling will be the same, but you may need to customize the code in order to prepare your dataset into the training data format described in this notebook.
Objective and Problem Statement
The goal of this notebook is to provide an end-to-end solution for propensity modeling to predict user churn on GA4 data using BigQuery ML. Using the "Flood It!" dataset, based on a user's activity within the first 24 hrs of app installation, you will try various classification models to predict the propensity to churn (1) or not churn (0).
By the end of this notebook, you will know how to:
* Explore the export of Google Analytics 4 data on BigQuery
* Prepare the training data using demographic, behavioral data, and the label (churn/not-churn)
* Train classification models using BigQuery ML
* Evaluate classification models using BigQuery ML
* Make predictions on which users will churn using BigQuery ML
* Activate on model predictions
Costs
There is no cost associated with using the free version of Google Analytics and using the BigQuery Export feature. This tutorial uses billable components of Google Cloud Platform (GCP):

BigQuery
BigQuery ML

Learn about BigQuery pricing, BigQuery ML
pricing and use the Pricing
Calculator
to generate a cost estimate based on your projected usage.
Setup
PIP Install Packages and dependencies
End of explanation
"""


PROJECT_ID = "YOUR-PROJECT-ID"  # replace with your project id
REGION = "US"

"""
Explanation: Set up your GCP project
The following steps are required, regardless of your notebook environment.


Select or create a GCP project. When you first create an account, you get a $300 free credit towards your compute/storage costs.


Make sure that billing is enabled for your project.


Enable the AI Platform APIs and Compute Engine APIs.


Enter your project ID and region in the cell below. Then run the  cell to make sure the
Cloud SDK uses the right project for all the commands in this notebook.
End of explanation
"""


import pandas as pd
from google.cloud import bigquery

pd.set_option("display.float_format", lambda x: "%.3f" % x)

"""
Explanation: Import libraries and define constants
End of explanation
"""


DATASET_NAME = "bqmlga4"
!bq mk --location=$REGION --dataset $PROJECT_ID:$DATASET_NAME

"""
Explanation: Create a BigQuery dataset
In this notebook, you will need to create a dataset in your project called bqmlga4. To create it, run the following cell:
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT 
    *
FROM
  `firebase-public-project.analytics_153293282.events_*`
    
TABLESAMPLE SYSTEM (1 PERCENT)

"""
Explanation: The dataset
Using the sample gaming event data from Flood it!
The sample dataset contains raw event data, as shown in the next cell:
Note: Jupyter runs cells starting with %%bigquery as SQL queries
End of explanation
"""


%%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_*`

"""
Explanation: It may be helpful to take a look at the overall schema used in Google Analytics 4. As mentioned earlier, Google Analytics 4 uses an event based measurement model and each row in this dataset is an event. Click here to view the complete schema and details about each column. As you can see above, certain columns are nested records and contain detailed information:

app_info
device
ecommerce
event_params
geo
traffic_source
user_properties
items*
web_info*

* present by default in GA4 datasets
As we can see below, there are 15K users and 5.7M events in this dataset:
End of explanation
"""


%%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;

"""
Explanation: Preparing the training data
You cannot simply use raw event data to train a machine learning model as it would not be in the right shape and format to use as training data. So in this section, you will learn how to pre-process the raw data into an appropriate format to use as training data for classification models.
To predict which user is going to churn or return, the ideal training data format for classification should look like the following:  
|User ID|User demographic data|User behavioral data|Churned|
|-|-|-|-|
|User1|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|1
|User2|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|0
|User3|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|1
Characteristics of the training data:
- each row is a separate unique user ID
- feature(s) for demographic data
- feature(s) for behavioral data
- the actual label that you want to train the model to predict (e.g., 1 = churned, 0 = returned)
You can train a model with only demographic data or behavioral data, but having a combination of both will likely help you create a more predictive model. For this reason, in this section, you will learn how to pre-process the raw data to follow this training data format.
The following sections will walk you through preparing the demographic data, behavioral data, and the label before joining them all together as the training data.

Identifying the label for each user (churned or returned)
Extracting demographic data for each user
Extracting behavioral data for each user
Combining the label, demographic and behavioral data together as training data

Step 1: Identifying the label for each user
The raw dataset doesn't have a feature that simply identifies users as "churned" or "returned", so in this section, you will need to create this label based on some of the existing columns.
There are many ways to define user churn, but for the purposes of this notebook, you will predict 1-day churn as users who do not come back and use the app again after 24 hr of the user's first engagement. 
In other words, after 24 hr of a user's first engagement with the app:
- if the user shows no event data thereafter, the user is considered churned. 
- if the user does have at least one event datapoint thereafter, then the user is considered returned
You may also want to remove users who were unlikely to have ever returned anyway after spending just a few minutes with the app, which is sometimes referred to as "bouncing". For example, we can say want to build our model only on users who spent at least 10 minutes with the app (users who didn't bounce).
So your updated definition of a churned user for this notebook is:

"any user who spent at least 10 minutes on the app, but after 24 hour from when they first engaged with the app, never used the app again"

In SQL, since the raw data contains all of the events for every user, from their first touch (app installation) to their last touch, you can use this information to create two columns: churned and bounced.
Take a look at the following SQL query and the results:
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
    bounced,
    churned, 
    COUNT(churned) as count_users
FROM
    bqmlga4.returningusers
GROUP BY 1,2
ORDER BY bounced

"""
Explanation: For the churned column, churned=0 if the user performs an action after 24 hours since their first touch, otherwise if their last action was only within the first 24 hours, then churned=1.
For the bounced column, bounced=1 if the user's last action was within the first ten minutes since their first touch with the app, otherwise bounced=0. We can use this column to filter our training data later on, by conditionally querying for users where bounced = 0.
You might wonder how many of these 15k users bounced and returned? You can run the following query to check:
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
    COUNTIF(churned=1)/COUNT(churned) as churn_rate
FROM
    bqmlga4.returningusers
WHERE bounced = 0

"""
Explanation: For the training data, you will only end up using data where bounced = 0. Based on the 15k users, you can see that 5,557 (\~41%) users bounced within the first ten minutes of their first engagement with the app, but of the remaining 8,031 users, 1,883 users (\~23%) churned after 24 hours.
End of explanation
"""


%%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

"""
Explanation: Step 2. Extracting demographic data for each user
This section is focused on extracting the demographic information for each user. Different demographic information about the user is available in the dataset already, including app_info, device, ecommerce, event_params, geo. Demographic features can help the model predict whether users on certain devices or countries are more likely to churn.
For this notebook, you can start just with geo.country, device.operating_system, and device.language. If you are using your own dataset and have joinable first-party data, this section is a good opportunity to add any additional attributes for each user that may not be readily available in Google Analytics 4.
Note that a user's demographics may occasionally change (e.g. moving from one country to another). For simplicity, you will just use the demographic information that Google Analytics 4 provides when the user first engaged with the app as indicated by MIN(event_timestamp). This enables every unique user to be represented by a single row.
End of explanation
"""


%%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

"""
Explanation: Step 3. Extracting behavioral data for each user
Behavioral data in the raw event data spans across multiple events -- and thus rows -- per user. The goal of this section is to aggregate and extract behavioral data for each user, resulting in one row of behavioral data per unique user.
But what kind of behavioral data will you need to prepare? Since the end goal of this notebook is to predict, based on a user's activity within the first 24 hrs since app installation, whether that user will churn or return thereafter, then you will want to use behavioral data from the first 24 hrs in your training data. Later on, we can also extract some extra time-related features from user_first_engagement, such as the month or day of the first engagement.
Google Analytics automatically collects certain events that you can use to analyze behavior. In addition, there are certain recommended events for games. 
As a first step, you can explore all the unique events that exist in this dataset, based on event_name:
End of explanation
"""


%%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

"""
Explanation: For this notebook, to predict whether a user will churn or return, you can start by counting the number of times a user engages in the following event types:

user_engagement
level_start_quickplay
level_end_quickplay
level_complete_quickplay
level_reset_quickplay
post_score
spend_virtual_currency
ad_reward
challenge_a_friend
completed_5_levels
use_extra_steps

In SQL, you can aggregate the behavioral data by calculating the total number of times when each of the above event_names occurred in the data set per user.
If you are using your own dataset, you may have different event types that you can aggregate and extract. Your app may be sending very different event_names to Google Analytics so be sure to use events most suitable to your scenario.
End of explanation
"""


%%bigquery --project $PROJECT_ID

CREATE OR REPLACE VIEW bqmlga4.train AS (
    
  SELECT
    dem.*,
    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
  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.train
LIMIT 10

"""
Explanation: Note that in addition to frequency of performing an action, you can also include other behavioral features in this step such as the total amount of in-game currency they spent, or if they reached certain app-specifc milestones that may be more relevant to your app (e.g., gained a certain threshold amount of XP or leveled up at least 5 times). This is an opportunity for you to extend this notebook to suit your needs.
Step 4: Combining the label, demographic and behavioral data together as training data
In this section, you can now combine these three intermediary views (label, demographic, and behavioral data) into the final training data. Here you can also specify bounced = 0, in order to limit the training data only to users who did not "bounce" within the first 10 minutes of using the app.
End of explanation
"""


%%bigquery --project $PROJECT_ID

CREATE OR REPLACE MODEL bqmlga4.churn_logreg

OPTIONS(
  MODEL_TYPE="LOGISTIC_REG",
  INPUT_LABEL_COLS=["churned"]
) AS

SELECT
  *
FROM
  bqmlga4.train

"""
Explanation: Training the propensity model with BigQuery ML
In this section, using the training data you prepared, you will now train machine learning models in SQL using BigQuery ML. The remainder of the notebook will only use logistic regression, but you can also follow the optional code below to train other model types.
Choosing the model:
As this is a binary classification task, for simplicity, you can start with logistic regression, but you can also train other classification models like XGBoost, deep neural networks and AutoML Tables in BigQuery ML to calculate propensity scores. Each of these models will output a probability score (propensity) between 0 and 1.0 of how likely the model prediction is based on the training data. In this notebook, the model predicts whether the user will churn (1) or return (0) after 24 hours of the user's first engagement with the app.
|Model| model_type| Advantages | Disadvantages|
|-|-|-|-|
|Logistic Regression| LOGISTIC_REG (documentation)| Fast to train vs. other model types | May not have the highest model performance |
|XGBoost| BOOSTED_TREE_CLASSIFIER (documentation)| Higher model performance. Can inspect feature importance. | Slower to train vs. LOGISTIC_REG.|
|Deep Neural Networks| DNN_CLASSIFIER (documentation)| Higher model performance | Slower to train vs. LOGISTIC_REG.|
|AutoML Tables| AUTOML_CLASSIFIER (documentation)| Very high model performance | May take at least a few hours to train, not easy to explain how the model works. |
There's no need to split your data into train/test:
- When you run the CREATE MODEL statement, BigQuery ML will automatically split your data into training and test, so you can evaluate your model immediately after training (see the documentation for more information or how to specify the split manually).
Hyperparameter tuning:
Note that you can also tune hyperparameters for each model, although it is beyond the scope of this notebook. See the BigQuery ML documentation for CREATE MODEL for further details on the available hyperparameters.
TRANSFORM(): 
It may also be useful to extract features from datetimes/timestamps as one simple example of additional feature preprocessing before training. For example, we can extract the month, day of year, and day of week from user_first_engagement.  TRANSFORM() allows the model to remember the extracted values so you won't need to extract them again when making predictions using the model later on.
Train a logistic regression model
The following code trains a logistic regression model. This should only take a minute or two to train.
For more information on the default hyperparameters used, you can read the documentation:
CREATE MODEL statement
End of explanation
"""


# %%bigquery --project $PROJECT_ID

# CREATE OR REPLACE MODEL bqmlga4.churn_xgb

# OPTIONS(
#   MODEL_TYPE="BOOSTED_TREE_CLASSIFIER",
#   INPUT_LABEL_COLS=["churned"]
# ) AS

# SELECT
#   * EXCEPT(user_pseudo_id)
# FROM
#   bqmlga4.train

"""
Explanation: Train an XGBoost model (optional)
The following code trains an XGBoost model. This may take several minutes to train.
For more information on the default hyperparameters used, you can read the documentation:
CREATE MODEL statement for Boosted Tree models using XGBoost
End of explanation
"""


# %%bigquery --project $PROJECT_ID

# CREATE OR REPLACE MODEL bqmlga4.churn_dnn

# OPTIONS(
#   MODEL_TYPE="DNN_CLASSIFIER",
#   INPUT_LABEL_COLS=["churned"]
# ) AS

# SELECT
#   * EXCEPT(user_pseudo_id)
# FROM
#   bqmlga4.train

"""
Explanation: Train a deep neural network (DNN) model  (optional)
The following code trains a deep neural network. This may take several minutes to train.
For more information on the default hyperparameters used, you can read the documentation:
CREATE MODEL statement for Deep Neural Network (DNN) models
End of explanation
"""


# %%bigquery --project $PROJECT_ID

# CREATE OR REPLACE MODEL bqmlga4.churn_automl

# OPTIONS(
#   MODEL_TYPE="AUTOML_CLASSIFIER",
#   INPUT_LABEL_COLS=["churned"],
#   BUDGET_HOURS=1.0
# ) AS

# SELECT
#   * EXCEPT(user_pseudo_id)
# FROM
#   bqmlga4.train

"""
Explanation: Train an AutoML Tables model  (optional)
AutoML Tables enables you to automatically build state-of-the-art machine learning models on structured data at massively increased speed and scale. AutoML Tables automatically searches through Google’s model zoo for structured data to find the best model for your needs, ranging from linear/logistic regression models for simpler datasets to advanced deep, ensemble, and architecture-search methods for larger, more complex ones.
You can train an AutoML model directly with BigQuery ML, as in the code below.
Note that the BUDGET_HOURS parameter is for AutoML Tables training, specified in hours. The default value is 1.0 hour and must be between 1.0 and 72.0. The total query processing time can be greater than the budgeted hours specified in the query.
Note: This may take a few hours to train.
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlga4.churn_logreg)

"""
Explanation: Model Evaluation
To evaluate the model, you can run ML.EVALUATE on a model that has finished training to inspect some of the metrics.
The metrics are based on the test sample data that was automatically split during model creation (documentation).
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
  expected_label,
  _0 AS predicted_0,
  _1 AS predicted_1
FROM
  ML.CONFUSION_MATRIX(MODEL bqmlga4.churn_logreg)

"""
Explanation: ML.EVALUATE generates the precision, recall, accuracy and f1_score using the default classification threshold of 0.5, which can be modified by using the optional THRESHOLD parameter.
Generally speaking, you can use the log_loss and roc_auc metrics to compare  model performance.
The log_loss ranges between 0 and 1.0, and the closer the log_loss is the zero, the closer the predicted labels were to the actual labels.
The roc_auc ranges between 0 and 1.0, and the closer the roc_auc is to 1.0, the better the model is at distinguishing between the classes.
For more information on these metrics, you can read through the definitions on precision and recall, accuracy, f1-score, log_loss and roc_auc.
Confusion matrix: predicted vs actual values
In addition to model evaluation metrics, you may also want to use a confusion matrix to inspect how well the model predicted the labels, compared to the actual labels.
With the rows indicating the actual labels, and the columns as the predicted labels, the resulting format for ML.CONFUSION_MATRIX for binary classification looks like:
| | Predicted_0 | Predicted_1|
|-|-|-|
|Actual_0| True Negatives | False Positives|
|Actual_1| False Negatives | True Positives|
For more information on confusion matrices, you can read through a detailed explanation here.
End of explanation
"""


%%bigquery df_roc --project $PROJECT_ID
SELECT * FROM ML.ROC_CURVE(MODEL bqmlga4.churn_logreg)

df_roc

"""
Explanation: ROC Curve
You can plot the AUC-ROC curve by using ML.ROC_CURVE to return the metrics for different threshold values for the model (documentation).
End of explanation
"""


df_roc.plot(x="false_positive_rate", y="recall", title="AUC-ROC curve")

"""
Explanation: Plot the AUC-ROC curve
End of explanation
"""


%%bigquery --project $PROJECT_ID

SELECT
  *
FROM
  ML.PREDICT(MODEL bqmlga4.churn_logreg,
  (SELECT * FROM bqmlga4.train)) #can be replaced with a test dataset

"""
Explanation: Model prediction
You can run ML.PREDICT to make predictions on the propensity to churn. The following code returns all the information from ML.PREDICT.
End of explanation
"""


%%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_logreg,
  (SELECT * FROM bqmlga4.train)) #can be replaced with a proper test dataset

"""
Explanation: For propensity modeling, the most important output is the probability of a behavior occuring. The following query returns the probability that the user will return after 24 hrs. The higher the probability and closer it is to 1, the more likely the user is predicted to churn, and the closer it is to 0, the more likely the user is predicted to return.
End of explanation
"""


%%bigquery df --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_logreg,
  (SELECT * FROM bqmlga4.train)) #can be replaced with a proper test dataset

df.head()

"""
Explanation: Exporting the predictions out of Bigquery
Reading the predictions directly from BigQuery
With the predictions from ML.PREDICT, you can export the data into a Pandas dataframe using the BigQuery Storage API (see documentation and code samples). You can also use other BigQuery client libraries.
Alternatively you can also export directly into pandas in a notebook using the %%bigquery <variable name> as in:
End of explanation
"""


%%bigquery --project $PROJECT_ID

EXPORT DATA OPTIONS (
uri="gs://mybucket/myfile/churnpredictions.csv", 
  format=CSV
) AS 
SELECT
  user_pseudo_id,
  churned,
  predicted_churned,
  predicted_churned_probs[OFFSET(0)].prob as probability_churned
FROM
  ML.PREDICT(MODEL bqmlga4.churn_logreg,
  (SELECT * FROM bqmlga4.train)) #can be replaced with a proper test dataset

"""
Explanation: Export predictions table to Google Cloud Storage
There are several ways to export the predictions table to Google Cloud Storage (GCS), so that you can use them in a separate service. Perhaps the easiest way is to export directly to GCS using SQL (documentation).
End of explanation
"""


