UCCE 7.5 how to get BHCA and Average Call Handle time?

[Nguyen Quang Son]

Hi everybody,

I'm trying to do an audit of the existing UCCE system.

Can someone please tell me how to get the BHCA and Average Call Handle time for agent?

I did a SQL query against the CallTypeHalfHour table for all the entering Call Type but I'm not sure that's the right BHCA.

Thanks in advance,

Best Regards,

[geoff]

The best way to get the BHCA is to first plot the calls per second against the time for the entire day and locate your busy hour. Now you can integrate over that 1 hour and find the number of calls that arrived - ergo, BHCA.

You can get the CPS every (say) five minutes from the Logger using the Logger_Meters table, getting the output as a "grid", and draw your graph.

Here is a query for today - adjust the dates to suit.

SELECT Time=CONVERT(char,DateTime,108), CPS=CONVERT(decimal(5,2), RouteCallDetailTo5/300.0)

FROM Logger_Meters

WHERE DateTime BETWEEN '02/01/11 00:05' AND '02/01/11 23:59'

ORDER BY Time

Average handle time is a simple query on the TCD - check the Schema Guide.

Regards,

Geoff

[geoff]

To the original poster:

Did you try this out?  Just a drive-by? I gave you a really good answer.

Regards,

Geoff

[Shalid Kurunnan Chalil]

Dear Geoff,

we tried above query and didnt get any result.

SELECT Time=CONVERT(char,DateTime,108), CPS=CONVERT(decimal(5,2), RouteCallDetailTo5/300.0)

FROM Logger_Meters

WHERE DateTime BETWEEN '09/10/13 00:05' AND '09/10/13 23:59'

ORDER BY Time

Tried from Logger, AWDB and even HDS.

our UCCE version is 8.5.4.

Please request your help on this.

Regards,

Shalid K.C

[venkatesh]

Hi Shalid,

Are you able to see the table in the logger. If yes, are you able to see some data (you can use query like select * from

Logger_Meters or top 10).

This will let you know, whether the query has problem.

[Shalid Kurunnan Chalil]

Thank you Venky,

i could see the datas and i modified the query.

now i have the data which is required for me.

i believe if i take the average of complete CPS value i will get eh cps which i am looking here.

Thank you Venky for the clue.

Regards,

Shalid K.C

[Shalid Kurunnan Chalil]

just change the date time format.

SELECT DateTime, CPS=CONVERT(decimal(5,2), RouteCallDetailTo5/300.0)

FROM Logger_Meters

WHERE DateTime BETWEEN '2013-10-09 00:00' AND '2013-10-09 23:59'

ORDER BY DateTime desc

Regards,

shalid K.C

[Nguyen Quang Son]

Hi Geoff,

Thank you very much for your reply.

I'm terribly sorry for the late. I've been off site for a moment.

Hey, excellent solution. I gave it a shot and it worked like a charm. Never thought of using the Logger_Meter table. Great !

And for those who dont know, we can also enable the ICM Perfmon to to log the calls/sec values. It gives BHCA as well

And about the AHT, thanks to your suggestion, I found this - it's truly a simple query against the Skill_Group_Half_Hour table.

http://www.cisco.com/en/US/products/sw/custcosw/ps1001/products_tech_note09186a0080094b0f.shtml

Hope this helps,

Best Regards,

Son NGUYEN,

[Nguyen Quang Son]

Hi again Geoff,

Before closing this thread, I'm curious about your query against TCD for average handle time.

Here's mine using Skill_Group_Half_Hour

SELECT

    SUM(HandledCallsTalkTimeToHalf) AS TalkTime,

    SUM(IncomingCallsOnHoldTimeToHalf) AS HoldTime,

    SUM(CallsHandledToHalf) AS HandledCalls,

      AHT =

            CASE

            WHEN SUM(CallsHandledToHalf) > 0 THEN (SUM(HandledCallsTalkTimeToHalf) +SUM(IncomingCallsOnHoldTimeToHalf)) / SUM(CallsHandledToHalf)

            ELSE 0

            END

FROM t_Skill_Group_Half_Hour

WHERE DateTime between '02/01/11 00:00' and '02/28/11 23:30'

Besides, can you please also tell me how to get the Average Waiting Time (time before get answered by an agent) ?

Thank you in advance, Geoff,

Best Regards,

[geoff]

Thanks for psoting your SQL query. I'll try it out.

For call waiting time before delivery to agent, your best bet is to query the Route_Call_Detail table. It will give you exactly what you need.

Regards,

Geoff

[Nguyen Quang Son]

Hi Geoff,

Are you cibling the RouterQueueTime?

I'm not sure I understand what Schema Guide says: "Number of seconds the call was held in the CallRouter queue"

Does this include the time that call is queued in the IVR? I'm really confused between

- RouterQueueTime from RCD

- LocalQTime from TCD

- NetQTime from TCD

Can you please explain briefly their relation?

I suppose to measure the call waiting time, the better bet is TCD as their we have records of calls that arrive at the agent. RCD only gives you calls that are routed.

Correct me if I'm wrong.

Thanks,

[geoff]

The RCD is going to start the clock at the moment the call first arrives on the dialed number and makes a route request, and then stops the clock once the route has been completed. Either the route is completed or the call is abandobed by the customer.

Isn't that what you want?

Regards,

Geoff

[Nguyen Quang Son]

By "the route has been completed", you meant the call is completely answered by the agent?

I did a little query from the RCD and all I found is routes to IVR.

I want to calculate the average wait time for answered call only. I'm thinking of Sum(RouterQTime)/Number of calls answered by agent.

Regards,

[geoff]

I mean delivered to the agent - the Call Router has done its job.

Abandoned calls in the RCD are easy to eliminate - the RouterErrorCode is 448.

The IVR is queuing the call, waiting for an agent who is located by ICM - correct? That time is there. Study the table. For routing, it has all you need. The TCD is for other things, in my opinion.

Regards,

Geoff

[anwar-ahmed]

Hi Geoff

Can we create this query for HDS due to unavailability of data in Logger database. Thanks in advance