PureConnect

 View Only

Discussion Thread View

  • 1.  Calculating Service Level per queue using data sourced from database

    Posted 08-22-2019 21:59
    Edited by Jayden Bradow 08-27-2019 19:03
    Hi All,

    I would like to see what the service level is per queue at various ASA targets (10, 20, 30, 40 etc.) by pulling data from the database as opposed to running a report through IC Business Manager.
    Does anyone have any suggestions as to the best tables/fields to use for this?

    I've looked at the table DQStatistics and can see there are fields called nAnsweredAcdSvcLvl, nAnsweredAcdSvcLvl etc but I am not sure what their service levels are. We have 8 service levels configured but this table only shows a total of 7.

    I've also researched extensively in the Help and Data Dictionary but am unable to find a solution.
    #Reporting/Analytics

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------
    ​
    Edit: Added AMA topic flag.


  • 2.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-22-2019 23:26
    Hi All,

    I have done some further research and have proceeded with using DQStatistics.
    I have found that the 6 service level fields represent 5, 10, 20, 30, 40, and 50 second targets.

    How can I change these?
    Can I add additional service levels or is it limited to 6?

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------

    Original Message


  • 3.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-23-2019 08:12
    Edited by Philip Last 08-23-2019 08:14

    I would suggest using the table InteractionSummary

     

    Assume it is inbound calls and not emails, chats etc., then limit with

     

    WHERE Direction = 1 AND ConnectionType = 1 AND MediaType = 0

     

    Add whatever fields you want (date etc.) then to get a field to allow you to gather your statistics (remember fields are milliseconds):

     

    CASE

    WHEN ([tQueueWait] + [tAlert]) = 0 THEN 'No queue'
           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] =0 THEN 'Abandoned' < - missed abandoned from original post.

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 5000 THEN 'Ans<5'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 10000 THEN 'Ans<10'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 15000 THEN 'Ans<15'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 20000 THEN 'Ans<20'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 25000 THEN 'Ans<25'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 30000 THEN 'Ans<30'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 35000 THEN 'Ans<35'

           WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] < 40000 THEN 'Ans<40'

           ELSE 'Ans>=40'

           END AS 'AnswerStatus',



    ------------------------------
    Philip Last
    Arvato Limited
    ------------------------------

    Original Message


  • 4.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-27-2019 18:57
    Hi @Philip Last

    Can you explain the use of tConnected in the example you provided?
    As far as I know, tConnected is total talk time?

    Cheers.

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------

    Original Message


  • 5.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-28-2019 02:58
    Good Morning

    I use tConnected as an indicator the call was connected. So if tConnected = 0 then the call was never passed to an agent.

    tQueueWait shows it was passed from IVR into the queue (for an agent on auto-answer). Similarly, tAlert, shows how long it tries to get an agents attention when they are on manual pickup.

    WHEN ([tQueueWait] + [tAlert]) = 0 THEN 'No queue'
    indicates the call never left the IVR (Closed or abandoned in IVR)

    WHEN ([tQueueWait] + [tAlert]) > 0 AND [tConnected] =0 THEN 'Abandoned'
    indicates the calls was in the queue, but there was no talk time so it was an abandoned call.

    The remainder of the query was simply wrong, I was thinking of something else. The tConnected time should be there only to indicate it was an answered call, the length does not indicate time to answer. Corrected query would be

    WHEN ([tQueueWait] + [tAlert]) > 0 AND ([tQueueWait] + [tAlert]) <5000 and [tConnected] > 0 THEN 'Ans<5'

    WHEN ([tQueueWait] + [tAlert]) >= 5000 AND ([tQueueWait] + [tAlert]) <10000 and [tConnected] > 0 THEN 'Ans<10' 

    WHEN ([tQueueWait] + [tAlert]) >= 10000 AND ([tQueueWait] + [tAlert]) <15000 and [tConnected] > 0 THEN 'Ans<15'

    etc., to include as many segments as you want.



    ------------------------------
    Philip Last
    Arvato Limited
    ------------------------------

    Original Message


  • 6.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-28-2019 18:16
    Hi @Philip Last,

    Thanks for clarifying, that all makes sense.

    I initially looked at using ​InteractionSummary to calculate service level per queue and doing something similar to what you mentioned (however I didn't think of including tAlert).
    InteractionSummary has 2 fields for showing what workgroup the interaction relates to: FirstAssignedWorkgroupID and LastAssignedWorkgroupID.
    In my early build I looked at using LastAssignedWorkgroupID however this means in the scenario where calls are answered in queue A and then transferred to queue B will not have any service level results for queue A as it was not the LastAssignedWorkgroup.

    Would you use the FirstAssignedWorkgroupID or LastAssignedWorkgroupID, and how would you look at the scenario I described above?
    Is the reporting of service level per queue in this way appropriate or not ideal in your opinion?

    Cheers.

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------

    Original Message


  • 7.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-27-2019 15:02
    Jayden,

    Are you familiar with the configuration section shown below, this is found in IA > Workgroups > Configuration Tab > click "Configure Service Levels..." button

    Not a lot of doucumenation on this topic that I could find.   The IA Help is a little more detailed than the link below.

    https://help.genesys.com/cic/mergedProjects/wh_ia/desktop/people/workgroups/workgroup_queue_service_level_configuration.htm

    ------------------------------
    Pete Schroeder
    TMP Direct LLC DBA TMP Direct
    ------------------------------

    Original Message


  • 8.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-27-2019 19:00
    Hi @Pete Schroeder

    Thanks for the reply.

    Yes I have seen this and I can see we have configured 8 service levels.
    I cannot for the life of me find any of these in the database in order to use them however :(​

    Perhaps this can be answered in the AMA @Chip Funk?
    ​

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------

    Original Message


  • 9.  RE: Calculating Service Level per queue using data sourced from database
    Best Answer

    Posted 08-27-2019 20:53
    Jayden,

    In my pervious post, the link I shared, states that you can have up to 14 ACDSvcLvls 

    I think some of your answers will come from the PureConnect Reporting Data Dictionary: https://help.genesys.com/cic/datadictionary/content/Landing.html
    The following is copied right from the data dictionary

    Workgroup Queue Statistics Interval View contains Service Levels 1-6

    Workgroup Queue Service Level Overflows View - contains Service Levels 7-14 
    With this release, more than six service level distribution buckets can be configured. These additional service level statistics or service level overflows are stored in a separate table apart from the underlying queue period statistics table. The underlying table is called DQServiceLevelOverflows. A view is introduced to include those buckets as well as the first, original six called DQServiceLevel_viw.

    And the same goes for the following:

    Statistics Group Queue Interval View
    Statistics Group Queue Service Level Overflows View
    Similar to Workgroup Queue Interval view, the additional service level statistics or service level overflows are stored in a separate table apart from the underlying queue period statistics table. The underlying table is called SGServiceLevelOverflows. A view is introduced to include those buckets as well as the first, original six: SGServiceLevel_viw.

    Hope this helps

    ------------------------------
    Pete Schroeder
    TMP Direct LLC DBA TMP Direct
    ------------------------------

    Original Message


  • 10.  RE: Calculating Service Level per queue using data sourced from database

    Posted 08-27-2019 22:13
    @Pete Schroeder​,

    You sir, are a God amongst men.

    We did not have those tables/views replicated to our reporting database so I was unaware of their existence.
    They are exactly what I was looking for.

    I must admit I do use the data dictionary quite a lot but somehow I did not notice those tables/views.

    Thanks so much!

    ------------------------------
    Jayden Bradow
    Service Performance Analyst
    Precision Administration Services (Pty) Ltd
    ------------------------------

    Original Message


Need Help finding something?

Check out the Genesys Knowledge Network - your all-in-one access point for Genesys resources