Chatbot Dataset for Developers and Testers

• intents.conversation_id →references conversations.conversation_idmany to one: each Intents row points to one Conversations row
• responses.conversation_id →references conversations.conversation_idmany to one: each Responses row points to one Conversations row

1. Analyze the distribution of detected intents across all conversations. Which intents are most common?

   Join conversations and intents tables, then group by detected_intent and count occurrences.

   tables: conversations, intents

2. Calculate the average confidence score for each detected intent. Which intents have the highest and lowest average confidence?

   Group intents by detected_intent and calculate the average of confidence_score.

   tables: intents

3. Identify conversations where the chatbot response type is 'error' and analyze the corresponding user utterances.

   Join conversations and responses tables, filter for response_type = 'error', and select user_utterance.

   tables: conversations, responses

4. For a specific user_id, retrieve their conversation history, including user utterances and chatbot responses, ordered by timestamp.

   Filter the conversations table by a specific user_id and order by timestamp.

   tables: conversations

Table names match the SQLite file and the SQL script.

SELECT detected_intent, COUNT(*) AS intent_count FROM intents GROUP BY detected_intent ORDER BY intent_count DESC LIMIT 5;

SELECT detected_intent, AVG(confidence_score) AS average_confidence FROM intents GROUP BY detected_intent ORDER BY average_confidence DESC LIMIT 20;

SELECT c.user_utterance, r.response_text FROM conversations c JOIN responses r ON c.conversation_id = r.conversation_id WHERE r.response_type = 'error' LIMIT 20;

SELECT conversation_id, timestamp, user_utterance, chatbot_response FROM conversations WHERE user_id = 'ab939f12-594e-4c4c-91c1-3b15bea6607e' ORDER BY timestamp LIMIT 20;

SELECT detected_intent, COUNT(*) AS intent_count, RANK() OVER (ORDER BY COUNT(*) DESC) as rank FROM intents GROUP BY detected_intent LIMIT 20;

Excel

Load each table into a separate Excel sheet. Use XLOOKUP to join tables based on conversation_id. Create a PivotTable on the 'intents' sheet to analyze intent distribution and average confidence scores.

Power BI

Load all three tables. Create relationships: conversations.conversation_id to intents.conversation_id and conversations.conversation_id to responses.conversation_id. Create measures for total conversations, average confidence score, and count of error responses.

SQL

Load the SQLite or SQL dump into your preferred database. Use the provided table and column names for queries. Joins are typically performed on conversation_id. Primary keys are listed in the table schemas.

Python

Use pandas to load CSV or Parquet files. Merge tables using conversation_id. Analyze intent distributions, confidence scores, and response types using DataFrame operations.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file