Skip to main content

A8 Analytics Database Architecture

Analytics for A8Chat are stored in Timescale database. Since this database is a collection of timeseries data, all the tables in the database have a time column denoting the time of event/incident.

ER Diagram​

Timescale Database Architecture

To generate this entire schema on your local machine, please make use of this script.

Tables and its Attributes​

User Table​

The user table consists of all the details of the users who have interacted with the chatbot. Entry into this table are made only once whenever a new user starts interaction with the chatbot.

Field-wise descriptions are given below:

  • time - Time, the user connected to the chatbot. (UTC time)
  • client_id - Unique Id used to connect to the MQTT server.
  • org_id - Organisation Id of the chatbot that the user interacts to. Every bot is associated with an orgId. Multiple bots can belong to one organisation Id.
  • channel - The channel through which user interacts with the bot. Eg.: Web, Facebook, and Whatsapp
  • timezone - Timezone to which the user belongs to. Eg.: Asia/Kolkatta
  • OS - Operating System from which the user interacts. Eg.: Linux and MacOS
  • lang - Device Language of the user. Eg.: en (English)
  • url - URL on which the chatbot is hosted.
  • browser_name - Name of the browser that the user used to access the chatbot. Eg.: Chrome or Firefox
  • browser_version - Version number of the browser used. Eg.: 78.5
  • ip_address - IP address of the user that is connected to the geo-coordinates.
  • latitude - Calculated based on the IP address to know the user location.
  • longitude - Calculated based on the IP address to know the user location.
  • device_name - Name of the device used by the user.
  • screen_size_width - Width of the screen in the device used by the user.
  • screen_size_height - Height of the screen in the device used by the user.
  • device_orientation - User device orientation while connecting to the chatbot.
  • isp_provider - User ISP provider. Eg.: Airtel, Jio, and/or Hathway
  • data_speed - Internet Speed of the user.
  • bot_handle - Unique Identifier of the bot belonging to an organisation.
  • custom_fields - Any custom data - Stored as JSON.
  • channel_data - Other meta data collected while connecting to the bot from channels like Facebook.

Chat State Table​

Chat state table logs different stages of a conversation. The latest stage denotes the current state of the chat. Say a conversation has three entries in this table in chronological order (based on time field) like,

state - newChat

state - assignedChat

state - TransferChat

So the last state TransferChat is the current state of the chat - i.e. It is transfered from bot waiting for a human agent to pick up.

  • time - Time at which the chat state event occured.
  • chat_id - Unique Id of the chat.
  • agent_id - Agent Id (currently handling agent be it bot or human agent).
  • state - State of the chat while making this entry. It is triggered by bot. If it was a transfer event - It checks whether the event was triggered by the chatbot or human agent can be known with help of this boolean field.
  • custom_fields - Any custom data (JSONB).
  • bot_handle - Unique Identifier of the bot belonging to organisation.
  • id -
  • group_id - Group to which the chat belongs to.

Bot Messages Table​

Details in regards to the bot messages in a conversation are stored here. Bot is considered as another agent.

  • time - Time of the bot message.
  • chat_id - Unique Id of the chat in which the bot replied to.
  • mid - Unique Id of the message that the bot sent.
  • is_flow_response - Boolean field to know whether the response was from flow reply.
  • is_qna_response - Boolean field to know whether the response was a QnA reply.
  • from_id - Unique ID that corresponds to any agent making the reply (bot agent Id here).
  • flow_name - If the reply was from flow (is_flow_response would be true) name of the flow would be here.
  • node_name - Name of the node from which the message was sent would be here.
  • bot_handle - Unique Identifier of the bot belonging to an organisation.

User Message Details​

Details in regards to the users messages in a conversation are stored here.

  • time - Time when the user messages.
  • chat_id - Unique Id of the chat in which the user is interacting.
  • mid - Unique Id of every message that the user sends.
  • is_handeled - Boolean field that lets you to know whether this user message was handled by the bot/agent.
  • handling_agent - Agent currently handling this chat (can be bot/agent).
  • from_suggestion - Boolean field tells whether the user message was from the suggestion displayed in the chat widget.

User Activities​

