Displaying Live Chat Statistics in the UI

1 like Updated 9/2/13 2:05 AM by LilianaOrozcoTags: None

In SmartCloud Control Desk 7.5.1 there are no out of the box reports or start center graphs for Live Chat but all the data that is needed is stored in the database and start center portlets and reports can be written and customized to get the data needed.  All you need to know is where the data is stored.

 

Live Chat uses two main database tables:  pmtcolivechat for waiting and in progress chat data and pmtcolivechatstats  to store completed chat data. The table pmtcolivechatstats needs to be treated as a historical table and should be periodically pruned to avoid taking up too much space.

 

The following tables describe the relevant columns for each of the database tables and the steps below describe how to create a start center template full of Live Chat data.

Live Chat Database Table: PMTCOLIVECHAT (Relevant Reporting Columns)

Column

Description

Type

Relationship to other DB Tables/Views

requesttime

Time the chat was requested

DATETIME

 

accepttime

Time the chat was accepted by an agent (null if it has not been accepted yet)

DATETIME

 

userid

User id of the user who requested the chat

ALN (String)

Maxuser (userid)

agentid

User id of the agent who accepted the chat (null if it has not been accepted yet)

ALN (String)

Maxuser (userid)

chatqueueid

Person group id of the chat queue the chat request got routed to

UPPER (Uppercased string)

Persongroup (persongroupid)

newsr

Whether a new SR was created due to the chat request (i.e the user did not select an existing SR when starting the chat)

YORN (Boolean)

 

 

status

Status of the chat request: WAITING if the agent has not accepted the chat yet or CHATTING if the agent has accepted it already

UPPER (Uppercased string) - Domain

 

ticketuid

Ticket UID of the SR the chat created or the user selected before starting the chat

BIGINT

Ticket, SR (ticketuid)

 

Live Chat Database Table: PMTCOLIVECHATSTATS (Relevant Reporting Columns)

Column

Description

Type

Relationship to other DB Tables/Views

requesttime

Time the chat was requested

DATETIME

 

accepttime

Time the chat was accepted by an agent (null if it has not been accepted yet)

DATETIME

 

endtime

Time the chat ended

DATETIME

 

userid

User id of the user who requested the chat

ALN (String)

Maxuser (userid)

agentid

User id of the agent who accepted the chat (null if it has not been accepted yet)

ALN (String)

Maxuser (userid)

chatqueueid

Person group id of the chat queue the chat request got routed to

UPPER (Uppercased String)

Persongroup (persongroupid)

waittime

Time (in seconds) the end user waited before an agent accepted the call or before exiting the chat application

INTEGER

 

chattime

Time (in seconds) the chat lasted before the agent closed their chat window.

INTEGER

 

timedout

Whether the chat timed out due to inactivity and was closed by chat server

YORN (Boolean)

 

abandoned

Whether the end user closed the chat window before an agent accepted the chat

YORN (Boolean)

 

numofchatsagent

 

Number of simultaneous chat windows the agent has opened at chat end time

INTEGER

 

ticketuid

Ticket UID of the SR the chat created or the user selected before starting the chat

BIGINT

Ticket, SR (ticketuid)

 

Step 1: Create two applications, one for each of the database tables.

Step 2: Create queries for the newly created applications to be used by the portlets in the new Live Chat start center template.

Step 3: Create KPI Graphs for Live Chat data to add them to the new Live Chat start center template.

Step 4 : Create a new start center template and add portlets with Live Chat data using the newly created queries and KPI graphs. 

 

 

Step 1: Create two applications, one for each of the database tables.

 

  1.  Go to System Configuration > Platform Configuration > Application Designer and click on the new icon to create a new application.

 

Give it the following parameters.

  • Application: CHATRUN
  • Description: Live Chat Runtime
  • Main Object: PMTCOLIVECHAT
  • Key Attribute: USERID
  • Module Name: SETUP

Select Power App as the type of application to create.

 

Click Save.

  1. (Optional) Customize the table to show more data.

 

To change the title of the table, right click on the TABLE TITLE and choose Properties.

Edit table properties

 

Change the value of the Label property and click on the X button of the dialog to close the dialog.

 

 

Add columns to the table by selecting a column, right clicking on it and then choosing Copy.

Copy a column

 

Right click on the column again and choose Paste.

 

 

The new column will be added to the table.

 

 

To edit the column properties, right click on the second User ID column and choose Properties.

Edit column properties

Click on the magnifying glass next to Attribute to change the attribute of the column.

 

 

Filter the Attributes by typing in PMTCOLIVECHAT in the Object text box and clicking Enter.

 

 

Choose another attribute, for example, REQUESTTIME. The label is automatically changed to the default label of the chosen attribute. Click on the X to close the dialog.

 

Repeat the steps to add others columns to the table. Remember to save frequently.

 

 

  1. (Optional) Delete the Main tab by clicking on it and clicking on the Delete Node icon. Save the application afterwards.

 

 

 

  1. Create the second application by clicking on the new icon again in the Application Designer application.

 

Give it the following parameters:

  • Application: CHATSTATS
  • Description: Live Chat Statistics
  • Main Object: PMTCOLIVECHATSTATS
  • Key Attribute: USERID
  • Module Name: SETUP

 

