qwiklabs/colab-enterprise/gen-ai-demo/Marketing-Campaign-Generate-Insight-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\"> Using GenAI (Gemini Pro and Imagen2) to create a Marketing Campaign\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### License"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "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": {},
      "source": [
        "### Notebook Overview"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0zKI-ZR1Spog"
      },
      "source": [
        "**Goal:**\n",
        "- Create a marketing campaign that will be email to customers.  The campaign should be a HTML email with a picture of the coffee/food.\n",
        "\n",
        "**Steps:**\n",
        "1. Determine the items we want to sell from our menu table.\n",
        "2. Create a basic LLM prompt for Imagen 2 to render the image.\n",
        "3. We want a really good image that inspires our customers to purchase our coffee/food so we will brainstorm with the LLM\n",
        "   - Ask Gemini Pro to take our original image prompt and create 10 new creative prompts.\n",
        "   - We are using the LLM to write our prompt for us.\n",
        "   - We will also add in a prompt that has nothing to do with coffee/food ()\n",
        "4. We want to verify the image contains the original coffee/food items we requested.\n",
        "   - The LLM might have been too creative so let's verify each image.\n",
        "   - Pass each image to Gemini Pro Vision and ask it to verify the contents of the image and explain its reasoning.\n",
        "5. For each of the verified images, let's do a taste test.\n",
        "   - Pass all the images to Gemini Pro Vision and ask it to rate each image from 1 to 100.\n",
        "   - Rate the images based upon how realistic they are and creative.\n",
        "   - Have the LLM explain its reasoning\n",
        "6. We now have the highest rated image and we can see it compared to the original image.\n",
        "7. Now, we will generate our marketing campaign.  \n",
        "   - We will pass our prompt and the image to Gemini Pro Vision and ask it generate a marketing campaign.\n",
        "   - We will do this for each City\n",
        "   - We will ask the LLM to write the campaign in the native language for each city (English, Japanese)\n",
        "8. With the marketing text we will now need it formatted as HTML\n",
        "   - Pass the marketing text to ask Gemini to create HTML\n",
        "   - Ask Gemini to style the email\n",
        "   - Ask Gemini to leave a placeholder for our image\n",
        "9. With the HMTL now replace the placeholder with our image\n",
        "   - The image is base64 encoded so we inline the image versus hosting it on a server.\n",
        "10. Save everything to BigQuery\n",
        "      - Save a record for each city\n",
        "      - Save all of our prompts (we can use this later to learn which prompts generate the best results)\n",
        "      - Save the HTML (UTF-8 for international character support)\n",
        "      - Save the images to a GCS bucket"
      ]
    },
    {
      "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",
        "llm_marketing_prompt = []\n",
        "llm_marketing_prompt_text = []\n",
        "\n",
        "llm_marketing_prompt_html = []\n",
        "llm_marketing_prompt_html_text = []\n",
        "\n",
        "gcs_storage_bucket = \"${data_beans_curated_bucket}\"\n",
        "gcs_storage_path = \"data-beans/marketing-campaign/\""
      ]
    },
    {
      "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",
        "\n",
        "from google.cloud import bigquery\n",
        "client = bigquery.Client()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YtZuFgjbOjso"
      },
      "source": [
        "## Imagen2 / Gemini Pro / Gemini Pro Vision (Helper Functions)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xUolPsMFOjpZ"
      },
      "source": [
        "#### Imagen2"
      ]
    },
    {
      "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": [
        "#### 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": "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": "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": "markdown",
      "metadata": {
        "id": "pc1GJhToOv2A"
      },
      "source": [
        "## Generating a Marketing Campaign for each City"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xb-29vadVthD"
      },
      "source": [
        "#### Let's start with a basic Vision prompt that a Human might create.\n",
        "- This is a basic prompt that someone might create"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Il7CxvmP62DY"
      },
      "outputs": [],
      "source": [
        "# This will generate a fairly basic image that might not be the most inviting image\n",
        "\n",
        "from random import randrange\n",
        "random_1_to_5 = randrange(5) + 1;  \n",
        "\n",
        "# you can hard code this for a specific value\n",
        "random_1_to_5 = 5 \n",
        "\n",
        "match random_1_to_5:\n",
        "  case 1:\n",
        "        picture_description = \"Coffee Glazed Donut with Java Chip Pancakes\"\n",
        "  case 2:\n",
        "        picture_description = \"Cappuccino Cheesecake with a Cinnamon Spice latte\"\n",
        "  case 3:\n",
        "        picture_description = \"Coffee-Infused Chocolate Truffles with Coffee Bean Ice Cream\"\n",
        "  case 4:\n",
        "        picture_description = \"Coffee Tiramisu with Cafe Con Leche\"\n",
        "  case 5:\n",
        "        picture_description = \"Mocha Pancakes with Vanilla Bean ice cream\"\n",
        "  case _:\n",
        "      picture_description = \"Coffee Glazed Donut with Java Chip Pancakes\"\n",
        "\n",
        "print(f\"Picture Description: {picture_description}\")\n",
        "\n",
        "llm_human_image_prompt = f\"Create an image that contains a {picture_description} that I can use for a marketing campaign.\"\n",
        "human_prompt_filename = ImageGen(llm_human_image_prompt)\n",
        "img = Image.open(human_prompt_filename)\n",
        "llm_human_image_json = {\n",
        "    \"gcs_storage_bucket\" : gcs_storage_bucket,\n",
        "    \"gcs_storage_path\" : gcs_storage_path,\n",
        "    \"llm_image_filename\" : human_prompt_filename\n",
        "}\n",
        "img.thumbnail([500,500])\n",
        "IPython.display.display(img)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hZnrIe-3WWWf"
      },
      "source": [
        "#### Now let's improve our image by calling Gemini Pro create a better Imagen 2 prompt\n",
        "- LLMs can be used to create LLM prompts for better results.\n",
        "- We are basically asking the LLM to write the prompt that we will then submit as our Imagen 2 prompt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OL8d9iuwIv-D"
      },
      "outputs": [],
      "source": [
        "example_return_json = '[ { \"prompt\" : \"response 1\" }, { \"prompt\" : \"response 2\" }, { \"prompt\" : \"A prompt for good LLMs\" }, { \"prompt\" : \"Generate an image that is awesome\" }]'\n",
        "\n",
        "llm_generated_image_prompt=f\"\"\"For the below prompt, rewrite it in 10 different ways so I get the most creative images to be generated.\n",
        "Try creative thinking, generate innovative and out-of-the-box ideas to solve the problem.\n",
        "Explore unconventional solutions, thinking beyond traditional boundaries, and encouraging imagination and originality.\n",
        "Embrace unconventional ideas and mutate the prompt in a way that surprises and inspires unique variations.\n",
        "Think outside the box and develop a mutator prompt that encourages unconventional approaches and fresh perspectives.\n",
        "Return the results in below \"JSON format\" with no special characters, headers or formatting.\n",
        "Limit each json result to 256 characters.\n",
        "\n",
        "JSON format:\n",
        "{example_return_json}\n",
        "\n",
        "Prompt: \"Create a picture of a {picture_description} to be used for a marketing campaign.\"\n",
        "\"\"\"\n",
        "\n",
        "llm_success = False\n",
        "temperature=.8\n",
        "while llm_success == False:\n",
        "  try:\n",
        "    llm_response = GeminiProLLM(llm_generated_image_prompt, temperature=temperature, topP=.8, topK = 40)\n",
        "    print(llm_response)\n",
        "    bracket_index = llm_response.find('[')\n",
        "    if bracket_index > 0:\n",
        "      print(\"Removing LLM text that preceeds the JSON\")\n",
        "      llm_response = llm_response[bracket_index:]\n",
        "      print(llm_response)\n",
        "    json.loads(llm_response)\n",
        "    llm_success = True\n",
        "  except:\n",
        "    # Reduce the temperature for more accurate generation\n",
        "    temperature = temperature - .05\n",
        "    print(\"Regenerating...\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_yDXoVv3W8TA"
      },
      "source": [
        "#### Generate the images with our enhanced prompts\n",
        "- Use our generated prompts to pass to Imagen 2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8MBobVxXPfqN"
      },
      "outputs": [],
      "source": [
        "llm_json = json.loads(llm_response)\n",
        "\n",
        "# Add an image to the generation that will not contain any food items.  We will test this later.\n",
        "llm_json.append({'prompt' : 'Draw a coffee truck with disco lights.'})\n",
        "\n",
        "image_files = []\n",
        "\n",
        "for item in llm_json:\n",
        "  print(item[\"prompt\"])\n",
        "  try:\n",
        "    image_file = ImageGen(item[\"prompt\"])\n",
        "    image_files.append ({\n",
        "        \"llm_prompt\" : item[\"prompt\"],\n",
        "        \"gcs_storage_bucket\" : gcs_storage_bucket,\n",
        "        \"gcs_storage_path\" : gcs_storage_path,\n",
        "        \"llm_image_filename\" : image_file,\n",
        "        \"llm_validated_by_llm\" : False\n",
        "    })\n",
        "  except:\n",
        "    print(\"Image failed to generate.\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cvxibhW3MZxg"
      },
      "outputs": [],
      "source": [
        "# View the results\n",
        "\n",
        "llm_generated_image_json = []\n",
        "\n",
        "for item in image_files:\n",
        "  # Json for BigQuery\n",
        "  llm_generated_image_json.append( {\n",
        "      \"llm_prompt\" : item[\"llm_prompt\"],\n",
        "      \"gcs_storage_bucket\" : item[\"gcs_storage_bucket\"],\n",
        "      \"gcs_storage_path\" : item[\"gcs_storage_path\"],\n",
        "      \"llm_image_filename\" : item[\"llm_image_filename\"]\n",
        "      })\n",
        "\n",
        "  print(f\"Filename: {item['llm_image_filename']}\")\n",
        "  img = Image.open(item[\"llm_image_filename\"])\n",
        "  img.thumbnail([500,500])\n",
        "  IPython.display.display(img)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JpQ_CEYkBUYu"
      },
      "source": [
        "#### Verify that each image contains the food items we asked for\n",
        "- Is the image an image that actaully represents what we asked for?\n",
        "- Sometimes the image might not contain the details we asked for.\n",
        "- We will run each image through Gemini Pro Vision and verify it is actaully what we want."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Hgg_UaKDCNzf"
      },
      "outputs": [],
      "source": [
        "example_return_json = '{ \"response\" : true, \"explanation\" : \"Reason why...\" }'\n",
        "\n",
        "llm_validated_image_json = []\n",
        "number_of_valid_images = 0\n",
        "\n",
        "llm_validated_image_prompt = f\"\"\"Is attached image a picture of \"{picture_description}\"?\n",
        "Respond with a boolean of true or false and place in the \"response\" field.\n",
        "Explain your reasoning for each image and place in the \"explanation\" field.\n",
        "Return the results in JSON with no special characters or formatting.\n",
        "\n",
        "Place the results in the following JSON structure:\n",
        "{example_return_json}\n",
        "\"\"\"\n",
        "\n",
        "for item in image_files:\n",
        "  print(f\"LLM Prompt : {item['llm_prompt']}\")\n",
        "  print(f\"LLM Filename : {item['llm_image_filename']}\")\n",
        "  imageBase64 = convert_png_to_base64(item['llm_image_filename'])\n",
        "\n",
        "  llm_success = False\n",
        "  temperature = .4\n",
        "  while llm_success == False:\n",
        "    try:\n",
        "      llm_response = GeminiProVisionLLM(llm_validated_image_prompt, imageBase64, temperature=temperature)\n",
        "      print(f\"llm_response : {llm_response}\")\n",
        "      llm_json = json.loads(llm_response)\n",
        "      llm_success = True\n",
        "    except:\n",
        "      # Reduce the temperature for more accurate generation\n",
        "      temperature = temperature - .05\n",
        "      print(\"Regenerating...\")\n",
        "\n",
        "  # Mark this item as useable\n",
        "  if llm_json[\"response\"] == True:\n",
        "    item[\"llm_validated_by_llm\"] = True\n",
        "    number_of_valid_images = number_of_valid_images + 1\n",
        "\n",
        "  item[\"llm_validated_by_llm_explanation\"] = llm_json[\"explanation\"]\n",
        "\n",
        "  llm_validated_image_json.append( {\n",
        "      \"llm_prompt\" : item[\"llm_prompt\"],\n",
        "      \"gcs_storage_bucket\" : item[\"gcs_storage_bucket\"],\n",
        "      \"gcs_storage_path\" : item[\"gcs_storage_path\"],\n",
        "      \"llm_image_filename\" : item[\"llm_image_filename\"],\n",
        "      \"llm_validated_by_llm\" : item[\"llm_validated_by_llm\"],\n",
        "      \"llm_validated_by_llm_explanation\" : item[\"llm_validated_by_llm_explanation\"]\n",
        "      })"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UEs2SVfVeNnJ"
      },
      "source": [
        "#### Now let's ask the LLM which one looks the tastiest\n",
        "- We want the image that looks the best so we will rate each one from 1 to 10\n",
        "- Add all the verified images to a Gemini Pro Vision prompt.  Gemini supports multimodal prompts.\n",
        "- This will help us narrow down which image we will use for the marketing campaign"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7gz9GTcJeR89"
      },
      "outputs": [],
      "source": [
        "image_prompt = []\n",
        "\n",
        "for item in image_files:\n",
        "  if item[\"llm_validated_by_llm\"] == True:\n",
        "    print(f\"Adding image {item['llm_image_filename']} to taste test.\")\n",
        "    imageBase64 = convert_png_to_base64(item['llm_image_filename'])\n",
        "    new_item = {\n",
        "        \"llm_image_filename\" : item['llm_image_filename'],\n",
        "        \"llm_image_base64\" : f\"{imageBase64}\"\n",
        "    }\n",
        "    image_prompt.append(new_item)\n",
        "\n",
        "example_return_json='[ {\"image_name\" : \"name\", \"rating\" : 10, \"explanation\": \"\"}]'\n",
        "\n",
        "llm_taste_test_image_prompt=f\"\"\"You are going to be presented with {number_of_valid_images}.\n",
        "You must critique each image and assign it a score from 1 to 100.\n",
        "You should compare the images to one another.\n",
        "You should evaluate each image multiple times.\n",
        "\n",
        "Score each image based upon the following:\n",
        "- Appetizing images should get a high rating.\n",
        "- Realistic images should get a high rating.\n",
        "- Thought provoking images should get a high rating.\n",
        "- Plastic looking images should get a low rating.\n",
        "- Abstract images should get a low rating.\n",
        "\n",
        "Think the rating through step by step for each image.\n",
        "\n",
        "Place the result of the scoring process in the \"rating\" field.\n",
        "Return the image name and place in the \"image_name\" field.\n",
        "Explain your reasoning and place in the \"explanation\" field in less than 20 words.\n",
        "\n",
        "Place the results in the following JSON structure:\n",
        "{example_return_json}\n",
        "\"\"\"\n",
        "\n",
        "llm_success = False\n",
        "temperature=1\n",
        "while llm_success == False:\n",
        "  try:\n",
        "    llm_response = GeminiProVisionMultipleFileLLM(llm_taste_test_image_prompt, image_prompt, temperature = temperature)\n",
        "    llm_success = True\n",
        "  except:\n",
        "    # Reduce the temperature for more accurate generation\n",
        "    temperature = temperature - .05\n",
        "    print(\"Regenerating...\")\n",
        "\n",
        "\n",
        "print(f\"llm_response : {llm_response}\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "h5qP1YpWluHH"
      },
      "outputs": [],
      "source": [
        "# Show the highest rated image (and save the id)\n",
        "llm_json = json.loads(llm_response)\n",
        "highest_rated_llm_image_rating = 0\n",
        "highest_rated_llm_image_filename = \"\"\n",
        "llm_taste_test_image_json = []\n",
        "\n",
        "for item in llm_json:\n",
        "  # Find and update each image with the rating and explanation\n",
        "  for existing in image_files:\n",
        "    if existing[\"llm_image_filename\"] == item[\"image_name\"]:\n",
        "      existing[\"image_rating\"] = item[\"rating\"]\n",
        "      existing[\"image_explanation\"] = item[\"explanation\"]\n",
        "      llm_taste_test_image_json.append( {\n",
        "          \"llm_prompt\" : existing[\"llm_prompt\"],\n",
        "          \"gcs_storage_bucket\" : existing[\"gcs_storage_bucket\"],\n",
        "          \"gcs_storage_path\" : existing[\"gcs_storage_path\"],\n",
        "          \"llm_image_filename\" : existing[\"llm_image_filename\"],\n",
        "          \"llm_validated_by_llm\" : existing[\"llm_validated_by_llm\"],\n",
        "          \"llm_validated_by_llm_explanation\" : existing[\"llm_validated_by_llm_explanation\"],\n",
        "          \"image_rating\" : existing[\"image_rating\"],\n",
        "          \"image_rating_explanation\" : existing[\"image_explanation\"]\n",
        "          })\n",
        "      if existing[\"image_rating\"] >= highest_rated_llm_image_rating:\n",
        "        highest_rated_llm_image_rating = existing[\"image_rating\"]\n",
        "        highest_rated_llm_image_filename = existing[\"llm_image_filename\"]\n",
        "      break\n",
        "\n",
        "\n",
        "print(f\"Highest rated highest_rated_llm_image_filename: {highest_rated_llm_image_filename}\")\n",
        "img = Image.open(highest_rated_llm_image_filename)\n",
        "img.thumbnail([500,500])\n",
        "IPython.display.display(img)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qal6ElkGoYRX"
      },
      "outputs": [],
      "source": [
        "# Compare this with our human generated prompt image\n",
        "img = Image.open(human_prompt_filename)\n",
        "img.thumbnail([500,500])\n",
        "IPython.display.display(img)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Mvrq-tC_im2M"
      },
      "source": [
        "#### Now that we have our image, let's generate the best marketing text"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zUMQERBS1QoB"
      },
      "outputs": [],
      "source": [
        "example_return_json_1 = '{ \"city\" : \"New York City\", \"subject\" : \"email subject\" , \"body\" : \"email body\" }'\n",
        "example_return_json_2 = '{ \"city\" : \"London\", \"subject\" : \"marketing subject\" , \"body\" : \"The body of the email message\" }'\n",
        "imageBase64 = convert_png_to_base64(highest_rated_llm_image_filename)\n",
        "\n",
        "llm_marketing_campaign = []\n",
        "\n",
        "# Loop for each city\n",
        "for city_index in range(0, 4):\n",
        "  print(f\"Processing city: {city_names[city_index]}\")\n",
        "\n",
        "  prompt=f\"\"\"You run a fleet of coffee trucks in {city_names[city_index]}.\n",
        "  We need to craft a compelling email marketing campaign for the below image.\n",
        "  Embrace unconventional ideas and thinking that surprises and inspires unique variations.\n",
        "  We want to offer exclusive discounts or early access to drive sales.\n",
        "  Look at the image and come up with a savory message.\n",
        "  The image contains \"{picture_description}\".\n",
        "\n",
        "  The marketing campaign should per personalize for {city_names[city_index]}.\n",
        "  Do not mention the weather in the message.\n",
        "  Write the response in using the language \"{city_languages[city_index]}\".\n",
        "  The campaign should be less than 1500 characters.\n",
        "  The email message should be formatted in text, not HTML.\n",
        "  Sign the email message \"Sincerely, Data Beans\".\n",
        "  Mention that people can find the closest coffee truck location by using our mobile app.\n",
        "\n",
        "  Return the results in JSON with no special characters or formatting.\n",
        "  Double check for special characters especially for Japanese.\n",
        "  Place the value of \"{city_names[city_index]}\" in the \"city\" field of the JSON response.\n",
        "\n",
        "  Example Return Data:\n",
        "  {example_return_json_1}\n",
        "  {example_return_json_2}\n",
        "\n",
        "  Image of items we want to sell:\n",
        "  \"\"\"\n",
        "\n",
        "  llm_success = False\n",
        "  temperature = .8\n",
        "  while llm_success == False:\n",
        "    try:\n",
        "      llm_response = GeminiProVisionLLM(prompt, imageBase64, temperature=.8, topP=.9, topK = 40)\n",
        "      llm_json = json.loads(llm_response)\n",
        "      print(llm_response)\n",
        "      llm_success = True\n",
        "    except:\n",
        "      # Reduce the temperature for more accurate generation\n",
        "      temperature = temperature - .05\n",
        "      print(\"Regenerating...\")\n",
        "\n",
        "  llm_marketing_campaign.append(llm_json)\n",
        "\n",
        "  llm_marketing_prompt.append(prompt)\n",
        "  llm_marketing_prompt_text.append(llm_response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rVDYesaXitpd"
      },
      "source": [
        "#### Create the HTML email message we will send to our customers\n",
        "- We do not know html\n",
        "- Also, it would be nice to base64 encode the image into the stylesheet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xN03D3esuFl_"
      },
      "outputs": [],
      "source": [
        "imageBase64 = convert_png_to_base64(highest_rated_llm_image_filename)\n",
        "\n",
        "for item in llm_marketing_campaign:\n",
        "  print (f'City:    {item[\"city\"]}')\n",
        "  print (f'Subject: {item[\"subject\"]}')\n",
        "\n",
        "  prompt=f\"\"\"Convert the below text into a well-structured HTML document.\n",
        "Refrain from using <h1>, <h2>, <h3>, and <h4> tags.\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",
        "Do not include any notes, only generate the HTML.\n",
        "\n",
        "Include the following image in the html:\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",
        "{item[\"body\"]}\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",
        "      # 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",
        "      item[\"html\"] = html_text\n",
        "\n",
        "      #print (f'Html:    {item[\"html\"]}')\n",
        "      filename= str(item[\"city\"]).replace(\" \",\"-\") + \".html\"\n",
        "      with open(filename, \"w\", encoding='utf8') as f:\n",
        "        f.write(item[\"html\"])\n",
        "      print (\"\")\n",
        "\n",
        "      llm_success = True\n",
        "      llm_marketing_prompt_html.append(prompt)\n",
        "      llm_marketing_prompt_html_text.append(html_text)\n",
        "\n",
        "    except:\n",
        "      # Reduce the temperature for more accurate generation\n",
        "      temperature = temperature - .05\n",
        "      print(\"Regenerating...\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ju6iFxIcwBsX"
      },
      "outputs": [],
      "source": [
        "HTML(filename=str(\"New York City\").replace(\" \",\"-\") + \".html\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CIi_hpq-jqO5"
      },
      "outputs": [],
      "source": [
        "HTML(filename=str(\"London\").replace(\" \",\"-\") + \".html\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-OCQZdaOknm6"
      },
      "outputs": [],
      "source": [
        "HTML(filename=str(\"Tokyo\").replace(\" \",\"-\") + \".html\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NPhGYsYhvcS9"
      },
      "outputs": [],
      "source": [
        "HTML(filename=str(\"San Francisco\").replace(\" \",\"-\") + \".html\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d2EuHkw_0m4z"
      },
      "source": [
        "#### Save the results to BigQuery"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9WXk4_sML41o"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE TABLE IF NOT EXISTS `${project_id}.${bigquery_data_beans_curated_dataset}.marketing_gen_ai_insight`\n",
        "--CREATE OR REPLACE TABLE `${project_id}.${bigquery_data_beans_curated_dataset}.marketing_gen_ai_insight` -- only use to replace the entire table\n",
        "(\n",
        "    marketing_gen_ai_insight_id    INTEGER   NOT NULL OPTIONS(description=\"Primary key.\"),\n",
        "    marketing_gen_ai_insight_type  STRING    NOT NULL OPTIONS(description=\"The type of insight: WEATHER, EVENT, CAMPAIGN, SOCIAL, WAIT-TIME, SALES, VIDEO-AI\"),\n",
        "    insight_datetime               TIMESTAMP NOT NULL OPTIONS(description=\"The datetime of the GenAI insight.\"),\n",
        "    applies_to_entity_type         STRING    NOT NULL OPTIONS(description=\"The type of entity to which the insight applies: Company, City, Truck, Customer\"),\n",
        "    applies_to_entity_id           INTEGER   NOT NULL OPTIONS(description=\"The id (primary key) of entity to which the insight applies.\"),\n",
        "    applies_to_entity_name         STRING    NOT NULL OPTIONS(description=\"The name to which the insight applies.\"),\n",
        "\n",
        "    picture_description            STRING    NOT NULL OPTIONS(description=\"The description of the image we want to generate.\"),\n",
        "    json_filename                  STRING    NOT NULL OPTIONS(description=\"The location of the json file that holds all the prompts.\"),\n",
        "    html_filename                  STRING    NOT NULL OPTIONS(description=\"The location of the HTML file for the marketing campaign.\"),\n",
        "    subject                        STRING    NOT NULL OPTIONS(description=\"The generated subject line for the marketing campaign.\"),\n",
        ")\n",
        "CLUSTER BY marketing_gen_ai_insight_id;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pp7l-TB5YDNr"
      },
      "outputs": [],
      "source": [
        "from datetime import datetime\n",
        "\n",
        "# Get the current date and time\n",
        "now = datetime.now()\n",
        "\n",
        "# Format the date and time as desired\n",
        "formatted_date = now.strftime(\"%Y-%m-%d-%H-%M\")\n",
        "\n",
        "print(formatted_date)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dMV-ohC9Mcoc"
      },
      "outputs": [],
      "source": [
        "# Copy all image files to storage\n",
        "for item in image_files:\n",
        "  copy_file_to_gcs(item[\"llm_image_filename\"],item[\"gcs_storage_bucket\"], item[\"gcs_storage_path\"] + formatted_date + \"/Imagen2_\" + item[\"llm_image_filename\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ece4AdUFi2Vv"
      },
      "outputs": [],
      "source": [
        "copy_file_to_gcs(llm_human_image_json[\"llm_image_filename\"],llm_human_image_json[\"gcs_storage_bucket\"], llm_human_image_json[\"gcs_storage_path\"] + formatted_date + \"/Imagen2_basic_prompt_\" + item[\"llm_image_filename\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cWqUVSkXXO5E"
      },
      "outputs": [],
      "source": [
        "marketing_gen_ai_insight_id = GetNextPrimaryKey(\"${project_id}.${bigquery_data_beans_curated_dataset}.marketing_gen_ai_insight\",\"marketing_gen_ai_insight_id\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pcnk_S6yXQB8"
      },
      "outputs": [],
      "source": [
        "# Copy the html images\n",
        "for city_index in range(0, 4):\n",
        "  filename = city_names[city_index].replace(\" \",\"-\") + \".html\"\n",
        "  file_destination_name = formatted_date + \"/\" + str(marketing_gen_ai_insight_id + city_index) + \"-\" + city_names[city_index].replace(\" \",\"-\") + \".html\"\n",
        "  copy_file_to_gcs(filename,gcs_storage_bucket, gcs_storage_path + file_destination_name)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iCZPSdktEiV1"
      },
      "outputs": [],
      "source": [
        "# Output the full JSON\n",
        "for city_index in range(0, 4):\n",
        "  html_filename = \"gs://\" + gcs_storage_bucket + \"/\" + gcs_storage_path + formatted_date + \"/\" + str(marketing_gen_ai_insight_id + city_index) + \"-\" + city_names[city_index].replace(\" \",\"-\") + \".html\"\n",
        "\n",
        "  json_payload = {\n",
        "      \"llm_human_image_prompt\" : llm_human_image_prompt,\n",
        "      \"llm_human_image_json\" : llm_human_image_json,\n",
        "      \"llm_generated_image_prompt\" : llm_generated_image_prompt,\n",
        "      \"llm_generated_image_json\" : llm_generated_image_json,\n",
        "      \"llm_validated_image_prompt\" : llm_validated_image_prompt,\n",
        "      \"llm_validated_image_json\" : llm_validated_image_json,\n",
        "      \"llm_taste_test_image_prompt\" : llm_taste_test_image_prompt,\n",
        "      \"llm_taste_test_image_json\" : llm_taste_test_image_json,\n",
        "      \"llm_marketing_prompt\" : llm_marketing_prompt[city_index],\n",
        "      \"llm_marketing_prompt_text\" : llm_marketing_prompt_text[city_index],\n",
        "      \"llm_marketing_prompt_html\" : llm_marketing_prompt_html[city_index],\n",
        "      \"llm_marketing_prompt_html_filename\" : html_filename,\n",
        "  }\n",
        "\n",
        "  json_filename = str(marketing_gen_ai_insight_id + city_index) + \"-\" + city_names[city_index].replace(\" \",\"-\") + \".json\"\n",
        "  with open(json_filename, \"w\") as f:\n",
        "    f.write(json.dumps(json_payload, indent=3))\n",
        "\n",
        "  copy_file_to_gcs(json_filename,gcs_storage_bucket, gcs_storage_path + formatted_date + \"/\" +json_filename)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1PXOO3MjVX1z"
      },
      "outputs": [],
      "source": [
        "for city_index in range(0, 4):\n",
        "  print(f\"Processing city: {city_names[city_index]}\")\n",
        "\n",
        "  json_filename = \"gs://\" + gcs_storage_bucket + \"/\" + \\\n",
        "    gcs_storage_path + formatted_date + \"/\" +str(marketing_gen_ai_insight_id) + '-' + str(city_ids[city_index]) + \"-\" + city_names[city_index].replace(\" \",\"-\") + \".json\"\n",
        "\n",
        "  html_filename = \"gs://\" + gcs_storage_bucket + \"/\" + \\\n",
        "    gcs_storage_path + formatted_date + \"/\" +str(marketing_gen_ai_insight_id) + '-' + str(city_ids[city_index]) + \"-\" + city_names[city_index].replace(\" \",\"-\") + \".html\"\n",
        "\n",
        "  subject = \"\"\n",
        "  for item in llm_marketing_campaign:\n",
        "    if item[\"city\"] == city_names[city_index]:\n",
        "      subject = item[\"subject\"]\n",
        "      break\n",
        "\n",
        "  picture_description_string = picture_description.replace(\"'\",\"\\'\")\n",
        "  subject_string = subject.replace(\"'\",\"\\\\'\")\n",
        "\n",
        "  sql=f\"\"\"INSERT INTO `${project_id}.${bigquery_data_beans_curated_dataset}.marketing_gen_ai_insight`\n",
        "                      (marketing_gen_ai_insight_id, marketing_gen_ai_insight_type, insight_datetime,\n",
        "                      applies_to_entity_type, applies_to_entity_id, applies_to_entity_name,\n",
        "                      picture_description, json_filename, html_filename, subject)\n",
        "              VALUES ({marketing_gen_ai_insight_id}, 'MARKETING-EMAIL', CURRENT_TIMESTAMP(),\n",
        "                      'CITY', {city_ids[city_index]}, '{city_names[city_index]}',\n",
        "                      '{picture_description_string}', '{json_filename}', '{html_filename}', '{subject_string}')\"\"\"\n",
        "\n",
        "  print(sql)\n",
        "  RunQuery(sql)\n",
        "  marketing_gen_ai_insight_id = marketing_gen_ai_insight_id + 1\n"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "k6eIqerFOzyj",
        "8zy0eEJmHxRZ",
        "YtZuFgjbOjso",
        "xUolPsMFOjpZ",
        "E5CFSdK3HxYm",
        "-L93udtrH1Oz",
        "rVCY93IyXPoO"
      ],
      "name": "BigQuery table",
      "private_outputs": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}