Fourth February Post

Today, I worked on chatbot as continuing from last week.

Every time someone asks a question on chatbot, the conversation is automatically recorded, which allows us to see what kind of things costumers are interested in or have questions on. Since we have been testing the chatbot by asking questions and see if the response is correct, I used the conversation saved, cleaned up the data, and visualize the result.

The data looks above. My goal was to make a graph that explains what kind of topics have been asked more frequently. First, I categorized the response using the term sheet I helped create. This term sheet is like a dictionary which shows category for each term and definition. In order group up the answers, I manually search the answer/definition of the record on the term sheet to categorize.

When I was thinking how to find out each category based on definition, I had an idea of coding an "if" statement where if XXX matches, it would be categorized as YYY. However, I did not know how to do that on Excel, so I did each by each, but there is definitely a way to do it automatically. I made a new column consisting of the category. For some of the definitions, there were from different dictionary, so I left those blank. After that, I made a pivot table like shown below by setting the column of category as the rows and count of category as the values.

Here, I was able to clearly see the distribution of each category. Based on this pivot table, I created a graph which essentially illustrates frequency of category.

I was able to visualize the data more clearly by sorting out the number, from bigger values to smaller values. This is effective because now the result is obvious to anyone.

When the chatbot gets published, this operation will be done automatically so we will be able to see the result every day. This will be helpful since we get to understand consumers' need.