colab-enterprise/gen-ai-demo/Menu-A-B-Testing-Generate-Campaign-GenAI.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k6eIqerFOzyj"
      },
      "source": [
        "## <img src=\"https://lh3.googleusercontent.com/mUTbNK32c_DTSNrhqETT5aQJYFKok2HB1G2nk2MZHvG5bSs0v_lmDm_ArW7rgd6SDGHXo0Ak2uFFU96X6Xd0GQ=w160-h128\" width=\"45\" valign=\"top\" alt=\"BigQuery\"> Generating A/B Menu Marketing Campaign using Gemini Pro\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2uVnvfwDB3-r"
      },
      "source": [
        "### License"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "El-xCKXBB3-r"
      },
      "outputs": [],
      "source": [
        "##################################################################################\n",
        "# Copyright 2024 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.\n",
        "###################################################################################"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "msqH1FnmB3-s"
      },
      "source": [
        "### Notebook Overview"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UzdCT2JdB3-s"
      },
      "source": [
        "- This notebook will create new a marketing campaign for newly generated items in the menu_a_b_testing table.  You can run the \"Menu-A-B-Testing-Generate-Insight-GenAI\" notebook first if you like.\n",
        "\n",
        "- Notebook Logic:\n",
        "    1. Create a prompt for our marketing campaign and store it in the table.\n",
        "    2. Run Gemini Pro on the prompt and save the results.\n",
        "    3. Parse the embedded JSON out of the respose and save as JSON\n",
        "    4. Create an HTML message with different sections\n",
        "    5. Update the results to GCS and update the table."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8zy0eEJmHxRZ"
      },
      "source": [
        "## Initialize Python"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3wiruT266H3e"
      },
      "outputs": [],
      "source": [
        "project_id=\"${project_id}\"\n",
        "location=\"us-central1\"\n",
        "model_id = \"imagegeneration@005\"\n",
        "\n",
        "# No need to set these\n",
        "city_names=[\"New York City\", \"London\", \"Tokyo\", \"San Francisco\"]\n",
        "city_ids=[1,2,3,4]\n",
        "city_languages=[\"American English\", \"British English\", \"Japanese\", \"American English\"]\n",
        "number_of_coffee_trucks = \"4\"\n",
        "\n",
        "dataset_id = \"data_beans_synthetic_data\"\n",
        "\n",
        "gcs_storage_bucket = \"${data_beans_curated_bucket}\"\n",
        "gcs_storage_path = \"data-beans/menu-images-a-b-testing/\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "z4NpP0pCH0pj"
      },
      "outputs": [],
      "source": [
        "from PIL import Image\n",
        "from IPython.display import HTML\n",
        "import IPython.display\n",
        "import google.auth\n",
        "import requests\n",
        "import json\n",
        "import uuid\n",
        "import base64\n",
        "import os\n",
        "import cv2\n",
        "import time\n",
        "\n",
        "from google.cloud import bigquery\n",
        "client = bigquery.Client()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YtZuFgjbOjso"
      },
      "source": [
        "## ImageGen2 / Gemini Pro / Gemini Pro Vision (Helper Functions)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xUolPsMFOjpZ"
      },
      "source": [
        "#### ImageGen2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LPf6NurhNi2l"
      },
      "outputs": [],
      "source": [
        "def ImageGen(prompt):\n",
        "  creds, project = google.auth.default()\n",
        "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
        "  creds.refresh(auth_req)\n",
        "  access_token=creds.token\n",
        "\n",
        "  headers = {\n",
        "      \"Content-Type\" : \"application/json\",\n",
        "      \"Authorization\" : \"Bearer \" + access_token\n",
        "  }\n",
        "\n",
        "  # https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/image-generation\n",
        "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/publishers/google/models/imagegeneration:predict\"\n",
        "\n",
        "  payload = {\n",
        "    \"instances\": [\n",
        "      {\n",
        "        \"prompt\": prompt\n",
        "      }\n",
        "    ],\n",
        "    \"parameters\": {\n",
        "      \"sampleCount\": 1\n",
        "    }\n",
        "  }\n",
        "\n",
        "  response = requests.post(url, json=payload, headers=headers)\n",
        "\n",
        "  if response.status_code == 200:\n",
        "    image_data = json.loads(response.content)[\"predictions\"][0][\"bytesBase64Encoded\"]\n",
        "    image_data = base64.b64decode(image_data)\n",
        "    filename= str(uuid.uuid4()) + \".png\"\n",
        "    with open(filename, \"wb\") as f:\n",
        "      f.write(image_data)\n",
        "    print(f\"Image generated OK.\")\n",
        "    return filename\n",
        "  else:\n",
        "    error = f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\"\n",
        "    raise RuntimeError(error)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E5CFSdK3HxYm"
      },
      "source": [
        "#### Gemini Pro LLM"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9jTBzcSIMbwg"
      },
      "outputs": [],
      "source": [
        "def GeminiProLLM(prompt, temperature = .8, topP = .8, topK = 40):\n",
        "\n",
        "  if temperature < 0:\n",
        "    temperature = 0\n",
        "\n",
        "  creds, project = google.auth.default()\n",
        "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
        "  creds.refresh(auth_req)\n",
        "  access_token=creds.token\n",
        "\n",
        "  headers = {\n",
        "      \"Content-Type\" : \"application/json\",\n",
        "      \"Authorization\" : \"Bearer \" + access_token\n",
        "  }\n",
        "\n",
        "  # https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/gemini\n",
        "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project_id}/locations/{location}/publishers/google/models/gemini-2.0-flash:streamGenerateContent\"\n",
        "\n",
        "  payload = {\n",
        "    \"contents\": {\n",
        "      \"role\": \"user\",\n",
        "      \"parts\": {\n",
        "          \"text\": prompt\n",
        "      },\n",
        "    },\n",
        "    \"safety_settings\": {\n",
        "      \"category\": \"HARM_CATEGORY_SEXUALLY_EXPLICIT\",\n",
        "      \"threshold\": \"BLOCK_LOW_AND_ABOVE\"\n",
        "    },\n",
        "    \"generation_config\": {\n",
        "      \"temperature\": temperature,\n",
        "      \"topP\": topP,\n",
        "      \"topK\": topK,\n",
        "      \"maxOutputTokens\": 8192,\n",
        "      \"candidateCount\": 1\n",
        "    }\n",
        "  }\n",
        "\n",
        "  response = requests.post(url, json=payload, headers=headers)\n",
        "\n",
        "  if response.status_code == 200:\n",
        "    json_response = json.loads(response.content)\n",
        "    llm_response = \"\"\n",
        "    for item in json_response:\n",
        "      try:\n",
        "        llm_response = llm_response + item[\"candidates\"][0][\"content\"][\"parts\"][0][\"text\"]\n",
        "      except Exception as err:\n",
        "        print(f\"response.content: {response.content}\")\n",
        "        raise RuntimeError(err)\n",
        "\n",
        "    # Remove some typically response characters (if asking for a JSON reply)\n",
        "    llm_response = llm_response.replace(\"```json\",\"\")\n",
        "    llm_response = llm_response.replace(\"```\",\"\")\n",
        "\n",
        "    # print(f\"llm_response:\\n{llm_response}\")\n",
        "    return llm_response\n",
        "  else:\n",
        "    error = f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\"\n",
        "    raise RuntimeError(error)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-L93udtrH1Oz"
      },
      "source": [
        "#### Gemini Pro Vision LLM"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ecvrUyp0BcXg"
      },
      "outputs": [],
      "source": [
        "# Use the Gemini with Vision\n",
        "def GeminiProVisionLLM(prompt, imageBase64, temperature = .4, topP = 1, topK = 32):\n",
        "\n",
        "  if temperature < 0:\n",
        "    temperature = 0\n",
        "\n",
        "  creds, project = google.auth.default()\n",
        "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
        "  creds.refresh(auth_req)\n",
        "  access_token=creds.token\n",
        "\n",
        "  headers = {\n",
        "      \"Content-Type\" : \"application/json\",\n",
        "      \"Authorization\" : \"Bearer \" + access_token\n",
        "  }\n",
        "\n",
        "  # https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/gemini\n",
        "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project_id}/locations/{location}/publishers/google/models/gemini-2.0-flash:streamGenerateContent\"\n",
        "\n",
        "  payload = {\n",
        "  \"contents\": [\n",
        "      {\n",
        "        \"role\": \"user\",\n",
        "        \"parts\": [\n",
        "          {\n",
        "            \"text\": prompt\n",
        "          },\n",
        "          {\n",
        "            \"inlineData\": {\n",
        "              \"mimeType\": \"image/png\",\n",
        "              \"data\": f\"{imageBase64}\"\n",
        "            }\n",
        "          }\n",
        "        ]\n",
        "      }\n",
        "    ],\n",
        "    \"safety_settings\": {\n",
        "      \"category\": \"HARM_CATEGORY_SEXUALLY_EXPLICIT\",\n",
        "      \"threshold\": \"BLOCK_LOW_AND_ABOVE\"\n",
        "    },\n",
        "    \"generation_config\": {\n",
        "      \"temperature\": temperature,\n",
        "      \"topP\": topP,\n",
        "      \"topK\": topK,\n",
        "      \"maxOutputTokens\": 2048,\n",
        "      \"candidateCount\": 1\n",
        "    }\n",
        "  }\n",
        "\n",
        "  response = requests.post(url, json=payload, headers=headers)\n",
        "\n",
        "  if response.status_code == 200:\n",
        "    json_response = json.loads(response.content)\n",
        "    llm_response = \"\"\n",
        "    for item in json_response:\n",
        "      llm_response = llm_response + item[\"candidates\"][0][\"content\"][\"parts\"][0][\"text\"]\n",
        "\n",
        "    # Remove some typically response characters (if asking for a JSON reply)\n",
        "    llm_response = llm_response.replace(\"```json\",\"\")\n",
        "    llm_response = llm_response.replace(\"```\",\"\")\n",
        "\n",
        "    # print(f\"llm_response:\\n{llm_response}\")\n",
        "    return llm_response\n",
        "  else:\n",
        "    error = f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\"\n",
        "    raise RuntimeError(error)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QNz6pofvfXDS"
      },
      "outputs": [],
      "source": [
        "# Use the Gemini with Vision\n",
        "def GeminiProVisionMultipleFileLLM(prompt, image_prompt, temperature = .4, topP = 1, topK = 32):\n",
        "  creds, project = google.auth.default()\n",
        "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
        "  creds.refresh(auth_req)\n",
        "  access_token=creds.token\n",
        "\n",
        "  headers = {\n",
        "      \"Content-Type\" : \"application/json\",\n",
        "      \"Authorization\" : \"Bearer \" + access_token\n",
        "  }\n",
        "\n",
        "  # https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/gemini\n",
        "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project_id}/locations/{location}/publishers/google/models/gemini-2.0-flash:streamGenerateContent\"\n",
        "\n",
        "\n",
        "  parts = []\n",
        "  new_item = {\n",
        "      \"text\": prompt\n",
        "      }\n",
        "  parts.append(new_item)\n",
        "\n",
        "  for item in image_prompt:\n",
        "    new_item = {\n",
        "        \"text\": f\"Image Name: {item['llm_image_filename']}:\\n\"\n",
        "        }\n",
        "    parts.append(new_item)\n",
        "    new_item = {\n",
        "        \"inlineData\": {\n",
        "            \"mimeType\": \"image/png\",\n",
        "            \"data\": item[\"llm_image_base64\"]\n",
        "            }\n",
        "        }\n",
        "    parts.append(new_item)\n",
        "\n",
        "  payload = {\n",
        "  \"contents\": [\n",
        "      {\n",
        "        \"role\": \"user\",\n",
        "        \"parts\": parts\n",
        "      }\n",
        "    ],\n",
        "    \"safety_settings\": {\n",
        "      \"category\": \"HARM_CATEGORY_SEXUALLY_EXPLICIT\",\n",
        "      \"threshold\": \"BLOCK_LOW_AND_ABOVE\"\n",
        "    },\n",
        "    \"generation_config\": {\n",
        "      \"temperature\": temperature,\n",
        "      \"topP\": topP,\n",
        "      \"topK\": topK,\n",
        "      \"maxOutputTokens\": 2048,\n",
        "      \"candidateCount\": 1\n",
        "    }\n",
        "  }\n",
        "\n",
        "  response = requests.post(url, json=payload, headers=headers)\n",
        "\n",
        "  if response.status_code == 200:\n",
        "    json_response = json.loads(response.content)\n",
        "    llm_response = \"\"\n",
        "    for item in json_response:\n",
        "      llm_response = llm_response + item[\"candidates\"][0][\"content\"][\"parts\"][0][\"text\"]\n",
        "\n",
        "    # Remove some typically response characters (if asking for a JSON reply)\n",
        "    llm_response = llm_response.replace(\"```json\",\"\")\n",
        "    llm_response = llm_response.replace(\"```\",\"\")\n",
        "\n",
        "    # print(f\"llm_response:\\n{llm_response}\")\n",
        "    return llm_response\n",
        "  else:\n",
        "    error = f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\"\n",
        "    raise RuntimeError(error)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rVCY93IyXPoO"
      },
      "source": [
        "#### SQL Functions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jHCtXYuRNU0p"
      },
      "outputs": [],
      "source": [
        "def RunQuery(sql):\n",
        "  import time\n",
        "\n",
        "  if (sql.startswith(\"SELECT\") or sql.startswith(\"WITH\")):\n",
        "      df_result = client.query(sql).to_dataframe()\n",
        "      return df_result\n",
        "  else:\n",
        "    job_config = bigquery.QueryJobConfig(priority=bigquery.QueryPriority.INTERACTIVE)\n",
        "    query_job = client.query(sql, job_config=job_config)\n",
        "\n",
        "    # Check on the progress by getting the job's updated state.\n",
        "    query_job = client.get_job(\n",
        "        query_job.job_id, location=query_job.location\n",
        "    )\n",
        "    print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
        "\n",
        "    while query_job.state != \"DONE\":\n",
        "      time.sleep(2)\n",
        "      query_job = client.get_job(\n",
        "          query_job.job_id, location=query_job.location\n",
        "          )\n",
        "      print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
        "\n",
        "    if query_job.error_result == None:\n",
        "      return True\n",
        "    else:\n",
        "      return False"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "p9j1GdAwNifB"
      },
      "outputs": [],
      "source": [
        "def GetNextPrimaryKey(fully_qualified_table_name, field_name):\n",
        "  sql = f\"\"\"\n",
        "  SELECT IFNULL(MAX({field_name}),0) AS result\n",
        "    FROM `{fully_qualified_table_name}`\n",
        "  \"\"\"\n",
        "  # print(sql)\n",
        "  df_result = client.query(sql).to_dataframe()\n",
        "  # display(df_result)\n",
        "  return df_result['result'].iloc[0] + 1"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ws3i8HVGkk5p"
      },
      "outputs": [],
      "source": [
        "def GetTableSchema(dataset_name, table_name):\n",
        "  import io\n",
        "\n",
        "  dataset_ref = client.dataset(dataset_name, project=project_id)\n",
        "  table_ref = dataset_ref.table(table_name)\n",
        "  table = client.get_table(table_ref)\n",
        "\n",
        "  f = io.StringIO(\"\")\n",
        "  client.schema_to_json(table.schema, f)\n",
        "  return f.getvalue()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qx0q3yUNl63e"
      },
      "outputs": [],
      "source": [
        "def GetStartingValue(dataset_name, table_name, field_name):\n",
        "  sql = f\"\"\"\n",
        "  SELECT IFNULL(MAX({field_name}),0) + 1 AS result\n",
        "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
        "  \"\"\"\n",
        "  #print(sql)\n",
        "  df_result = client.query(sql).to_dataframe()\n",
        "  #display(df_result)\n",
        "  return df_result['result'].iloc[0]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6z5Fc5Mel6sH"
      },
      "outputs": [],
      "source": [
        "def GetForeignKeys(dataset_name, table_name, field_name):\n",
        "  sql = f\"\"\"\n",
        "  SELECT STRING_AGG(CAST({field_name} AS STRING), \",\" ORDER BY {field_name}) AS result\n",
        "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
        "  \"\"\"\n",
        "  #print(sql)\n",
        "  df_result = client.query(sql).to_dataframe()\n",
        "  #display(df_result)\n",
        "  return df_result['result'].iloc[0]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9-6THGgjnDKg"
      },
      "outputs": [],
      "source": [
        "def GetDistinctValues(dataset_name, table_name, field_name):\n",
        "  sql = f\"\"\"\n",
        "  SELECT STRING_AGG(DISTINCT {field_name}, \",\" ) AS result\n",
        "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
        "  \"\"\"\n",
        "  #print(sql)\n",
        "  df_result = client.query(sql).to_dataframe()\n",
        "  #display(df_result)\n",
        "  return df_result['result'].iloc[0]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BlxddNzpmAgp"
      },
      "source": [
        "#### Helper Functions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "E1yrPjvVXNCz"
      },
      "outputs": [],
      "source": [
        "def convert_png_to_base64(image_path):\n",
        "  image = cv2.imread(image_path)\n",
        "\n",
        "  # Convert the image to a base64 string.\n",
        "  _, buffer = cv2.imencode('.png', image)\n",
        "  base64_string = base64.b64encode(buffer).decode('utf-8')\n",
        "\n",
        "  return base64_string"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ylq2crklNuCB"
      },
      "outputs": [],
      "source": [
        "# This was generated by GenAI\n",
        "\n",
        "def copy_file_to_gcs(local_file_path, bucket_name, destination_blob_name):\n",
        "  \"\"\"Copies a file from a local drive to a GCS bucket.\n",
        "\n",
        "  Args:\n",
        "      local_file_path: The full path to the local file.\n",
        "      bucket_name: The name of the GCS bucket to upload to.\n",
        "      destination_blob_name: The desired name of the uploaded file in the bucket.\n",
        "\n",
        "  Returns:\n",
        "      None\n",
        "  \"\"\"\n",
        "\n",
        "  import os\n",
        "  from google.cloud import storage\n",
        "\n",
        "  # Ensure the file exists locally\n",
        "  if not os.path.exists(local_file_path):\n",
        "      raise FileNotFoundError(f\"Local file '{local_file_path}' not found.\")\n",
        "\n",
        "  # Create a storage client\n",
        "  storage_client = storage.Client()\n",
        "\n",
        "  # Get a reference to the bucket\n",
        "  bucket = storage_client.bucket(bucket_name)\n",
        "\n",
        "  # Create a blob object with the desired destination path\n",
        "  blob = bucket.blob(destination_blob_name)\n",
        "\n",
        "  # Upload the file from the local filesystem\n",
        "  content_type = \"\"\n",
        "  if local_file_path.endswith(\".html\"):\n",
        "    content_type = \"text/html; charset=utf-8\"\n",
        "\n",
        "  if local_file_path.endswith(\".json\"):\n",
        "    content_type = \"application/json; charset=utf-8\"\n",
        "\n",
        "  if content_type == \"\":\n",
        "    blob.upload_from_filename(local_file_path)\n",
        "  else:\n",
        "    blob.upload_from_filename(local_file_path, content_type = content_type)\n",
        "\n",
        "  print(f\"File '{local_file_path}' uploaded to GCS bucket '{bucket_name}' as '{destination_blob_name}.  Content-Type: {content_type}'.\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fObymYQPeqIm"
      },
      "outputs": [],
      "source": [
        "def download_from_gcs(filename, gcs_storage_bucket, gcs_storage_path):\n",
        "  # prompt: Write python code to download a blob from a gcs bucket.  do not use the requests method\n",
        "\n",
        "  from google.cloud import storage\n",
        "\n",
        "  # The ID of your GCS object\n",
        "  object_name = gcs_storage_path + filename\n",
        "\n",
        "  # The path to which the file should be downloaded\n",
        "  destination_file_name = filename\n",
        "\n",
        "  storage_client = storage.Client()\n",
        "\n",
        "  bucket = storage_client.bucket(gcs_storage_bucket)\n",
        "\n",
        "  # Construct a client side representation of a blob.\n",
        "  # Note `Bucket.blob` differs from `Bucket.get_blob` as it doesn't retrieve\n",
        "  # any content from Google Cloud Storage. As we don't need additional data,\n",
        "  # using `Bucket.blob` is preferred here.\n",
        "  blob = bucket.blob(object_name)\n",
        "  blob.download_to_filename(destination_file_name)\n",
        "\n",
        "  print(\n",
        "      \"Downloaded storage object {} from bucket {} to local file {}.\".format(\n",
        "          object_name, gcs_storage_bucket, destination_file_name\n",
        "      )\n",
        "  )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MOnML8jpdwzg"
      },
      "source": [
        "## Create the Marketing Campaign"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kgYIuouWCViv"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "\n",
        "-- Copy the table from the curated dataset if the Menu-A-B-Testing-Generate-Insight-GenAI notebook has not been run.\n",
        "\n",
        "CREATE SCHEMA IF NOT EXISTS `${project_id}.data_beans_synthetic_data`;\n",
        "CREATE TABLE IF NOT EXISTS `${project_id}.data_beans_synthetic_data.menu_a_b_testing` COPY `${project_id}.data_beans_curated.menu_a_b_testing`;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aCkkfFr2H5wk"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "\n",
        "-- Create our marketing prompt for Email, Twitter (X), and Instragram\n",
        "\n",
        "UPDATE `${project_id}.data_beans_synthetic_data.menu_a_b_testing` AS parent\n",
        "   SET llm_marketing_prompt =\n",
        "       CONCAT(\"You run a fleet of coffee trucks in \", city.city_name, \". \",\n",
        "       \"We need to craft a compelling marketing campaign for a new menu item. \",\n",
        "       \"Craft 3 sections for the campaign: A Twitter section, An Instragram section and an Email section. \",\n",
        "       \"The new menu item's name is: \", child.item_name, \". \",\n",
        "       \"The new menu item's description is: \", IFNULL(child.item_description,child.item_name), \", \",\n",
        "       \"The new menu item's image looks like: \", child.llm_item_image_prompt, \". \",\n",
        "       \"Embrace unconventional ideas and thinking that surprises and inspires unique variations. \",\n",
        "       \"Write the response in using the language \",\n",
        "       CASE WHEN city.city_id = 1 THEN \"American English\"\n",
        "            WHEN city.city_id = 2 THEN \"British English\"\n",
        "            WHEN city.city_id = 3 THEN \"Japanese\"\n",
        "            WHEN city.city_id = 4 THEN \"American English\"\n",
        "            ELSE \"English\"\n",
        "       END, \". \",\n",
        "       \"The campaign should be 3 to 4 sentences. \",\n",
        "       \"Do not mention the weather in the message. \",\n",
        "       \"Do not mention 'Coming Soon' since the menu item is available now. \",\n",
        "       \"Do not create any token replacements like square brackets or curly braces. \",\n",
        "       \"This is not a template it is a complete message. \",\n",
        "       \"Return the results in JSON with no special characters or formatting. \",\n",
        "       \"Double check for special characters especially for Japanese. \",\n",
        "       'Create a subject up to 150 characters and place in the JSON \"subject\" field. ',\n",
        "       'Place the twitter text in the JSON \"twitter\" field. ',\n",
        "       'Place the instagram text in the JSON \"instagram\" field. ',\n",
        "       'Place the email text in the JSON \"email\" field. ',\n",
        "       \"Example Return Data:\",\n",
        "       '{ \"subject\" : \"Wow! Mocha Munchkins!, \"twitter\": \"Mocha Munchkins get some before they are gone.\", \"instagram\" : \"Check out this photo\", \"email\" : \"Check this out\"} ',\n",
        "       '{ \"subject\" : \"Robo Coffee a Must!\", \"twitter\": \"Try our all new Robo coffee.  Available Now.\", \"instagram\" : \"Wow new item\", \"email\" : \"You must taste this\"} '\n",
        "       ),\n",
        "       llm_marketing_response = null,\n",
        "       llm_marketing_parsed_response = null,\n",
        "       html_filename = null,\n",
        "       html_url = null,\n",
        "       html_generated = FALSE\n",
        "  FROM `${project_id}.data_beans_synthetic_data.menu_a_b_testing` AS child\n",
        "       INNER JOIN `${project_id}.data_beans_curated.location` AS location\n",
        "               ON child.location_id = location.location_id\n",
        "       INNER JOIN `${project_id}.data_beans_curated.city` AS city\n",
        "               ON location.city_id = city.city_id\n",
        " WHERE child.menu_a_b_testing_id = parent.menu_a_b_testing_id\n",
        "   AND parent.llm_marketing_prompt IS NULL;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vkhtwAMCOih_"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "\n",
        "-- Score our LLM prompt\n",
        "UPDATE `${project_id}.data_beans_synthetic_data.menu_a_b_testing` AS menu_a_b_testing\n",
        "   SET llm_marketing_response = llm_query.ml_generate_text_result,\n",
        "       llm_marketing_parsed_response = NULL\n",
        "  FROM (SELECT *\n",
        "          FROM ML.GENERATE_TEXT(MODEL `${project_id}.data_beans_curated.gemini_model`,\n",
        "              (SELECT menu_a_b_testing_id,\n",
        "                      llm_marketing_prompt AS prompt\n",
        "                 FROM `${project_id}.data_beans_synthetic_data.menu_a_b_testing`\n",
        "                WHERE (llm_marketing_response IS NULL\n",
        "                       OR\n",
        "                       JSON_VALUE(llm_marketing_response, '$.candidates[0].content.parts[0].text') IS NULL\n",
        "                       )\n",
        "                ),\n",
        "               STRUCT(\n",
        "               .9 AS temperature,\n",
        "               5000 AS max_output_tokens,\n",
        "               .8 AS top_p,\n",
        "               30 AS top_k\n",
        "           ))\n",
        "      ) AS llm_query\n",
        "WHERE menu_a_b_testing.menu_a_b_testing_id = llm_query.menu_a_b_testing_id;\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "V8FEcWsbRn-h"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "\n",
        "-- Parse our custom JSON out of the LLM response JSON\n",
        "UPDATE `${project_id}.data_beans_synthetic_data.menu_a_b_testing`\n",
        "   SET llm_marketing_parsed_response = `${project_id}.data_beans_curated.gemini_model_result_as_json`(llm_marketing_response)\n",
        " WHERE llm_marketing_parsed_response IS NULL;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "mwn9L2ybSYxe"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "\n",
        "-- Show the results\n",
        "SELECT menu_a_b_testing_id, llm_marketing_parsed_response\n",
        " FROM `${project_id}.data_beans_synthetic_data.menu_a_b_testing`\n",
        "ORDER BY menu_a_b_testing_id DESC\n",
        "LIMIT 10;\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3_SuFob3Wyjp"
      },
      "source": [
        "## Create the HTML message"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "SnhkGf-3W33m"
      },
      "outputs": [],
      "source": [
        "sql = \"\"\"SELECT menu_a_b_testing_id, llm_marketing_parsed_response\n",
        "           FROM `${project_id}.data_beans_synthetic_data.menu_a_b_testing`\n",
        "         WHERE html_generated IS NULL OR html_generated = FALSE\n",
        "        ORDER BY menu_a_b_testing_id\n",
        "\"\"\"\n",
        "\n",
        "df_process = client.query(sql).to_dataframe()\n",
        "\n",
        "for row in df_process.itertuples():\n",
        "  menu_a_b_testing_id = row.menu_a_b_testing_id\n",
        "  llm_marketing_parsed_response = row.llm_marketing_parsed_response\n",
        "\n",
        "  print(f\"menu_a_b_testing_id: {menu_a_b_testing_id}\")\n",
        "  print(f\"llm_marketing_parsed_response: {llm_marketing_parsed_response}\")\n",
        "\n",
        "  prompt=f\"\"\"Convert the below text into a well-structured HTML document.\n",
        "The document should have an email section, a Twitter section called \"X\" and an Instagram section.\n",
        "Create a header for each section: Email Campaign, X Campaign, Instagram Campaign.\n",
        "The Email Campaign section should use the color \"#EDF2F9\" for the background.\n",
        "The X Campaign section should use the color White for the background.\n",
        "The Instagram Campaign section should use the color \"#EDF2F9\" for the background.\n",
        "Seperate each Campaign section with space and a line.\n",
        "Refrain from using <h1>, <h2>, <h3>, and <h4> tags.\n",
        "Do not create a template that uses square or curly braces, use the actual text.\n",
        "Create inline styles.\n",
        "Make the styles fun, eye catching and exciting.\n",
        "Use \"Helvetica Neue\" as the font.\n",
        "All text should be left aligned.\n",
        "Avoid fonts larger than 16 pixels.\n",
        "Do not change the language.  Keep the text in the native language.\n",
        "\n",
        "Include the following image in the html at the very botton in its own section:\n",
        "- The image is located at: https://REPLACE_ME\n",
        "- The image url should have a \"width\" of 500 and \"height\" of 500.\n",
        "\n",
        "Double check that you did not use any <h1>, <h2>, <h3>, or <h4> tags.\n",
        "\n",
        "Text:\n",
        "{llm_marketing_parsed_response}\n",
        "\"\"\"\n",
        "\n",
        "  print(f\"prompt: {prompt}\")\n",
        "\n",
        "\n",
        "  # The LLM can generate some HTML tags that can cause issues when parsing the result\n",
        "  llm_success = False\n",
        "  temperature = .5\n",
        "\n",
        "  while llm_success == False:\n",
        "    try:\n",
        "      llm_response = GeminiProLLM(prompt, temperature=temperature, topP=.5, topK = 20)\n",
        "\n",
        "      if llm_response.startswith(\"html\"):\n",
        "        llm_response = llm_response[4:] # incase the response is formatted like markdown\n",
        "\n",
        "      # download file\n",
        "      filename = str(menu_a_b_testing_id) + \".png\"\n",
        "      download_from_gcs(filename, gcs_storage_bucket, gcs_storage_path)\n",
        "\n",
        "      # open locally\n",
        "      imageBase64 = convert_png_to_base64(filename)\n",
        "\n",
        "      # Replace the image with an inline image (this avoids a SignedURL or public access to GCS bucket)\n",
        "      html_text = llm_response.replace(\"https://REPLACE_ME\", f\"data:image/png;base64, {imageBase64}\")\n",
        "\n",
        "      filename= str(menu_a_b_testing_id) + \".html\"\n",
        "      with open(filename, \"w\", encoding='utf8') as f:\n",
        "        f.write(html_text)\n",
        "      print (\"\")\n",
        "\n",
        "      copy_file_to_gcs(filename, gcs_storage_bucket, gcs_storage_path + filename)\n",
        "\n",
        "      sql = \"UPDATE `${project_id}.data_beans_synthetic_data.menu_a_b_testing` \" + \\\n",
        "              \"SET html_generated = TRUE, \" + \\\n",
        "                  \"html_filename = '\" + \"gs://${project_id}/data-beans/menu-images-a-b-testing/\" + filename + \"', \" \\\n",
        "                  \"html_url = '\" + \"https://storage.cloud.google.com/${data_beans_curated_bucket}/data-beans/menu-images-a-b-testing/\" + filename + \"' \" \\\n",
        "            \"WHERE menu_a_b_testing_id = \" + str(menu_a_b_testing_id)\n",
        "\n",
        "      RunQuery(sql)\n",
        "\n",
        "      llm_success = True\n",
        "\n",
        "    except:\n",
        "      # Reduce the temperature for more accurate generation\n",
        "      temperature = temperature - .05\n",
        "      print(\"Regenerating...\")\n",
        "      if temperature <= 0:\n",
        "        llm_success = True\n",
        "\n",
        "  # break\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "138Vue03Y_1O"
      },
      "outputs": [],
      "source": [
        "# You can hardcode one if you like: e.g. \"1.html\"\n",
        "HTML(filename)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "2uVnvfwDB3-r",
        "msqH1FnmB3-s",
        "8zy0eEJmHxRZ",
        "YtZuFgjbOjso",
        "xUolPsMFOjpZ",
        "E5CFSdK3HxYm",
        "-L93udtrH1Oz",
        "rVCY93IyXPoO",
        "BlxddNzpmAgp",
        "MOnML8jpdwzg",
        "3_SuFob3Wyjp"
      ],
      "name": "BigQuery table",
      "private_outputs": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}