colab-enterprise/rideshare_llm_step_07_customer_quantitative_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "U8ddz--L2sjh"
   },
   "source": [
    "# Create Customer Summary (Quantitative Analysis)\n",
    "- This notebook take about 5 to 10 minutes to execute\n",
    "- Extract quantitative data from the Trips data\n",
    "  - Does the customer only use the service certain days of the week?\n",
    "  - What time of day does the customer use the service (rush hour)?\n",
    "- Create a LLM summary of the extracted data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bJKcUh_-4VIP"
   },
   "source": [
    "## Create Summary Prompt and run through LLM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5UTx68uxanLl"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "-- OPTIONAL: Reset all the fields to null\n",
    "-- If you need to reset you data back to fresh data run the stored procedure: CALL `${project_id}.${bigquery_rideshare_llm_curated_dataset}.sp_reset_demo`();\n",
    "\n",
    "/*\n",
    "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n",
    "   SET customer_quantitative_analysis_prompt     = NULL,\n",
    "       llm_customer_quantitative_analysis_json   = NULL,\n",
    "       llm_customer_quantitative_analysis        = NULL\n",
    " WHERE TRUE;\n",
    "*/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Rymol4B-4l0J"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "-- Create the LLM prompt\n",
    "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n",
    "   SET customer_quantitative_analysis_prompt =\n",
    "       CONCAT('Write a 2 to 3 sentence summary of the following attributes of a customer who uses a rideshare services. ',\n",
    "                CASE WHEN day_of_week = 'weekend-customer' THEN CONCAT('- ', customer.customer_name, ' uses the service on weekends.\\n')\n",
    "                     WHEN day_of_week = 'weekday-customer' THEN CONCAT('- ', customer.customer_name, ' uses the service on weekdays.\\n')\n",
    "                     ELSE CONCAT('- ', customer.customer_name ,' uses the rideshare service any day of the week.\\n')\n",
    "                END,\n",
    "\n",
    "                CASE WHEN hour_of_day = 'night-hour-customer' THEN CONCAT('- ',customer.customer_name,' likes to use the service at night.\\n')\n",
    "                     WHEN hour_of_day = 'rush-hour-customer'  THEN CONCAT('- ',customer.customer_name,' likes to use the service during the morning and afternoon rush hours.\\n')\n",
    "                    ELSE CONCAT('- ',customer.customer_name,' uses the rideshare service at any time of the day.\\n')\n",
    "                END\n",
    "             )\n",
    "  FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer_quantitative_analysis` AS customer_quantitative_analysis\n",
    "WHERE customer.customer_id = customer_quantitative_analysis.customer_id\n",
    ";\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "FTZEM81t6mif"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT customer_quantitative_analysis_prompt\n",
    "  FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n",
    " WHERE customer_quantitative_analysis_prompt IS NOT NULL\n",
    " LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ju-Xd8rc6vLs"
   },
   "source": [
    "## Run the LLM to generate a Customer Summary on Quantitative Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BoYRQRoy6yr1"
   },
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "import pandas as pd\n",
    "\n",
    "client = bigquery.Client()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "dOG1zYZ260oF"
   },
   "outputs": [],
   "source": [
    "# Process in batches\n",
    "batch_size = 100\n",
    "\n",
    "# Set the parameters so we are more deterministic and less creative/random responses\n",
    "llm_temperature = .80\n",
    "llm_max_output_tokens = 1024\n",
    "llm_top_p = .70\n",
    "llm_top_k = 25\n",
    "\n",
    "update_sql=\"\"\"\n",
    "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n",
    "  SET llm_customer_quantitative_analysis_json = child.ml_generate_text_result\n",
    "  FROM (SELECT *\n",
    "          FROM ML.GENERATE_TEXT(MODEL`${project_id}.${bigquery_rideshare_llm_enriched_dataset}.gemini_model`,\n",
    "              (SELECT customer_id,\n",
    "                      customer_quantitative_analysis_prompt AS prompt\n",
    "                FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n",
    "                WHERE (llm_customer_quantitative_analysis_json IS NULL\n",
    "                       OR\n",
    "                       JSON_VALUE(llm_customer_quantitative_analysis_json, '$.candidates[0].content.parts[0].text') IS NULL\n",
    "                       )\n",
    "                  AND include_in_llm_processing = TRUE\n",
    "                  AND customer_quantitative_analysis_prompt IS NOT NULL\n",
    "                LIMIT {batch_size}),\n",
    "              STRUCT(\n",
    "                {llm_temperature} AS temperature,\n",
    "                {llm_max_output_tokens} AS max_output_tokens,\n",
    "                {llm_top_p} AS top_p,\n",
    "                {llm_top_k} AS top_k\n",
    "                ))\n",
    "  ) AS child\n",
    "WHERE customer.customer_id = child.customer_id\n",
    "  \"\"\".format(batch_size = batch_size,\n",
    "             llm_temperature = llm_temperature,\n",
    "             llm_max_output_tokens = llm_max_output_tokens,\n",
    "             llm_top_p = llm_top_p,\n",
    "             llm_top_k = llm_top_k)\n",
    "\n",
    "print(\"SQL: {update_sql}\".format(update_sql=update_sql))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qeUw8dqf63cz"
   },
   "outputs": [],
   "source": [
    "# Score while records remain\n",
    "# score in groups of batch_size records (we can do up to 10,000 at a time)\n",
    "import time\n",
    "\n",
    "done = False\n",
    "displayed_first_sql = False\n",
    "original_record_count = 0\n",
    "\n",
    "while done == False:\n",
    "  # Get the count of records to score\n",
    "  sql = \"\"\"\n",
    "        SELECT COUNT(*) AS cnt\n",
    "          FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n",
    "         WHERE (llm_customer_quantitative_analysis_json IS NULL\n",
    "                OR\n",
    "                JSON_VALUE(llm_customer_quantitative_analysis_json, '$.candidates[0].content.parts[0].text') IS NULL\n",
    "                )\n",
    "           AND include_in_llm_processing = TRUE\n",
    "           AND customer_quantitative_analysis_prompt IS NOT NULL;\n",
    "        \"\"\"\n",
    "\n",
    "  df_record_count = client.query(sql).to_dataframe()\n",
    "  cnt = df_record_count['cnt'].head(1).item()\n",
    "  if displayed_first_sql == False:\n",
    "    original_record_count = cnt\n",
    "    displayed_first_sql = True\n",
    "\n",
    "  print(\"Remaining records to process: \", cnt, \" out of\", original_record_count, \" batch_size: \", batch_size)\n",
    "\n",
    "\n",
    "  if cnt == 0:\n",
    "    done = True\n",
    "  else:\n",
    "    # https://github.com/googleapis/python-bigquery/tree/master/samples\n",
    "    job_config = bigquery.QueryJobConfig(priority=bigquery.QueryPriority.INTERACTIVE)\n",
    "    query_job = client.query(update_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 {}\".format(query_job.job_id, query_job.state))\n",
    "\n",
    "    while query_job.state != \"DONE\":\n",
    "      time.sleep(5)\n",
    "      query_job = client.get_job(\n",
    "          query_job.job_id, location=query_job.location\n",
    "          )\n",
    "      print(\"Job {} is currently in state {}\".format(query_job.job_id, query_job.state))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ri7kJ1IX6728"
   },
   "source": [
    "## Parse the LLM JSON results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "a6M1YZ-J6-Kt"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` customer\n",
    "   SET llm_customer_quantitative_analysis = JSON_VALUE(llm_customer_quantitative_analysis_json, '$.candidates[0].content.parts[0].text')\n",
    " WHERE llm_customer_quantitative_analysis_json IS NOT NULL\n",
    "   AND llm_customer_quantitative_analysis IS NULL;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "PwZx_GIR6_bL"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT customer_id, customer_quantitative_analysis_prompt, llm_customer_quantitative_analysis_json, llm_customer_quantitative_analysis\n",
    "  FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n",
    " WHERE llm_customer_quantitative_analysis_json IS NOT NULL\n",
    "   AND llm_customer_quantitative_analysis IS NOT NULL\n",
    "LIMIT 20;\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "BigQuery table",
   "private_outputs": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}