{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "FEzbCDDc6eLi", "metadata": { "id": "FEzbCDDc6eLi" }, "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." ] }, { "cell_type": "markdown", "id": "J2DBfi_I-MYX", "metadata": { "id": "J2DBfi_I-MYX" }, "source": [ "# Exploratory Data Analysis\n", "\n", "## 📋 Overview\n", "\n", "This notebook demonstrates a comprehensive data science approach to investigating declining product sales using Google Cloud's AI and analytics capabilities. Through this case study, you'll learn how to combine traditional data analysis with modern AI-powered insights to uncover actionable business intelligence.\n", "\n", "## 🏢 Business Context\n", "\n", "**Global Gadgets** is an electronics retailer operating through multiple channels:\n", "- Physical retail stores\n", "- E-commerce platform \n", "- House brand products\n", "\n", "### The Challenge\n", "\n", "Global Gadgets recently launched the **Quantum AI Speaker 2**, the successor to their successful Smart Speaker V1. However, initial performance data reveals a concerning trend:\n", "\n", "- **V2 sales are significantly underperforming** compared to V1 at the same point in their respective lifecycles\n", "- After an initially promising start that tracked with V1's growth trajectory, **V2's sales growth has dramatically slowed**\n", "- The company needs to **identify root causes** and develop actionable strategies to address the sales decline\n", "\n", "## 🎯 Analysis Objectives\n", "\n", "This investigation aims to:\n", "\n", "1. **Validate the sales shortfall** through data visualization and comparative analysis\n", "2. **Analyze customer sentiment** at scale using AI-powered review scoring\n", "3. **Identify key factors** influencing purchase decisions through machine learning\n", "4. **Provide actionable insights** for product and marketing teams\n", "\n", "## 🛠️ Methodology & Technologies\n", "\n", "This notebook showcases several advanced techniques:\n", "\n", "- **BigQuery Analytics**: Large-scale data processing and SQL-based analysis\n", "- **AI.GENERATE Function**: Automated sentiment analysis of unstructured review data\n", "- **XGBoost Machine Learning**: Feature importance analysis for purchase prediction\n", "- **BigFrames**: Pandas-style data manipulation at BigQuery scale\n", "- **Data Science Agent**: AI-assisted model development and insights\n", "\n", "## 📊 What You'll Learn\n", "\n", "By following this analysis, you'll discover how to:\n", "\n", "- Use AI to score unstructured data (product reviews) at scale\n", "- Build comprehensive datasets by joining multiple data sources\n", "- Apply machine learning to identify the most influential factors in customer behavior\n", "- Translate technical findings into actionable business recommendations\n", "- Leverage Google Cloud's AI capabilities for rapid data science workflows\n", "\n", "---" ] }, { "cell_type": "markdown", "id": "zMm-FoQLw6NN", "metadata": { "id": "zMm-FoQLw6NN" }, "source": [ "## **0.** Setup" ] }, { "cell_type": "code", "execution_count": null, "id": "3b7ebcb4", "metadata": {}, "outputs": [], "source": [ "%pip install bigquery-magics bigframes xgboost google-cloud-bigquery-connection matplotlib seaborn pandas scikit-learn -q" ] }, { "cell_type": "code", "execution_count": null, "id": "df5195ff", "metadata": {}, "outputs": [], "source": [ "%load_ext google.cloud.bigquery" ] }, { "cell_type": "code", "execution_count": null, "id": "8ad89ab2", "metadata": {}, "outputs": [], "source": [ "PROJECT_ID = \"\" # @param {type:\"string\"}\n", "if not PROJECT_ID: \n", " PROJECT_ID = input(\"Enter project id\")\n", "\n", "DATASET_ID = \"\" # @param {type:\"string\"}\n", "if not DATASET_ID: \n", " DATASET_ID = input(\"Enter dataset id\")\n", "\n", "LOCATION = \"US\" # @param {type:\"string\"}\n", "if not LOCATION: \n", " LOCATION = input(\"Enter location\")" ] }, { "cell_type": "markdown", "id": "otL0C3Gt2Euu", "metadata": { "id": "otL0C3Gt2Euu" }, "source": [ "### Create the Dataset and Pull the Demo Data\n", "Run the below script to create the dataset in your project and load the needed tables." ] }, { "cell_type": "code", "execution_count": null, "id": "1eee8a2c", "metadata": {}, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE SCHEMA IF NOT EXISTS `{PROJECT_ID}.{DATASET_ID}`\n", " OPTIONS (\n", " location = '{LOCATION}');\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": null, "id": "a60d98ed", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "code", "execution_count": null, "id": "603c06b0", "metadata": {}, "outputs": [], "source": [ "query = f'''\n", "DECLARE target_dataset_name STRING DEFAULT '{DATASET_ID}';\n", "DECLARE source_bucket_name STRING DEFAULT 'data-analytics-golden-demo';\n", "DECLARE source_base_folder STRING DEFAULT 'demo-data/global-gadgets-fork';\n", "\n", "DECLARE table_list ARRAY DEFAULT [\n", " 'monthly_product_sales',\n", " 'product_reviews',\n", " 'session_to_user_map',\n", " 'sessions',\n", " 'sessions_reviews',\n", " 'user_info'\n", "];\n", "\n", "FOR table_row IN (SELECT t_name FROM UNNEST(table_list) AS t_name)\n", "DO\n", " EXECUTE IMMEDIATE FORMAT(\"\"\"\n", " CREATE OR REPLACE EXTERNAL TABLE `%s.%s.temp_external_table_for_copy`\n", " OPTIONS (\n", " format = 'PARQUET',\n", " uris = ['gs://%s/%s/%s']\n", " );\n", " \"\"\", @@project_id, target_dataset_name, source_bucket_name, source_base_folder, table_row.t_name);\n", "\n", " EXECUTE IMMEDIATE FORMAT(\"\"\"\n", " CREATE OR REPLACE TABLE `%s.%s.%s` AS\n", " SELECT * FROM `%s.%s.temp_external_table_for_copy`;\n", " \"\"\", @@project_id, target_dataset_name, table_row.t_name, @@project_id, target_dataset_name);\n", " \n", " SELECT FORMAT(\"✅ Table created: %s.%s.%s\",\n", " @@project_id, target_dataset_name, table_row.t_name) AS status;\n", "\n", "END FOR;\n", "'''" ] }, { "cell_type": "code", "execution_count": null, "id": "09c704a6", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "Fo2LNzWECUDF", "metadata": { "id": "Fo2LNzWECUDF" }, "source": [ "#### The Dataset" ] }, { "cell_type": "markdown", "id": "TuMb3KULC-hB", "metadata": { "id": "TuMb3KULC-hB" }, "source": [ "\n", "\n", "* **user_info:** This table has general demographic information about the site user such as the age range and gender and the urbanicity of the user’s address (urban, suburban, rural).\n", "* **product_reviews:** Basic review information including review ID, star rating and the review body/text.\n", "* **sessions:** Basic info about the session such as the session ID, date-time, device and, most importantly, whether that session converted to a sale.\n", "* **sessions_reviews:** A mapping of each session ID to a comma-separated list of the review IDs of the reviews that were seen within that session.\n", "* **session_to_user_map:** For each session, a map to the user ID that was in that session.\n", "* **monthly_product_sales:** The sales by month of both products from the time of the launch of Speaker 1.\n", "\n", "Generated Tables: During the demo, you will create additional tables.\n", "* **reviews_feature_sentiment_score:** For each review, a mapping to each of the four product features and a score from -2 to +2 of how negative to positive it is as determined by the AI.GENERATE function.\n", "* **FINAL_ANALYSIS:** The final joined table for analysis. It has the session info, the user info and the negative review seen info.\n", "\n" ] }, { "cell_type": "markdown", "id": "IC4Y2Z9tERkv", "metadata": { "id": "IC4Y2Z9tERkv" }, "source": [ "### Visualizing the sales shortfall with the Data Science Agent" ] }, { "cell_type": "markdown", "id": "GmeyJ77OEXw0", "metadata": { "id": "GmeyJ77OEXw0" }, "source": [ "The first thing we'll want to do is validate that the sales shortfall for V2 of the product is actually true.\n", "\n", "To that end, we'll ask the agent to create a plan and chart the data to compare the sales of V1 at its launch with the sales of V2 at its launch a year later." ] }, { "cell_type": "markdown", "id": "032f333a", "metadata": { "id": "032f333a" }, "source": [ "### Data loading" ] }, { "cell_type": "code", "execution_count": null, "id": "ee3621a0", "metadata": {}, "outputs": [], "source": [ "import bigframes.pandas as bpd\n", "# Note: The project option is not required in all environments.\n", "# On BigQuery Studio, the project ID is automatically detected.\n", "bpd.options.bigquery.project = PROJECT_ID\n", "# Note: The location option is not required.\n", "# It defaults to the location of the first table or query\n", "# passed to read_gbq(). For APIs where a location can't be\n", "# auto-detected, the location defaults to the \"US\" location.\n", "bpd.options.bigquery.location = LOCATION" ] }, { "cell_type": "code", "execution_count": null, "id": "4fd9063c", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 242 }, "executionInfo": { "elapsed": 2729, "status": "ok", "timestamp": 1754944993250, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "4fd9063c", "outputId": "9c658675-798e-416e-8d44-5fff5a8d9e74" }, "outputs": [], "source": [ "df_sales = bpd.read_gbq(f\"{PROJECT_ID}.{DATASET_ID}.monthly_product_sales\")\n", "df_sales.head()" ] }, { "cell_type": "markdown", "id": "75365aa1", "metadata": { "id": "75365aa1" }, "source": [ "### Data preparation\n", "\n", "Prepare the data for plotting by selecting the relevant sales and 'months_since_launch' columns for V1 and V2, and restructuring the data into a suitable format for visualization.\n" ] }, { "cell_type": "markdown", "id": "1c24ebb7", "metadata": {}, "source": [ "The Prompt:\n", "\n", "Use the global_gadgets_fork dataset for all of the following commands. Using the monthly_product_sales table, create a line chart comparing the sales growth of V1 and V2. For the x-axis, use 'Months Since Launch' to align their starting points. Plot the sales for V1 against months_since_launch for V1 and sales for V2 against months_since_launch for V2 on a single chart. Note that there is a “months_since_launch” column for each product. Please set the visualization so that the Y-axis grid lines are whole numbers. And please set the x-axis so that grid lines show every whole number from 0 through 18." ] }, { "cell_type": "code", "execution_count": null, "id": "fb9c2e86", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 34, "status": "ok", "timestamp": 1754945203945, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "fb9c2e86", "outputId": "0d5ccc62-95cc-43d6-ab79-bd2e07d5d641" }, "outputs": [], "source": [ "# Select relevant columns\n", "df_prepared = df_sales[['V1_Sales', 'V1_months_since_launch', 'V2_Sales', 'V2_months_since_launch']].copy()\n", "\n", "# Create DataFrame for V1 sales\n", "df_v1 = df_prepared[['V1_months_since_launch', 'V1_Sales']].copy()\n", "df_v1.rename(columns={'V1_months_since_launch': 'months_since_launch', 'V1_Sales': 'sales'}, inplace=True)\n", "df_v1['product'] = 'V1'\n", "\n", "# Create DataFrame for V2 sales\n", "df_v2 = df_prepared[['V2_months_since_launch', 'V2_Sales']].copy()\n", "df_v2.rename(columns={'V2_months_since_launch': 'months_since_launch', 'V2_Sales': 'sales'}, inplace=True)\n", "df_v2['product'] = 'V2'\n", "\n", "# Concatenate the two DataFrames\n", "df_combined = bpd.concat([df_v1, df_v2], ignore_index=True)\n", "\n", "# Drop rows where 'sales' or 'months_since_launch' are missing or zero\n", "df_combined.replace({0: None}) # Replace 0 with None/NaN for dropping\n", "df_combined.dropna(subset=['sales', 'months_since_launch'])\n", "\n", "# Convert 'months_since_launch' to integer\n", "df_combined['months_since_launch'] = df_combined['months_since_launch'].astype(int)\n", "\n", "print(\"Data prepared successfully. Displaying the first 5 rows of the combined DataFrame:\")\n", "print(df_combined.head())\n", "print(\"\\nInfo of the combined DataFrame:\")\n", "df_combined.info()" ] }, { "cell_type": "markdown", "id": "48956118", "metadata": { "id": "48956118" }, "source": [ "### Data visualization\n", "\n", "Create a line chart comparing the sales of V1 and V2 against 'Months Since Launch', applying the specified x and y axis grid line customizations.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "1cec9ace", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 582 }, "executionInfo": { "elapsed": 517, "status": "ok", "timestamp": 1754945231784, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "1cec9ace", "outputId": "de7e3d0e-550b-42a7-a97a-7f82b5faf4ed" }, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import matplotlib.ticker as mticker\n", "\n", "# Drop any rows where sales might have become NaN after conversion\n", "df_combined.dropna(subset=['sales'])\n", "\n", "# Create the line plot\n", "plt.figure(figsize=(10, 6))\n", "\n", "# Plot V1 sales\n", "df_v1_plot = df_combined[df_combined['product'] == 'V1'].sort_values(by='months_since_launch')\n", "plt.plot(df_v1_plot['months_since_launch'], df_v1_plot['sales'], label='V1 Sales', marker='o')\n", "\n", "# Plot V2 sales\n", "df_v2_plot = df_combined[df_combined['product'] == 'V2'].sort_values(by='months_since_launch')\n", "plt.plot(df_v2_plot['months_since_launch'], df_v2_plot['sales'], label='V2 Sales', marker='o')\n", "\n", "# Customize x-axis\n", "plt.xlabel('Months Since Launch')\n", "plt.xticks(range(0, 19)) # Show whole numbers from 0 to 18\n", "plt.xlim(0, 18) # Set x-axis limits from 0 to 18\n", "\n", "# Customize y-axis\n", "plt.ylabel('Sales')\n", "plt.gca().yaxis.set_major_locator(mticker.MaxNLocator(integer=True)) # Ensure whole number grid lines\n", "plt.grid(axis='y', linestyle='--', alpha=0.7)\n", "\n", "# Add title and legend\n", "plt.title('Sales Comparison of V1 and V2 by Months Since Launch')\n", "plt.legend()\n", "plt.grid(True)\n", "\n", "# Display the plot\n", "plt.show()\n", "\n", "print(\"Line chart comparing V1 and V2 sales created successfully.\")" ] }, { "cell_type": "markdown", "id": "8a6a7160", "metadata": { "id": "8a6a7160" }, "source": [ "### Summary:\n", "\n", "### Data Analysis Key Findings\n", "\n", "* The `global_gadgets_fork.monthly_product_sales` table was successfully loaded into a pandas DataFrame, providing sales data for V1 and V2 products, along with their respective 'months\\_since\\_launch' columns.\n", "* Data preparation involved restructuring the sales data for V1 and V2 into a combined DataFrame (`df_combined`). This DataFrame contains 24 entries, with sales figures and 'months\\_since\\_launch' values for both products in a unified format, after removing any missing or zero values.\n", "* A line chart was successfully generated comparing the sales of V1 and V2. The x-axis accurately represents 'Months Since Launch' from 0 to 18, with grid lines showing every whole number. The y-axis displays sales, with grid lines set to whole numbers for clarity.\n", "\n", "### Insights or Next Steps\n", "\n", "* The generated chart provides a clear visual comparison of the sales growth trajectories for V1 and V2 products over their respective launch periods, allowing for direct comparison of their performance from inception.\n", "* A potential next step could involve calculating and plotting the month-over-month growth rate for each product to provide a more detailed understanding of their sales momentum, and identify any specific months where one product significantly outperformed the other in terms of growth.\n" ] }, { "cell_type": "markdown", "id": "zfU_zFPDFVzk", "metadata": { "id": "zfU_zFPDFVzk" }, "source": [ "### Confirming the drop" ] }, { "cell_type": "markdown", "id": "fJky0mJAFcid", "metadata": { "id": "fJky0mJAFcid" }, "source": [ "As can be seen from the chart, the sales of V2 were initially tracking with the growth of V1 at the same point in its lifecycle but then the growth slowed dramatically." ] }, { "cell_type": "markdown", "id": "NEq98qq-nfLJ", "metadata": { "id": "NEq98qq-nfLJ" }, "source": [ "## **2.** AI to Score Unstructured Data at Scale" ] }, { "cell_type": "markdown", "id": "7X5yqYmwFxjt", "metadata": { "id": "7X5yqYmwFxjt" }, "source": [ "### Formulating a plan and using the AI Query Engine functions to analyze unstructured data" ] }, { "cell_type": "markdown", "id": "4OKS0pMiF4Rt", "metadata": { "id": "4OKS0pMiF4Rt" }, "source": [ "We've seen some negative product reviews of the V2 speaker and are wondering how much those reviews might be impacting sales. We'll need to look into other factors such as shopper demographics as well.\n", "\n", "But first, to wrangle the review data, we'll need to categorize the reviews in terms of how positive or negative EACH review is toward the key product features that are most often mentioned in reviews for this type of product.\n", "\n", "We'll use the **AI.GENERATE** function to analyze every V2 product review and to give each review a score from -2 (very negative) to +2 (very positive) with zero being either neutral or not mentioned for each of the four key product features.\n", "\n", "The product features are:\n", "* AI Assistant Pro (a new AI assistant added with V2)\n", "* Audio Quality\n", "* Seamless Connectivity\n", "* Smarthome Integration\n", "\n", "The following code generates a new table with that information for every V2 product review." ] }, { "cell_type": "markdown", "id": "4b7e7554", "metadata": {}, "source": [ "#### To use BigQuery's AI.GENERATE function, we will need to create a BigQuery connection" ] }, { "cell_type": "code", "execution_count": null, "id": "88b74e0e", "metadata": {}, "outputs": [], "source": [ "from google.cloud import bigquery_connection_v1 as bq_connection\n", "client = bq_connection.ConnectionServiceClient()\n", "\n", "CONNECTION_ID = \"connection\"\n", "\n", "try:\n", " request = client.get_connection(\n", " request=bq_connection.GetConnectionRequest(name=f\"projects/{PROJECT_ID}/locations/{LOCATION}/connections/{CONNECTION_ID}\")\n", " )\n", " CONN_SERVICE_ACCOUNT = f\"serviceAccount:{request.cloud_resource.service_account_id}\"\n", "\n", "except Exception:\n", " connection = bq_connection.types.Connection(\n", " {\"friendly_name\": CONNECTION_ID, \"cloud_resource\": bq_connection.CloudResourceProperties({})}\n", " )\n", " request = bq_connection.CreateConnectionRequest(\n", " {\n", " \"parent\": f\"projects/{PROJECT_ID}/locations/{LOCATION}\",\n", " \"connection_id\": CONNECTION_ID,\n", " \"connection\": connection,\n", " }\n", " )\n", " response = client.create_connection(request)\n", " CONN_SERVICE_ACCOUNT = (\n", " f\"serviceAccount:{response.cloud_resource.service_account_id}\"\n", " )\n", "\n", "print(CONN_SERVICE_ACCOUNT)" ] }, { "cell_type": "markdown", "id": "f638549d", "metadata": {}, "source": [ "Bind the required IAM roles " ] }, { "cell_type": "code", "execution_count": null, "id": "9355485e", "metadata": {}, "outputs": [], "source": [ "!gcloud projects add-iam-policy-binding {PROJECT_ID} --condition=None --no-user-output-enabled --member={CONN_SERVICE_ACCOUNT} --role='roles/bigquery.connectionUser'\n", "!gcloud projects add-iam-policy-binding {PROJECT_ID} --condition=None --no-user-output-enabled --member={CONN_SERVICE_ACCOUNT} --role='roles/aiplatform.user'" ] }, { "cell_type": "markdown", "id": "39edf621", "metadata": {}, "source": [ "#### Generate scores with BigQuery's AI.GENERATE function" ] }, { "cell_type": "code", "execution_count": null, "id": "uaUbPOjdVHVR", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 38407, "status": "ok", "timestamp": 1756831907044, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "uaUbPOjdVHVR", "outputId": "b2872187-7585-455b-9152-021774801010" }, "outputs": [], "source": [ "query = f\"\"\"\n", "CREATE OR REPLACE TABLE {PROJECT_ID}.{DATASET_ID}.reviews_feature_sentiment_score AS SELECT\n", " review_id,\n", "\n", " -- Score for \"AI Assistant Pro\"\n", " AI.GENERATE((\n", " 'Score the following review based on its sentiment towards \"AI Assistant Pro\". '\n", " 'This feature refers to the smart assistant\\\\'s intelligence, responsiveness, and ability to understand commands (e.g., \"the assistant is dumb\", \"the AI can\\\\'t understand me\"). '\n", " 'Use this scoring rubric: +2 (Very Positive), +1 (Positive), 0 (Neutral or Not Mentioned), -1 (Negative), -2 (Very Negative). '\n", " 'Review: ', review_body),\n", " connection_id => '{LOCATION}.{CONNECTION_ID}'\n", " ).result AS ai_assistant_pro_score,\n", "\n", " -- Score for \"Audio Quality\"\n", " AI.GENERATE((\n", " 'Score the following review based on its sentiment towards \"Audio Quality\". '\n", " 'This feature refers to how the speaker sounds (e.g., \"great bass\", \"clear sound\", \"it\\\\'s loud\"). '\n", " 'Use this scoring rubric: +2 (Very Positive), +1 (Positive), 0 (Neutral or Not Mentioned), -1 (Negative), -2 (Very Negative). '\n", " 'Review: ', review_body),\n", " connection_id => '{LOCATION}.{CONNECTION_ID}'\n", " ).result AS audio_quality_score,\n", "\n", " -- Score for \"Seamless Connectivity\"\n", " AI.GENERATE((\n", " 'Score the following review based on its sentiment towards \"Seamless Connectivity\". '\n", " 'This feature refers to how well the speaker connects to Wi-Fi or Bluetooth (e.g., \"connection drops\", \"setup was easy\"). '\n", " 'Use this scoring rubric: +2 (Very Positive), +1 (Positive), 0 (Neutral or Not Mentioned), -1 (Negative), -2 (Very Negative). '\n", " 'Review: ', review_body),\n", " connection_id => '{LOCATION}.{CONNECTION_ID}'\n", " ).result AS seamless_connectivity_score,\n", "\n", " -- Score for \"Smart Home Integration\"\n", " AI.GENERATE((\n", " 'Score the following review based on its sentiment towards \"Smart Home Integration\". '\n", " 'This feature refers to its ability to control other devices like lights or thermostats. '\n", " 'Use this scoring rubric: +2 (Very Positive), +1 (Positive), 0 (Neutral or Not Mentioned), -1 (Negative), -2 (Very Negative). '\n", " 'Review: ', review_body),\n", " connection_id => '{LOCATION}.{CONNECTION_ID}'\n", " ).result AS smart_home_integration_score\n", "\n", "FROM\n", " `{PROJECT_ID}`.{DATASET_ID}.product_reviews\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": null, "id": "c4320f7d", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "9WW4hy0BI-Sj", "metadata": { "id": "9WW4hy0BI-Sj" }, "source": [ "## **3.** Gathering all of the data: The bigger picture" ] }, { "cell_type": "markdown", "id": "t8TPK8XjJDDx", "metadata": { "id": "t8TPK8XjJDDx" }, "source": [ "Now that we have the review sentiment information, we need to do two more things to prepare for our final analysis. We need to look at which site visitors to the V2 product page did NOT convert to purchases and for them:\n", "* Which negatively reviewed features did they see\n", "* What is all of the demographic and device data that we have\n", "\n", "We need to prepare a table for a final analysis so that our model can determine the weight of the different features to predict which feature is most influential in visitors choosing NOT to purchase. We need anctionable insights.\n", "\n", "The demographic features we'll look at are gender, age range and the urbanicity of the visitors location. The device information we'll consider will be mobile, desktop, tablet.\n", "\n", "The code below will identify which V2 product page visits saw which reviews and will have 1-hot columns for the four features to determine if any of those reviews had a very negative review of any of those features. It will also have 1-hot columns for demographic and device informaiton.\n", "\n", "This massively joined table will be used in the final analysis." ] }, { "cell_type": "code", "execution_count": null, "id": "ZjPfb2weW7dJ", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 3302, "status": "ok", "timestamp": 1756832201592, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "ZjPfb2weW7dJ", "outputId": "ea3947f9-c8b0-4b75-ba8d-42fdcc61cc90" }, "outputs": [], "source": [ "# 1. Define the full SQL query using the f-string\n", "query = f\"\"\"\n", "CREATE OR REPLACE TABLE `{PROJECT_ID}`.{DATASET_ID}.FINAL_ANALYSIS AS (\n", "WITH\n", " -- Unnest the comma-separated list of review IDs for each session\n", " session_reviews_unnested AS (\n", " SELECT\n", " session_id,\n", " -- Using REPLACE to remove single quotes and TRIM for whitespace\n", " REPLACE(TRIM(review_id), \"'\", \"\") AS review_id\n", " FROM\n", " `{PROJECT_ID}`.{DATASET_ID}.sessions_reviews,\n", " UNNEST(SPLIT(reviews_seen, ',')) AS review_id\n", " )\n", "-- Join all tables and perform the final aggregation\n", "SELECT\n", " s.session_id,\n", " s.`session_date_time`,\n", " s.device_type,\n", " s.converted,\n", " -- Create a flag (1 or 0) if any review seen had a score of -2 for each feature\n", " -- Instead of direct comparison, use COALESCE to handle NULLs\n", " MAX(IF(COALESCE(scores.ai_assistant_pro_score, \"0\") = \"-2\", 1, 0)) AS AI_Assistant_neg_review_seen,\n", " MAX(IF(COALESCE(scores.audio_quality_score, \"0\") = \"-2\", 1, 0)) AS Audio_Quality_neg_review_seen,\n", " MAX(IF(COALESCE(scores.seamless_connectivity_score, \"0\") = \"-2\", 1, 0)) AS Seamless_Connectivity_neg_review_seen,\n", " MAX(IF(COALESCE(scores.smart_home_integration_score, \"0\") = \"-2\", 1, 0)) AS Smart_Home_Integration_neg_review_seen,\n", " -- One-hot encoded columns from user_info (based on actual distinct values)\n", " CASE WHEN ui.gender = 'M' THEN 1 ELSE 0 END AS gender_M,\n", " CASE WHEN ui.gender = 'F' THEN 1 ELSE 0 END AS gender_F,\n", " CASE WHEN ui.age_range = '31 - 45' THEN 1 ELSE 0 END AS age_range_31_45,\n", " CASE WHEN ui.age_range = '>60' THEN 1 ELSE 0 END AS age_range_gt60,\n", " CASE WHEN ui.age_range = '20 - 30' THEN 1 ELSE 0 END AS age_range_20_30,\n", " CASE WHEN ui.age_range = '46 - 60' THEN 1 ELSE 0 END AS age_range_46_60,\n", " CASE WHEN ui.age_range = '<20' THEN 1 ELSE 0 END AS age_range_lt20,\n", " CASE WHEN ui.urbanicity = 'suburban' THEN 1 ELSE 0 END AS urbanicity_suburban,\n", " CASE WHEN ui.urbanicity = 'rural' THEN 1 ELSE 0 END AS urbanicity_rural,\n", " CASE WHEN ui.urbanicity = 'urban' THEN 1 ELSE 0 END AS urbanicity_urban,\n", " -- New one-hot encoded columns for device_type\n", " CASE WHEN s.device_type = 'desktop' THEN 1 ELSE 0 END AS device_type_desktop,\n", " CASE WHEN s.device_type = 'mobile' THEN 1 ELSE 0 END AS device_type_mobile,\n", " CASE WHEN s.device_type = 'tablet' THEN 1 ELSE 0 END AS device_type_tablet\n", "FROM\n", " `{PROJECT_ID}`.{DATASET_ID}.sessions AS s\n", " -- Join sessions with the now-clean unnested reviews\n", " INNER JOIN session_reviews_unnested AS ur ON s.session_id = ur.session_id\n", " -- Join with the scores table to get the sentiment for each review\n", " INNER JOIN `{PROJECT_ID}`.{DATASET_ID}.reviews_feature_sentiment_score AS scores ON ur.review_id = scores.review_id\n", " -- Join with session_to_user_map to get user_id\n", " INNER JOIN `{PROJECT_ID}`.{DATASET_ID}.session_to_user_map AS stum ON s.session_id = stum.session_id\n", " -- Join with user_info table\n", " INNER JOIN `{PROJECT_ID}`.{DATASET_ID}.user_info AS ui ON stum.user_id = ui.user_id\n", "GROUP BY\n", " s.session_id,\n", " s.`session_date_time`,\n", " s.device_type,\n", " s.converted,\n", " -- Include new one-hot encoded columns in GROUP BY\n", " gender_M,\n", " gender_F,\n", " age_range_31_45,\n", " age_range_gt60,\n", " age_range_20_30,\n", " age_range_46_60,\n", " age_range_lt20,\n", " urbanicity_suburban,\n", " urbanicity_rural,\n", " urbanicity_urban,\n", " -- New one-hot encoded columns for device_type in GROUP BY\n", " device_type_desktop,\n", " device_type_mobile,\n", " device_type_tablet\n", ")\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": null, "id": "295c6163", "metadata": {}, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "$query" ] }, { "cell_type": "markdown", "id": "lWPRtLQtId2K", "metadata": { "id": "lWPRtLQtId2K" }, "source": [ "## **4.** Model to predict the feature weight: The data science heavy lifting with the Data Science Agent" ] }, { "cell_type": "markdown", "id": "XpcAKkvOIscQ", "metadata": { "id": "XpcAKkvOIscQ" }, "source": [ "Now that we have all of the data in a final table ready for analysis, we'll ask the Data Science Agent to run an XGBOOST model to identify the feature weights and to visualize the weights. The prompt below was used the generate the code and analysis that follows. It is included here for demonstration purposes.\n", "\n", "Our hope is that we can identify key factor(s) driving the drop in sales growth so that we can take corrective action." ] }, { "cell_type": "markdown", "id": "18vMVyKjAO4r", "metadata": { "id": "18vMVyKjAO4r" }, "source": [ "**The Prompt:**\n", "\n", "*Using the FINAL_ANALYSIS table, create an XGBOOST model to determine to what degree any demographic factors or device types or negative review seen is impacting why a shopper decided not to purchase the device. Show a visualization of the feature importance.*" ] }, { "cell_type": "markdown", "id": "d72b1455", "metadata": { "id": "d72b1455" }, "source": [ "### Task\n", "Create an XGBoost model using the data from `FINAL_ANALYSIS` table to determine the impact of demographic factors, device types, and negative reviews on shopper purchase decisions." ] }, { "cell_type": "markdown", "id": "39fe516f", "metadata": { "id": "39fe516f" }, "source": [ "### Data loading\n", "\n", "### Subtask:\n", "Load the `FINAL_ANALYSIS` table into a pandas DataFrame.\n" ] }, { "cell_type": "markdown", "id": "c7fe55c0", "metadata": { "id": "c7fe55c0" }, "source": [ "**Reasoning**:\n", "To load the `FINAL_ANALYSIS` table into a pandas DataFrame, I will import `pandas_gbq`, define the project and table IDs, construct the SQL query, and then use `gbq.read_gbq` to fetch the data. Finally, I will display the head and info of the DataFrame to confirm successful loading.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "abc32231", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 4573, "status": "ok", "timestamp": 1754946545138, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "abc32231", "outputId": "b2bb6f26-5112-447a-96b8-0cce47891575" }, "outputs": [], "source": [ "df_final_analysis = bpd.read_gbq(f\"{PROJECT_ID}.{DATASET_ID}.FINAL_ANALYSIS\")\n", "\n", "print(\"Data loaded successfully. Displaying the first 5 rows:\")\n", "df_final_analysis.head()\n", "print(\"\\nInfo of the DataFrame:\")\n", "df_final_analysis.info()" ] }, { "cell_type": "markdown", "id": "bd38027e", "metadata": { "id": "bd38027e" }, "source": [ "### Data preprocessing\n", "\n", "### Subtask:\n", "Prepare the data for the XGBoost model, including handling categorical features and splitting into training and testing sets.\n" ] }, { "cell_type": "markdown", "id": "7e0d953d", "metadata": { "id": "7e0d953d" }, "source": [ "**Reasoning**:\n", "To prepare the data for the XGBoost model, I will separate the target and features, drop irrelevant columns, and then split the data into training and testing sets as per the instructions.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "ce3561f7", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 1462, "status": "ok", "timestamp": 1754946638781, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "ce3561f7", "outputId": "53b0d307-4e1f-4dd2-b752-53e83a90eb8a" }, "outputs": [], "source": [ "from sklearn.model_selection import train_test_split\n", "\n", "df_final_analysis = df_final_analysis.to_pandas()\n", "\n", "# Separate target variable 'converted' from features\n", "X = df_final_analysis.drop('converted', axis=1)\n", "y = df_final_analysis['converted']\n", "\n", "# Drop irrelevant columns from features DataFrame\n", "X = X.drop(['session_id', 'session_date_time', 'device_type'], axis=1)\n", "\n", "# Split the data into training and testing sets (80/20 ratio)\n", "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n", "\n", "print(\"Data prepared and split into training and testing sets successfully.\")\n", "print(f\"Shape of X_train: {X_train.shape}\")\n", "print(f\"Shape of X_test: {X_test.shape}\")\n", "print(f\"Shape of y_train: {y_train.shape}\")\n", "print(f\"Shape of y_test: {y_test.shape}\")" ] }, { "cell_type": "markdown", "id": "c9639369", "metadata": { "id": "c9639369" }, "source": [ "### Model training\n", "\n", "### Subtask:\n", "Train an XGBoost classification model.\n" ] }, { "cell_type": "markdown", "id": "e704e814", "metadata": { "id": "e704e814" }, "source": [ "**Reasoning**:\n", "To train the XGBoost classification model, I will import `XGBClassifier`, instantiate it with the specified parameters to avoid warnings, and then fit the model to the training data.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "8f134744", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 2363, "status": "ok", "timestamp": 1754946787164, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "8f134744", "outputId": "1c0a5550-d0ff-419a-9fb8-1474123cfb26" }, "outputs": [], "source": [ "import xgboost as xgb\n", "\n", "# Instantiate XGBClassifier\n", "# Setting use_label_encoder=False and eval_metric='logloss' to suppress warnings\n", "model = xgb.XGBClassifier(use_label_encoder=False, eval_metric='logloss', random_state=42)\n", "\n", "# Train the model\n", "model.fit(X_train, y_train)\n", "\n", "print(\"XGBoost classification model trained successfully.\")" ] }, { "cell_type": "markdown", "id": "191c0337", "metadata": { "id": "191c0337" }, "source": [ "### Model evaluation\n", "\n", "### Subtask:\n", "Evaluate the trained model's performance.\n" ] }, { "cell_type": "markdown", "id": "204bf4dc", "metadata": { "id": "204bf4dc" }, "source": [ "**Reasoning**:\n", "To evaluate the model, I will import the necessary metrics from `sklearn.metrics`, make predictions on the test set, and then compute and display the accuracy, precision, recall, and F1-score.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "082aef90", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 48, "status": "ok", "timestamp": 1754946841962, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "082aef90", "outputId": "76e71b92-32e0-4605-cac1-7c16f0c7bc44" }, "outputs": [], "source": [ "from sklearn.metrics import accuracy_score, precision_score, recall_score, f1_score\n", "\n", "# Make predictions on the test set\n", "y_pred = model.predict(X_test)\n", "\n", "# Calculate evaluation metrics\n", "accuracy = accuracy_score(y_test, y_pred)\n", "precision = precision_score(y_test, y_pred)\n", "recall = recall_score(y_test, y_pred)\n", "f1 = f1_score(y_test, y_pred)\n", "\n", "# Print the evaluation metrics\n", "print(f\"Model Evaluation Metrics:\\n\")\n", "print(f\"Accuracy: {accuracy:.4f}\")\n", "print(f\"Precision: {precision:.4f}\")\n", "print(f\"Recall: {recall:.4f}\")\n", "print(f\"F1-Score: {f1:.4f}\")" ] }, { "cell_type": "markdown", "id": "a74d3ecb", "metadata": { "id": "a74d3ecb" }, "source": [ "### Feature importance analysis\n", "\n", "### Subtask:\n", "Determine the importance of each feature in predicting shopper purchase decisions.\n" ] }, { "cell_type": "markdown", "id": "25a14b8e", "metadata": { "id": "25a14b8e" }, "source": [ "**Reasoning**:\n", "To determine feature importance, I will access the trained model's `feature_importances_` attribute, create a pandas Series with feature names from `X_train`, sort it, and then print the results.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "8b97f03e", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "executionInfo": { "elapsed": 28, "status": "ok", "timestamp": 1754946865293, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "8b97f03e", "outputId": "b0b334d9-009f-4dcb-b676-4e39d7a72ef9" }, "outputs": [], "source": [ "import pandas as pd\n", "\n", "# Get feature importances from the trained model\n", "feature_importances = model.feature_importances_\n", "\n", "# Create a pandas Series with feature names\n", "feature_names = X_train.columns\n", "importance_df = pd.Series(feature_importances, index=feature_names)\n", "\n", "# Sort feature importances in descending order\n", "sorted_importance = importance_df.sort_values(ascending=False)\n", "\n", "print(\"Feature Importances (sorted descending):\")\n", "print(sorted_importance)" ] }, { "cell_type": "markdown", "id": "f5ae46c9", "metadata": { "id": "f5ae46c9" }, "source": [ "### Summary:\n", "\n", "### Q&A\n", "\n", "**To what degree are demographic factors, device types, or negative reviews impacting why a shopper decided not to purchase the device?**\n", "\n", "The XGBoost model indicates that the most significant factor impacting a shopper's decision not to purchase (or conversion) is `AI_Assistant_neg_review_seen`, with an importance score of approximately 0.74. This suggests that whether a shopper has viewed negative reviews related to AI Assistant features is highly influential. Other impactful factors, though to a much lesser degree, include `urbanicity_rural` (0.079), `age_range_46_60` (0.046), and `device_type_mobile` (0.044). Several demographic factors (like gender_F) and other negative review categories (Audio Quality, Seamless Connectivity, Smart Home Integration) showed zero importance in this model.\n", "\n", "### Data Analysis Key Findings\n", "\n", "* The `FINAL_ANALYSIS` table, containing 10884 entries and 21 columns, was successfully loaded into a pandas DataFrame.\n", "* The data was successfully preprocessed, separating the target variable 'converted' from 17 features, and then split into training (8707 samples) and testing (2177 samples) sets.\n", "* An XGBoost classification model was successfully trained on the prepared data.\n", "* The trained model achieved an accuracy of 0.8222. However, the precision, recall, and F1-score for the positive class (converted) were 0.0000. This indicates that the model did not predict any positive conversions in the test set, likely due to a significant class imbalance in the target variable.\n", "* Feature importance analysis revealed that `AI_Assistant_neg_review_seen` is by far the most influential factor, contributing to approximately 74% of the feature importance.\n", "* Other features with notable, but significantly lower, importance include `urbanicity_rural` (0.079), `age_range_46_60` (0.046), and `device_type_mobile` (0.044).\n", "* Several features, including `gender_F`, `Audio_Quality_neg_review_seen`, `Seamless_Connectivity_neg_review_seen`, and `Smart_Home_Integration_neg_review_seen`, showed no importance (0.0000) in this model.\n", "\n", "### Insights or Next Steps\n", "\n", "* The dominance of `AI_Assistant_neg_review_seen` as a predictor highlights a critical area for improving conversion rates. Businesses should investigate the content of these negative reviews and consider strategies to mitigate their impact, such as improving AI Assistant features or proactively addressing common complaints.\n", "* The model's inability to predict any positive conversions (Precision, Recall, F1-Score of 0.0000) strongly suggests a class imbalance issue. Future steps should involve addressing this imbalance using techniques like oversampling (SMOTE), undersampling, or adjusting class weights in the XGBoost model to improve its ability to identify and predict conversions.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "384db7b8", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 619 }, "executionInfo": { "elapsed": 423, "status": "ok", "timestamp": 1754947076798, "user": { "displayName": "", "userId": "" }, "user_tz": 420 }, "id": "384db7b8", "outputId": "607b6743-a8cd-4f10-f02f-2a39191c83f6" }, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "# Create a bar plot of feature importances\n", "plt.figure(figsize=(12, 8))\n", "sns.barplot(x=sorted_importance.values, y=sorted_importance.index)\n", "plt.title('Feature Importances from XGBoost Model')\n", "plt.xlabel('Importance')\n", "plt.ylabel('Feature')\n", "plt.grid(axis='x', linestyle='--', alpha=0.7)\n", "plt.show()\n", "\n", "print(\"Feature importance visualization created successfully.\")" ] }, { "cell_type": "markdown", "id": "KdUR8Sb4X_DA", "metadata": { "id": "KdUR8Sb4X_DA" }, "source": [ "## **5.** Closing: AI scoring at scale and agentic assist are force multipliers" ] }, { "cell_type": "markdown", "id": "cKLTDNimYCl5", "metadata": { "id": "cKLTDNimYCl5" }, "source": [ "This visualization provides clear confirmation that the product reviews are most responsible for the decline in sales growth. In particular, it is the AI Assistant feature that is being poorly reviewed and when shoppers see those negative reviews they are choosing not to purchase.\n", "\n", "This gives us immediate actionable insight and means that our engineering team should be laser focused on addressing the complaints, bugs, issues with the AI Assistant Pro.\n", "\n", "The Data Science Agent provides **a powerful multiplier effect for our data scientists,** allowing them to rapidly build, train, and deploy models, delivering critical, actionable intelligence at an unprecedented pace.\n", "\n", "As you can see from this notebook, what at first seemed like a difficult question that would have required a team and several days, was quickly broken down and identified within minutes using Google AI Query Engine and Data Science Agent.\n", "\n" ] } ], "metadata": { "colab": { "name": "Investigating Poor Product Sales", "provenance": [] }, "kernelspec": { "display_name": "myenv", "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.13.12" } }, "nbformat": 4, "nbformat_minor": 5 }