As a Developer Advocate, it\u2019s challenging to keep up with user forum messages and understand the big picture of what users are saying. There\u2019s plenty of valuable content \u2014 but how can you quickly spot the key conversations? In this tutorial, I\u2019ll show you an AI hack to perform semantic clustering simply by prompting LLMs!<\/p>\n
TL;DR ![\"\ud83d\udd04\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f504.png?ssl=1\")
this blog post is about how to go from (data science + code) \u2192 (AI prompts + LLMs) for the same results \u2014 just faster and with less effort! ![\"\ud83e\udd16\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f916.png?ssl=1\")
![\"\u26a1\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/26a1.png?ssl=1\")
. It is organized as follows:<\/p>\n
- \n
- Inspiration and Data Sources<\/li>\n
- Exploring the Data with Dashboards<\/li>\n
- LLM Prompting to produce KNN Clusters<\/li>\n
- Experimenting with Custom Embeddings<\/li>\n
- Clustering Across Multiple Discord Servers<\/li>\n<\/ul>\n
Inspiration and Data Sources<\/strong><\/h2>\n
First, I\u2019ll give props to the December 2024 paper <\/strong>Clio<\/strong><\/a> (Claude insights and observations)<\/strong>, a privacy-preserving platform that uses AI assistants to analyze and surface aggregated usage patterns across millions of conversations. Reading this paper inspired me to try this.<\/p>\n
Data<\/strong>. I used only publicly available Discord<\/a> messages, specifically \u201cforum threads\u201d, where users ask for tech help. In addition, I aggregated and anonymized content for this blog.\u00a0 Per thread, I formatted the data into conversation turn format, with user roles identified as either \u201cuser\u201d, asking the question or \u201cassistant\u201d, anyone answering the user\u2019s initial question. I also added a simple, hard-coded binary sentiment score (0 for \u201cnot happy\u201d and 1 for \u201chappy\u201d) based on whether the user said thank you anytime in their thread. For vectorDB vendors I used Zilliz\/Milvus, Chroma, and Qdrant.<\/p>\n
The first step was to convert the data into a pandas data frame. Below is an excerpt. You can see for thread_id=2, a user only asked 1 question. But for thread_id=3, a user asked 4 different questions in the same thread (other 2 questions at farther down timestamps, not shown below).<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-1.png?resize=512%2C79&ssl=1\")
The first step was to convert the anonymized data into a pandas data frame with columns: score, user, role, message, timestamp, thread, user_turns.
<\/figcaption><\/figure>\nI added a naive sentiment 0|1 scoring function.<\/p>\n
Python\">def calc_score(df):\n # Define the target words\n target_words = [\"thanks\", \"thank you\", \"thx\", \"![\"\ud83d\ude42\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f642.png?ssl=1\")
\", \"![\"\ud83d\ude09\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f609.png?ssl=1\")
\", \"![\"\ud83d\udc4d\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f44d.png?ssl=1\")
\"]\n\n\n # Helper function to check if any target word is in the concatenated message content\n def contains_target_words(messages):\n concatenated_content = \" \".join(messages).lower()\n return any(word in concatenated_content for word in target_words)\n\n\n # Group by 'thread_id' and calculate score for each group\n thread_scores = (\n df[df['role_name'] == 'user']\n .groupby('thread_id')['message_content']\n .apply(lambda messages: int(contains_target_words(messages)))\n )\n # Map the calculated scores back to the original DataFrame\n df['score'] = df['thread_id'].map(thread_scores)\n return df\n\n\n...\n\n\nif __name__ == \"__main__\":\n \n # Load parameters from YAML file\n config_path = \"config.yaml\"\n params = load_params(config_path)\n input_data_folder = params['input_data_folder']\n processed_data_dir = params['processed_data_dir']\n threads_data_file = os.path.join(processed_data_dir, \"thread_summary.csv\")\n \n # Read data from Discord Forum JSON files into a pandas df.\n clean_data_df = process_json_files(\n input_data_folder,\n processed_data_dir)\n \n # Calculate score based on specific words in message content\n clean_data_df = calc_score(clean_data_df)\n\n\n # Generate reports and plots\n plot_all_metrics(processed_data_dir)\n\n\n # Concat thread messages & save as CSV for prompting.\n thread_summary_df, avg_message_len, avg_message_len_user = \n concat_thread_messages_df(clean_data_df, threads_data_file)\n assert thread_summary_df.shape[0] == clean_data_df.thread_id.nunique()\n<\/code><\/pre>\nExploring the Data with Dashboards<\/strong><\/h2>\n
From the processed data above, I built traditional dashboards:<\/p>\n
- \n
- \nMessage Volumes:<\/strong> One-off peaks in vendors like Qdrant and Milvus (possibly due to marketing events).<\/li>\n
- \nUser Engagement:<\/strong> Top users bar charts and scatterplots of response time vs. number of user turns show that, in general, more user turns mean higher satisfaction. But, satisfaction does NOT look correlated with response time<\/em>. Scatterplot dark dots seem random with regard to y-axis (response time). Maybe users are not in production, their questions are not very urgent? Outliers exist, such as Qdrant and Chroma, which may have bot-driven anomalies.<\/li>\n
- \nSatisfaction Trends:<\/strong> Around 70% of users appear happy to have any interaction. Data note: make sure to check emojis per vendor, sometimes users respond using emojis instead of words! Example Qdrant and Chroma.<\/em>\n<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-2.png?resize=512%2C279&ssl=1\")
Image by author of aggregated, anonymized data. Top lefts: Charts display Chroma\u2019s highest message volume, followed by Qdrant, and then Milvus. Top rights: Top messaging users, Qdrant + Chroma possible bots (see top bar in top messaging users chart). Middle rights: Scatterplots of Response time vs Number of user turns shows no correlation with respect to dark dots and y-axis (response time). Usually higher satisfaction w.r.t. x-axis (user turns), except Chroma. Bottom lefts: Bar charts of satisfaction levels, make sure you catch possible emoji-based feedback, see Qdrant and Chroma.<\/figcaption><\/figure>\n LLM Prompting to produce KNN Clusters<\/strong><\/h2>\n
For prompting, the next step was to aggregate data by thread_id. For LLMs, you need the texts concatenated together. I separate out user messages from entire thread messages, to see if one or the other would produce better clusters. I ended up using just user messages.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-3.png?resize=512%2C103&ssl=1\")
Example anonymized data for prompting. All message texts concatenated together.<\/figcaption><\/figure>\n With a CSV file for prompting, you\u2019re ready to get started using a LLM to do data science!<\/p>\n
!pip install -q google.generativeai\nimport os\nimport google.generativeai as genai\n\n\n# Get API key from local system\napi_key=os.environ.get(\"GOOGLE_API_KEY\")\n\n\n# Configure API key\ngenai.configure(api_key=api_key)\n\n\n# List all the model names\nfor m in genai.list_models():\n if 'generateContent' in m.supported_generation_methods:\n print(m.name)\n\n\n# Try different models and prompts\nGEMINI_MODEL_FOR_SUMMARIES = \"gemini-2.0-pro-exp-02-05\"\nmodel = genai.GenerativeModel(GEMINI_MODEL_FOR_SUMMARIES)\n# Combine the prompt and CSV data.\nfull_input = prompt + \"nnCSV Data:n\" + csv_data\n# Inference call to Gemini LLM\nresponse = model.generate_content(full_input)\n\n\n# Save response.text as .json file...\n\n\n# Check token counts and compare to model limit: 2 million tokens\nprint(response.usage_metadata)\n<\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-4.png?resize=512%2C133&ssl=1\")
Image by author. Top: Example LLM model names. Bottom: Example inference call to Gemini LLM token counts: prompt_token_count = input tokens; candidates_token_count = output tokens; total_token_count = sum total tokens used.<\/figcaption><\/figure>\n Unfortunately Gemini API kept cutting short the
response.text<\/code>. I had better luck using AI Studio<\/a> directly.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-5.png?resize=512%2C249&ssl=1\")
Image by author: Screenshot of example outputs from Google AI Studio<\/a>.<\/figcaption><\/figure>\n My 5 prompts to Gemini Flash & Pro<\/a> (temperature set to 0) are below.<\/p>\n
Prompt#1: Get thread Summaries:<\/strong><\/h3>\n
\n
Given this .csv file, per row, add 3 columns:<\/em>
<\/em>\u2013 thread_summary = 205 characters or less summary of the row\u2019s column \u2018message_content\u2019<\/em>
<\/em>\u2013 user_thread_summary = 126 characters or less summary of the row\u2019s column \u2018message_content_user\u2019<\/em>
<\/em>\u2013 thread_topic = 3\u20135 word super high-level category<\/em>
<\/em>Make sure the summaries capture the main content without losing too much detail. Make user thread summaries straight to the point, capture the main content without losing too much detail, skip the intro text. If a shorter summary is good enough prefer the shorter summary. Make sure the topic is general enough that there are fewer than 20 high-level topics for all the data. Prefer fewer topics. Output JSON columns: thread_id, thread_summary, user_thread_summary, thread_topic.<\/em><\/p>\n<\/blockquote>\nPrompt#2: Get cluster stats:<\/strong><\/h3>\n
\n
Given this CSV file of messages, use column=\u2019user_thread_summary\u2019 to perform semantic clustering of all the rows. Use technique = Silhouette, with linkage method = ward, and distance_metric = Cosine Similarity. Just give me the stats for the method Silhouette analysis for now.<\/em><\/p>\n<\/blockquote>\n
Prompt#3: Perform initial clustering:<\/strong><\/h3>\n
\n
Given this CSV file of messages, use column=\u2019user_thread_summary\u2019 to perform semantic clustering of all the rows into N=6 clusters using the Silhouette method. Use column=\u201dthread_topic\u201d to summarize each cluster topic in 1\u20133 words. Output JSON with columns: thread_id, level0_cluster_id, level0_cluster_topic.<\/em><\/p>\n<\/blockquote>\n
Silhouette Score <\/strong>measures how similar an object is to its own cluster (cohesion) versus other clusters (separation). Scores range from -1 to 1. A higher average silhouette score generally indicates better-defined clusters with good separation. For more details, check out the scikit-learn silhouette score documentation<\/a>.<\/p>\n
Applying it to Chroma Data. <\/strong>Below, I show results from Prompt#2, as a plot of silhouette scores. I chose N=6 clusters<\/strong> as a compromise between high score and fewer clusters. Most LLMs these days for data analysis take input as CSV and output JSON.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-6.png?resize=512%2C186&ssl=1\")
Image by author of aggregated, anonymized data. Left: I chose N=6 clusters as compromise between higher score and fewer clusters. Right: The actual clusters using N=6. Highest sentiment (highest scores) are for topics about Query. Lowest sentiment (lowest scores) are for topics about \u201cClient Problems\u201d.<\/figcaption><\/figure>\n From the plot above, you can see we are finally getting into the meat of what users are saying!<\/p>\n
Prompt#4: Get hierarchical cluster stats:<\/strong><\/h3>\n
\n
Given this CSV file of messages, use the column=\u2019thread_summary_user\u2019 to perform semantic clustering of all the rows into Hierarchical Clustering (Agglomerative) with 2 levels. Use Silhouette score. What is the optimal number of next Level0 and Level1 clusters? How many threads per Level1 cluster? Just give me the stats for now, we\u2019ll do the actual clustering later.<\/em><\/p>\n<\/blockquote>\n
Prompt#5: Perform hierarchical clustering:<\/strong><\/h3>\n
\n
Accept this clustering with 2-levels. Add cluster topics that summarize text column \u201cthread_topic\u201d. Cluster topics should be as short as possible without losing too much detail in the cluster meaning.<\/em>
<\/em>\u2013 Level0 cluster topics ~1\u20133 words.<\/em>
<\/em>\u2013 Level1 cluster topics ~2\u20135 words.<\/em>
<\/em>Output JSON with columns: thread_id, level0_cluster_id, level0_cluster_topic, level1_cluster_id, level1_cluster_topic.<\/em><\/p>\n<\/blockquote>\nI also prompted to generate Streamlit code to visualize the clusters (since I\u2019m not a JS expert
![\"\ud83d\ude04\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f604.png?ssl=1\")
). Results for the same Chroma data are shown below.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-7.png?resize=512%2C241&ssl=1\")
Image by author of aggregated, anonymized data. Left image: Each scatterplot dot is a thread with hover-info. Right image: Hierarchical clustering with raw data drill-down capabilities. Api and Package Errors looks like Chroma\u2019s most urgent topic to fix, because sentiment is low and volume of messages is high.<\/figcaption><\/figure>\n I found this very insightful. For Chroma, clustering revealed that while users were happy with topics like Query, Distance, and Performance, they were unhappy about areas such as Data, Client, and Deployment.<\/p>\n
Experimenting with Custom Embeddings<\/strong><\/h2>\n
I repeated the above clustering prompts, using just the numerical embedding (\u201cuser_embedding\u201d) in the CSV instead of the raw text summaries (\u201cuser_text\u201d).I\u2019ve explained embeddings in detail in previous blogs<\/a> before, and the risks of overfit models on leaderboards. OpenAI has reliable embeddings<\/a> which are extremely affordable by API call. Below is an example code snippet how to create embeddings.<\/p>\n
from openai import OpenAI\n\n\nEMBEDDING_MODEL = \"text-embedding-3-small\"\nEMBEDDING_DIM = 512 # 512 or 1536 possible\n\n\n# Initialize client with API key\nopenai_client = OpenAI(\n api_key=os.environ.get(\"OPENAI_API_KEY\"),\n)\n\n\n# Function to create embeddings\ndef get_embedding(text, embedding_model=EMBEDDING_MODEL,\n embedding_dim=EMBEDDING_DIM):\n response = openai_client.embeddings.create(\n input=text,\n model=embedding_model,\n dimensions=embedding_dim\n )\n return response.data[0].embedding\n\n\n# Function to call per pandas df row in .apply()\ndef generate_row_embeddings(row):\n return {\n 'user_embedding': get_embedding(row['user_thread_summary']),\n }\n\n\n# Generate embeddings using pandas apply\nembeddings_data = df.apply(generate_row_embeddings, axis=1)\n# Add embeddings back into df as separate columns\ndf['user_embedding'] = embeddings_data.apply(lambda x: x['user_embedding'])\ndisplay(df.head())\n\n\n# Save as CSV ...\n<\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-8.png?resize=512%2C102&ssl=1\")
Example data for prompting. Column \u201cuser_embedding\u201d is an array length=512 of floating point numbers.<\/figcaption><\/figure>\n Interestingly, both Perplexity Pro and Gemini 2.0 Pro sometimes hallucinated cluster topics (e.g., misclassifying a question about slow queries as \u201cPersonal Matter\u201d).<\/p>\n
Conclusion: When performing NLP with prompts, let the LLM generate its own embeddings \u2014 externally generated embeddings seem to confuse the model.<\/em><\/strong><\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-9.png?resize=512%2C240&ssl=1\")
Image by author of aggregated, anonymized data. Both Perplexity Pro and Google\u2019s Gemini 1.5 Pro hallucinated Cluster Topics when given an externally-generated embedding column. Conclusion \u2014 when performing NLP with prompts, just keep the raw text and let the LLM create its own embeddings behind the scenes. Feeding in externally-generated embeddings seems to confuse the LLM!<\/figcaption><\/figure>\n Clustering Across Multiple Discord Servers<\/strong><\/h2>\n
Finally, I broadened the analysis to include Discord messages from three different VectorDB vendors. The resulting visualization highlighted common issues \u2014 like both Milvus and Chroma facing authentication problems.<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/unnamed-10.png?resize=512%2C255&ssl=1\")
Image by author of aggregated, anonymized data: A multi-vendor VectorDB dashboard displays top issues across many companies. One thing that stands out is both Milvus and Chroma are having trouble with Authentication.<\/figcaption><\/figure>\n Summary<\/strong><\/h2>\n
Here\u2019s a summary of the steps I followed to perform semantic clustering using LLM prompts:<\/p>\n
- \n
- Extract Discord threads.<\/li>\n
- Format data into conversation turns with roles (\u201cuser\u201d, \u201cassistant\u201d).<\/li>\n
- Score sentiment and save as CSV.<\/li>\n
- Prompt Google Gemini 2.0 flash for thread summaries.<\/li>\n
- Prompt Perplexity Pro or Gemini 2.0 Pro for clustering based on thread summaries using the same CSV.<\/li>\n
- Prompt Perplexity Pro or Gemini 2.0 Pro to write Streamlit<\/a> code to visualize clusters (because I\u2019m not a JS expert
![\"\ud83d\ude06\"](\"https:\/\/i0.wp.com\/s.w.org\/images\/core\/emoji\/15.0.3\/72x72\/1f606.png?ssl=1\")
).<\/li>\n<\/ol>\nBy following these steps, you can quickly transform raw forum data into actionable insights \u2014 what used to take days of coding can now be done in just one afternoon!<\/p>\n
References<\/strong><\/h3>\n
- \n
- Clio: Privacy-Preserving Insights into Real-World AI Use, https:\/\/arxiv.org\/abs\/2412.13678<\/a>\n<\/li>\n
- Anthropic blog about Clio, https:\/\/www.anthropic.com\/research\/clio<\/a>\n<\/li>\n
- \nMilvus Discord Server<\/a>, last accessed Feb 7, 2025
Chroma Discord Server<\/a>, last accessed Feb 7, 2025
Qdrant Discord Server<\/a>, last accessed Feb 7, 2025<\/li>\n- Gemini models, https:\/\/ai.google.dev\/gemini-api\/docs\/models\/gemini<\/a>\n<\/li>\n
- Blog about Gemini 2.0 models, https:\/\/blog.google\/technology\/google-deepmind\/gemini-model-updates-february-2025\/<\/a>\n<\/li>\n
- Scikit-learn Silhouette Score<\/a><\/li>\n
- OpenAI Matryoshka embeddings<\/a><\/li>\n
- Streamlit<\/a><\/li>\n<\/ol>\n
\n
The post Tutorial: Semantic Clustering of User Messages with LLM Prompts<\/a> appeared first on Towards Data Science<\/a>.<\/p>\n<\/div>\n
- Anthropic blog about Clio, https:\/\/www.anthropic.com\/research\/clio<\/a>\n<\/li>\n
- Clio: Privacy-Preserving Insights into Real-World AI Use, https:\/\/arxiv.org\/abs\/2412.13678<\/a>\n<\/li>\n
- \nUser Engagement:<\/strong> Top users bar charts and scatterplots of response time vs. number of user turns show that, in general, more user turns mean higher satisfaction. But, satisfaction does NOT look correlated with response time<\/em>. Scatterplot dark dots seem random with regard to y-axis (response time). Maybe users are not in production, their questions are not very urgent? Outliers exist, such as Qdrant and Chroma, which may have bot-driven anomalies.<\/li>\n
- \nMessage Volumes:<\/strong> One-off peaks in vendors like Qdrant and Milvus (possibly due to marketing events).<\/li>\n