mis report
Summary Doc
Type of Report -
Standard / Brand-Specific
Purpose of the Report -
A Management Information System (MIS) for the live chat team audit is a structured and automated system designed to collect, process, and present relevant information regarding the performance and activities of the live chat team. It serves the purpose of auditing and evaluating the quality, efficiency, and effectiveness of live chat interactions managed by individual agents or the team as a whole. The report we have created to check various aspects of agents that are handling different types of chats while conversation.
The report is divided into two parts :
Chat Dump Report - This is a channel wise dump report consisting of
all session wise dump data.
Chat summary Report - This is a summary report for all the above
mentioned dump data.
Frequency of Report : This is an hourly report which is sent with the cumulative data. We also have an EOD Report for the same.
Detailed Description -
Dump Report:
| Name of Column | Description of Field | Where is the value fetched from |
|---|---|---|
| Session Created at BOT | Timestamp of session creation by BOT | sessionInfo collection from createdAt |
| Session Created at Agent | Timestamp when chat is pushed to queue | sessionInfo collect from inQueueTime |
| Session accepted or assigned at Agent | Timestamp when chat is accepted by agent | Agent Customer collection from createdAt |
| Session ended at Agent | Timestamp when session is ended by agent | Agent customer collection from endTime |
| Agent First Response | Timestamp of agent's first message | Agent customer collection from agentFirstResponseTime |
| Session lifetime BOT + Agent | Total session time which includes bot interaction and live chat interaction | sessionInfo and agent Customer collection botLifeTime = sessionBot - inQueueTime shortChat = sessionAgentStart - sessionAgentEnd Value = botLifeTime + shortChat |
| Session Life time Agent | Total time where chat was handled by agent | Agent customer collection. shortChat = sessionAgentStart - sessionAgentEnd |
| Session wait time Agent | Total waiting time till the agent assignment | sessionInfo Value = assignTime - inQueueTime |
| Agent - Stall count | When agent didn't reply in 15 sec | sessionInfo from stall.customer.stallCount |
| Agent - Stall Occurrences | Counter to determine the count for agent stall occurrences | sessionInfo from stall.customer.iterationCount |
| Customer - Stall count | Counter to determine the late reply of customer | sessionInfo collection from stall.customer.stallCount |
| Customer - Stall Occurrences | Counter to determine how many times customer stall have occurred | sessionInfo collection from stall.customer.iterationCount |
| Session Status | Indicator for session status such as disconnected, answered, etc. | sessionInfo collection from status |
| TNPS Trigger | Indication to check whether tnps is triggered | sessionInfo collection from endConversation |
| TNPS last Shown | Timestamp of last tnps triggered to user | sessionInfo collection from endTime |
| Message ID | Unique id of customer (PSID) | sessionInfo collection from psid |
| Interaction ID | Unique id of chat session (sessionId) | sessionInfo collection from sessionId |
| Mobile | Phone number of user (msisdn) | sessionInfo collection from userInfo.phone_no |
| LOB | Key to identify segments of users. For example: prepaid or postpaid | sessionInfo collection from userInfo.subscriptionType |
| Agent Login name | Name of agent including firstname and lastname | AdminUsers collection from Agent Login name |
| Agent Psudeo Name | Pseudo name of agent | Agent customer collection from agentPseudoName |
| Circle | Code to determine location of number | sessionInfo collection from userInfo.DXLcircleID |
| Media | Indicator to know channel of user | chatSessions collection from log_info.query.channelName |
| Alt Contact No Query | Alternate contact number of users. | sessionInfo from customAgentNotes.alternateNumber |
| Browser | Browser information | chatSessions collection from log_info .platform_info.name |
| OS Name | Operating system information | chatSessions collection from log_info.platform_info.os.family |
| OS Version | Version of operating system | chatSessions collection from log_info.platform_info.os.version |
| Customer Name | Name of customer | sessionInfo collection from userInfo.customer_name |
| Queue | Name of queue where chat is pushed (customer group) | sessionInfo collection from userInfo.customerGroup |
| Chat Disconnection | Reason for chat disconnection | sessionInfo collection from disconnectionReason |
| Company | Company details | sessionInfo collection from userInfo.company |
| Callback | Indicator if callback is required for this session | sessionInfo collection from blank |
| Redirected to App | Indicator if user is redirected to app | sessionInfo collection from redirectedToApp |
| Escalate | Indicator of chat escalation | sessionInfo collection from Escalate |
| AON in Months | Age of network | Admin Users collenction from goLiveDate |
| Recommendation | Feedback scores for recommendation in tnps feedback | sessionInfo collection from feedback.recommendation |
| Satisfaction | Feedback scores for satisfaction in tnps feedback | sessionInfo collection from feedback.adviserRating |
| Ceis | Feedback scores for satisfaction in tnps feedback | sessionInfo collection from feedback.ceis |
| Verbatim | Feedback comment in tnps feedback | sessionInfo collection from feedback.feedback |
| IP address | Ip address of user | Chatsessions collection from log_info.ip_addr |
| Agent Con Cap | Concurrency of agent | adminUsers collection from chatConcurrency from preChatQuery1 |
| Query Type from Pre chat Form 2 | Events before livechat transfer | sessionInfo collection from preChatQuery2 |
| Query Type from Prechat Form 3 | Events before livechat transfer | sessionInfo collection from preChatQuery3 |
| Tranfer - P2P | Flag for identify if transfer is due to promise 2 pay | Agent customer collection |
| Direct or BOT - Chat | Flag to identify transfer type | sessionInfo collection from transferType |
| Short Chat - \< = 60 Secs | Flag to identify short interaction on livechat | Agent customer collection from createdAt and endTime shortChat = endTime -createdAt <= 60 sec |
| AHT Slab \< 3 \ 3 to 5 \ 5 to 10 \ 10 to 15 \ 15 to 20 \ 20 to 30 \ > 30 | Slab data to know AHT(average handling time) | Agent customer collection from createdAt and endTime According to time in above column slabs are decided |
| LTR | Feedback Scores | sessionInfo collection from feedback.recommendation |
| ASAT | Feedback Scores | sessionInfo collection from feedback.adviserRating |
| FTF | Feedback Scores | sessionInfo collection from feedback.ceis |
| Unique / Repeat | Flag to identify customer type | sessionInfo collection |
| Unique Sub / Month | sessionInfo collection | |
| Agent Status - Chat Ended | Agent status while chat ending. Eg : active, busy etc. | agentCustomer collection from agentStateDuringClosure |
| Agent Con - Accepted | Concurrency after agent have accepted the chat | agentCustomer collection from agentConcurrencyDuringAcceptance |
| Agent Con - Closed | Concurrency after agent have closed the chat | agentCustomerCollection from agentConcurrencyDuringClosure |
| Agent Stall Time | Total time agent went for stall | Chat Logs collection Compute average time for this. |
| Response - Total | Total number of responses of a particular interaction | Chat Logs collection |
| Customer Response - Total | Total number of responses of customer | Chat Logs collection |
| Agent Response - Total | Total number of responses of agent | Total of agent canned response and agent free |
| Agent Response - Canned | Total number of responses in which agent used canned responses | sessionInfo collection from cannedResponseUsed |
| Agent Response - Free Text | Total number of responses in which agent used free text | sessionInfo collection from freeTextUsed |
| Outcall | Flag to know if agent have called the user when requested for callback | sessionInfo collection from blankInfo |
| Outcall Status | Flag to know if users have met the TAT. | sessionInfo collection from blankInfo |
| blank Check Time | Timestamp when agent have flagged the interaction for callback | sessionInfo collection from blankInfo |
| blank Uncheck Time | Timestamp when the agent has called the user . | sessionInfo collection from blankInfo |
Summary Report:
| Name of Column | Description of Field | Where is the value fetched from |
|---|---|---|
| Queue | Name of queue | sessionInfo collection from userInfo.customerGroup |
| Chat Offered | Total no of inqueue chats | sessionInfo collection from size of pushedToQueueFromBot post grouping by customer group |
| Chat Answered | Total no. of chats answered | status field in sessionInfo collection. (Grouped by CG). |
| Chat Abandoned | Total no. of chats abandoned (chats greater than 15 sec that is disconnected by customer) | sessionInfo collection.(Grouped by CG). |
| Chat Customer Disconnects | Total no. of chats where chat got disconnected by customer (chats less than 15 sec that is disconnected by customer). | status field in sessionInfo collection. (Grouped by CG). |
| Chats Rerouted to BOT | Total no. of chats that were rerouted to bot due to inqueue time expiration. | status field in sessionInfo collection. (Grouped by CG). |
| Blank status | No of chats where agent joined but did not messaged | status field in sessionInfo collection (Grouped by CG). |
| SL% | Percentage of session where agent have joined within service limit | sessionInfo collection |
| Chat Answered within SLA | Count of chats that were answered within service limit | sessionInfo collection by computing slcount if (slvalue1 < 15 && slvalue1 !== 0) { slCount++ } |
| Short chats < 60 secs | Count of chats where chat was short | sessionInfo collection from shortChat |
| ACHT | Total average chat handling time for a queue | sessionInfo collection aht = ahtAgent / ahtCount; ['ahtAvg'] = timestamp(aht) |
| ACHT Slab n Mins | Slab details of acht for a group | sessionInfo collection sessionInfo collection if (aht < 180000) { ele['ahtSlab'] = '< 3 ' } else if (aht >= 180000 && aht < 300000) { ele['ahtSlab'] = '3 to 5' } else if (aht >= 300000 && aht < 600000) { ele['ahtSlab'] = '5 to 10' } else if (aht >= 600000 && aht < 900000) { ele['ahtSlab'] = '10 to 15' } else if (aht >= 900000 && aht < 1200000) { ele['ahtSlab'] = '15 to 20' } else if (aht >= 1200000 && aht < 1800000) { ele['ahtSlab'] = '20 to 30'; } else if (aht >= 1800000) { ele['ahtSlab'] = '> 30 ' } |
| TNPS Trigger | Total count of tnps trigger for a group | sessionInfo collection Computed as below : const tnpsTrigger = e.tnpsTrigger.filter(v => v).length; |
| TNPS Response | Total count of tnps response for a group | sessionInfo collection Computed as below : const tnpsResponse = e.tnpsResponse.filter(v => v).length; |
| LTR | Feedback scores from tnps of a queue | sessionInfo collection . ltr: { $divide: [{ $subtract: ["$promoterCount", "$detractorCount"] }, "$totalCount"] } |
| ASAT | Feedback scores from tnps of a queue | sessionInfo collection asat: { $divide: [{ $subtract: ["$promoterCount", "$detractorCount"] }, "$totalCount"] }, |
| FTF | Feedback scores from tnps of a queue | sessionInfo collection ftf: { $divide: ["$promoterCount", "$divisor"] }, |
| First time customer count | Total of first time customer count | sessionInfo collection ele['slab0'] = res5[key]['count0'] + ' ' + res5[key]['uniSlab0']; ele['slab1'] = res5[key]['count1'] + ' ' + res5[key]['uniSlab1']; ele['slab2'] = res5[key]['count2'] + ' ' + res5[key]['uniSlab2']; ele['slab3'] = res5[key]['count3'] + ' ' + res5[key]['uniSlab3']; ele['slab4'] = res5[key]['count4'] + ' ' + res5[key]['uniSlab4']; |
| Unique Customer | Total number of unique customers in a queue | brandUsers Collection and sessionInfo collection |
| Same Day repeat Subs | Same customer Same query | brandUsers Collection and sessionInfo collection |
| Sum of Same day Repeats Chats | Same customer different queries | brandUsers Collection and sessionInfo collection |
| 7 day repeat Subs | Same customer Same query for 7 days | brandUsers Collection and sessionInfo collection |
| Sum of7 Day repeat Chats | Same customer different query for 7 days | brandUsers Collection and sessionInfo collection |
| Media | Total count of various media like android , website etc | Sessioninfo collection Computed by creating a media array of platforms. |
| WEB | Total count of sessions where session is created on website | Sessioninfo collection Computed by creating a media array of platforms. |
| Mobile App | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a media array of platforms. |
| Browser - IE | Total count of sessions where session is created on internet explorer | Sessioninfo collection. Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Edge | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Chrome | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Mozilla | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Safari | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Android | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - IOS | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Browser - Windows | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| WEB Others | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Mobile App Others | Total count of sessions where session is created on mobile app | Sessioninfo collection Computed by creating a browser array of chat.log_info.platform_info.name |
| Concurrency Chat Accept | Percentage of agent utilisation in while chat acceptance | Agent customer collection and adminUsers Avg chatconcurrency during acceptance/ avg chat concurrency from adminUser |
| Concurrency Chat Closed | Percentage of agent utilisation in while chat closure | Agent customer collection and adminUsers Avg chatconcurrency during acceptance/ avg chat concurrency from adminUser |
| Blank | Count of session where blank is selected | sesssionInfo collection |
| Agent Stall Time | Total time spent in agent stall | Chatlog and sessionInfo collection adding all agent stall of specific group element['agentStallTime'] = timestamp(finalAvg) |
| Agent Stall Count | Agent not 60 sec | sessionInfo collection. We compute total stall count as below $group: { _id: '$group', totalStall: { $sum: "$stallCount" }, totalIteration: { $sum: "$iterationCount" } } |
| Agent Stall Occurrences | How many times agent stall happened | sessionInfo collection. We compute total stall occurance as below $group: { _id: '$group', totalStall: { $sum: "$stallCount" }, totalIteration: { $sum: "$iterationCount" } } |
| Customer Stall | Customer not replied in specific time | sessionInfo collection\ |
| Customer Stall Occurrence | How many time customer stall happened | sessionInfo collection\ |
| Agent First Response - Average | Total average time of agent first response in a queue | Session Info collection and agent customer collection We compute average agent first response time { $addFields: { firstResponse: { $subtract: ['$collection.agentFirstResponseTime', '$collection.createdAt'] } }}, { $group: { _id: '$userInfo.customerGroup', response: { $push: '$firstResponse' } } }, |
| Customer wait Time -Average | Average wait time for customers in a queue. | sessionInfo collection\ |
| Customer wait Time - Max | Maximum wait time for a customer in queue | sessionInfo collection waitTimeMax = (Mathmax.apply(Math, waitArr)) |
| Customer wait Time - Min | Minimum wait time for a customer in queue | sessionInfo collection. waitTimeMin = (Math.min.apply(Math, waitArr)) |
| Query Type From Prechat Form Level 1 | Last query data | sessionInfo collection Computed by creating a set of all prechat1 values in a CG. |
| Query Type From Prechat Form Level 2 | Last query data | sessionInfo collection Computed by creating a set of all prechat2 values in a CG. |
| Query Type From Prechat Form Level 3 | Last query data | sessionInfo collection Computed by creating a set of all prechat3 values in a CG. |
| Occupancy | Occupancy of agents in queue | Admin collection. count = ((value.count element.overallAHT) / (element.hoursRequired 3600000)); element['occupancy'] = parseFloat(count).toFixed(2) |
| Net Abandon% | Total net percentage of abandoned chats | sessionInfo collection netAbandon = (abandoned + rerouted) / offeredCount1 |
| Total Agent logged in | Total count of agent logged in | Agent analytics |
| Total Agent logged in handled chats | Total count of agent logged in that was handling the chat | Agent analytics collection and admin Users collection |
| Agent Logged in Hrs required | Total logged in hours of agent that are required | Agent analytics collection and admin Users collection |
| Agent Logged in Hrs - Actual | Total logged in hours of agent that are actual | Agent analytics collection and admin Users collection |
| AHT Chats handled - Active | Total agent handling time with active state | sessionInfo and agent customer collection |
| AHT Chats handled - (1st & 2nd Break/Out for Lunch & Diner) | Total agent handling time with (1st & 2nd Break/Out for Lunch & Diner) state | sessionInfo and agent customer collection |
| AHT Chats handled - (Busy) | Total agent handling time with busy state | sessionInfo and agent customer collection |
| Response - Total | Total count of responses in an interaction in a queue | Chatlog , agent customer and sessionInfo collection |
| Customer Response - Total | Total count of customer response in a queue | Chatlog and sessionInfo collection |
| Agent Response - Total | Total count of agent responses in a queue | Chatlog , sessionInfo collection |
| Agent Response - Canned | Total count of agent responses where agent have used canned responses for a queue | sessionInfo collection from $d.cannedResponseUsed |
| Agent Response - Free Text | Total count of agent responses where agent have used free text for a queue | sessionInfo collection $d.FreeTextUsed |
Example of Report -
Link of Report : MIS Report-Original
Report Format -
branch voda-uat-final
BrainService/brand-files/Reports/MISReport.js
Aggregation -
voda-uat-final
BrainService/brand-files/Reports/Chat/ChatReport.js
BrainService/brand-files/Reports/Chat/chatMessageSummary.js