notebooks/official/workbench/gaming_churn_prediction/churn_prediction_for_game_developers.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DWW_k7u_zaER"
      },
      "outputs": [],
      "source": [
        "# Copyright 2022 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "py8EYwG_91Pn"
      },
      "source": [
        "# Churn prediction for game developers using Google Analytics 4 and BigQuery ML\n",
        "\n",
        "<table align=\"left\">\n",
        "\n",
        "  <td>\n",
        "<a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/workbench/gaming_churn_prediction/churn_prediction_for_game_developers.ipynb\" target='_blank'>\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Colab logo\"> Run in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "<a href=\"https://github.com/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/workbench/gaming_churn_prediction/churn_prediction_for_game_developers.ipynb\" target='_blank'>\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
        "      View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "<a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/vertex-ai-samples/main/notebooks/official/workbench/gaming_churn_prediction/churn_prediction_for_game_developers.ipynb\" target='_blank'>\n",
        "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\">\n",
        "      Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>                                                                                               \n",
        "</table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "81facaeb11b6"
      },
      "source": [
        "## Table of contents\n",
        "\n",
        "* [Overview](#section-1)\n",
        "* [Objective](#section-2)\n",
        "* [Dataset](#section-3)\n",
        "* [Costs](#section-4)\n",
        "* [Create a BigQuery dataset](#section-5)\n",
        "* [Explore data](#section-6)\n",
        "* [Preparing the training data](#section-7)\n",
        "\t* [Identifying the label for each user](#section-7-subsection-1)\n",
        "    * [Extracting demographic data for each user](#section-7-subsection-2)\n",
        "\t* [Extracting behavioral data for each user](#section-7-subsection-3)\n",
        "\t* [Combining the label, demographic and behavioral data together as training data](#section-7-subsection-4)\n",
        "* [Training the propensity model with BigQuery ML](#section-8)\n",
        "* [Model evaluation](#section-9)\n",
        "\t* [Confusion matrix: predicted vs actual values](#section-9-subsection-1)\n",
        "\t* [ROC Curve](#section-9-subsection-2)\n",
        "* [Model prediction](#section-10)  \n",
        "* [Export predictions table to Cloud Storage](#section-11)\n",
        "* [Clean up](#section-12)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "277178e15cdb"
      },
      "source": [
        "## Overview\n",
        "<a name=\"section-1\"></a>\n",
        "\n",
        "This tutorial shows you how to train, evaluate a propensity model in BigQuery ML to predict user retention on a mobile game, based on app measurement data from Google Analytics 4.\n",
        "\n",
        "Learn more about [Vertex AI Workbench](https://cloud.google.com/vertex-ai/docs/workbench/introduction) and Learn more about [BigQuery ML](https://cloud.google.com/vertex-ai/docs/beginner/bqml#machine_learning_directly_in)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sH0CZGku0BPp"
      },
      "source": [
        "### Objective\n",
        "<a name=\"section-2\"></a>\n",
        "\n",
        "In this tutorial, you learn how to train, evaluate a propensity model in BigQuery ML.\n",
        "\n",
        "This tutorial uses the following Google Cloud ML services and resources:\n",
        "* BigQuery.\n",
        "\n",
        "The steps performed include:\n",
        "\n",
        "* Explore an export of Google Analytics 4 data on BigQuery.\n",
        "* Prepare the training data using demographic, behavioral data, and labels (churn/not-churn).\n",
        "* Train an XGBoost model using BigQuery ML.\n",
        "* Evaluate a model using BigQuery ML.\n",
        "* Make predictions on which users will churn using BigQuery ML."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b07fc9940120"
      },
      "source": [
        "### Dataset\n",
        "<a name=\"section-3\"></a>\n",
        "\n",
        "This notebook uses [this public BigQuery dataset](https://console.cloud.google.com/bigquery?p=firebase-public-project&d=analytics_153293282&t=events_20181003&page=table), which contains raw event data from a real mobile gaming app called Flood It! ([Android app](https://play.google.com/store/apps/details?id=com.labpixies.flood), [iOS app](https://itunes.apple.com/us/app/flood-it!/id476943146?mt=8)). The [data schema](https://support.google.com/analytics/answer/7029846) originates from Google Analytics for Firebase, but is the same schema as [Google Analytics 4](https://support.google.com/analytics/answer/9358801); the techniques in this notebook can be applied to either Google Analytics for Firebase or Google Analytics 4 data.\n",
        "\n",
        "Google Analytics 4 (GA4) uses an [event-based](https://support.google.com/analytics/answer/9322688) 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.\n",
        "\n",
        "Note that as you cannot simply use the raw event data to train a machine learning model, this notebook shows you some important steps of how to pre-process the raw data into an appropriate format to use as training data for classification models."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "589ffe790261"
      },
      "source": [
        "### Costs\n",
        "<a name=\"section-4\"></a>\n",
        "\n",
        "This tutorial uses the following billable components of Google Cloud:\n",
        "\n",
        "* Vertex AI\n",
        "* BigQuery\n",
        "* Cloud Storage\n",
        "\n",
        "Learn about [Vertex AI\n",
        "pricing](https://cloud.google.com/vertex-ai/pricing), [BigQuery\n",
        "pricing](https://cloud.google.com/bigquery/pricing) and [Cloud Storage\n",
        "pricing](https://cloud.google.com/storage/pricing) and use the [Pricing\n",
        "Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OMUaAMn091Pu"
      },
      "source": [
        "### Install additional packages\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WCPcEIZc91Pu"
      },
      "outputs": [],
      "source": [
        "! pip3 install --quiet --upgrade pandas-gbq 'google-cloud-bigquery[bqstorage,pandas]'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e9255e3b156f"
      },
      "source": [
        "### Colab Only: Uncomment the following cell to restart the kernel"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0c0b2427998a"
      },
      "outputs": [],
      "source": [
        "# Automatically restart kernel after installs so that your environment can access the new packages\n",
        "# import IPython\n",
        "\n",
        "# app = IPython.Application.instance()\n",
        "# app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "435b8e413535"
      },
      "source": [
        "### Before you begin\n",
        "\n",
        "#### Set your project ID\n",
        "\n",
        "**If you don't know your project ID**, try the following:\n",
        "-  Run `gcloud config list`\n",
        "-  Run `gcloud projects list`\n",
        "-  See the support page: [Locate the project ID](https://support.google.com/googleapi/answer/7014113)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "be175254a715"
      },
      "outputs": [],
      "source": [
        "PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\"}\n",
        "\n",
        "# set the project id\n",
        "! gcloud config set project $PROJECT_ID"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2e6b8b324ce1"
      },
      "source": [
        "#### Region\n",
        "\n",
        "You can also change the `REGION` variable used by Vertex AI. \n",
        "Learn more about [Vertex AI regions](https://cloud.google.com/vertex-ai/docs/general/locations)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ae43d96c4b1b"
      },
      "outputs": [],
      "source": [
        "REGION = \"[your-region]\"  # @param {type: \"string\"}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6c43a8673066"
      },
      "source": [
        "### Authenticate your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you may have to manually authenticate. Follow the relevant instructions below.\n",
        "\n",
        "**1. Vertex AI Workbench** \n",
        "- Do nothing as you are already authenticated.\n",
        "\n",
        "**2. Local JupyterLab Instance,** uncomment and run."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fbc9cd30cc4b"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cd0da2c26879"
      },
      "source": [
        "**3. Colab,** uncomment and run:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a336a05c6149"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0461097edfa5"
      },
      "source": [
        "**4. Service Account or other**\n",
        "- See all the authentication options here: [Google Cloud Platform Jupyter Notebook Authentication Guide](https://github.com/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/notebook_authentication_guide.ipynb)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e5755d1a554f"
      },
      "source": [
        "### Create a Cloud Storage bucket\n",
        "\n",
        "Create a storage bucket to store intermediate artifacts such as datasets."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d2de92accb67"
      },
      "outputs": [],
      "source": [
        "BUCKET_NAME = \"your-bucket-name-unique\"  # @param {type:\"string\"}\n",
        "BUCKET_URI = f\"gs://{BUCKET_NAME}\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b72bfdf29dae"
      },
      "source": [
        "**Only if your bucket doesn't already exist**: Run the following cell to create your Cloud Storage bucket."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a4453435d115"
      },
      "outputs": [],
      "source": [
        "! gsutil mb -l $REGION $BUCKET_URI"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "06571eb4063b"
      },
      "source": [
        "#### UUID\n",
        "\n",
        "If you are in a live tutorial session, you might be using a shared test account or project. To avoid name collisions between users on resources created, you create a uuid for each instance session, and append it onto the name of resources you create in this tutorial."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "697568e92bd6"
      },
      "outputs": [],
      "source": [
        "import random\n",
        "import string\n",
        "\n",
        "\n",
        "# Generate a uuid of a specifed length(default=8)\n",
        "def generate_uuid(length: int = 8) -> str:\n",
        "    return \"\".join(random.choices(string.ascii_lowercase + string.digits, k=length))\n",
        "\n",
        "\n",
        "UUID = generate_uuid()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ad3b13ceaebe"
      },
      "source": [
        "### Import libraries and define constants"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "c05096f78e7e"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "\n",
        "import matplotlib.pyplot as plt\n",
        "from google.cloud import bigquery\n",
        "from google.cloud.bigquery import Client"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rlykz4_I91P1"
      },
      "source": [
        "#### Initialize BigQuery Client"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XtuQpJsj91P1"
      },
      "outputs": [],
      "source": [
        "client = Client(project=PROJECT_ID)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BlfwBj4P0rHD"
      },
      "source": [
        "## Create a BigQuery dataset\n",
        "<a name=\"section-5\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S4qnPgig91P1"
      },
      "source": [
        "If you are using ***Vertex AI Workbench managed notebooks instance***, every cell which starts with \"#@bigquery\" will be a SQL Query. If you are using Vertex AI Workbench user managed notebooks instance or Colab it will be a markdown cell."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X9TLkb8f0tCE"
      },
      "source": [
        "In this notebook, you create a dataset in your project. To create it, run the following cell:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9f9a7052f37d"
      },
      "source": [
        "#@bigquery\n",
        "-- create a dataset in BigQuery\n",
        "\n",
        "CREATE SCHEMA bqmlga4\n",
        "OPTIONS(\n",
        "  location=\"us\"\n",
        "  )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L0JQL6mO91P2"
      },
      "source": [
        "(**Optional**)If you are using Vertex AI Workbench managed notebooks instance, once the results from BigQuery are displayed in the above cell, click the **Query and load as DataFrame** button and execute the generated code stub to fetch the data into the current notebook as a dataframe.\n",
        "\n",
        "*Note: By default the data is loaded into a `df` variable, though this can be changed before executing the cell if required.*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JcXdSY_UC9NI"
      },
      "outputs": [],
      "source": [
        "dataset_id = \"bqmlga4\" + \"_\" + UUID"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dbLPODaG91P2"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE SCHEMA `{PROJECT_ID}.{dataset_id}`\n",
        "OPTIONS(\n",
        "  location=\"us\"\n",
        "  )\n",
        "\"\"\".format(\n",
        "    PROJECT_ID=PROJECT_ID, dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)\n",
        "\n",
        "print(query_job.result())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dcd477550f3f"
      },
      "source": [
        "## Explore data\n",
        "<a name=\"section-6\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wV7wi8X51eNw"
      },
      "source": [
        "The sample dataset contains raw event data, as shown in the next cell:\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "2wrjAyDa91P2"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT \n",
        "    *\n",
        "FROM\n",
        "  `firebase-public-project.analytics_153293282.events_*`\n",
        "    \n",
        "LIMIT 5\n",
        "\"\"\"\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wyOr7IQX91P3"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PYBMN963Qydl"
      },
      "source": [
        "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. [View the complete schema and details about each column](https://support.google.com/analytics/answer/7029846). As you can see above, specific columns are nested records and contain detailed information:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IaM8co6eRsOp"
      },
      "source": [
        "* `app_info`\n",
        "* `device`\n",
        "* `ecommerce`\n",
        "* `event_params`\n",
        "* `geo`\n",
        "* `traffic_source`\n",
        "* `user_properties`\n",
        "* `items` (present by default in GA4 datasets)\n",
        "* `web_info` (present by default in GA4 datasets)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dLUv-7xNRhAj"
      },
      "source": [
        "The query results below show that there are 15K users and 5.7M events in this dataset:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "74c054f90876"
      },
      "source": [
        "#@bigquery\n",
        "SELECT \n",
        "    COUNT(DISTINCT user_pseudo_id) as count_distinct_users,\n",
        "    COUNT(event_timestamp) as count_events\n",
        "FROM\n",
        "  `firebase-public-project.analytics_153293282.events_*`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "c22a9EbJ91P3"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT \n",
        "    COUNT(DISTINCT user_pseudo_id) as count_distinct_users,\n",
        "    COUNT(event_timestamp) as count_events\n",
        "FROM\n",
        "  `firebase-public-project.analytics_153293282.events_*`\n",
        "\"\"\"\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bh43xrEs91P4"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3iHaV9-q1k1i"
      },
      "source": [
        "## Preparing the training data\n",
        "<a name=\"section-7\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P358Jo_s8WC0"
      },
      "source": [
        "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 learn how to pre-process the raw data into an appropriate format to use as training data for classification models.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eXl1kSh1yPXk"
      },
      "source": [
        "To predict which user is going to _churn_ or _return_, the ideal training data format for classification should look like the following:  \n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xv8ibjMNy_bV"
      },
      "source": [
        "|User ID|User demographic data|User behavioral data|Churned|\n",
        "|-|-|-|-|\n",
        "|User1|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|1\n",
        "|User2|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|0\n",
        "|User3|(e.g., country, device_type)|(e.g., # of times they did something within a time period)|1\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HydYCB2jzrzn"
      },
      "source": [
        "Characteristics of the training data:\n",
        "- Each row is a separate unique user ID\n",
        "- Feature(s) for **demographic data**\n",
        "- Feature(s) for **behavioral data**\n",
        "- The actual **label** that you want to train the model to predict (for example, 1 = churned, 0 = returned)\n",
        "\n",
        "You can train a model with only demographic data or behavioral data, but having a combination of both likely help you create a more predictive model. For this reason, in this section, you learn how to pre-process the raw data to follow this training data format."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ICpTsrfg2-Cw"
      },
      "source": [
        "The following sections walk you through preparing the demographic data, behavioral data, and the label before joining them all together as the complete training dataset. The steps are:\n",
        "\n",
        "1. Identify the label for each user (churned or returned)\n",
        "1. Extract demographic data for each user\n",
        "1. Extract behavioral data for each user\n",
        "1. Combine the label, demographic data, and behavioral data together as training data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZYHefnNx21lO"
      },
      "source": [
        "#### Step 1: Identify the label for each user\n",
        "<a name=\"section-7-subsection-1\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Qt6a5Kv-25iq"
      },
      "source": [
        "The raw dataset doesn't have a feature that simply identifies users as \"churned\" or \"returned\", so in this section, you need to create this label based on some of the existing columns."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lgqxD30Hl6FM"
      },
      "source": [
        "There are many ways to define user churn, but for the purposes of this notebook, you predict 1-day churn as users who do not come back and use the app again after 24 hours of the user's first engagement. \n",
        "\n",
        "In other words, after 24 hours of a user's first engagement with the app:\n",
        "- if the user _shows no event data thereafter_, the user is considered **churned**. \n",
        "- if the user _does have at least one event datapoint thereafter_, then the user is considered **returned**\n",
        "\n",
        "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, you may want to build the model only on users who spent at least 10 minutes with the app (users who did not bounce).\n",
        "\n",
        "So your updated definition of a **churned user** for this notebook is:\n",
        "> \"any user who spent at least 10 minutes on the app, but after 24 hours from when they first engaged with the app, never used the app again\"\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_YyxwMQQ1uQW"
      },
      "source": [
        "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`.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d_JCCtuZVzne"
      },
      "source": [
        "Take a look at the following SQL query and the results:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eb5c29c83181"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE VIEW bqmlga4.returningusers AS (\n",
        "  WITH firstlasttouch AS (\n",
        "    SELECT\n",
        "      user_pseudo_id,\n",
        "      MIN(event_timestamp) AS user_first_engagement,\n",
        "      MAX(event_timestamp) AS user_last_engagement\n",
        "    FROM\n",
        "      `firebase-public-project.analytics_153293282.events_*`\n",
        "    WHERE event_name=\"user_engagement\"\n",
        "    GROUP BY\n",
        "      user_pseudo_id\n",
        "\n",
        "  )\n",
        "  SELECT\n",
        "    user_pseudo_id,\n",
        "    user_first_engagement,\n",
        "    user_last_engagement,\n",
        "    EXTRACT(MONTH from TIMESTAMP_MICROS(user_first_engagement)) as month,\n",
        "    EXTRACT(DAYOFYEAR from TIMESTAMP_MICROS(user_first_engagement)) as julianday,\n",
        "    EXTRACT(DAYOFWEEK from TIMESTAMP_MICROS(user_first_engagement)) as dayofweek,\n",
        "\n",
        "    (user_first_engagement + 86400000000) AS ts_24hr_after_first_engagement,\n",
        "\n",
        "IF (user_last_engagement < (user_first_engagement + 86400000000),\n",
        "    1,\n",
        "    0 ) AS churned,\n",
        "\n",
        "IF (user_last_engagement <= (user_first_engagement + 600000000),\n",
        "    1,\n",
        "    0 ) AS bounced,\n",
        "  FROM\n",
        "    firstlasttouch\n",
        "  GROUP BY\n",
        "    1,2,3\n",
        "    );\n",
        "\n",
        "SELECT \n",
        "  * \n",
        "FROM \n",
        "  bqmlga4.returningusers \n",
        "LIMIT 100;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DNMAe3jr91P6"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {dataset_id}.returningusers AS (\n",
        "  WITH firstlasttouch AS (\n",
        "    SELECT\n",
        "      user_pseudo_id,\n",
        "      MIN(event_timestamp) AS user_first_engagement,\n",
        "      MAX(event_timestamp) AS user_last_engagement\n",
        "    FROM\n",
        "      `firebase-public-project.analytics_153293282.events_*`\n",
        "    WHERE event_name=\"user_engagement\"\n",
        "    GROUP BY\n",
        "      user_pseudo_id\n",
        "\n",
        "  )\n",
        "  SELECT\n",
        "    user_pseudo_id,\n",
        "    user_first_engagement,\n",
        "    user_last_engagement,\n",
        "    EXTRACT(MONTH from TIMESTAMP_MICROS(user_first_engagement)) as month,\n",
        "    EXTRACT(DAYOFYEAR from TIMESTAMP_MICROS(user_first_engagement)) as julianday,\n",
        "    EXTRACT(DAYOFWEEK from TIMESTAMP_MICROS(user_first_engagement)) as dayofweek,\n",
        "\n",
        "    (user_first_engagement + 86400000000) AS ts_24hr_after_first_engagement,\n",
        "\n",
        "IF (user_last_engagement < (user_first_engagement + 86400000000),\n",
        "    1,\n",
        "    0 ) AS churned,\n",
        "\n",
        "IF (user_last_engagement <= (user_first_engagement + 600000000),\n",
        "    1,\n",
        "    0 ) AS bounced,\n",
        "  FROM\n",
        "    firstlasttouch\n",
        "  GROUP BY\n",
        "    1,2,3\n",
        "    );\n",
        "\n",
        "SELECT \n",
        "  * \n",
        "FROM \n",
        "  {dataset_id}.returningusers \n",
        "LIMIT 100;\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NF_UObpK91P6"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d10507bd2366"
      },
      "source": [
        "#@bigquery\n",
        "SELECT \n",
        "  * \n",
        "FROM \n",
        "  bqmlga4.returningusers "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IbL-Nxk-91P6"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT \n",
        "  * \n",
        "FROM \n",
        "  {dataset_id}.returningusers \n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CXbtfAT091P6"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FOoqPb2J2Q5f"
      },
      "source": [
        "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`.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sC3sIc0C2a4Z"
      },
      "source": [
        "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`. You can use this column to filter the training data later on by conditionally querying for users where `bounced = 0`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ulbfb8SY2fSM"
      },
      "source": [
        "You might wonder how many of these 15k users bounced and returned? You can run the following query to check:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "21119ffadd56"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "    bounced,\n",
        "    churned, \n",
        "    COUNT(churned) as count_users\n",
        "FROM\n",
        "    bqmlga4.returningusers\n",
        "GROUP BY 1,2\n",
        "ORDER BY bounced"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FvFvPgXq91P7"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "    bounced,\n",
        "    churned, \n",
        "    COUNT(churned) as count_users\n",
        "FROM\n",
        "    {dataset_id}.returningusers\n",
        "GROUP BY 1,2\n",
        "ORDER BY bounced\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xxO88qS391P7"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z29RsKJi2uwO"
      },
      "source": [
        "For the training data, you 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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f328a88b39e1"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "    COUNTIF(churned=1)/COUNT(churned) as churn_rate\n",
        "FROM\n",
        "    bqmlga4.returningusers\n",
        "WHERE bounced = 0"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "x8NDwFet91P8"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "    COUNTIF(churned=1)/COUNT(churned) as churn_rate\n",
        "FROM\n",
        "    {dataset_id}.returningusers\n",
        "WHERE bounced = 0\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lsIydME391P8"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "08a5324dac82"
      },
      "source": [
        "There are 23% churners in the data which is not bad for training a churn prediction model. If the class imbalance seems to be high, oversampling or undersampling techniques can be considered to balance the class distribution."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "daSQViux_XWR"
      },
      "source": [
        "#### Step 2: Extract demographic data for each user\n",
        "<a name=\"section-7-subsection-2\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7417ae7aa9c4"
      },
      "source": [
        "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`, and `geo`. Demographic features can help the model predict whether users on certain devices or countries are more likely to churn.\n",
        "\n",
        "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.\n",
        "\n",
        "Note that a user's demographics may occasionally change (e.g. moving from one country to another). For simplicity, you just use the demographic information that Google Analytics 4 provides when the user LAST engaged with the app as indicated by `MAX(event_timestamp)`. This enables every unique user to be represented by a single row."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7bb11498b1ea"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE VIEW bqmlga4.user_demographics AS (\n",
        "\n",
        "  WITH first_values AS (\n",
        "      SELECT\n",
        "          user_pseudo_id,\n",
        "          geo.country as country,\n",
        "          device.operating_system as operating_system,\n",
        "          device.language as language,\n",
        "          ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num\n",
        "      FROM `firebase-public-project.analytics_153293282.events_*`\n",
        "      WHERE event_name=\"user_engagement\"\n",
        "      )\n",
        "  SELECT * EXCEPT (row_num)\n",
        "  FROM first_values\n",
        "  WHERE row_num = 1\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.user_demographics\n",
        "LIMIT 10"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nMZrVP_i91P9"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {dataset_id}.user_demographics AS (\n",
        "\n",
        "  WITH first_values AS (\n",
        "      SELECT\n",
        "          user_pseudo_id,\n",
        "          geo.country as country,\n",
        "          device.operating_system as operating_system,\n",
        "          device.language as language,\n",
        "          ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC) AS row_num\n",
        "      FROM `firebase-public-project.analytics_153293282.events_*`\n",
        "      WHERE event_name=\"user_engagement\"\n",
        "      )\n",
        "  SELECT * EXCEPT (row_num)\n",
        "  FROM first_values\n",
        "  WHERE row_num = 1\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.user_demographics\n",
        "LIMIT 10\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fL9h6l2s91P9"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a25422405548"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.user_demographics\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "B42mcZqB91P-"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.user_demographics\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JxrUaUeu91P-"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "17ffc440f6ba"
      },
      "source": [
        "#### Step 3: Extract behavioral data for each user\n",
        "<a name=\"section-7-subsection-3\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b2f87ff04147"
      },
      "source": [
        "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.\n",
        "\n",
        "But what kind of behavioral data you need to prepare? Since the end goal of this notebook is to predict, based on a user's activity within the first 24 hours since app installation, whether that user will churn or return thereafter, then you want to use behavioral data from the first 24 hours in your training data. Later on, you can also extract some extra time-related features from `user_first_engagement`, such as the month or day of the first engagement.\n",
        "\n",
        "Google Analytics automatically collects [specific events](https://support.google.com/analytics/answer/6317485) that you can use to analyze behavior. In addition, there are recommended [events for games](https://support.google.com/analytics/answer/6317494). \n",
        "\n",
        "\n",
        "As a first step, you can explore all the unique events that exist in this dataset, based on `event_name`:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "da6075561092"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "    event_name,\n",
        "    COUNT(event_name) as event_count\n",
        "FROM\n",
        "    `firebase-public-project.analytics_153293282.events_*`\n",
        "GROUP BY 1\n",
        "ORDER BY\n",
        "   event_count DESC"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "99YAplzT91P-"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "    event_name,\n",
        "    COUNT(event_name) as event_count\n",
        "FROM\n",
        "    `firebase-public-project.analytics_153293282.events_*`\n",
        "GROUP BY 1\n",
        "ORDER BY\n",
        "   event_count DESC\n",
        "\"\"\"\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oWnvGphz91P-"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "074a03fae065"
      },
      "source": [
        "For this tutorial, 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:\n",
        "\n",
        "* `user_engagement`\n",
        "* `level_start_quickplay`\n",
        "* `level_end_quickplay`\n",
        "* `level_complete_quickplay`\n",
        "* `level_reset_quickplay`\n",
        "* `post_score`\n",
        "* `spend_virtual_currency`\n",
        "* `ad_reward`\n",
        "* `challenge_a_friend`\n",
        "* `completed_5_levels`\n",
        "* `use_extra_steps`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6d89bc9dfccc"
      },
      "source": [
        "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 dataset per user.\n",
        "\n",
        "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 suitable to your scenario."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b47843e3b022"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE VIEW bqmlga4.user_aggregate_behavior AS (\n",
        "WITH\n",
        "  events_first24hr AS (\n",
        "    SELECT\n",
        "      e.*\n",
        "    FROM\n",
        "      `firebase-public-project.analytics_153293282.events_*` e\n",
        "    JOIN\n",
        "      bqmlga4.returningusers r\n",
        "    ON\n",
        "      e.user_pseudo_id = r.user_pseudo_id\n",
        "    WHERE\n",
        "      e.event_timestamp <= r.ts_24hr_after_first_engagement\n",
        "    )\n",
        "SELECT\n",
        "  user_pseudo_id,\n",
        "  SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,\n",
        "  SUM(IF(event_name = 'level_start_quickplay', 1, 0)) AS cnt_level_start_quickplay,\n",
        "  SUM(IF(event_name = 'level_end_quickplay', 1, 0)) AS cnt_level_end_quickplay,\n",
        "  SUM(IF(event_name = 'level_complete_quickplay', 1, 0)) AS cnt_level_complete_quickplay,\n",
        "  SUM(IF(event_name = 'level_reset_quickplay', 1, 0)) AS cnt_level_reset_quickplay,\n",
        "  SUM(IF(event_name = 'post_score', 1, 0)) AS cnt_post_score,\n",
        "  SUM(IF(event_name = 'spend_virtual_currency', 1, 0)) AS cnt_spend_virtual_currency,\n",
        "  SUM(IF(event_name = 'ad_reward', 1, 0)) AS cnt_ad_reward,\n",
        "  SUM(IF(event_name = 'challenge_a_friend', 1, 0)) AS cnt_challenge_a_friend,\n",
        "  SUM(IF(event_name = 'completed_5_levels', 1, 0)) AS cnt_completed_5_levels,\n",
        "  SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,\n",
        "FROM\n",
        "  events_first24hr\n",
        "GROUP BY\n",
        "  1\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.user_aggregate_behavior\n",
        "LIMIT 10\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yaqslQ1Q91P_"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {dataset_id}.user_aggregate_behavior AS (\n",
        "WITH\n",
        "  events_first24hr AS (\n",
        "    SELECT\n",
        "      e.*\n",
        "    FROM\n",
        "      `firebase-public-project.analytics_153293282.events_*` e\n",
        "    JOIN\n",
        "      {dataset_id}.returningusers r\n",
        "    ON\n",
        "      e.user_pseudo_id = r.user_pseudo_id\n",
        "    WHERE\n",
        "      e.event_timestamp <= r.ts_24hr_after_first_engagement\n",
        "    )\n",
        "SELECT\n",
        "  user_pseudo_id,\n",
        "  SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,\n",
        "  SUM(IF(event_name = 'level_start_quickplay', 1, 0)) AS cnt_level_start_quickplay,\n",
        "  SUM(IF(event_name = 'level_end_quickplay', 1, 0)) AS cnt_level_end_quickplay,\n",
        "  SUM(IF(event_name = 'level_complete_quickplay', 1, 0)) AS cnt_level_complete_quickplay,\n",
        "  SUM(IF(event_name = 'level_reset_quickplay', 1, 0)) AS cnt_level_reset_quickplay,\n",
        "  SUM(IF(event_name = 'post_score', 1, 0)) AS cnt_post_score,\n",
        "  SUM(IF(event_name = 'spend_virtual_currency', 1, 0)) AS cnt_spend_virtual_currency,\n",
        "  SUM(IF(event_name = 'ad_reward', 1, 0)) AS cnt_ad_reward,\n",
        "  SUM(IF(event_name = 'challenge_a_friend', 1, 0)) AS cnt_challenge_a_friend,\n",
        "  SUM(IF(event_name = 'completed_5_levels', 1, 0)) AS cnt_completed_5_levels,\n",
        "  SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,\n",
        "FROM\n",
        "  events_first24hr\n",
        "GROUP BY\n",
        "  1\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.user_aggregate_behavior\n",
        "LIMIT 10\n",
        "\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ph-UAx0R91P_"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "89f8c6c44b9c"
      },
      "source": [
        "#### Step 4: Combine the label, demographic data, and behavioral data together as training data\n",
        "<a name=\"section-7-subsection-4\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ed161899b055"
      },
      "source": [
        "In this section, you can now combine these three intermediary views (label, demographic data, and behavioral data) into the final training dataset. 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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bd02c06df5e7"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE VIEW bqmlga4.train AS (\n",
        "    \n",
        "  SELECT\n",
        "    dem.*,\n",
        "    IFNULL(beh.cnt_user_engagement, 0) AS cnt_user_engagement,\n",
        "    IFNULL(beh.cnt_level_start_quickplay, 0) AS cnt_level_start_quickplay,\n",
        "    IFNULL(beh.cnt_level_end_quickplay, 0) AS cnt_level_end_quickplay,\n",
        "    IFNULL(beh.cnt_level_complete_quickplay, 0) AS cnt_level_complete_quickplay,\n",
        "    IFNULL(beh.cnt_level_reset_quickplay, 0) AS cnt_level_reset_quickplay,\n",
        "    IFNULL(beh.cnt_post_score, 0) AS cnt_post_score,\n",
        "    IFNULL(beh.cnt_spend_virtual_currency, 0) AS cnt_spend_virtual_currency,\n",
        "    IFNULL(beh.cnt_ad_reward, 0) AS cnt_ad_reward,\n",
        "    IFNULL(beh.cnt_challenge_a_friend, 0) AS cnt_challenge_a_friend,\n",
        "    IFNULL(beh.cnt_completed_5_levels, 0) AS cnt_completed_5_levels,\n",
        "    IFNULL(beh.cnt_use_extra_steps, 0) AS cnt_use_extra_steps,\n",
        "    ret.user_first_engagement,\n",
        "    ret.month,\n",
        "    ret.julianday,\n",
        "    ret.dayofweek,\n",
        "    ret.churned\n",
        "  FROM\n",
        "    bqmlga4.returningusers ret\n",
        "  LEFT OUTER JOIN\n",
        "    bqmlga4.user_demographics dem\n",
        "  ON \n",
        "    ret.user_pseudo_id = dem.user_pseudo_id\n",
        "  LEFT OUTER JOIN \n",
        "    bqmlga4.user_aggregate_behavior beh\n",
        "  ON\n",
        "    ret.user_pseudo_id = beh.user_pseudo_id\n",
        "  WHERE ret.bounced = 0\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.train\n",
        "LIMIT 10"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lzM9ZRy991QA"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {dataset_id}.train AS (\n",
        "    \n",
        "  SELECT\n",
        "    dem.*,\n",
        "    IFNULL(beh.cnt_user_engagement, 0) AS cnt_user_engagement,\n",
        "    IFNULL(beh.cnt_level_start_quickplay, 0) AS cnt_level_start_quickplay,\n",
        "    IFNULL(beh.cnt_level_end_quickplay, 0) AS cnt_level_end_quickplay,\n",
        "    IFNULL(beh.cnt_level_complete_quickplay, 0) AS cnt_level_complete_quickplay,\n",
        "    IFNULL(beh.cnt_level_reset_quickplay, 0) AS cnt_level_reset_quickplay,\n",
        "    IFNULL(beh.cnt_post_score, 0) AS cnt_post_score,\n",
        "    IFNULL(beh.cnt_spend_virtual_currency, 0) AS cnt_spend_virtual_currency,\n",
        "    IFNULL(beh.cnt_ad_reward, 0) AS cnt_ad_reward,\n",
        "    IFNULL(beh.cnt_challenge_a_friend, 0) AS cnt_challenge_a_friend,\n",
        "    IFNULL(beh.cnt_completed_5_levels, 0) AS cnt_completed_5_levels,\n",
        "    IFNULL(beh.cnt_use_extra_steps, 0) AS cnt_use_extra_steps,\n",
        "    ret.user_first_engagement,\n",
        "    ret.month,\n",
        "    ret.julianday,\n",
        "    ret.dayofweek,\n",
        "    ret.churned\n",
        "  FROM\n",
        "    {dataset_id}.returningusers ret\n",
        "  LEFT OUTER JOIN\n",
        "    {dataset_id}.user_demographics dem\n",
        "  ON \n",
        "    ret.user_pseudo_id = dem.user_pseudo_id\n",
        "  LEFT OUTER JOIN \n",
        "    {dataset_id}.user_aggregate_behavior beh\n",
        "  ON\n",
        "    ret.user_pseudo_id = beh.user_pseudo_id\n",
        "  WHERE ret.bounced = 0\n",
        "  );\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.train\n",
        "LIMIT 10\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rfuOI63N91QA"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "68a2aa3e63ce"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.train"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "foLF2W-_91QB"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.train\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)\n",
        "df = query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sKqXDbVz91QB"
      },
      "outputs": [],
      "source": [
        "df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "121ca5d0d531"
      },
      "source": [
        "Check the percentage of null values."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8c1a07395def"
      },
      "outputs": [],
      "source": [
        "(\n",
        "    100 * df[[\"operating_system\", \"language\", \"country\"]].isna().sum() / df.shape[0]\n",
        ").plot.bar(figsize=(15, 4))\n",
        "plt.title(\"Null-percentage of the columns\")\n",
        "plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "05bb76e78220"
      },
      "source": [
        "## Training the propensity model with BigQuery ML\n",
        "<a name=\"section-8\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ec1a7945f3cb"
      },
      "source": [
        "In this section, using the training data you prepared, you now train machine learning models in SQL using BigQuery ML."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fe89ff9223d7"
      },
      "source": [
        "You use an [XGBoost](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-boosted-tree) model here. 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.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1244e58f0560"
      },
      "source": [
        "#### Train an XGBoost model"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1bfabaccff00"
      },
      "source": [
        "The following code trains an XGBoost model. This may take several minutes.\n",
        "\n",
        "For more information on the default hyperparameters used, see [The CREATE MODEL statement for boosted tree models using XGBoost](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-boosted-tree)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6e04e73f353d"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE MODEL bqmlga4.churn_xgb\n",
        "\n",
        "OPTIONS(\n",
        "  MODEL_TYPE=\"BOOSTED_TREE_CLASSIFIER\",\n",
        "  DATA_SPLIT_METHOD='RANDOM',\n",
        "  DATA_SPLIT_EVAL_FRACTION=0.2,\n",
        "    \n",
        "  INPUT_LABEL_COLS=[\"churned\"]\n",
        ") AS\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  bqmlga4.train"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vE9eyg_591QC"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE MODEL {dataset_id}.churn_xgb\n",
        "\n",
        "OPTIONS(\n",
        "  MODEL_TYPE=\"BOOSTED_TREE_CLASSIFIER\",\n",
        "  DATA_SPLIT_METHOD='RANDOM',\n",
        "  DATA_SPLIT_EVAL_FRACTION=0.2,\n",
        "    \n",
        "  INPUT_LABEL_COLS=[\"churned\"]\n",
        ") AS\n",
        "\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  {dataset_id}.train\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vu-H3ICL91QD"
      },
      "outputs": [],
      "source": [
        "query_job.result()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6a189c676815"
      },
      "source": [
        "## Model evaluation\n",
        "<a name=\"section-9\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "33e0cb8eddd8"
      },
      "source": [
        "To evaluate the model, you can run [`ML.EVALUATE`](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate) on a model that has finished training to inspect some of the metrics.\n",
        "\n",
        "The metrics are based on the test sample data that was automatically split during model creation ([see the CREATE MODEL documentation for more information](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create#data_split_method))."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e736b0d3906b"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  ML.EVALUATE(MODEL bqmlga4.churn_xgb)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UTbi8gA691QD"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  ML.EVALUATE(MODEL {dataset_id}.churn_xgb)\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qIj17ASs91QD"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "56ab69bf8857"
      },
      "source": [
        "`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`](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate#eval_threshold) parameter.\n",
        "\n",
        "Generally speaking, you can use the `log_loss` and `roc_auc` metrics to compare  model performance.\n",
        "\n",
        "The `log_loss` ranges between 0 and 1.0, and the closer the `log_loss` is to zero, the closer the predicted labels were to the actual labels.\n",
        "\n",
        "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.\n",
        "\n",
        "For more information on these metrics, you can read through the definitions on [precision and recall](https://developers.google.com/machine-learning/crash-course/classification/precision-and-recall), [accuracy](https://developers.google.com/machine-learning/crash-course/classification/accuracy), [f1-score](https://en.wikipedia.org/wiki/F-score), [log_loss](https://en.wikipedia.org/wiki/Loss_functions_for_classification#Logistic_loss) and [roc_auc](https://developers.google.com/machine-learning/crash-course/classification/roc-and-auc)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a219caf7e0fc"
      },
      "source": [
        "#### Confusion matrix: predicted vs actual values\n",
        "<a name=\"section-9-subsection-1\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dc448a895918"
      },
      "source": [
        "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.\n",
        "\n",
        "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:\n",
        "\n",
        "| | Predicted_0 | Predicted_1|\n",
        "|-|-|-|\n",
        "|Actual_0| True Negatives | False Positives|\n",
        "|Actual_1| False Negatives | True Positives|\n",
        "\n",
        "For more information on confusion matrices, see [Classification: True vs. False and Positive vs. Negative](https://developers.google.com/machine-learning/crash-course/classification/true-false-positive-negative)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "89cf66f58b74"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  expected_label,\n",
        "  _0 AS predicted_0,\n",
        "  _1 AS predicted_1\n",
        "FROM\n",
        "  ML.CONFUSION_MATRIX(MODEL bqmlga4.churn_xgb)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rGqg47NS91QE"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  expected_label,\n",
        "  _0 AS predicted_0,\n",
        "  _1 AS predicted_1\n",
        "FROM\n",
        "  ML.CONFUSION_MATRIX(MODEL {dataset_id}.churn_xgb)\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pmiKyp5t91QE"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6c57eb63ee0b"
      },
      "source": [
        "#### ROC Curve\n",
        "<a name=\"section-9-subsection-2\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "126bf7ab6607"
      },
      "source": [
        "#@bigquery\n",
        "SELECT * FROM ML.ROC_CURVE(MODEL bqmlga4.churn_xgb)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "drD7syfx91QF"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT * FROM ML.ROC_CURVE(MODEL {dataset_id}.churn_xgb)\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "h35Fx8V791QF"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cc3d2981c877"
      },
      "source": [
        "## Model prediction\n",
        "<a name=\"section-10\"></a>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "431ffebc5667"
      },
      "source": [
        "You can run [`ML.PREDICT`](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-predict) to make predictions on the propensity to churn. The following code returns all of the information from `ML.PREDICT`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "befa36fe5913"
      },
      "source": [
        "#@bigquery\n",
        "CREATE OR REPLACE VIEW bqmlga4.prediction_data AS(\n",
        "(SELECT * FROM bqmlga4.train where churned=1 limit 10)\n",
        "union all\n",
        "(SELECT * FROM bqmlga4.train where churned=0 limit 20))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bYfx_IvK91QG"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE VIEW {dataset_id}.prediction_data AS(\n",
        "(SELECT * FROM {dataset_id}.train where churned=1 limit 10)\n",
        "union all\n",
        "(SELECT * FROM {dataset_id}.train where churned=0 limit 20))\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bxZotPpk91QG"
      },
      "outputs": [],
      "source": [
        "query_job.result()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6a6831e141b7"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  ML.PREDICT(MODEL bqmlga4.churn_xgb,\n",
        "  (SELECT * FROM bqmlga4.prediction_data)\n",
        "            ) "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pRTxjtLu91QH"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  *\n",
        "FROM\n",
        "  ML.PREDICT(MODEL {dataset_id}.churn_xgb,\n",
        "  (SELECT * FROM {dataset_id}.prediction_data)\n",
        "            ) \n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qqp5gwHQ91QH"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fccf138adcfa"
      },
      "source": [
        "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 hours. 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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e311ec7695fa"
      },
      "source": [
        "#@bigquery\n",
        "SELECT\n",
        "  user_pseudo_id,\n",
        "  churned,\n",
        "  predicted_churned,\n",
        "  predicted_churned_probs[OFFSET(0)].prob as probability_churned\n",
        "  \n",
        "FROM\n",
        "  ML.PREDICT(MODEL bqmlga4.churn_xgb,\n",
        "  (SELECT * FROM bqmlga4.train))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ofaONrnJ91QH"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "  user_pseudo_id,\n",
        "  churned,\n",
        "  predicted_churned,\n",
        "  predicted_churned_probs[OFFSET(0)].prob as probability_churned\n",
        "  \n",
        "FROM\n",
        "  ML.PREDICT(MODEL {dataset_id}.churn_xgb,\n",
        "  (SELECT * FROM {dataset_id}.train))\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wLUdpUCl91QH"
      },
      "outputs": [],
      "source": [
        "query_job.to_dataframe()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1af4216bbb3d"
      },
      "source": [
        "## Export the predictions table to Cloud Storage\n",
        "<a name=\"section-11\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2839b839f440"
      },
      "source": [
        "There are several ways to export the predictions table to Cloud Storage, so that you can use the information in a separate service. Perhaps the easiest way is to export directly to Cloud Storage using SQL. [Learn more about the EXPORT DATA statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements#export_data_statement)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AhogPydo91QI"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "CREATE OR REPLACE TABLE {dataset_id}.prediction_data_table AS (\n",
        "SELECT \n",
        "  * \n",
        "FROM \n",
        "  {dataset_id}.prediction_data\n",
        ")\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "query_job = client.query(query)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5y8By3d791QI"
      },
      "outputs": [],
      "source": [
        "query_job.result()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o1CeLVJL91QI"
      },
      "outputs": [],
      "source": [
        "FILE_PATH = BUCKET_URI + \"/\" + \"*.csv\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bAQSJjqz91QI"
      },
      "outputs": [],
      "source": [
        "query = \"\"\"\n",
        "EXPORT DATA OPTIONS (\n",
        "uri= @FILE_PATH, \n",
        "  format=CSV,\n",
        "  header=True, \n",
        "  overwrite=True \n",
        "    \n",
        ") AS \n",
        "SELECT\n",
        "  * from {dataset_id}.prediction_data_table\n",
        "\"\"\".format(\n",
        "    dataset_id=dataset_id\n",
        ")\n",
        "\n",
        "job_config = bigquery.QueryJobConfig(\n",
        "    query_parameters=[\n",
        "        bigquery.ScalarQueryParameter(\"FILE_PATH\", \"STRING\", FILE_PATH),\n",
        "    ]\n",
        ")\n",
        "query_job = client.query(query, job_config=job_config)  # Make an API request."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mdi1K7gt91QI"
      },
      "outputs": [],
      "source": [
        "query_job.result()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "08ca03535d87"
      },
      "source": [
        "## Clean up\n",
        "<a name=\"section-12\"></a>\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud\n",
        "project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
        "\n",
        "Otherwise, you can delete the individual resources you created in this tutorial. The following code deletes the entire dataset."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "04a96ad8abdc"
      },
      "outputs": [],
      "source": [
        "# Set dataset variable to the ID of the BigQuery dataset to fetch.\n",
        "dataset = f\"{PROJECT_ID}.{dataset_id}\"\n",
        "\n",
        "# Use the delete_contents parameter to delete a dataset and its contents.\n",
        "# Use the not_found_ok parameter to not receive an error if the dataset has already been deleted.\n",
        "client.delete_dataset(\n",
        "    dataset, delete_contents=True, not_found_ok=True\n",
        ")  # Make an API request.\n",
        "\n",
        "print(\"Deleted dataset '{}'.\".format(dataset_id))\n",
        "\n",
        "delete_bucket = False\n",
        "if delete_bucket or os.getenv(\"IS_TESTING\"):\n",
        "    ! gsutil -m rm -r $BUCKET_URI"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "churn_prediction_for_game_developers.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}