Colab_Product_recommendation_stadium.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# ハンズオン環境\n",
        "\n",
        "https://colab.research.google.com/\n",
        "へアクセスし、\"uplopad\"をクリックして本ノートブックをアップロードしてください。\n"
      ],
      "metadata": {
        "id": "XGAxLSFRF0aB"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "-sandbox\n",
        "# ライブイベント向け商品レコメンド - スタジアムアナリティクス\n",
        "<img style='float: right' width='600px' src='https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-notif.png'>\n",
        "\n",
        "**ユースケースの概要**\n",
        "* スポーツのライブ中継では大量の顧客データが生成されます。より良いファン体験を実現するのに活用できます。ファンとの関わりは、売上や顧客維持率の向上に直結します。このノートブックでは、ファンの購買履歴とスタジアムの座席の位置情報をもとにパーソナライズされた割引オファーを作成し、イベント中の追加販売を促進し、より個人的な体験を実現する方法を紹介します。\n",
        "*目標：ファンへキャンペーンのオファーのプッシュ通知を送信します。\n",
        " \n",
        "**ソリューションのビジネスインパクト**\n",
        "* **ファン・エンゲージメントと顧客維持：** イベントに参加している間、より良い経験をしているファンは、将来的に別のイベントのために戻ってくる可能性が高くなります。\n",
        "* **収益の増加：**ファンに パーソナライズされた割引キャンペーンを送信すると、イベント中にスタジアムの中の店からの購入が増えるのでお店(ベンダー)の収益の増加にも繋がります。\n",
        "\n",
        "<!-- Collect usage data (view). Remove it to disable collection. View README for more details.  -->\n",
        "<img width=\"1px\" src=\"https://www.google-analytics.com/collect?v=1&gtm=GTM-NKQ8TT7&tid=UA-163989034-1&cid=555&aip=1&t=event&ec=field_demos&ea=display&dp=%2F42_field_demos%2Fmedia%2Fproduct_recommender_stadium%2Fnotebook&dt=MEDIA_USE_CASE\">\n",
        "<!-- [metadata={\"description\":\"Product recommendation for live events. This demo shows how to create a personalized discount for a fan based on their purchasing history and location of where they sit in a stadium to drive additional sales during the game and create a more individualized experience.\",\n",
        " \"authors\":[\"dan.morris@databricks.com\"],\n",
        "  \"db_resources\":{},\n",
        "  \"search_tags\":{\"vertical\": \"media\", \"step\": \"Data Engineering\", \"components\": [\"sparkml\", \"mlflow\", \"als\", \"recommender\"]},\n",
        "                 \"canonicalUrl\": {\"AWS\": \"\", \"Azure\": \"\", \"GCP\": \"\"}}] -->"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "e31d10a3-dced-4440-9d0b-d34ec5296956",
          "inputWidgets": {},
          "title": ""
        },
        "id": "uuEo_dmNgWOH"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 設定と準備"
      ],
      "metadata": {
        "id": "yfwG7L9lzHJM"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Sparkの準備"
      ],
      "metadata": {
        "id": "fYx6ohCwFqj1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pyspark"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "3e1fccc6-c0f6-4923-910e-501ac827b6e0",
          "inputWidgets": {},
          "title": ""
        },
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6wfI5vJqgWOJ",
        "outputId": "5d13656b-7047-4d2a-acb8-f7fdfd8a1944"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyspark\n",
            "  Downloading pyspark-3.3.1.tar.gz (281.4 MB)\n",
            "\u001b[K     |████████████████████████████████| 281.4 MB 44 kB/s \n",
            "\u001b[?25hCollecting py4j==0.10.9.5\n",
            "  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\n",
            "\u001b[K     |████████████████████████████████| 199 kB 39.6 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.3.1-py2.py3-none-any.whl size=281845514 sha256=e5e6c6892d26a759fe61d9fcce38ae0e812b4bacd76356426074ce71d6e7ef34\n",
            "  Stored in directory: /root/.cache/pip/wheels/42/59/f5/79a5bf931714dcd201b26025347785f087370a10a3329a899c\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.5 pyspark-3.3.1\n"
          ]
        }
      ],
      "execution_count": null
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession \n",
        "spark = SparkSession.builder \\\n",
        "    .master(\"local[1]\") \\\n",
        "    .appName(\"IAB\") \\\n",
        "    .getOrCreate()\n"
      ],
      "metadata": {
        "id": "e9R4TF-YFeQQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.context import SparkContext\n",
        "sc = SparkContext.getOrCreate()"
      ],
      "metadata": {
        "id": "kBvEKOCxNSje"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## MLflowの準備"
      ],
      "metadata": {
        "id": "YIL506DKMx39"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install mlflow"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iA0fCJDcM2AS",
        "outputId": "10b13ad7-0e52-4747-e07a-5bfde6663a54"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting mlflow\n",
            "  Downloading mlflow-1.30.0-py3-none-any.whl (17.0 MB)\n",
            "\u001b[K     |████████████████████████████████| 17.0 MB 3.8 MB/s \n",
            "\u001b[?25hRequirement already satisfied: entrypoints<1 in /usr/local/lib/python3.7/dist-packages (from mlflow) (0.4)\n",
            "Collecting querystring-parser<2\n",
            "  Downloading querystring_parser-1.2.4-py2.py3-none-any.whl (7.9 kB)\n",
            "Collecting alembic<2\n",
            "  Downloading alembic-1.8.1-py3-none-any.whl (209 kB)\n",
            "\u001b[K     |████████████████████████████████| 209 kB 57.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: packaging<22 in /usr/local/lib/python3.7/dist-packages (from mlflow) (21.3)\n",
            "Collecting docker<7,>=4.0.0\n",
            "  Downloading docker-6.0.1-py3-none-any.whl (147 kB)\n",
            "\u001b[K     |████████████████████████████████| 147 kB 58.9 MB/s \n",
            "\u001b[?25hRequirement already satisfied: pyyaml<7,>=5.1 in /usr/local/lib/python3.7/dist-packages (from mlflow) (6.0)\n",
            "Requirement already satisfied: Flask<3 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.1.4)\n",
            "Collecting prometheus-flask-exporter<1\n",
            "  Downloading prometheus_flask_exporter-0.21.0-py3-none-any.whl (18 kB)\n",
            "Requirement already satisfied: pandas<2 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.3.5)\n",
            "Requirement already satisfied: sqlalchemy<2,>=1.4.0 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.4.43)\n",
            "Requirement already satisfied: protobuf<5,>=3.12.0 in /usr/local/lib/python3.7/dist-packages (from mlflow) (3.19.6)\n",
            "Requirement already satisfied: scipy<2 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.7.3)\n",
            "Collecting databricks-cli<1,>=0.8.7\n",
            "  Downloading databricks-cli-0.17.3.tar.gz (77 kB)\n",
            "\u001b[K     |████████████████████████████████| 77 kB 7.3 MB/s \n",
            "\u001b[?25hRequirement already satisfied: numpy<2 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.21.6)\n",
            "Requirement already satisfied: pytz<2023 in /usr/local/lib/python3.7/dist-packages (from mlflow) (2022.6)\n",
            "Collecting gunicorn<21\n",
            "  Downloading gunicorn-20.1.0-py3-none-any.whl (79 kB)\n",
            "\u001b[K     |████████████████████████████████| 79 kB 8.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: click<9,>=7.0 in /usr/local/lib/python3.7/dist-packages (from mlflow) (7.1.2)\n",
            "Requirement already satisfied: sqlparse<1,>=0.4.0 in /usr/local/lib/python3.7/dist-packages (from mlflow) (0.4.3)\n",
            "Requirement already satisfied: requests<3,>=2.17.3 in /usr/local/lib/python3.7/dist-packages (from mlflow) (2.23.0)\n",
            "Requirement already satisfied: importlib-metadata!=4.7.0,<6,>=3.7.0 in /usr/local/lib/python3.7/dist-packages (from mlflow) (4.13.0)\n",
            "Collecting gitpython<4,>=2.1.0\n",
            "  Downloading GitPython-3.1.29-py3-none-any.whl (182 kB)\n",
            "\u001b[K     |████████████████████████████████| 182 kB 55.1 MB/s \n",
            "\u001b[?25hRequirement already satisfied: cloudpickle<3 in /usr/local/lib/python3.7/dist-packages (from mlflow) (1.5.0)\n",
            "Requirement already satisfied: importlib-resources in /usr/local/lib/python3.7/dist-packages (from alembic<2->mlflow) (5.10.0)\n",
            "Collecting Mako\n",
            "  Downloading Mako-1.2.4-py3-none-any.whl (78 kB)\n",
            "\u001b[K     |████████████████████████████████| 78 kB 8.0 MB/s \n",
            "\u001b[?25hCollecting pyjwt>=1.7.0\n",
            "  Downloading PyJWT-2.6.0-py3-none-any.whl (20 kB)\n",
            "Requirement already satisfied: oauthlib>=3.1.0 in /usr/local/lib/python3.7/dist-packages (from databricks-cli<1,>=0.8.7->mlflow) (3.2.2)\n",
            "Requirement already satisfied: tabulate>=0.7.7 in /usr/local/lib/python3.7/dist-packages (from databricks-cli<1,>=0.8.7->mlflow) (0.8.10)\n",
            "Requirement already satisfied: six>=1.10.0 in /usr/local/lib/python3.7/dist-packages (from databricks-cli<1,>=0.8.7->mlflow) (1.15.0)\n",
            "Collecting requests<3,>=2.17.3\n",
            "  Downloading requests-2.28.1-py3-none-any.whl (62 kB)\n",
            "\u001b[K     |████████████████████████████████| 62 kB 1.5 MB/s \n",
            "\u001b[?25hCollecting websocket-client>=0.32.0\n",
            "  Downloading websocket_client-1.4.2-py3-none-any.whl (55 kB)\n",
            "\u001b[K     |████████████████████████████████| 55 kB 4.4 MB/s \n",
            "\u001b[?25hCollecting urllib3>=1.26.0\n",
            "  Downloading urllib3-1.26.12-py2.py3-none-any.whl (140 kB)\n",
            "\u001b[K     |████████████████████████████████| 140 kB 68.8 MB/s \n",
            "\u001b[?25hRequirement already satisfied: Werkzeug<2.0,>=0.15 in /usr/local/lib/python3.7/dist-packages (from Flask<3->mlflow) (1.0.1)\n",
            "Requirement already satisfied: Jinja2<3.0,>=2.10.1 in /usr/local/lib/python3.7/dist-packages (from Flask<3->mlflow) (2.11.3)\n",
            "Requirement already satisfied: itsdangerous<2.0,>=0.24 in /usr/local/lib/python3.7/dist-packages (from Flask<3->mlflow) (1.1.0)\n",
            "Requirement already satisfied: typing-extensions>=3.7.4.3 in /usr/local/lib/python3.7/dist-packages (from gitpython<4,>=2.1.0->mlflow) (4.1.1)\n",
            "Collecting gitdb<5,>=4.0.1\n",
            "  Downloading gitdb-4.0.9-py3-none-any.whl (63 kB)\n",
            "\u001b[K     |████████████████████████████████| 63 kB 1.3 MB/s \n",
            "\u001b[?25hCollecting smmap<6,>=3.0.1\n",
            "  Downloading smmap-5.0.0-py3-none-any.whl (24 kB)\n",
            "Requirement already satisfied: setuptools>=3.0 in /usr/local/lib/python3.7/dist-packages (from gunicorn<21->mlflow) (57.4.0)\n",
            "Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata!=4.7.0,<6,>=3.7.0->mlflow) (3.10.0)\n",
            "Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.7/dist-packages (from Jinja2<3.0,>=2.10.1->Flask<3->mlflow) (2.0.1)\n",
            "Requirement already satisfied: pyparsing!=3.0.5,>=2.0.2 in /usr/local/lib/python3.7/dist-packages (from packaging<22->mlflow) (3.0.9)\n",
            "Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas<2->mlflow) (2.8.2)\n",
            "Requirement already satisfied: prometheus-client in /usr/local/lib/python3.7/dist-packages (from prometheus-flask-exporter<1->mlflow) (0.15.0)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests<3,>=2.17.3->mlflow) (2022.9.24)\n",
            "Requirement already satisfied: charset-normalizer<3,>=2 in /usr/local/lib/python3.7/dist-packages (from requests<3,>=2.17.3->mlflow) (2.1.1)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests<3,>=2.17.3->mlflow) (2.10)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/lib/python3.7/dist-packages (from sqlalchemy<2,>=1.4.0->mlflow) (2.0.1)\n",
            "Building wheels for collected packages: databricks-cli\n",
            "  Building wheel for databricks-cli (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for databricks-cli: filename=databricks_cli-0.17.3-py3-none-any.whl size=139103 sha256=21cc12f832de373481f100c7836540b7e4dbd5c0bb8a0ebeabbf60d1d8670b5c\n",
            "  Stored in directory: /root/.cache/pip/wheels/3f/73/87/c1e4b2145eb6049bb6c9aaf7ea1e38302b77ca219b6fef5d5c\n",
            "Successfully built databricks-cli\n",
            "Installing collected packages: urllib3, smmap, websocket-client, requests, pyjwt, Mako, gitdb, querystring-parser, prometheus-flask-exporter, gunicorn, gitpython, docker, databricks-cli, alembic, mlflow\n",
            "  Attempting uninstall: urllib3\n",
            "    Found existing installation: urllib3 1.24.3\n",
            "    Uninstalling urllib3-1.24.3:\n",
            "      Successfully uninstalled urllib3-1.24.3\n",
            "  Attempting uninstall: requests\n",
            "    Found existing installation: requests 2.23.0\n",
            "    Uninstalling requests-2.23.0:\n",
            "      Successfully uninstalled requests-2.23.0\n",
            "Successfully installed Mako-1.2.4 alembic-1.8.1 databricks-cli-0.17.3 docker-6.0.1 gitdb-4.0.9 gitpython-3.1.29 gunicorn-20.1.0 mlflow-1.30.0 prometheus-flask-exporter-0.21.0 pyjwt-2.6.0 querystring-parser-1.2.4 requests-2.28.1 smmap-5.0.0 urllib3-1.26.12 websocket-client-1.4.2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## ダミデータ作成のdbldatagenのインストール"
      ],
      "metadata": {
        "id": "al-_jiyWH-W2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%pip install git+https://github.com/databrickslabs/dbldatagen faker"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QoD4nkEmvTZp",
        "outputId": "a97c11e5-c625-4776-c618-85006d814a07"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting git+https://github.com/databrickslabs/dbldatagen\n",
            "  Cloning https://github.com/databrickslabs/dbldatagen to /tmp/pip-req-build-r_ffsdhn\n",
            "  Running command git clone -q https://github.com/databrickslabs/dbldatagen /tmp/pip-req-build-r_ffsdhn\n",
            "Collecting faker\n",
            "  Downloading Faker-15.3.2-py3-none-any.whl (1.6 MB)\n",
            "\u001b[K     |████████████████████████████████| 1.6 MB 4.3 MB/s \n",
            "\u001b[?25hRequirement already satisfied: typing-extensions>=3.10.0.1 in /usr/local/lib/python3.7/dist-packages (from faker) (4.1.1)\n",
            "Requirement already satisfied: python-dateutil>=2.4 in /usr/local/lib/python3.7/dist-packages (from faker) (2.8.2)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.7/dist-packages (from python-dateutil>=2.4->faker) (1.15.0)\n",
            "Building wheels for collected packages: dbldatagen\n",
            "  Building wheel for dbldatagen (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for dbldatagen: filename=dbldatagen-0.2.1-py3-none-any.whl size=68644 sha256=7602bbb105e7ed2d1b29ee956c1437d3fb8d5a5d7a684564094bd98b30b3c9d4\n",
            "  Stored in directory: /tmp/pip-ephem-wheel-cache-tuu8zise/wheels/fa/2f/29/fd337c2f0a1da95c1069d7f4898c3ff5a0ea697cb5d31f319c\n",
            "Successfully built dbldatagen\n",
            "Installing collected packages: faker, dbldatagen\n",
            "Successfully installed dbldatagen-0.2.1 faker-15.3.2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## データの準備\n",
        "\n",
        "以下リンクからデータのZipファイルをダウンロードしてGoogle colabの/content/の直下にアップロードしてください。\n",
        "\n",
        "https://github.com/yulan-yan/Product_recommendation_stadium/blob/main/stadium_recommender.zip\n"
      ],
      "metadata": {
        "id": "6ukE-GSSzP4y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!unzip /content/stadium_recommender.zip"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Cq4gxCY7i7Bj",
        "outputId": "0a6bfac3-fe31-4067-91d5-c090adecc554"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Archive:  /content/stadium_recommender.zip\n",
            "   creating: stadium_recommender/\n",
            "  inflating: stadium_recommender/.DS_Store  \n",
            "  inflating: __MACOSX/stadium_recommender/._.DS_Store  \n",
            "   creating: stadium_recommender/data/\n",
            "  inflating: stadium_recommender/data/.DS_Store  \n",
            "  inflating: __MACOSX/stadium_recommender/data/._.DS_Store  \n",
            "  inflating: stadium_recommender/data/vendors.csv  \n",
            "  inflating: __MACOSX/stadium_recommender/data/._vendors.csv  \n",
            "  inflating: stadium_recommender/data/games.csv  \n",
            "  inflating: __MACOSX/stadium_recommender/data/._games.csv  \n",
            "  inflating: stadium_recommender/data/purchase_history.csv  \n",
            "  inflating: __MACOSX/stadium_recommender/data/._purchase_history.csv  \n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        " ## パスなど変数の設定"
      ],
      "metadata": {
        "id": "wakYWtGFEPSn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "data_path = \"/content/stadium_recommender\"\n",
        "dbName = \"yulanDB\"\n",
        "current_user = \"Yulan\""
      ],
      "metadata": {
        "id": "Lj8OHLQNEYzu"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Databaseの準備"
      ],
      "metadata": {
        "id": "flF0e8sB26as"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# オプション：Googleドライブのマウント\n",
        "# from google.colab import drive\n",
        "# drive.mount('/content/drive')"
      ],
      "metadata": {
        "id": "mdFtppRO13ue"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql(\"drop database if exists yulanDB\")\n",
        "spark.sql(\"create database if not exists yulanDB\")\n",
        "spark.sql(\"use yulanDB\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-nm3KvYU7liK",
        "outputId": "799b4a95-fd81-4446-cf02-0868a705e2c2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# レコメンデーションシステムの構築のワークフロー"
      ],
      "metadata": {
        "id": "fRqqLziDGhvy"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 1： データを読込・加工したらraw tablesに書き込む\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-1.png\" width=\"1000px\">\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "21aecc62-8a07-410b-b7dc-3016cca8867f",
          "inputWidgets": {},
          "title": ""
        },
        "id": "xfB2zrY_gWOK"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### VendorとItemのリスト（stadium_vendors）\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-vendors.png\" width=\"400px\" style=\"float: right\" />\n",
        "\n",
        " \n",
        "PandasとSparkを使えば、これらのデータを簡単に取り込み、データ型を調整することができます。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "220a0604-f8e0-44cc-a485-7dc0325fabd4",
          "inputWidgets": {},
          "title": ""
        },
        "id": "J32a_p8sgWOK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Vendors Item type data generation...\")\n",
        "import pandas as pd\n",
        "from pyspark.sql.functions import col\n",
        "\n",
        "pandasDF = pd.read_csv(f\"{data_path}/data/vendors.csv\")\n",
        "df_items = spark.createDataFrame(pandasDF, ['vendor_id', 'vendor_location_number', 'vendor_name', 'vendor_scope', 'section', 'item_id', 'item_type', 'item', 'price', 'error'])\n",
        "for c in \"vendor_id\", \"vendor_location_number\", \"section\", \"item_id\", \"price\":\n",
        "  df_items = df_items.withColumn(c, col(c).cast('int'))\n",
        "\n",
        "spark.sql(\"drop table if exists stadium_vendors\")\n",
        "df_items.write.saveAsTable(\"stadium_vendors\")"
      ],
      "metadata": {
        "id": "UImJyXD0LNXn",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "edbf4ce6-0187-498c-8070-f2a01463912f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Vendors Item type data generation...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "### チケットの売上データ（Tickets sales）\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-pricing.png\" width=\"400px\" style=\"float: right\" />\n",
        "\n",
        "チケットの売上データもraw tableに書き込みます。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "48e63399-a3cc-4114-9884-9e89782579ce",
          "inputWidgets": {},
          "title": ""
        },
        "id": "jS3cM39ngWOL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Ticket sales data generation...\")\n",
        "import dbldatagen as dg\n",
        "from pyspark.sql.types import *\n",
        "from pyspark.sql import functions as F\n",
        "from faker import Faker\n",
        "fake = Faker()\n",
        "\n",
        "fake_name = F.udf(fake.name)\n",
        "fake_phone = F.udf(fake.phone_number)\n",
        "\n",
        "data_rows = 70000\n",
        "df_spec = (dg.DataGenerator(spark, name=\"ticket_sales\", rows=data_rows, partitions=4)\n",
        "                            .withIdOutput()\n",
        "                            .withColumn(\"game_id\", IntegerType(), values=['3'])\n",
        "                            .withColumn(\"ticket_price\", IntegerType(), expr=\"floor(rand() * 350)\")\n",
        "                            .withColumn(\"gate_entrance\", StringType(), values=['a', 'b', 'c', 'd', 'e', ], random=True, weights=[9, 1, 1, 2, 2])\n",
        "                            .withColumn(\"section_number\", IntegerType(), minValue=100, maxValue=347, random=True)\n",
        "                            .withColumn(\"row_number\", IntegerType(), minValue=1, maxValue=35 , random=True)\n",
        "                            .withColumn(\"seat_number\", IntegerType(), minValue=1, maxValue=30, random=True)\n",
        "                            .withColumn(\"ticket_type\", StringType(), values=['single_game', 'packaged_game', 'season_holder'], random=True, weights=[7, 2, 1]))\n",
        "df_tickets = df_spec.build().withColumnRenamed(\"id\", \"customer_id\")\n",
        "df_tickets = df_tickets.withColumn(\"customer_name\", fake_name())\n",
        "df_tickets = df_tickets.withColumn(\"phone_number\", fake_phone())\n",
        "\n",
        "spark.sql(\"drop table if exists ticket_sales\")\n",
        "df_tickets.write.saveAsTable(\"ticket_sales\")"
      ],
      "metadata": {
        "id": "xYsS33NjLaYO",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "50dc7b81-ebd4-463a-8a38-ac96c2002eeb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Ticket sales data generation...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### イベントのスケジュール（Games）\n",
        "\n",
        "試合のイベントデータも同じ方法で取り込みます。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "799764ee-1aec-4967-9c2b-34855b5ed303",
          "inputWidgets": {},
          "title": ""
        },
        "id": "g0Hx_tUEgWOM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Game data generation...\")\n",
        "from pyspark.sql.functions import col, to_date\n",
        "spark.conf.set(\"spark.sql.parquet.datetimeRebaseModeInWrite\", \"LEGACY\")\n",
        "\n",
        "schemaDDL = \"game_id int, date_time string, date string, location string, home_team string, away_team string, error string, game_date string\"\n",
        "df_games = spark.read.csv(f\"{data_path}/data/games.csv\", sep=',', schema=schemaDDL)\n",
        "df_games = df_games.withColumn(\"game_date\", to_date(col(\"game_date\"),'yyyy-MM-dd'))\n",
        "\n",
        "spark.sql(\"drop table if exists games\")\n",
        "df_games.write.saveAsTable(\"games\")"
      ],
      "metadata": {
        "id": "GkSGHmFBLYg7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b13d82fd-0f52-42ab-d2c5-8665781f4d60"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Game data generation...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Points of Saleデータ\n",
        "\n",
        "POSデータも取り込みます。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "080478f4-4597-412a-9d0f-2d1022881930",
          "inputWidgets": {},
          "title": ""
        },
        "id": "hKmO1E3OgWOM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Point of sales generation...\")\n",
        "data_rows = 1000000\n",
        "df_spec = (dg.DataGenerator(spark, name=\"point_of_sale\", rows=data_rows, partitions=4)\n",
        "                            .withIdOutput()\n",
        "                            .withColumn(\"game\", IntegerType(), minValue=1, maxValue=97 , random=True)\n",
        "                            .withColumn(\"item_purchased\", IntegerType(), minValue=1, maxValue=364, random=True)\n",
        "                            .withColumn(\"customer\", IntegerType(), minValue=1, maxValue=1000, random=True))\n",
        "                            \n",
        "df_pos = df_spec.build().withColumnRenamed(\"id\", \"order_id\")\n",
        "\n",
        "# tableにデータを書き込みます。\n",
        "spark.sql(\"drop table if exists point_of_sale\")\n",
        "df_pos.write.saveAsTable(\"point_of_sale\")"
      ],
      "metadata": {
        "id": "fWrm82guLisZ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8551d3c6-e6b8-480b-962a-bc9c0428d741"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Point of sales generation...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 購入履歴(purchase_history)を取り込みます。\n"
      ],
      "metadata": {
        "id": "1s4znPBUyD8K"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "print(\"Purchase History to evaluate model efficiency after real game...\")\n",
        "import pandas as pd\n",
        "pandasDF = pd.read_csv(f\"{data_path}/data/purchase_history.csv\")\n",
        "df_spec = spark.createDataFrame(pandasDF, ['item_id', 'game_id', 'customer_id', 'recommended_item_purchased'])\n",
        "\n",
        "spark.sql(\"drop table if exists purchase_history\")\n",
        "df_spec.write.saveAsTable(\"purchase_history\")"
      ],
      "metadata": {
        "id": "5ulV1g4HLmFX",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "4a06de98-abd7-478a-9d01-b829366d2962"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Purchase History to evaluate model efficiency after real game...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 2: Silver tableを作成する\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-2.png\" width=\"1000px\">"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "6a238182-98fb-4863-9e61-ce09897964a7",
          "inputWidgets": {},
          "title": ""
        },
        "id": "4ZBKxrrIgWON"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "silver_sales_df = spark.sql('''\n",
        "  SELECT * FROM ticket_sales t \n",
        "  JOIN point_of_sale p ON t.customer_id = p.customer\n",
        "  JOIN stadium_vendors s ON p.item_purchased = s.item_id AND t.game_id = p.game;  \n",
        "''')\n",
        "\n",
        "silver_sales_df.createTempView(\"silver_sales\")\n",
        "silver_sales_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3xQeiFbFIJL1",
        "outputId": "73e384c5-0803-483c-d6bc-7382fe582f6a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+------------+-------------+--------------+----------+-----------+-------------+-----------------+--------------------+--------+----+--------------+--------+---------+----------------------+--------------------+------------+-------+-------+---------+--------------------+-----+-----+\n",
            "|customer_id|game_id|ticket_price|gate_entrance|section_number|row_number|seat_number|  ticket_type|    customer_name|        phone_number|order_id|game|item_purchased|customer|vendor_id|vendor_location_number|         vendor_name|vendor_scope|section|item_id|item_type|                item|price|error|\n",
            "+-----------+-------+------------+-------------+--------------+----------+-----------+-------------+-----------------+--------------------+--------+----+--------------+--------+---------+----------------------+--------------------+------------+-------+-------+---------+--------------------+-----+-----+\n",
            "|        409|      3|          23|            a|           131|         3|         24|packaged_game|     Ashlee Curry|    456-384-1654x036|      23|   3|           190|     409|       17|                     8|  Streats of Detroit|        Food|    223|    190| Pretzels|             Pretzel|    8|  NaN|\n",
            "|        130|      3|          76|            a|           319|        14|         10|  single_game|    Andrew Booker|    696-566-3323x323|      41|   3|           124|     130|       17|                     2|  Streats of Detroit|        Food|    120|    124| Pretzels|             Pretzel|    8|  NaN|\n",
            "|        255|      3|         183|            a|           156|        33|          5|season_holder|    Cindy Stanley|       (828)368-8703|      58|   3|           175|     255|       17|                     7|  Streats of Detroit|        Food|    217|    175|  Hot Dog|          Value Meal|   13|  NaN|\n",
            "|        962|      3|         195|            a|           283|        25|          7|  single_game|      Erin Miller|        002.493.3744|      63|   3|            24|     962|        2|                     3|             Big Boy|        Food|    230|     24|    Fries|               Fries|   12|  NaN|\n",
            "|        177|      3|         311|            a|           326|        31|         21|  single_game|    Daniel Lowery|     +1-474-658-0715|     434|   3|            80|     177|        9|                     4|      Hungry Howie's|        Food|    226|     80|  Alcohol|       Domestic Beer|   11|  NaN|\n",
            "|        833|      3|         289|            a|           225|         3|         20|  single_game|  Patrick Cabrera|+1-719-195-3857x6065|     458|   3|           313|     833|       23|                     1|    Kickoff Classics|        Food|    208|    313|    Fries|               Fries|   12|  NaN|\n",
            "|        127|      3|         188|            a|           115|        27|          2|season_holder|       Adam Jones|   (684)663-2623x760|     538|   3|           189|     127|       17|                     8|  Streats of Detroit|        Food|    223|    189|    Snack|             Popcorn|    7|  NaN|\n",
            "|         69|      3|          73|            d|           125|        12|         19|  single_game|    James Mccarty|        865-722-4703|     573|   3|           168|      69|       17|                     6|  Streats of Detroit|        Food|    137|    168| Pretzels|             Pretzel|    8|  NaN|\n",
            "|        474|      3|         214|            d|           216|        29|         12|season_holder|     Eric Montoya|  (955)372-3691x2813|     696|   3|           249|     474|       19|                     2|      Tailgate Grill|        Food|    135|    249| Sandwich|Chicken Tenders w...|   17|  NaN|\n",
            "|        189|      3|         167|            d|           237|        18|          2|  single_game|       Lisa Parks|       (444)130-7300|     707|   3|           133|     189|       17|                     3|  Streats of Detroit|        Food|    124|    133|  Hot Dog|             Hot Dog|   13|  NaN|\n",
            "|        397|      3|         245|            e|           166|        16|         21|  single_game| Matthew Chandler|        425-128-9691|     731|   3|           320|     397|       24|                     1|     Novella's Pizza|        Food|    212|    320|    Water|       Bottled Water|   10|  NaN|\n",
            "|         59|      3|           2|            d|           101|        27|         17|packaged_game|  Alexis Anderson|001-965-473-2574x...|    1044|   3|           171|      59|       17|                     6|  Streats of Detroit|        Food|    137|    171|  Alcohol|       Domestic Beer|   11|  NaN|\n",
            "|        253|      3|         180|            a|           156|         7|         20|  single_game|   Robin Sandoval|        861-585-5647|    1095|   3|             5|     253|        2|                     1|             Big Boy|        Food|    108|      5|   Burger|Big Boy Original ...|   16|  NaN|\n",
            "|        152|      3|         132|            a|           225|        26|         13|packaged_game|      Jose Taylor|   (657)869-7224x097|    1316|   3|           261|     152|       19|                     3|      Tailgate Grill|        Food|    219|    261|    Snack|             Peanuts|    8|  NaN|\n",
            "|        639|      3|         340|            a|           232|        17|         17|  single_game|Virginia Marshall|          7075569427|    1345|   3|            74|     639|        9|                     3|      Hungry Howie's|        Food|    131|     74|  Alcohol|       Domestic Beer|   11|  NaN|\n",
            "|        767|      3|         267|            a|           111|        35|         12|packaged_game|Elizabeth Brennan|        084-388-9871|    1377|   3|           154|     767|       17|                     5|  Streats of Detroit|        Food|    130|    154|   Nachos|        Nacho Grande|   14|  NaN|\n",
            "|        943|      3|         253|            e|           218|        16|          5|season_holder|       Dawn Kelly| +1-757-942-3737x680|    1458|   3|           215|     943|       17|                    10|  Streats of Detroit|        Food|    238|    215|  Alcohol|       Domestic Beer|   11|  NaN|\n",
            "|        687|      3|         283|            d|           300|        21|          2|  single_game|      John Wilson|   177-900-7032x2488|    1459|   3|            33|     687|        3|                     4|Coppercraft Disti...|         Bar|    212|     33|  Alcohol|Cocktails and Dom...|   10|  NaN|\n",
            "|          5|      3|           9|            c|           229|         5|         29|packaged_game|     Megan Vargas|   670.153.3409x1784|    1518|   3|           204|       5|       17|                     9|  Streats of Detroit|        Food|    227|    204|  Alcohol|       Domestic Beer|   11|  NaN|\n",
            "|        489|      3|         227|            a|           320|        14|          6|packaged_game|   Alexa Trujillo|    052-186-7879x731|    1602|   3|           346|     489|       26|                     2|      South East Pub|         Bar|    214|    346|  Alcohol|          Craft Beer|   14|  NaN|\n",
            "+-----------+-------+------------+-------------+--------------+----------+-----------+-------------+-----------------+--------------------+--------+----+--------------+--------+---------+----------------------+--------------------+------------+-------+-------+---------+--------------------+-----+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "最も購入されたアイテムのトップ 10"
      ],
      "metadata": {
        "id": "MHzEZwgvygOO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "spark.sql('''\n",
        " SELECT item_id, count(item_id) AS item_purchases FROM silver_sales GROUP BY item_id order by item_purchases desc limit 10\n",
        "''').show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "00RtwYg6J8rk",
        "outputId": "99439059-528f-4744-83d0-35d6c6ee304d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+--------------+\n",
            "|item_id|item_purchases|\n",
            "+-------+--------------+\n",
            "|    344|            44|\n",
            "|     94|            43|\n",
            "|    187|            43|\n",
            "|    170|            43|\n",
            "|    110|            43|\n",
            "|     86|            42|\n",
            "|     56|            41|\n",
            "|    124|            41|\n",
            "|    275|            41|\n",
            "|    202|            40|\n",
            "+-------+--------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 3: リコメンデーション・モデルの構築\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-3.png\" width=\"1000px\">\n",
        "\n",
        "### MLFlowを用いたALSレコメンダーの構築\n",
        " \n",
        "\n",
        "Mlflowは、モデル自身を含むすべての実験メトリクスの追跡に使用されています。\n",
        "\n",
        "構築したモデルを再利用して、最終的なレコメンデーションを取得し、パーソナルなオファーをプッシュ通知で送ることができるようになります。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "748b0d5d-96f0-45f2-9d9d-aba5c46cabc3",
          "inputWidgets": {},
          "title": ""
        },
        "id": "VNZmM14jgWON"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import mlflow\n",
        "from pyspark.ml.recommendation import ALS\n",
        "\n",
        "with mlflow.start_run() as run:\n",
        "  df = spark.sql(\"select customer_id, item_id, count(item_id) as item_purchases from silver_sales group by customer_id, item_id\")\n",
        "  # ALSを使用してトレーニングデータからレコメンデーションモデルを構築する。\n",
        "  # コールドスタート戦略を'drop'に設定して、NaN評価メトリクスにならないようにすることに注意してください。\n",
        "  # 評価マトリックスは別の情報源から導出されます (つまり、他の情報から推測されます)。より良い結果を得るために、implicitPrefs を true に設定します。\n",
        "  als = ALS(rank=3, userCol=\"customer_id\", itemCol=\"item_id\", ratingCol=\"item_purchases\", implicitPrefs=True, seed=0, coldStartStrategy=\"nan\")\n",
        "  \n",
        "  num_cores = sc.defaultParallelism\n",
        "  als.setNumBlocks(num_cores)\n",
        "  \n",
        "  model = als.fit(df)\n",
        "  #　別のセルからこの実行の他の数値を追加する必要があるため、実行IDを取得しましょう。\n",
        "  run_id = run.info.run_id"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "7204ec48-ac74-4432-ad65-7054032012b8",
          "inputWidgets": {},
          "title": ""
        },
        "id": "SVyiSwixgWOO"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "## Step 4: モデルでレコメンデーションリストを作成する\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-4.png\" width=\"1000px\">\n"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "1a5968c4-7526-4cce-863a-3c1fb920debb",
          "inputWidgets": {},
          "title": ""
        },
        "id": "SV-aDCjVgWOO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# 顧客毎の上位10件のレコメンデーションアイテムを取得する\n",
        "recommendations = model.recommendForAllUsers(10)\n",
        "\n",
        "spark.sql(\"drop table if exists silver_recommendations\")\n",
        "recommendations.createOrReplaceTempView(\"silver_recommendations\")\n",
        "recommendations.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "0b400753-7c9b-4779-a2ed-14193dc5e50a",
          "inputWidgets": {},
          "title": ""
        },
        "id": "t9aBSP_ygWOP",
        "outputId": "54424512-71bb-44e8-851d-be62db539641",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------------+\n",
            "|customer_id|     recommendations|\n",
            "+-----------+--------------------+\n",
            "|          1|[{110, 0.06207476...|\n",
            "|          2|[{225, 0.21452291...|\n",
            "|          3|[{275, 0.09176123...|\n",
            "|          4|[{110, 0.10726592...|\n",
            "|          5|[{344, 0.10935707...|\n",
            "|          6|[{344, 0.07213435...|\n",
            "|          7|[{124, 0.18917444...|\n",
            "|          8|[{124, 0.32242697...|\n",
            "|          9|[{137, 0.25173628...|\n",
            "|         10|[{305, 0.22389345...|\n",
            "|         11|[{225, 0.17785867...|\n",
            "|         12|[{33, 0.16146883}...|\n",
            "|         13|[{33, 0.1658469},...|\n",
            "|         14|[{110, 0.09178765...|\n",
            "|         15|[{124, 0.23991564...|\n",
            "|         16|[{170, 0.09936026...|\n",
            "|         17|[{124, 0.15195665...|\n",
            "|         18|[{33, 0.12710817}...|\n",
            "|         19|[{33, 0.15763727}...|\n",
            "|         20|[{124, 0.21426772...|\n",
            "+-----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 5: レコメンデーション結果のフィルタリングと再ランク付け\n",
        "\n",
        "\n",
        "顧客毎のレコメンドを展開し、アイテムや顧客の詳細を取得します。\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-5.png\" width=\"1000px\">"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "f8c1ec84-a304-4290-9b69-715a163cfec1",
          "inputWidgets": {},
          "title": ""
        },
        "id": "nqs_pobYgWOP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "gold_recommendations_df = spark.sql('''\n",
        "SELECT ci.*, vendor_id, vendor_location_number, vendor_name, vendor_scope, section, item_type, item, price, error\n",
        "FROM stadium_vendors v\n",
        "JOIN (SELECT customer_id, items.*\n",
        "FROM   (SELECT customer_id, explode(recommendations) AS items\n",
        "FROM   silver_recommendations) a) ci\n",
        "ON v.item_id = ci.item_id\n",
        "''') \n",
        "\n",
        "spark.sql(\"drop table if exists gold_recommendations\")\n",
        "gold_recommendations_df.createTempView(\"gold_recommendations\")\n",
        "spark.sql('select * from gold_recommendations').show()"
      ],
      "metadata": {
        "id": "ajUoh70qRDE3",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "44118d4b-d980-4a83-f3e8-0e613c41cd11"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-----------+---------+----------------------+--------------------+------------+-------+-------------+--------------------+-----+-----+\n",
            "|customer_id|item_id|     rating|vendor_id|vendor_location_number|         vendor_name|vendor_scope|section|    item_type|                item|price|error|\n",
            "+-----------+-------+-----------+---------+----------------------+--------------------+------------+-------+-------------+--------------------+-----+-----+\n",
            "|          1|    110| 0.06207476|       17|                     1|  Streats of Detroit|        Food|    109|       Nachos|        Nacho Grande|   14|  NaN|\n",
            "|          1|    137|0.060412034|       17|                     3|  Streats of Detroit|        Food|    124|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          1|    170|0.055190045|       17|                     6|  Streats of Detroit|        Food|    137|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          1|    225|0.051179606|       17|                    11|  Streats of Detroit|        Food|    229|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          1|    234|0.049891207|       17|                    12|  Streats of Detroit|        Food|    244|     Pretzels|             Pretzel|    8|  NaN|\n",
            "|          1|    187|0.049530078|       17|                     8|  Streats of Detroit|        Food|    223|       Nachos|        Nacho Grande|   14|  NaN|\n",
            "|          1|    156|  0.0489764|       17|                     5|  Streats of Detroit|        Food|    130|        Snack|             Popcorn|    7|  NaN|\n",
            "|          1|     94| 0.04766779|       13|                     1|    Poletown Sausage|        Food|    103|      Hot Dog|          Value Meal|   13|  NaN|\n",
            "|          1|    147|0.046597462|       17|                     4|  Streats of Detroit|        Food|    126|        Snack|             Peanuts|    8|  NaN|\n",
            "|          1|    348|0.046327006|       26|                     2|      South East Pub|         Bar|    214|      Alcohol|                Wine|   10|  NaN|\n",
            "|          2|    225| 0.21452291|       17|                    11|  Streats of Detroit|        Food|    229|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          2|    170| 0.20312713|       17|                     6|  Streats of Detroit|        Food|    137|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          2|    110|  0.1918864|       17|                     1|  Streats of Detroit|        Food|    109|       Nachos|        Nacho Grande|   14|  NaN|\n",
            "|          2|    234|  0.1836621|       17|                    12|  Streats of Detroit|        Food|    244|     Pretzels|             Pretzel|    8|  NaN|\n",
            "|          2|     55| 0.18116626|        6|                     1|Goal Post Grill &...|        Food|    119|Non-Alcoholic|Regular or Large ...|   10|  NaN|\n",
            "|          2|     16| 0.17927662|        2|                     2|             Big Boy|        Food|    127|Non-Alcoholic|          Milkshakes|   11|  NaN|\n",
            "|          2|     88| 0.17372102|       11|                     1|Lefty's Cheesesteaks|        Food|    103|     Sandwich|      Chicken Hoagie|   15|  NaN|\n",
            "|          2|    137| 0.17362076|       17|                     3|  Streats of Detroit|        Food|    124|        Snack|         Cheese Cups|   10|  NaN|\n",
            "|          2|    341| 0.17146161|       26|                     2|      South East Pub|         Bar|    214|      Alcohol|    Premium Cocktail|   15|  NaN|\n",
            "|          2|    348| 0.17122616|       26|                     2|      South East Pub|         Bar|    214|      Alcohol|                Wine|   10|  NaN|\n",
            "+-----------+-------+-----------+---------+----------------------+--------------------+------------+-------+-------------+--------------------+-----+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### 顧客毎のベストアイテムを取得します。\n",
        "\n",
        "アイテムのパーソナライゼドレコメンドを行う時に、近い距離で購入できるアイテムを探したいです。そのために、お客様の席からお店の場所までの距離を計算します。"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "112f2409-c700-40aa-9f6d-abdb621705bb",
          "inputWidgets": {},
          "title": ""
        },
        "id": "XLXAjrwEgWOP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "sections_recommendations_df = spark.sql(\"\"\"\n",
        "SELECT r.customer_id, item_id, rating, section, section_number, abs(section-section_number) as distance\n",
        "FROM gold_recommendations r\n",
        "JOIN ticket_sales s on s.customer_id = r.customer_id\n",
        "\"\"\")\n",
        "sections_recommendations_df.createTempView(\"sections_recommendations\")\n",
        "       \n",
        "spark.sql(\"select * from sections_recommendations\").show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {
            "implicitDf": true
          },
          "nuid": "0c2f7530-b3f6-489c-b32d-17c54ace3f58",
          "inputWidgets": {},
          "title": ""
        },
        "id": "mb4S03H_gWOQ",
        "outputId": "c1fc00d6-52de-4c99-b406-58768862bfb5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-----------+-------+--------------+--------+\n",
            "|customer_id|item_id|     rating|section|section_number|distance|\n",
            "+-----------+-------+-----------+-------+--------------+--------+\n",
            "|          1|    110| 0.06207476|    109|           127|      18|\n",
            "|          1|    137|0.060412034|    124|           127|       3|\n",
            "|          1|    170|0.055190045|    137|           127|      10|\n",
            "|          1|    225|0.051179606|    229|           127|     102|\n",
            "|          1|    234|0.049891207|    244|           127|     117|\n",
            "|          1|    187|0.049530078|    223|           127|      96|\n",
            "|          1|    156|  0.0489764|    130|           127|       3|\n",
            "|          1|     94| 0.04766779|    103|           127|      24|\n",
            "|          1|    147|0.046597462|    126|           127|       1|\n",
            "|          1|    348|0.046327006|    214|           127|      87|\n",
            "|          2|    225| 0.21452291|    229|           179|      50|\n",
            "|          2|    170| 0.20312713|    137|           179|      42|\n",
            "|          2|    110|  0.1918864|    109|           179|      70|\n",
            "|          2|    234|  0.1836621|    244|           179|      65|\n",
            "|          2|     55| 0.18116626|    119|           179|      60|\n",
            "|          2|     16| 0.17927662|    127|           179|      52|\n",
            "|          2|     88| 0.17372102|    103|           179|      76|\n",
            "|          2|    137| 0.17362076|    124|           179|      55|\n",
            "|          2|    341| 0.17146161|    214|           179|      35|\n",
            "|          2|    348| 0.17122616|    214|           179|      35|\n",
            "+-----------+-------+-----------+-------+--------------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": null
    },
    {
      "cell_type": "code",
      "source": [
        "final_recommendations_df = spark.sql(\"\"\"\n",
        "SELECT * FROM (\n",
        "  SELECT *, RANK() OVER (PARTITION BY customer_id ORDER BY distance ASC) AS rnk FROM sections_recommendations\n",
        "  ) WHERE rnk = 1\n",
        "\"\"\")\n",
        "\n",
        "final_recommendations_df.createTempView(\"final_recommendations\")\n",
        "spark.sql(\"SELECT * FROM final_recommendations\").show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": true,
          "cellMetadata": {
            "implicitDf": true
          },
          "nuid": "62140037-d43a-45aa-85be-5607eae3701c",
          "inputWidgets": {},
          "title": "Let's take the closest item for each customer"
        },
        "id": "4Thf5mQbgWOQ",
        "outputId": "56698e27-c83c-42ac-b50d-5486bca70606",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-------+-----------+-------+--------------+--------+---+\n",
            "|customer_id|item_id|     rating|section|section_number|distance|rnk|\n",
            "+-----------+-------+-----------+-------+--------------+--------+---+\n",
            "|          1|    147|0.046597462|    126|           127|       1|  1|\n",
            "|          2|    341| 0.17146161|    214|           179|      35|  1|\n",
            "|          2|    348| 0.17122616|    214|           179|      35|  1|\n",
            "|          3|    333| 0.08083317|    206|           204|       2|  1|\n",
            "|          4|    137| 0.10688759|    124|           117|       7|  1|\n",
            "|          5|    202|  0.0948009|    227|           229|       2|  1|\n",
            "|          6|    277| 0.06821223|    242|           277|      35|  1|\n",
            "|          7|    239|  0.1689277|    244|           343|      99|  1|\n",
            "|          8|    239| 0.29360628|    244|           280|      36|  1|\n",
            "|          9|    348| 0.18603727|    214|           186|      28|  1|\n",
            "|         10|    343| 0.19850445|    214|           221|       7|  1|\n",
            "|         10|    350| 0.17839779|    214|           221|       7|  1|\n",
            "|         11|    170| 0.17641331|    137|           165|      28|  1|\n",
            "|         12|    105| 0.12829916|    117|           116|       1|  1|\n",
            "|         13|    105| 0.14003196|    117|           121|       4|  1|\n",
            "|         14|    348|  0.0713565|    214|           180|      34|  1|\n",
            "|         15|     89| 0.21162738|    135|           142|       7|  1|\n",
            "|         16|    234| 0.08491379|    244|           252|       8|  1|\n",
            "|         17|    305| 0.14672752|    208|           184|      24|  1|\n",
            "|         18|     58|0.099125706|    133|           159|      26|  1|\n",
            "+-----------+-------+-----------+-------+--------------+--------+---+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 6: ファンにキャンペーンのオファーの送信\n",
        "\n",
        "これで、全顧客に送るべきアイテムが揃いました！通知をテストしてみましょう。\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-6.png\" width=\"1000px\">"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "892a6c38-6113-44a4-ac37-f4771c1401d4",
          "inputWidgets": {},
          "title": ""
        },
        "id": "ytGMM8qZgWOQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import IPython\n",
        "from IPython.display import display, HTML\n",
        "\n",
        "def send_push_notification(title, subject, phone_number):\n",
        "  #ノートブックを実行しているユーザーでメッセージを調整してください。リアルでは、顧客の詳細を含むテーブルから情報を取るべきです。\n",
        "  first_name = \"Yulan\"\n",
        "  import re\n",
        "  subject = re.sub(\"\\n\", \"<br/>\", subject) \n",
        "  subject = re.sub(\"こんにちは！([0-9]*)\", \"こんにちは！\"+first_name, subject) \n",
        "  subject = re.sub(\"大好きな([0-9]*)\", \"大好きなピザ\", subject) \n",
        "  subject = re.sub(\"([0-9]*)で販売されている\", \"Goal Post Grill & Pizzeriaで販売されている\", subject) \n",
        "\n",
        "\n",
        "  htm = HTML(f\"\"\"<div style=\"border-radius: 10px; background-color: #adeaff; padding: 10px; width: 400px; box-shadow: 2px 2px 2px #F7f7f7; margin-bottom: 3px\">\n",
        "        <div style=\"padding-bottom: 5px\"><img style=\"width:20px; margin-bottom: -3px\" src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/bell.png\"/> <strong>{title}</strong></div>\n",
        "        {subject}\n",
        "        </div>\"\"\")\n",
        "  display(htm)"
      ],
      "metadata": {
        "id": "JwykSlt1Qw-E"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "recommendation = spark.sql(\"select * from final_recommendations where customer_id = 1\").collect()[0]\n",
        "\n",
        "title = \"あなたにお得なキャンペーンオファー！\"\n",
        "subject= f\"こんにちは！{recommendation['customer_id']}さん! 大好きな{recommendation['item_id']}を特別価格でご提供いたします。 {recommendation['section']}で販売されているので、チェックしてみてくださいね!\" \n",
        "\n",
        "user_cell_phone = \"<Put your real cell phone here to test the notification!>\"\n",
        " \n",
        "send_push_notification(title, subject, user_cell_phone)"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "abf7d7fc-3682-4684-b81b-82309e792a72",
          "inputWidgets": {},
          "title": ""
        },
        "id": "4s9eDG_sgWOQ",
        "outputId": "92544065-d7c1-4d86-998d-82ad38bff3ab",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 118
        }
      },
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "<div style=\"border-radius: 10px; background-color: #adeaff; padding: 10px; width: 400px; box-shadow: 2px 2px 2px #F7f7f7; margin-bottom: 3px\">\n",
              "        <div style=\"padding-bottom: 5px\"><img style=\"width:20px; margin-bottom: -3px\" src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/bell.png\"/> <strong>あなたにお得なキャンペーンオファー！</strong></div>\n",
              "        こんにちは！Yulanさん! 大好きなピザを特別価格でご提供いたします。 Goal Post Grill & Pizzeriaで販売されているので、チェックしてみてくださいね!\n",
              "        </div>"
            ]
          },
          "metadata": {}
        }
      ],
      "execution_count": null
    },
    {
      "cell_type": "code",
      "source": [
        "print(recommendation)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FJh3loVSRGD6",
        "outputId": "d5d7bdff-e044-4f3d-bf14-f883799edb26"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Row(customer_id=1, item_id=147, rating=0.04659746214747429, section=126, section_number=127, distance=1, rnk=1)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Step 7: イベントキャンペーンとメトリクスのトラッキング\n",
        "\n",
        "イベント中は、すべての指標をリアルタイムで取得し、イベントのKPIをモニターするダッシュボードを構築することができます。\n",
        " \n",
        "ゲーム終了後、プロモーションの成功率を確認し、次のゲームに必要な調整を行うこともできます。\n",
        "\n",
        "<img src=\"https://github.com/QuentinAmbard/databricks-demo/raw/main/media/resources/images/product_reco_stadium-7.png\" width=\"1000px\">"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {},
          "nuid": "b57e690b-9551-4046-adfa-65252b2e87d8",
          "inputWidgets": {},
          "title": ""
        },
        "id": "QgDLOPLagWOQ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "\n",
        "## visualisation: Bar, Series groupings: item_purchased, Values: count, Aggregation: SUM\n",
        "\n",
        "visual_df = spark.sql(\"\"\"\n",
        "select count(*) as count, item_purchased from (\n",
        "  SELECT *, CASE WHEN recommended_item_purchased = 1 THEN 'Yes' ELSE 'No' END as item_purchased\n",
        "  FROM final_recommendations r\n",
        "  LEFT JOIN ticket_sales s USING(customer_id)\n",
        "  LEFT JOIN purchase_history p USING(game_id, customer_id)) group by item_purchased\n",
        "\"\"\")\n",
        "visual_df.show()"
      ],
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "showTitle": false,
          "cellMetadata": {
            "implicitDf": true
          },
          "nuid": "ff4cd08e-da82-434c-bac2-d42b68c8d746",
          "inputWidgets": {},
          "title": ""
        },
        "id": "pZWjm-C8gWOQ",
        "outputId": "e9d686cd-d1e9-473b-bfb5-0306eea9c31b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----+--------------+\n",
            "|count|item_purchased|\n",
            "+-----+--------------+\n",
            "|  479|            No|\n",
            "|  760|           Yes|\n",
            "+-----+--------------+\n",
            "\n"
          ]
        }
      ],
      "execution_count": null
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "notebookName": "01_Product_recommendation_stadium",
      "dashboards": [],
      "notebookMetadata": {
        "pythonIndentUnit": 2,
        "mostRecentlyExecutedCommandWithImplicitDF": {
          "commandId": 363313668509488,
          "dataframes": [
            "_sqldf"
          ]
        }
      },
      "language": "python",
      "widgets": {
        "reset_all_data": {
          "nuid": "7c52cfd3-9f8d-4a25-9832-524b02f6f3df",
          "currentValue": "false",
          "widgetInfo": {
            "widgetType": "dropdown",
            "name": "reset_all_data",
            "defaultValue": "false",
            "label": "Reset all data",
            "options": {
              "widgetType": "dropdown",
              "choices": [
                "true",
                "false"
              ]
            }
          }
        }
      },
      "notebookOrigID": 363313668509229
    },
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "fYx6ohCwFqj1",
        "YIL506DKMx39",
        "al-_jiyWH-W2",
        "6ukE-GSSzP4y",
        "wakYWtGFEPSn",
        "flF0e8sB26as"
      ],
      "toc_visible": true
    },
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "gpuClass": "standard"
  },
  "nbformat": 4,
  "nbformat_minor": 0
}