Tracking and analyzing the data users generate through the chatbot provides substantial insight into your bot's workings and performance. Also, it is beneficial in determining your bot's viability, interaction tendencies, as well as its strong points and potential for improvements.
There are numerous ways to approach the analysis and reporting of data via Certainly functionalities. This article provides guidance on how to best utilize the Message Report Excel datasets, as it covers:
- requesting the dataset,
- understanding the dataset,
- dataset & layout preparation,
- formulas & calculations,
- why Excel Pivot Table is your new best friend, and
The metrics you will learn to calculate include the unique total or over time count of conversations, users, fallback, ticket creation, as well as recognition rate, and many more.Requesting the dataset
The Message Reports section of the Certainly platform can be accessed via the Navigation bar. After you specify the bot and time period you wish to look into, you will see a newly added functionality called "Report Type". This filter allows you to choose one of our pre-made message report templates.
These report templates contain various columns and categorization of data, depending on your needs. We currently offer:
- Message Data: includes (meta) data and details about every message sent and received by the bot
- Standard: gives an overview of the user's interaction with the bot and the flow of the conversation
- Advanced: provides the largest amount of information on conversation flow, message content, and bot label configuration
- Content Management: allows for detailed analysis of the bot's Module content
Once you decide upon the report type that best suits your analysis, click on the "Request Report" button, and the report will be sent to your e-mail in the form of an Excel workbook. Please note that in order to open the CSV file correctly, you will need to import it into Excel.
The other possibility is to download the CSV file directly from the page, but keep in mind that it will only account for a period of 15 days. Longer periods can only be accessed via e-mail.
For this article, we use the Advanced report template as an example for our analysis.Understanding the dataset
Once you receive the dataset and open it in Excel, there will be an abundance of data you can work with. The data entries recorded in rows start from the most recent and finish with the oldest entry in the range. These data entries reflect each interaction that occurred in a conversation. The columns represent the categories by which the specific data is placed and correspond to the column names and descriptions listed in the Message Reports article.
For now, we will focus on the most general but fundamental categories of data that can paint an overall picture and give you a more definite sense of your bot's performance alongside the KPI Reports. These are the categories we'll use as an example:
- Conversation / Message date & time
- Conversation / Module ID
- Conversation / Module name
- Conversation / Next bot module ID
- Conversation / Conversation ID
- User / Certainly User ID
The simplest way to extract the mentioned columns is by copying and pasting them into a new worksheet or workbook. This way, you leave the original data source intact, allowing for the dataset's future usability.
Alongside the columns, it is advisable to create a map legend that will hold information on the key Modules you deem to be most important. It will help you keep track of the Modules, especially if there are multiple bots involved. The best Modules to start with include the Starting Module, Fallback Module(s), Handover Module, Ticket Creation Module, etc. These Modules will most likely be the ones you usually track with KPI tags, so be sure to consider those as well.
Table 1. An example of a layout that holds a short name description of the Module and its corresponding ID
Since each bot design is unique and serves a different purpose, there will likely be other indicators you want to consider. The most common totals include the sum of unique users, unique conversations, conversations with or without handover attempts, fallback occurrences, and the count of how many times the start Module has been passed, as is shown in the table below:
Table 2. An example of general totals that could be considered based on the map legend Modules
The final thing that can be added is an additional column called "Recognition", which will evaluate the potential connection of the two Modules. That is to say, it checks the bot's flow to determine whether the current Module leads to the next predicted Module. This calculation is crucial for the analysis of AI recognition.
Table 3. An example of the most typical categories used for analysis including the new column “Recognition”Formulas & calculations
The totals presented in Table 2 can be calculated in the following manner:
Table 4. Display of calculation names, formulas, and descriptions
The Recognition column evaluates the flow’s ability to go from one predicted Module to another. If the bot asks for the user’s input in the form of an open-ended question, it is expected of the bot to recognize the input and point the upcoming connection to the intended, correct Module.
In the example, we evaluated how often the bot recognizes the user’s intent of the Module “User Question Module”. If the intent was recognized, the bot should visit the appropriate Module. On the other hand, if the bot fails to recognize the intent behind the user’s input, it triggers “Fallback”. The formula below is used for such recognition calculation:
=IF(AND(Conversation / Module ID first entry = 247844 , Conversation / Next bot module ID first entry = 233571), "Recognition Fail", IF((Conversation / Module ID first entry =247844), "Recognition Success", "//"))
First, we are creating the formula while keeping in mind the three possible outcomes of the calculation: “Recognition Fail”, “Recognition Success”, and “//”. We will return to these later.
Then, we check the condition where the Module ID of the row entry must correspond to the ID of the Module you wish to consider as the current location (in this example, the name of the current Module is “User Question Module“, and its ID 247844) and the upcoming/predicted next bot Module ID you believe the bot should go to (in this example we seek how many times it didn't perform as intended, so we use “Fallback” Module and its ID 233571). Once the formula reaches a cell in which the Module ID is 247844, and the Next bot Module ID is 233571, it will give a result of “Recognition Fail”.
However, if the initial condition is not fulfilled, it checks the next one where the current Module ID corresponds to 247844, but the Next bot Module ID is NOT 233571. In that case, we have a “Recognition Success”, as the connection between the current and next Module did not yield a fallback.
Finally, the last condition marks every other connection as “//” since it doesn’t contain either 247844 or 233571, meaning the connection we are searching for was not established at all.Excel Pivot Table is your new best friend
Once all the values and information are gathered, it is time to create a pivot table. To do so, go to the “Insert” tab, click “PivotTable”, select the column categories as the range, and don’t tick the box that adds this data to the Data Model. Once a Pivot Table is created, organize the Pivot Table Fields in a way you prefer by using drag-drop.
The way we combined the fields for our example is:
Table 5. Example of Excel's PivotTable Fields settings
Our PivotTable setup led to the following table layout, which depicts how many times good or bad recognition connections were established in a 3-month period:
Table 6. PivotTable layout based on the setting of the fields in Table 5
To get a clear perspective of the recognition success-fail ratio in the occurrences, there are two ways to get those percentages.
In the first, you create a new field by dragging the “Recognition” field again into the Values section and setting its value as a “% of Rows Total” with “Recognition” as a base field. This will generate new columns holding the share of each recognition type occurrence. For better visibility, make sure to untick the “Show grand totals for rows” box located in the “Options” tab, as shown in the picture below.
However, if you are not that fond of in-depth PivotTable features, we highly suggest taking the other route, with which we will continue for the rest of the article.
In this option, you copy the PivotTable that was initially created (like in Table 6) and paste it into a worksheet in the form of values. If you paste it as you usually do, the pivot functionality will stay, but in this case, we want to remove it and continue working solely on the values. To get the % share values, you need to divide the wanted recognition type with the grand total column.Visualization
Now that you have your data ready, it's time to get creative and visualize it! Play around with the data you have, and display it in the way you find the most appealing and suitable.
For our example, we have created a combo chart with custom combinations. Here, we set the absolute values on the primary, and the relative values on the secondary axis, as shown below:
With this setting, we can show different value types on one chart without the expense of distorting and misrepresenting data.
The final result should resemble the following chart, in which we have shown a specific period of November from the dataset:
Another chart example based solely on the totals from Table 2 and calculations from Table 4 showcases the bot's ability to independently answer users' questions without human agent interference.
Both methods with which we created the final 2 charts are usable across a vast number of different KPIs, which are not necessarily presented here. The PivotTable method through which we analyzed and presented the Recognition Success chart is employable on any KPI you wish to track chronologically/over time. The second chart utilizes the counting method to portray any aggregate value extracted from the specific Module ID visits.
Now it's time to take this chart, interpret it with your team, and find new, exciting ways to utilize and improve your chatbot!>