Details in regards to the users activities in a chatwidget are stored here. Eg.: A user can minimize/maximize the chat widget. Events like that are logged here.

  • _id
  • time - Time of the user's event.
  • client_id - Unique Id used to connect to the MQTT server.
  • channel - Channel in which the user interacts. Eg.: Web Widget , Whatsapp etc.
  • event_name - Name of the event the user did. Eg.: widgetMaximize, widgetMinimize etc.
  • error - Any error message for the particular activity.
  • bot_handle - Unique Identifier of the bot belonging to organisation.

Event Metrics​

  • time - Time of the event occurred.
  • user_id - Id of the user - (Got during the initialization of Chatbot).
  • chat_id - Unique Chat Id of the conversing user.
  • bot_handle - Unique Identifier of the bot belonging to organisation.
  • event_name - JSONB value that contains the event name.
  • error - JSONB value that contains other information regarding the event.

Agent Activities​

It comprises of all the agents activities. Eg.: logging in, logging out, and changing statuses from available to unavailable all these are logged here.

  • time - Time of the agent activity.
  • agent_id - Unique Id of the agent.
  • session_id - Session Id.
  • event_name - Name of the event - such as loggedIn and loggedOut.
  • custom_fields - Any custom data - Stored as a JSON.
  • chat_id - Chat Id of the activity that is related to the chat .

A8Bot NLP​

All the metrics related to chatbots natural language processing are logged here.

  • basicNLP : An algorithm that returns the intent of query without using NLP services such as LUIS.
  • mid - Middle of the user's message.
  • time - Time of the user's message.
  • basic_nlp_handled - Boolean to know whether basicNLP handled it.
  • nlp_provider - Name of the NLP provider. Eg.: LUIS
  • intent - Intent of the user's message.
  • intent_score - score from LUIS for the intent message.
  • entities - List of entities for that particular intent.
  • is_spellcheck_hit - Boolean value to know whether spell check was called.
  • spellcheck_status - Status of spell check response.
  • spellcheck_resp_time - Spell check response's time.
  • is_nlp_hit - Boolean to know whether LUIS was called.
  • nlp_status - LUIS response status code.
  • nlp_resp_time - Time taken by LUIS to respond.
  • error - Error message if any from NLP service.
  • altered_query - Boolean value when the user query was altered by the spell checker.
  • bot_handle - Unique Identifier of the bot belonging to an organisation.
  • event_triggered -

Agent Sessions​

Any new login session into the Livechat Console session is logged in here.

  • time - Time at which an agent logged in.
  • name - Name of the agent.
  • agent_id - Unique Id of the agent.
  • session_id - Session Id corresponding to one browser session.
  • groups - Groups to which the agent is assigned to.
  • timezone - Timezone in which the agent has logged in.
  • os - Operating System of Agent using the Livechat Console.
  • lang - Language of the device.
  • browser_name - Name of the browser that the agent uses to access the console.
  • browser_version - Browser Version Number.
  • device_name - Device Name of the Agent accessing the Livechat Console.
  • screen_size_width - Width of the screen in the device used by the user.
  • screen_size_height - Height of the screen in the device used by the user.
  • custom_fields - Any custom data - Stored as a JSON.
  • ip_address - IP address of the agent device.
  • email - Email address of the agent logging in through the Livechat Console.

Agent Message Details​

Details in regards to the Agent messages in a conversation are stored here.

  • time - Time of the agent's message.
  • mid - Unique Message Id of the agent's Message.
  • agent_id - Unique Id of the agent sending the message.
  • session_id - Logged In session Id of the agent.
  • chat_id - Chat Id to which the agent sends the message to .
  • assist - When agent made use of the assist feature (Bot helps agent with a query).
  • assist_passed - When assist was passed.
  • assist_edited - When assisted message was edited by the agent.
  • pilot_mode - When Auto Pilot mode is on .
  • pilot_passed - When message was from bot pilot mode.
  • is_faq - When the agent reply was a faq.
  • faq_id - If it was a faq, what was the id of the faq.
  • word_count - Word count of the agent's messsage.

Messages​

This table consist of the actual messages of all the participants in the conversation.

  • time - Time of the message.
  • chat_id - ChatId of the message.
  • mid - Unique message Id to identify a message.
  • msg_type - Type of the message whether it is a text/HyperLink/Attachments.
  • reply_to_id - Reply to another message - Empty if its the first message.
  • bot_handle - Unique Identifier of the bot belonging to an organisation.
  • msg_from - States whether the message was from user/bot /agent.
  • resp_time - Response time of the message.
  • msg - Actual message in the conversation stored as a JSON.