Select Power App as the type of application to create.

 

 

Click Save.

 

Modify the application the same way as the first one, modifying the table columns and deleting the Main tab. Remember to change the table name to PMTCOLIVECHATSTATS when looking for attributes.

 

 

  1. Log out and back in.
  2. To verify the new applications were added, Go to Administration in the App Menu. The two newly created applications should be there.

 

 

  1. Add the appropriate sigoptions to both applications by going to the Security > Security Groups application.  

 

Look for the group maxadmin or whichever group the Live Chat managers belong to.

 

Go to the Applications tab.

In the Applications, filter by description “Live Chat” and you should see the two new applications. Grant the following sigoptions to both applications.

  • Read Access
  • More Search Fields
  • Save Current Query
  • Sigoption to control the editability of the Clause field in the View/Manage Queries dialog
  • View/Manage Queries
  • Where Clause

     

 

Log out and back in for the sigoptions to take effect.

 

Step 2: Create queries for the newly created applications to be used by the portlets in the new Live Chat start center template.

 

In this example, two queries will be created. One for waiting chat requests and one for in progress chat requests. The queries needed will vary depending on business need.

 

  1. Go to Administration > Live Chat Runtime application.

 

Filter Chat Session Status with the text “WAITING” and press Enter.

 

Click on Magnifying Glass next to Find: and choose Save Current Query

 

 

Give it a name and description and click OK to save the query.

 

 

Repeat for CHATTING status to create a query for in progress chat requests.

 

 

  1. Go to the Administration > Live Chat Statistics application and create similar queries, for example, to query Abandoned Chat Requests, type Y in Abandoned? field, press Enter and select to save the current query.

 

Step 3: Create KPI Graphs for Live Chat data to add them to the new Live Chat start center template.

 

 

  1. Go to Administration > Reporting > KPI Manager and click on the New icon

 

 

Give it the following parameters:

  • KPI Name: WAITCHATS
  • Description: Live Chat Requests Waiting
  • Calculation Type: Decimal
  • Select:  select count(*) from pmtcolivechat
  • Where: where status in (select value from synonymdomain where maxvalue in ('WAITING') and domainid='PMTCOCHATSTAT')
  • Target: 5 (or 0)
  • Caution At: 25 (or other value that is appropriate)
  • Alert At: 50 (or other value that is appropriate)

 

 

 

  1. Repeat the steps to create the In Progress Chats KPI. 

 

  • Select: select count(*) from pmtcolivechat
  • Where: where status in (select value from synonymdomain where maxvalue in ('CHATTING') and domainid='PMTCOCHATSTAT')

 

 

Step 4 : Create a new start center template and add portlets with Live Chat data using the newly created queries and KPI graphs.

 

  1. Go to the Start Center (Home icon) and click on the Create New Template icon

 

 

 

  1. On the Left Column, add two KPI Graphs and Favorite Applications.
  2. On the Right Column, add two result sets.

 

 

 

  1. Click Finished to go back to the Start Center template.

 

 

  1. Customize each of the portlets by clicking on the edit icon for each. Let’s start with the top left hand one (KPI Graph) and make it the Waiting Chats KPI. The Edit button is not visible until you take your mouse and hover over the header area.

 

 

 

  1. Set Display Name to something like “Waiting Chats”.  Click on Select KPIs button to select the Waiting Chats KPI that was created in an earlier step. Click OK to dismiss the Select KPIs dialog and then Finished on the KPI Graph Setup to go back to the Start Center template.

 

 

 

  1. Edit the second KPI Graph and select the In Progress Chats KPI. Display Name can be something like “In Progress Chats”.

 

  1. Next, edit the Favorite Applications portlet and add the Person Groups and Service Request applications to it. Click Select Applications to search for and add the applications and then click Finished.

 

  1. For the first result set on the top right corner, let’s view the waiting chats per chat queue. Click the Edit button on that portlet. In the Available Queries tab, look for the Waiting Chat Requests and click on it to choose it.  In the Column Display tab, choose the columns to display, like REQUESTTIME and CHATQUEUEID.  In the Chart Options tab, check Show Chart by Default? and make sure Chart Type: is set to BAR. Set Display By to CHATQUEUEID.  Click Finished to return to the Start Center template. Remember to change the Display Name to something more descriptive, like “Waiting Chats Per Queue”.  Click Finished.

 

  1. For the last portlet in this example, let’s set it to the Query of Abandoned Chats per Chat Queue.  Click the Edit button on that portlet. In the Available Queries tab, look for the Abandoned Chats query and click on it to choose it.  In the Column Display tab, choose the columns to display, like REQUESTTIME, WAITTIME and CHATQUEUEID.  In the Chart Options tab, check Show Chart by Default? and make sure Chart Type: is set to PIE. Set Display By to CHATQUEUEID.  Click Finished to return to the Start Center template. Remember to change the Display Name to something more descriptive, like “Abandoned Chats”.  Click Finished.

 

 

 

  1. Voila! The Start Center template is complete!

 

Save the template by clicking on the Save icon.

  1. Last but not least.... Assign the template to the correct groups. For example, to add it to the maxadmin group, go to Security > Security Groups and look for maxadmin group. In the Group tab, click on the magnifying glass next to Start Center Template and choose the template you just saved to add it.