gaining_insights_from_unstructured_data.ipynb

{
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat_minor": 5,
 "nbformat": 4,
 "cells": [
  {
   "cell_type": "markdown",
   "id": "beafe711-0ff7-4aa6-bd2d-d58ffc95bb92",
   "metadata": {
    "collapsed": false,
    "name": "OVERVIEW_MD",
    "resultHeight": 339
   },
   "source": "# Gaining insights from Unstructured Data with Snowflake Cortex AI\n\nTasty Bytes is a global food truck network operating in 15 countries with fleet of 450 trucks. They collect customer reviews to get customer feedback on their food-trucks which come in from multiple sources and span multiple languages. \nThis enables them to better understand the areas which require improvement and drive up customer loyalty along with satisfaction. \n\nIn this notebook, we will look at how we analyze these collated customer reviews using Snowflake Cortex to understand :\n  * What our international customers are saying with Cortex **Translate**\n  * Get a summary of what customers are saying with Cortex **Summary**\n  * Classify reviews to determine if they would recommend a food truck with Cortex **ClassifyText**\n  * Gain specific insights with Cortex **Complete**\n  * Understand how customers are feeling with Cortex **Sentiment**"
  },
  {
   "cell_type": "markdown",
   "id": "ec42fdf2-de02-4e89-ae78-d4316f2dd09c",
   "metadata": {
    "collapsed": false,
    "name": "COUNT_REVIEWS_MD",
    "resultHeight": 41
   },
   "source": "Lets see how many reviews we have."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12220c10-ff2d-4d95-af0e-fc8135efaf99",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "sql",
    "name": "COUNT_REVIEWS",
    "resultHeight": 0
   },
   "outputs": [],
   "source": [
    "SELECT COUNT(*) FROM TRUCK_REVIEWS_V;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4db3f281-4a3d-48a5-b07e-3e967ea5a824",
   "metadata": {
    "collapsed": false,
    "name": "IMPORT_PACKAGES_MD",
    "resultHeight": 134
   },
   "source": "**Import python packages**\n\nSnowflake Notebooks include Streamlit and the third-party packages listed in the Snowflake Anaconda channel.  \nInstalling a package is made easy by enabling user to select required pacakges from a list of available pacakges under Packages on the top right corner.  \nOnce installed, we can import installed packages as we would in any other notebook."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3775908f-ca36-4846-8f38-5adca39217f2",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "IMPORT_PACKAGES",
    "resultHeight": 0,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "# Import python packages\nimport streamlit as st\nimport pandas as pd\n\n# Snowpark\nfrom snowflake.snowpark.context import get_active_session\nimport snowflake.snowpark.functions as F\nfrom snowflake.snowpark.functions import when, date_part\nfrom snowflake.snowpark.window import Window\n\n# Cortex Functions\nimport snowflake.cortex  as cortex\n\nsession = get_active_session()\n# Add a query tag to the session.\nsession.query_tag = {\"origin\":\"sf_sit-is\", \n                     \"name\":\"voc\", \n                     \"version\":{\"major\":1, \"minor\":0},\n                     \"attributes\":{\"is_quickstart\":1, \"source\":\"notebook\", \"vignette\":\"customer_reviews\"}}\n"
  },
  {
   "cell_type": "markdown",
   "id": "ba1549f9-f4d0-447d-b447-4927a17e5d56",
   "metadata": {
    "collapsed": false,
    "name": "PREVIEW_REVIEWS_MD",
    "resultHeight": 41
   },
   "source": "Lets preview the reviews"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a2f5851-3b19-41b1-81a3-73f82b9c0966",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "PREVIEW_REVIEWS",
    "resultHeight": 351,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": [
    "reviews_df = session.table('TRUCK_REVIEWS_V') \\\n",
    "             .filter(date_part(\"year\", F.col('DATE')) == 2024)\n",
    "reviews_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214db186-a7d5-49ba-a428-6c335199741c",
   "metadata": {
    "collapsed": false,
    "name": "CORTEX_INTRO_MD",
    "resultHeight": 67
   },
   "source": "In the next cell, we leverage **Translate** - one of the **Snowflake Cortex specialised LLM functions** are available in Snowpark ML, to translate the multilingual reviews to english to enable easier analysis for folks who don't speak the language the original review is in."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43b8f914-e41b-433a-8544-5c20eb742dc5",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "TRANSLATE",
    "resultHeight": 194
   },
   "outputs": [],
   "source": [
    "# Conditionally translate reviews that are not english using Cortex Translate\n",
    "reviews_df = reviews_df.withColumn('TRANSLATED_REVIEW',when(F.col('LANGUAGE') != F.lit(\"en\"), \\\n",
    "                                                            cortex.Translate(F.col('REVIEW'), \\\n",
    "                                                                             F.col('LANGUAGE'), \\\n",
    "                                                                             \"en\")) \\\n",
    "                                   .otherwise(F.col('REVIEW')))\n",
    "\n",
    "reviews_df.filter(F.col('LANGUAGE') != F.lit(\"en\")) \\\n",
    ".select([\"REVIEW\",\"LANGUAGE\",\"TRANSLATED_REVIEW\"]).show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "651ed2ac-d1a5-48ae-836b-cb7024c759e1",
   "metadata": {
    "name": "SUMMARIZE_MD",
    "collapsed": false,
    "resultHeight": 41
   },
   "source": "We can quickly learn what our customers are saying with Cortex **Summarize**"
  },
  {
   "cell_type": "code",
   "id": "ff217432-5256-473d-a317-33fa53b01a91",
   "metadata": {
    "language": "python",
    "name": "SUMMARY_OF_REVIEW",
    "collapsed": false,
    "resultHeight": 412,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "summarized_reviews_df = session.table(\"CONCATENATED_REVIEWS\").select(\n    F.col(\"TRUCK_BRAND_NAME\"),\n    cortex.Summarize(F.col(\"ALL_REVIEWS_TEXT\")).alias(\"SUMMARY\")\n)\n\nsummarized_reviews_df.select([\"TRUCK_BRAND_NAME\", \"SUMMARY\"]).show(3)",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "6bac2888-1344-4ea9-85aa-49e38afad859",
   "metadata": {
    "collapsed": false,
    "name": "CLASSIFY_TEXT_MD",
    "resultHeight": 41
   },
   "source": "We can similarly understand if a customer would recommend the food truck based on their review using Snowflake Cortex **ClassifyText**"
  },
  {
   "cell_type": "code",
   "id": "fa98f3d4-200d-4158-bc6f-b62132238ad8",
   "metadata": {
    "language": "python",
    "name": "CLASSIFY_TEXT",
    "resultHeight": 188,
    "collapsed": false,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "# Text description to understand whether a customer would recommend food truck based on their review \ntext_description = \"\"\"\nTell me based on the following food truck customer review, will they recommend the food truck to \\\ntheir friends and family?\n\"\"\"\n\nreviews_df = reviews_df.withColumn('RECOMMEND', cortex.ClassifyText(F.col('REVIEW'),[\"Likely\",\"Unlikely\",\"Unsure\"], text_description)['label'])\n\nreviews_df.select([\"REVIEW\",\"RECOMMEND\"]).show(3)",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "7fa7fefd-0e0a-48c3-967c-ee542a08541e",
   "metadata": {
    "name": "COMPLETE_MD",
    "collapsed": false,
    "resultHeight": 41
   },
   "source": "Gaining specific insights through Cortex Complete"
  },
  {
   "cell_type": "code",
   "id": "184bf8fa-3cc1-4494-b949-720049b92b90",
   "metadata": {
    "language": "python",
    "name": "COMPLETE",
    "collapsed": false,
    "resultHeight": 188,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "question = \"What is the number one dish positively mentioned in the feedback?\"\n\nsummarized_reviews_df = session.table(\"CONCATENATED_REVIEWS\").select(\n    F.col(\"TRUCK_BRAND_NAME\"),\n    cortex.Complete(\n        \"mistral-large2\",\n        F.concat(\n            F.lit(\"Context: \"),\n            F.col(\"ALL_REVIEWS_TEXT\"),\n            F.lit(f\" Question: {question} Answer briefly and concisely and only name the dish:\")\n        )\n    ).alias(\"NUMBER_ONE_DISH\")\n)\n\nsummarized_reviews_df.show(3)",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "90e8df6d-4bf1-42b2-b6db-30d109bd69c9",
   "metadata": {
    "collapsed": false,
    "name": "SENTIMENT_MD",
    "resultHeight": 67
   },
   "source": "Next we will look at another **task specific LLM function in Cortex - Sentiment**. We utilise sentiment function to understand customer's tone based on the review they provided. Sentiment return value between -1 and 1 such that -1 is the most negative while 1 is the most positive.  "
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "965c69e0-5312-4f4f-89f1-12f52c849453",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "CORTEX_SENTIMENT",
    "resultHeight": 188,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": [
    "# Understand the sentiment of customer review using Cortex Sentiment\n",
    "reviews_df = reviews_df.withColumn('SENTIMENT', cortex.Sentiment(F.col('REVIEW')))\n",
    "\n",
    "reviews_df.select([\"REVIEW\",\"SENTIMENT\"]).show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8da83a8e-d160-4e6c-9a08-fc8dd0fbcb85",
   "metadata": {
    "name": "SQL_MD",
    "collapsed": false,
    "resultHeight": 41
   },
   "source": "These robust Cortex functions seamlessly integrate with SQL, enabling powerful data processing capabilities."
  },
  {
   "cell_type": "code",
   "id": "2b1611e4-a52d-4f9d-a01a-5cc3f7cdb7dd",
   "metadata": {
    "language": "sql",
    "name": "SQL_TRANSLATE",
    "resultHeight": 182,
    "collapsed": false
   },
   "outputs": [],
   "source": "-- Add the TRANSLATED_REVIEW column with conditional translation\nWITH TRANSLATED_REVIEWS AS (\n    SELECT \n        REVIEW,\n        LANGUAGE,\n        CASE \n            WHEN LANGUAGE != 'en' THEN SNOWFLAKE.CORTEX.TRANSLATE(REVIEW, LANGUAGE, 'en') \n            ELSE REVIEW\n        END AS TRANSLATED_REVIEW\n    FROM TRUCK_REVIEWS_V\n)\n\n-- Filter rows where the LANGUAGE is not English and select the desired columns\nSELECT \n    REVIEW, \n    LANGUAGE, \n    TRANSLATED_REVIEW\nFROM TRANSLATED_REVIEWS\nWHERE LANGUAGE != 'en'\nLIMIT 3;",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "76ff3c22-d20c-49a2-9c10-d7e5878782d2",
   "metadata": {
    "language": "sql",
    "name": "SQL_SUMMARIZE",
    "collapsed": false,
    "resultHeight": 439,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "-- Generate summaries for each truck brand\nWITH SUMMARIZED_REVIEWS AS (\n    SELECT \n        TRUCK_BRAND_NAME,\n        SNOWFLAKE.CORTEX.SUMMARIZE(ALL_REVIEWS_TEXT) AS SUMMARY\n    FROM CONCATENATED_REVIEWS\n)\n\nSELECT * FROM SUMMARIZED_REVIEWS;",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "b0419487-3d8c-4842-bcad-b3edf43bb10c",
   "metadata": {
    "language": "sql",
    "name": "SQL_CLASSIFY_TEXT",
    "resultHeight": 182,
    "collapsed": false
   },
   "outputs": [],
   "source": "WITH CLASSIFIED_REVIEWS AS (\n    SELECT \n        REVIEW,\n        PARSE_JSON(SNOWFLAKE.CORTEX.CLASSIFY_TEXT(\n            REVIEW, \n            ['Likely', 'Unlikely', 'Unsure'], \n            OBJECT_CONSTRUCT('task_description', \n                'Tell me based on the following food truck customer review, will they recommend the food truck to their friends and family?'\n            )\n        )):label::TEXT AS RECOMMEND\n    FROM TRUCK_REVIEWS_V\n)\n\nSELECT * From CLASSIFIED_REVIEWS limit 3;\n",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "0b503e72-6e36-4407-a6e9-aebabe5efbfc",
   "metadata": {
    "language": "sql",
    "name": "SQL_COMPLETE",
    "resultHeight": 182,
    "collapsed": false
   },
   "outputs": [],
   "source": "-- Gain Learnings from a specific question\nWITH GAIN_LEARNINGS AS (\n    SELECT \n        TRUCK_BRAND_NAME,\n        SNOWFLAKE.CORTEX.COMPLETE(\n           'mistral-large2', \n           'Context:' || ALL_REVIEWS_TEXT || ' Question: What is the number one dish positively mentioned in the feedback? Answer briefly and concisely and only name the dish:'\n       ) AS NUMBER_ONE_DISH\n    FROM CONCATENATED_REVIEWS\n)\nSELECT TRUCK_BRAND_NAME, NUMBER_ONE_DISH FROM GAIN_LEARNINGS LIMIT 3;",
   "execution_count": null
  },
  {
   "cell_type": "code",
   "id": "93830f62-4c56-4b0c-9ee1-e4e520224f57",
   "metadata": {
    "language": "sql",
    "name": "SQL_SENTIMENT",
    "collapsed": false,
    "resultHeight": 182,
    "codeCollapsed": false
   },
   "outputs": [],
   "source": "SELECT \n    REVIEW, \n    SNOWFLAKE.CORTEX.SENTIMENT(REVIEW) AS SENTIMENT\nFROM TRUCK_REVIEWS_V\nLIMIT 3;\n",
   "execution_count": null
  }
 ]
}