{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "rRZ7j28KcY5ZyKWv7dQnxjxF", "metadata": { "id": "rRZ7j28KcY5ZyKWv7dQnxjxF", "tags": [] }, "outputs": [], "source": [ "# Copyright 2025 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", "id": "Z_Qroe-RvDQQ", "metadata": { "id": "Z_Qroe-RvDQQ" }, "source": [ "# Customer Segmentation" ] }, { "cell_type": "markdown", "id": "76c24018318e", "metadata": {}, "source": [ "## Overview" ] }, { "cell_type": "markdown", "id": "PUDZ25xLvDpS", "metadata": { "id": "PUDZ25xLvDpS" }, "source": [ "**The Business Challenge:** Marketing teams often need to optimize budget allocation to maximize return on investment (ROI). A one-size-fits-all marketing strategy is inefficient, leading to wasted ad spend and low engagement, as the messaging is not relevant to all recipients. The business problem is to develop a systematic, data-driven method for partitioning a customer base into distinct groups based on their behavior, enabling more effective and personalized marketing campaigns.\n", "\n", "While basic segmentation using simple demographic data is straightforward, it often fails to capture the more nuanced differences in customer purchasing patterns. The analytical challenge is to move beyond these simple heuristics and identify meaningful segments based on complex behavioral data, such as purchase frequency, monetary value, and product category preferences. The goal is to produce segments that are not only statistically distinct but also interpretable and actionable for the marketing team.\n" ] }, { "cell_type": "markdown", "id": "B9huAQgmvUjL", "metadata": { "id": "B9huAQgmvUjL" }, "source": [ "**The Data Science Approach:** In this use case, we will combine unsupervised machine learning with generative AI to create and characterize customer segments. First, we will apply a k-means clustering algorithm directly within BigQuery ML to efficiently partition the entire customer dataset based on purchasing behavior within BigQuery.\n", "\n", "Clustering effectively groups customers and generates a cluster ID. The second part of our approach is to automate the interpretation of these segments to provide business context. We will use a generative AI function to analyze the behavioral data of customers within each cluster and programmatically generate qualitative descriptions, including concise segment name, segment summary and tailored marketing suggestions for each segment." ] }, { "cell_type": "markdown", "id": "771239ce343a", "metadata": {}, "source": [ "## Setup" ] }, { "cell_type": "markdown", "id": "8b082ef2f84b", "metadata": {}, "source": [ "This section sets up the necessary environment and configurations for running the notebook. It includes defining project and dataset IDs and providing instructions for BigQuery connections." ] }, { "cell_type": "markdown", "id": "423583cd", "metadata": {}, "source": [ "Installs the `bigquery-magics` extension, which allows running BigQuery SQL queries directly within Jupyter notebooks." ] }, { "cell_type": "code", "execution_count": null, "id": "4d8ca68b", "metadata": {}, "outputs": [], "source": [ "%pip install bigquery-magics -q" ] }, { "cell_type": "markdown", "id": "2151333d", "metadata": {}, "source": [ "Loads the `bigquery_magics` extension, enabling the use of `%%bigquery --project $PROJECT_ID` cell magic for executing SQL." ] }, { "cell_type": "code", "execution_count": null, "id": "a382ebbc", "metadata": {}, "outputs": [], "source": [ "%load_ext bigquery_magics" ] }, { "cell_type": "markdown", "id": "47cc86ef", "metadata": {}, "source": [ "### Configure Google Cloud Project and Dataset\n", "\n", "Defines the Google Cloud project ID, dataset ID, and geographic location to be used for BigQuery operations. **Remember to replace `PROJECT_ID` and `DATASET_ID` with your actual values.**" ] }, { "cell_type": "code", "execution_count": null, "id": "6723c732", "metadata": {}, "outputs": [], "source": [ "PROJECT_ID = \"PROJECT_ID\"\n", "DATASET_ID = \"DATASET_ID\"\n", "LOCATION = \"US\"" ] }, { "cell_type": "markdown", "id": "b3e9444bb252", "metadata": {}, "source": [ "## Data Preparation" ] }, { "cell_type": "markdown", "id": "06394e1e0a29", "metadata": {}, "source": [ "### Preview Raw Customer Order Data\n", "\n", "This cell queries a sample of the raw customer order data from the `thelook_ecommerce` public dataset to understand its structure and content before further processing." ] }, { "cell_type": "code", "execution_count": null, "id": "YAh27zfewa03", "metadata": { "id": "YAh27zfewa03" }, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "fe4eed5349eb45debda2aff921fd663d", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Query is running: 0%| |" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "3c33dd452fae4ed68315cdcfedc9eeb5", "version_major": 2, "version_minor": 0 }, "text/plain": [ "Downloading: 0%| |" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.microsoft.datawrangler.viewer.v0+json": { "columns": [ { "name": "index", "rawType": "int64", "type": "integer" }, { "name": "order_id", "rawType": "Int64", "type": "integer" }, { "name": "user_id", "rawType": "Int64", "type": "integer" }, { "name": "status", "rawType": "object", "type": "string" }, { "name": "gender", "rawType": "object", "type": "string" }, { "name": "created_at", "rawType": "datetime64[us, UTC]", "type": "unknown" }, { "name": "returned_at", "rawType": "datetime64[us, UTC]", "type": "unknown" }, { "name": "shipped_at", "rawType": "datetime64[us, UTC]", "type": "unknown" }, { "name": "delivered_at", "rawType": "datetime64[us, UTC]", "type": "unknown" }, { "name": "num_of_item", "rawType": "Int64", "type": "integer" } ], "ref": "4878aeef-734f-47a5-ab97-a928d10b3f76", "rows": [ [ "0", "15", "10", "Cancelled", "F", "2025-07-19 06:06:00+00:00", null, null, null, "1" ], [ "1", "18", "14", "Cancelled", "F", "2024-07-28 03:35:00+00:00", null, null, null, "3" ], [ "2", "39", "32", "Cancelled", "F", "2021-05-19 16:28:00+00:00", null, null, null, "1" ], [ "3", "47", "37", "Cancelled", "F", "2023-03-02 01:10:00+00:00", null, null, null, "1" ], [ "4", "68", "54", "Cancelled", "F", "2023-11-28 10:53:00+00:00", null, null, null, "1" ], [ "5", "71", "56", "Cancelled", "F", "2025-07-02 14:05:00+00:00", null, null, null, "1" ], [ "6", "73", "59", "Cancelled", "F", "2023-09-05 14:53:00+00:00", null, null, null, "1" ], [ "7", "76", "63", "Cancelled", "F", "2023-02-07 02:37:00+00:00", null, null, null, "1" ], [ "8", "84", "70", "Cancelled", "F", "2021-10-08 05:27:00+00:00", null, null, null, "1" ], [ "9", "85", "71", "Cancelled", "F", "2024-08-04 13:54:00+00:00", null, null, null, "1" ], [ "10", "87", "72", "Cancelled", "F", "2022-08-12 06:45:00+00:00", null, null, null, "1" ], [ "11", "88", "72", "Cancelled", "F", "2025-01-19 06:45:00+00:00", null, null, null, "2" ], [ "12", "113", "96", "Cancelled", "F", "2023-05-27 16:56:00+00:00", null, null, null, "1" ], [ "13", "140", "117", "Cancelled", "F", "2022-09-10 18:20:00+00:00", null, null, null, "2" ], [ "14", "141", "117", "Cancelled", "F", "2023-12-11 18:20:00+00:00", null, null, null, "3" ], [ "15", "144", "120", "Cancelled", "F", "2024-01-22 18:34:00+00:00", null, null, null, "1" ], [ "16", "148", "123", "Cancelled", "F", "2024-03-21 06:48:00+00:00", null, null, null, "1" ], [ "17", "149", "123", "Cancelled", "F", "2025-03-29 06:48:00+00:00", null, null, null, "1" ], [ "18", "167", "140", "Cancelled", "F", "2024-01-31 12:27:00+00:00", null, null, null, "1" ], [ "19", "174", "143", "Cancelled", "F", "2023-04-10 14:08:00+00:00", null, null, null, "1" ], [ "20", "192", "155", "Cancelled", "F", "2019-12-25 04:16:00+00:00", null, null, null, "1" ], [ "21", "207", "166", "Cancelled", "F", "2024-05-31 01:03:00+00:00", null, null, null, "2" ], [ "22", "212", "172", "Cancelled", "F", "2025-07-18 04:15:00+00:00", null, null, null, "1" ], [ "23", "227", "181", "Cancelled", "F", "2025-08-21 17:49:00+00:00", null, null, null, "1" ], [ "24", "238", "186", "Cancelled", "F", "2024-09-10 13:05:00+00:00", null, null, null, "4" ], [ "25", "257", "198", "Cancelled", "F", "2024-01-02 07:38:00+00:00", null, null, null, "1" ], [ "26", "299", "233", "Cancelled", "F", "2024-05-04 03:32:00+00:00", null, null, null, "1" ], [ "27", "301", "234", "Cancelled", "F", "2024-04-11 15:14:00+00:00", null, null, null, "4" ], [ "28", "317", "242", "Cancelled", "F", "2024-07-03 04:35:00+00:00", null, null, null, "1" ], [ "29", "318", "243", "Cancelled", "F", "2025-09-03 02:52:42.726372+00:00", null, null, null, "4" ], [ "30", "337", "256", "Cancelled", "F", "2024-04-30 17:13:00+00:00", null, null, null, "1" ], [ "31", "364", "276", "Cancelled", "F", "2020-05-28 15:13:00+00:00", null, null, null, "2" ], [ "32", "373", "281", "Cancelled", "F", "2024-09-08 13:08:00+00:00", null, null, null, "1" ], [ "33", "374", "283", "Cancelled", "F", "2020-06-15 03:21:00+00:00", null, null, null, "2" ], [ "34", "381", "289", "Cancelled", "F", "2025-09-03 07:34:43.685205+00:00", null, null, null, "4" ], [ "35", "391", "299", "Cancelled", "F", "2025-05-16 17:35:00+00:00", null, null, null, "1" ], [ "36", "406", "314", "Cancelled", "F", "2021-12-21 02:49:00+00:00", null, null, null, "2" ], [ "37", "411", "319", "Cancelled", "F", "2025-05-16 11:53:00+00:00", null, null, null, "2" ], [ "38", "414", "321", "Cancelled", "F", "2023-11-10 09:34:00+00:00", null, null, null, "2" ], [ "39", "418", "323", "Cancelled", "F", "2023-11-07 15:24:00+00:00", null, null, null, "1" ], [ "40", "420", "326", "Cancelled", "F", "2022-02-05 04:17:00+00:00", null, null, null, "2" ], [ "41", "454", "355", "Cancelled", "F", "2023-07-02 15:06:00+00:00", null, null, null, "1" ], [ "42", "456", "358", "Cancelled", "F", "2024-06-06 11:45:00+00:00", null, null, null, "1" ], [ "43", "483", "386", "Cancelled", "F", "2023-08-20 00:59:00+00:00", null, null, null, "3" ], [ "44", "486", "389", "Cancelled", "F", "2025-04-24 08:00:00+00:00", null, null, null, "1" ], [ "45", "502", "398", "Cancelled", "F", "2024-12-17 02:46:00+00:00", null, null, null, "1" ], [ "46", "510", "402", "Cancelled", "F", "2023-02-21 18:49:00+00:00", null, null, null, "1" ], [ "47", "511", "403", "Cancelled", "F", "2024-08-31 00:25:00+00:00", null, null, null, "1" ], [ "48", "516", "406", "Cancelled", "F", "2025-08-15 03:47:00+00:00", null, null, null, "1" ], [ "49", "526", "417", "Cancelled", "F", "2024-02-06 05:05:00+00:00", null, null, null, "1" ] ], "shape": { "columns": 9, "rows": 1000 } }, "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_iduser_idstatusgendercreated_atreturned_atshipped_atdelivered_atnum_of_item
01510CancelledF2025-07-19 06:06:00+00:00NaTNaTNaT1
11814CancelledF2024-07-28 03:35:00+00:00NaTNaTNaT3
23932CancelledF2021-05-19 16:28:00+00:00NaTNaTNaT1
34737CancelledF2023-03-02 01:10:00+00:00NaTNaTNaT1
46854CancelledF2023-11-28 10:53:00+00:00NaTNaTNaT1
..............................
9951281610141CancelledF2024-10-01 11:40:00+00:00NaTNaTNaT1
9961283310152CancelledF2025-09-03 01:47:50.414919+00:00NaTNaTNaT1
9971286910183CancelledF2025-06-29 15:24:00+00:00NaTNaTNaT1
9981287510185CancelledF2024-06-17 17:06:00+00:00NaTNaTNaT1
9991287710186CancelledF2023-05-09 18:57:00+00:00NaTNaTNaT1
\n", "

1000 rows \u00d7 9 columns

\n", "
" ], "text/plain": [ " order_id user_id status gender created_at \\\n", "0 15 10 Cancelled F 2025-07-19 06:06:00+00:00 \n", "1 18 14 Cancelled F 2024-07-28 03:35:00+00:00 \n", "2 39 32 Cancelled F 2021-05-19 16:28:00+00:00 \n", "3 47 37 Cancelled F 2023-03-02 01:10:00+00:00 \n", "4 68 54 Cancelled F 2023-11-28 10:53:00+00:00 \n", ".. ... ... ... ... ... \n", "995 12816 10141 Cancelled F 2024-10-01 11:40:00+00:00 \n", "996 12833 10152 Cancelled F 2025-09-03 01:47:50.414919+00:00 \n", "997 12869 10183 Cancelled F 2025-06-29 15:24:00+00:00 \n", "998 12875 10185 Cancelled F 2024-06-17 17:06:00+00:00 \n", "999 12877 10186 Cancelled F 2023-05-09 18:57:00+00:00 \n", "\n", " returned_at shipped_at delivered_at num_of_item \n", "0 NaT NaT NaT 1 \n", "1 NaT NaT NaT 3 \n", "2 NaT NaT NaT 1 \n", "3 NaT NaT NaT 1 \n", "4 NaT NaT NaT 1 \n", ".. ... ... ... ... \n", "995 NaT NaT NaT 1 \n", "996 NaT NaT NaT 1 \n", "997 NaT NaT NaT 1 \n", "998 NaT NaT NaT 1 \n", "999 NaT NaT NaT 1 \n", "\n", "[1000 rows x 9 columns]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%bigquery --project $PROJECT_ID --project $PROJECT_ID\n", "SELECT * FROM `bigquery-public-data.thelook_ecommerce.orders` LIMIT 1000;" ] }, { "cell_type": "markdown", "id": "d6350d75ae25", "metadata": {}, "source": [ "### Create Customer Features Table\n", "\n", "This code aggregates raw order data to create a `customer_date` table, which includes `total_spend`, `number_of_orders`, and `last_purchase_date` for each customer. This table will be used for customer segmentation." ] }, { "cell_type": "markdown", "id": "3c919478", "metadata": {}, "source": [ "### Create BigQuery Dataset\n", "\n", "Defines a BigQuery SQL query to create a new dataset if it doesn't already exist. This dataset will store all tables created during this analysis." ] }, { "cell_type": "code", "execution_count": null, "id": "49c190fc", "metadata": {}, "outputs": [], "source": [ "#@title Create BigQuery dataset if not exists\n", "\n", "query = f\"\"\"\n", "CREATE SCHEMA IF NOT EXISTS `{PROJECT_ID}.{DATASET_ID}`\n", " OPTIONS (\n", " location = '{LOCATION}');\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "ebb9ebab", "metadata": {}, "source": [ "Executes the SQL query to create the BigQuery dataset." ] }, { "cell_type": "code", "execution_count": null, "id": "c6c41c9f", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "8ee095bc", "metadata": {}, "source": [ "### Define Customer Features Table Creation Query\n", "\n", "Defines a BigQuery SQL query to create or replace the `customer_date` table. This table aggregates customer information such as `total_spend`, `number_of_orders`, and `last_purchase_date` for each customer from the public e-commerce dataset." ] }, { "cell_type": "code", "execution_count": null, "id": "olb6Rz0yzsU7", "metadata": { "id": "olb6Rz0yzsU7" }, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE OR REPLACE TABLE {PROJECT_ID}.{DATASET_ID}.customer_date AS(\n", " SELECT\n", " users.id,\n", " SUM(order_items.sale_price) AS total_spend,\n", " COUNT(DISTINCT orders.order_id) AS number_of_orders,\n", " MAX(orders.created_at) AS last_purchase_date\n", "FROM\n", " `bigquery-public-data.thelook_ecommerce.users` AS users\n", "JOIN\n", " `bigquery-public-data.thelook_ecommerce.orders` AS orders\n", "ON\n", " users.id = orders.user_id\n", "JOIN\n", " `bigquery-public-data.thelook_ecommerce.order_items` AS order_items\n", "ON\n", " orders.order_id = order_items.order_id\n", "GROUP BY\n", " users.id );\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "3ff0dcb5", "metadata": {}, "source": [ "Executes the SQL query to create the `customer_date` table." ] }, { "cell_type": "code", "execution_count": null, "id": "d70021c5", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "31185f478a87", "metadata": {}, "source": [ "### Preview Prepared Customer Data\n", "\n", "Displays a sample of the newly created `customer_date` table, confirming the data preparation step was successful and the features are correctly structured." ] }, { "cell_type": "markdown", "id": "a49761df", "metadata": {}, "source": [ "### Define Query to Preview Prepared Data\n", "\n", "Defines a BigQuery SQL query to select a sample of data from the `customer_date` table." ] }, { "cell_type": "code", "execution_count": null, "id": "bAj6eGAmz42u", "metadata": { "id": "bAj6eGAmz42u" }, "outputs": [], "source": [ "query = f\"\"\"\n", "SELECT * FROM `{PROJECT_ID}.{DATASET_ID}.customer_date` LIMIT 1000;\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "33c01411", "metadata": {}, "source": [ "Executes the SQL query to display the first 1000 rows of the `customer_date` table." ] }, { "cell_type": "code", "execution_count": null, "id": "63705560", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "67246571a815", "metadata": {}, "source": [ "## Customer Segmentation with BigQuery ML" ] }, { "cell_type": "markdown", "id": "f51f49679f22", "metadata": {}, "source": [ "### Train a K-Means Clustering Model\n", "\n", "Utilizes BigQuery ML to train a k-means clustering model. This model will group customers into distinct segments based on their `total_spend`, `number_of_orders`, and `last_purchase_date`." ] }, { "cell_type": "markdown", "id": "567eaeb6", "metadata": {}, "source": [ "### Define K-Means Model Training Query\n", "\n", "Defines a BigQuery ML SQL query to create and train a K-Means clustering model. The model is configured to create 3 clusters and standardize features for better performance." ] }, { "cell_type": "code", "execution_count": null, "id": "wtdKqX6azrL_", "metadata": { "id": "wtdKqX6azrL_" }, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE OR REPLACE MODEL `{PROJECT_ID}.{DATASET_ID}.customer_clusters`\n", "OPTIONS(model_type='kmeans', num_clusters=3, standardize_features=TRUE) AS\n", "SELECT\n", " id,\n", " total_spend,\n", " number_of_orders,\n", " last_purchase_date\n", "FROM `{PROJECT_ID}.{DATASET_ID}.customer_date`;\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "d8ff6eef", "metadata": {}, "source": [ "Executes the SQL query to train the K-Means clustering model." ] }, { "cell_type": "code", "execution_count": null, "id": "e8bd50da", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "270c524b07fe", "metadata": {}, "source": [ "### Assign Customers to Segments\n", "\n", "Applies the trained k-means model using `ML.PREDICT` to assign each customer in the `customer_date` table to a specific cluster, creating a `predictions` table with their assigned `centroid_id`." ] }, { "cell_type": "markdown", "id": "7ba02d44", "metadata": {}, "source": [ "### Define Query to Assign Customers to Clusters\n", "\n", "Defines a BigQuery SQL query to apply the trained K-Means model using `ML.PREDICT` to assign each customer in the `customer_date` table to a specific cluster, creating a `predictions` table with their assigned `centroid_id`." ] }, { "cell_type": "code", "execution_count": null, "id": "8HT4bFwf1SXS", "metadata": { "id": "8HT4bFwf1SXS" }, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE OR REPLACE TABLE `{PROJECT_ID}.{DATASET_ID}.predictions` AS(\n", "SELECT\n", "id,\n", "centroid_id,\n", "total_spend,\n", "number_of_orders,\n", "last_purchase_date\n", "FROM\n", " ML.PREDICT(MODEL `{PROJECT_ID}.{DATASET_ID}.customer_clusters`,\n", " (SELECT * FROM\n", " `{PROJECT_ID}.{DATASET_ID}.customer_date`)\n", "\n", "));\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "3ed4c57c", "metadata": {}, "source": [ "Executes the SQL query to generate and save customer cluster predictions." ] }, { "cell_type": "code", "execution_count": null, "id": "33cd3c38", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "670c5f2425d7", "metadata": {}, "source": [ "### Review Customer Segment Assignments\n", "\n", "Displays a sample of the `predictions` table, showing customers along with their assigned cluster IDs and relevant features." ] }, { "cell_type": "markdown", "id": "d4c4169e", "metadata": {}, "source": [ "### Define Query to Review Segment Assignments\n", "\n", "Defines a BigQuery SQL query to select a sample of data from the `predictions` table." ] }, { "cell_type": "code", "execution_count": null, "id": "N2dmYZkX1uq8", "metadata": { "id": "N2dmYZkX1uq8" }, "outputs": [], "source": [ "query = f\"\"\"\n", "SELECT * FROM `{PROJECT_ID}.{DATASET_ID}.predictions` LIMIT 50;\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "5d46743c", "metadata": {}, "source": [ "Executes the SQL query to display the first 50 rows of the `predictions` table, showing assigned customer segments." ] }, { "cell_type": "code", "execution_count": null, "id": "fb8af8cb", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "d07jgX6evezX", "metadata": { "id": "d07jgX6evezX" }, "source": [ "## Generate Segment Personas with AI\n", "\n", "Use the AI.GENERATE_TABLE() function to analyze the members of each cluster and automatically generate a descriptive persona, a summary of their behavior, and potential marketing strategies for that segment.\n" ] }, { "cell_type": "markdown", "id": "f51efd612440", "metadata": {}, "source": [ "### Create BigQuery ML Remote Model for AI Functions\n", "\n", "Establishes a BigQuery ML remote model that connects to a generative AI endpoint (Gemini-2.5-Flash). This model enables the use of AI functions like `AI.GENERATE_TABLE` within BigQuery for advanced data analysis." ] }, { "cell_type": "markdown", "id": "cfbb8f59", "metadata": {}, "source": [ "### Define Remote Model for Generative AI\n", "\n", "Defines a BigQuery ML SQL query to create a remote model for integrating with generative AI (Gemini-2.5-Flash). This model will be used to generate textual descriptions for customer segments." ] }, { "cell_type": "code", "execution_count": null, "id": "NcC2-4Zc1nbL", "metadata": { "id": "NcC2-4Zc1nbL" }, "outputs": [], "source": [ "# Create a BQML model to use the AI functions\n", "\n", "query = f\"\"\"\n", "CREATE OR REPLACE MODEL`{PROJECT_ID}.{DATASET_ID}.ai_model`\n", "REMOTE WITH CONNECTION DEFAULT\n", "OPTIONS (ENDPOINT = 'gemini-2.5-flash');\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "5c2337a0", "metadata": {}, "source": [ "Executes the SQL query to create the BigQuery ML remote model, connecting it to the specified generative AI endpoint." ] }, { "cell_type": "code", "execution_count": null, "id": "3035591e", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "89b5c3789b96", "metadata": {}, "source": [ "### Generate Segment Profiles and Marketing Strategies\n", "\n", "Leverages the `AI.GENERATE_TABLE` function to analyze the characteristics of each customer segment (cluster) and generate a concise segment name, a summary of their behavior, and tailored marketing suggestions." ] }, { "cell_type": "markdown", "id": "8b0eafac", "metadata": {}, "source": [ "### Define Query to Generate Segment Profiles and Marketing Strategies\n", "\n", "Defines a BigQuery SQL query that uses the `AI.GENERATE_TABLE` function to analyze each customer segment's aggregated metrics (average spend, orders, and recency). It then prompts the generative AI model to create a segment name, summary, and marketing suggestions for each cluster. The output schema is explicitly defined for structured results." ] }, { "cell_type": "code", "execution_count": null, "id": "dnWe-AZ_2EkY", "metadata": { "id": "dnWe-AZ_2EkY" }, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE OR REPLACE TABLE `{PROJECT_ID}.{DATASET_ID}.segment_campaigns` AS(\n", "SELECT\n", " *\n", "FROM\n", " AI.GENERATE_TABLE(\n", " MODEL `{PROJECT_ID}.{DATASET_ID}.ai_model`,\n", " (\n", " SELECT\n", " 'For the customer segment with centroid_id, provide a concise segment_name, a segment_summary (1-2 sentences), and 3 numbered marketing_suggestions in a list based on the provided metrics. For context, these metrics are from an ecommerce store.'\n", " AS prompt\n", " FROM (\n", " SELECT\n", " CAST(centroid_id AS STRING) AS centroid_id,\n", " AVG(total_spend) AS avg_total_spend,\n", " AVG(number_of_orders) AS avg_number_of_orders,\n", " AVG(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_purchase_date, DAY)) AS avg_time_since_last_purchase_days\n", " FROM\n", " `{PROJECT_ID}.{DATASET_ID}.predictions`\n", " GROUP BY\n", " centroid_id\n", " )\n", " ),\n", " STRUCT (\"customer_segment STRING, segment_name STRING, segment_summary STRING, marketing_suggestions ARRAY\" AS output_schema ))\n", ");\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "5378894e", "metadata": {}, "source": [ "Executes the SQL query to generate the customer segment profiles and marketing strategies using generative AI and stores them in the `segment_campaigns` table." ] }, { "cell_type": "code", "execution_count": null, "id": "d473912b", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "d0c8976c75df", "metadata": {}, "source": [ "### Review Generated Segment Campaigns\n", "\n", "Displays the AI-generated segment personas, including their names, summaries, and proposed marketing strategies, providing actionable insights for targeted marketing." ] }, { "cell_type": "markdown", "id": "cb4a3b33", "metadata": {}, "source": [ "### Define Query to Review Generated Segment Campaigns\n", "\n", "Defines a BigQuery SQL query to retrieve all generated segment campaigns." ] }, { "cell_type": "code", "execution_count": null, "id": "PVdgdKOM6aqf", "metadata": { "id": "PVdgdKOM6aqf" }, "outputs": [], "source": [ "query = f\"\"\"\n", "SELECT * FROM `{PROJECT_ID}.{DATASET_ID}.segment_campaigns`\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "78279380", "metadata": {}, "source": [ "Executes the SQL query to display the final AI-generated customer segment personas and their corresponding marketing campaign suggestions." ] }, { "cell_type": "code", "execution_count": null, "id": "173eb802", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] } ], "metadata": { "accelerator": "GPU", "colab": { "gpuType": "L4", "name": "identifying_customer_segments", "provenance": [] }, "kernelspec": { "display_name": ".venv", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.6" } }, "nbformat": 4, "nbformat_minor": 5 }