colab-enterprise/rideshare_llm_step_05_customer

{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "HhgOK3uTr6M-" }, "source": [ "# Create Customer Summary (Preferences)\n", "- This notebook take about 5 to 10 minutes to execute\n", "- We will create 2 summaries\n", " - The customer preferences based upon what themes they mention in their reviews\n", " - A summary of all their reviews for a consolidated overall review " ] }, { "cell_type": "markdown", "metadata": { "id": "bP6mkKYNRx4s" }, "source": [ "## Create Summary Prompt and run through LLM" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "eGtjlAFZr3pO" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "-- View the attributes per customer\n", "\n", "SELECT *\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer_attribute`\n", "ORDER BY customer_id,\n", " customer_attribute_grouping,\n", " extracted_customer_attribute,\n", " rank_order\n", "LIMIT 100;" ] }, { "cell_type": "markdown", "metadata": { "id": "mHsiJiB5yHYN" }, "source": [ "## Aggregate the data for an LLM prompt" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xlwTohJKyGsS" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "SELECT customer_id,\n", " STRING_AGG(extracted_customer_attribute,', ') AS customer_attribute_agg\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer_attribute`\n", "GROUP BY customer_id\n", "LIMIT 20;" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "2ImyXelWWtFi" }, "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` AS customer\n", " SET customer_attribute_llm_summary_prompt = NULL,\n", " llm_summary_customer_attribute_json = NULL,\n", " llm_summary_customer_attribute = NULL\n", " WHERE TRUE;\n", "*/" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "sGmYo5mO1FIA" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "-- Create the LLM prompt\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " SET customer_attribute_llm_summary_prompt = CONCAT(\n", "'Write a 100 to 600 word summary for the following customer preferences.\\n',\n", "'1. The customer\\'s name is ', customer.customer_name ,'.\\n',\n", "'2. Write the summary in present tense.\\n',\n", "'3. Write the summary from the customers prespective.\\n',\n", "'4. Do not repeat the same subject in the summary.\\n',\n", "'5. Write 3 to 6 sentences.\\n',\n", "customer_attribute_agg)\n", " FROM (SELECT customer_id,\n", " STRING_AGG(\n", " CONCAT('Preference: ',\n", " extracted_customer_attribute,\n", " '.\\n')\n", " ,'') AS customer_attribute_agg\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer_attribute`\n", " GROUP BY customer_id) AS customer_attribute\n", "WHERE customer.customer_id = customer_attribute.customer_id;\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "rfyk4FRh12do" }, "outputs": [], "source": [ "%%bigquery\n", "SELECT customer_attribute_llm_summary_prompt\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " WHERE customer_attribute_llm_summary_prompt IS NOT NULL\n", " LIMIT 10;" ] }, { "cell_type": "markdown", "metadata": { "id": "UrQsqrtj2x69" }, "source": [ "## Run the LLM to generate a customer summary" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xEnr3nBp3y0Z" }, "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": "B8uFxM_220li" }, "outputs": [], "source": [ "# Process in batches\n", "batch_size = 100\n", "\n", "# Set the parameters for more creative\n", "llm_temperature = 1\n", "llm_max_output_tokens = 1024\n", "llm_top_p = 1\n", "llm_top_k = 40\n", "\n", "update_sql=\"\"\"\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " SET llm_summary_customer_attribute_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_attribute_llm_summary_prompt AS prompt\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n", " WHERE (llm_summary_customer_attribute_json IS NULL\n", " OR\n", " JSON_VALUE(llm_summary_customer_attribute_json, '$.candidates[0].content.parts[0].text') IS NULL\n", " )\n", " AND include_in_llm_processing = TRUE\n", " AND customer_attribute_llm_summary_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": "1gFA-Koc3nLx" }, "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_summary_customer_attribute_json IS NULL\n", " OR\n", " JSON_VALUE(llm_summary_customer_attribute_json, '$.candidates[0].content.parts[0].text') IS NULL\n", " )\n", " AND include_in_llm_processing = TRUE\n", " AND customer_attribute_llm_summary_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": "MU4GMYdH8cVx" }, "source": [ "## Parse the LLM JSON results" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "nFM-JNqg39up" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " SET llm_summary_customer_attribute = JSON_VALUE(llm_summary_customer_attribute_json, '$.candidates[0].content.parts[0].text')\n", " WHERE llm_summary_customer_attribute_json IS NOT NULL\n", " AND llm_summary_customer_attribute IS NULL;" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "_-YX3rPU39g5" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "SELECT customer_id, customer_attribute_llm_summary_prompt, llm_summary_customer_attribute_json, llm_summary_customer_attribute\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n", " WHERE llm_summary_customer_attribute_json IS NOT NULL\n", " AND llm_summary_customer_attribute IS NOT NULL\n", "LIMIT 20;" ] }, { "cell_type": "markdown", "metadata": { "id": "DwtNRW36MBOH" }, "source": [ "# Create Customer Summary (Summary of all Reviews)\n", "\n", "We will create 2 summaries\n", "1. The customer preferences based upon what themes they mention in their reviews\n", "2. A summary of all their reviews, so we understand their mindset\n", "\n", "Customer Summary:\n", " - Summarize all the customer reviews" ] }, { "cell_type": "markdown", "metadata": { "id": "OqvD9-bWSI4q" }, "source": [ "## Create Summary Prompt and run through LLM" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "0LQndkktXdDb" }, "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_review_summary_llm_summary_prompt = NULL,\n", " llm_summary_customer_review_summary_json = NULL,\n", " llm_summary_customer_review_summary = NULL\n", " WHERE TRUE;\n", "*/" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "udlrYDJVPQHk" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "-- Create the LLM prompt\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " SET customer_review_summary_llm_summary_prompt =\n", " CONCAT('Write a 100 to 600 word summary for the following customer reviews.\\n',\n", " '1. The reviews are written by ', customer.customer_name, '.\\n',\n", " '2. Write the summary in present tense.\\n',\n", " '3. Do not repeat the same subject in the summary.\\n',\n", " '4. The reviews are for different drivers.\\n',\n", " '5. The reviews are a single rideshare company.\\n',\n", " '6. The drivers all work for the rideshare company.\\n',\n", " '7. Write 3 to 6 sentences.\\n',\n", " customer_review_agg)\n", " FROM (SELECT customer_id,\n", " STRING_AGG(CONCAT('Review: ',customer_review_text,'\\n'),'') AS customer_review_agg\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer_review`\n", " GROUP BY customer_id) AS customer_review\n", "WHERE customer.customer_id = customer_review.customer_id;\n" ] }, { "cell_type": "markdown", "metadata": { "id": "2xuQNGmSSP0c" }, "source": [ "## Run the LLM to generate a customer summary" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "5tVSlz_3MIfK" }, "outputs": [], "source": [ "# Process in batches\n", "batch_size = 100\n", "\n", "# Set the parameters for a more creative response\n", "llm_temperature = 1\n", "llm_max_output_tokens = 1024\n", "llm_top_p = 1\n", "llm_top_k = 40\n", "\n", "update_sql=\"\"\"\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` AS customer\n", " SET llm_summary_customer_review_summary_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_review_summary_llm_summary_prompt AS prompt\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n", " WHERE include_in_llm_processing = TRUE\n", " AND customer_review_summary_llm_summary_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": "7QNPY6HmSF6F" }, "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_summary_customer_review_summary_json IS NULL\n", " OR\n", " JSON_VALUE(llm_summary_customer_review_summary_json, '$.candidates[0].content.parts[0].text') IS NULL\n", " )\n", " AND include_in_llm_processing = TRUE\n", " AND customer_review_summary_llm_summary_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", " 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": "dEQtCtlpSW1C" }, "source": [ "## Parse the LLM JSON results" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "rvfT9ftgSX8K" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "UPDATE `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer` customer\n", " SET llm_summary_customer_review_summary = JSON_VALUE(llm_summary_customer_review_summary_json, '$.candidates[0].content.parts[0].text')\n", " WHERE llm_summary_customer_review_summary_json IS NOT NULL\n", " AND llm_summary_customer_review_summary IS NULL;\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ApfssElgSYOy" }, "outputs": [], "source": [ "%%bigquery\n", "\n", "SELECT customer_id, customer_review_summary_llm_summary_prompt, llm_summary_customer_review_summary_json, llm_summary_customer_review_summary\n", " FROM `${project_id}.${bigquery_rideshare_llm_enriched_dataset}.customer`\n", " WHERE llm_summary_customer_review_summary_json IS NOT NULL\n", " AND llm_summary_customer_review_summary 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 }

colab-enterprise/rideshare_llm_step_05_customer_summary.ipynb (560 lines of code) (raw):