cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
3916
Views
5
Helpful
27
Replies

CUIC 9.x - Measuring AHT combined with Not Ready code

roberteliasson
Beginner
Beginner

Hello everyone,

We went live with UCCE 9.x a week ago, and I´m having some difficulties with measuring AHT in a correct way.

Right now, when our agents needs to do some after call-work, they put themselves on Not Ready with a reason code called "After call work".

When they´re done with that - they put themselves back on Ready.

But this reason code is never calculated into the ACW.

How do you report your AHT?

I´ve tried to combine the stock query"Agent Team Historical All Fields" with the query from "Agent Not Ready Detail" but I just can´t get it sorted. I would really appreciate some help here if this is possible.

Thanks a lot in advance!

Regards,

Robert

1 Accepted Solution

Accepted Solutions

Hmm, I think I was looking at that JOIN wrong. You should have a "RIGHT JOIN" instead of the "LEFT OUTER JOIN". That should take all appropriate rows from Agent_Skill_Group_Interval, and join them with any matching rows from the AED summary.

As an alternative, if you wanted to keep all of the JOINs as LEFT JOIN (probably an easier design to read), you can do this:

SELECT

Agent_Team.EnterpriseName as TeamName,

(Person.FirstName + ' ' + Person.LastName) as FullName,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as AHT,

ASGHH.LoggedOnTime as LoggedOnTime,

PerReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0 / ASGHH.LoggedOnTime, 0),

TimeReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0, 0),

PerNotReady =  ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime) / ASGHH.LoggedOnTime,

NotReadyTime = ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime)

FROM Agent_Skill_Group_Interval ASGHH

LEFT JOIN (SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID

) AED

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Team_Member

ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team

ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

GROUP BY

AED.Duration, Agent.EnterpriseName, ASGHH.CallsHandled, ASGHH.HandledCallsTime, ASGHH.LoggedOnTime, ASGHH.BusyOtherTime, ASGHH.WorkNotReadyTime, ASGHH.WorkReadyTime, ASGHH.ReservedStateTime, ASGHH.AvailTime, ASGHH.TalkInTime,  ASGHH.HoldTime, ASGHH.NotReadyTime, Person.FirstName, Person.LastName, Agent_Team.EnterpriseName, Agent_Team_Member.AgentTeamID


- Jameson

-Jameson

View solution in original post

27 Replies 27

Geevarghese Cheria
Cisco Employee
Cisco Employee

Hi Robert,

     %AHT (Average Handle Time Percentage) = Sum of Talk Time / Total number of Calls / interval length (in seconds) * 100

I would request you to refer Hold tim in Agent & Agent Skil group reports - Community - Cisco Developer Community

for related information.

Thanks and Regards,

Geevarghese

Hi Geevarghese,

Thanks for your reply - I appreciate it!

I´m aware of AHT is calculated as default - but we really require to merge the default AHT with Not Ready reasoncode 71.

The reason why is that we want to know how much the total AHT is, including the not ready state where the agent had to use the Not Ready state '71' to finish the case.

Thanks again!

Jameson Gagnepain
Rising star
Rising star

Robert,

What is your reasoning for not using the built-in wrapup mode? Wrapup time would be much simpler from a reporting standpoint than a specific Not Ready reason code, as it's already included in stock AHT reporting.

-Jameson

-Jameson

Hi Jameson

When selecting "Work not ready" we are automatically set to "Not ready" with reasoncode [0] Undefined.

So after the set (in our case) 20 seconds wrapup, the state changes to Not ready..

Is there any function we are missing here? I would really appreciate an explanation on how it should work, and I´ll take it with our Cisco team.

Thanks!

Robert,

How are you selecting "Work not ready"? What agent desktop are you using?

There are two types of wrapup:

  • Work Ready - automatically applied after finishing a call that started while "Ready". After the Work Ready state, agent will automatically go back to Ready.
  • Work Not Ready - automatically applied after finishing a call that started while "Not Ready". After the Work Not Ready state, agent will automatically go back to Not Ready.

-Jameson

-Jameson

Hi Jameson,

We´re using CAD 8.5.4.33.

With the current set up we have, unfortunately I dont see any other option right now than to include the ReasonCode 71.

Would it be hard to get this figure into the "Agent Team Historical All Fields" report definition?

Thanks!

Robert

Robert,

I've personally never had a chance to use CAD, but looking at the user guide page 21, I see this:

http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/crs/express_8_5/user/guide/cad85ccxug…

Work Ctrl+Y

Changes your state to Work, indicating

that you are in wrap-up work after

terminating a call and, when finished, will

be available to receive routed calls

As I outlined before, the system should only put an agent in Work Not Ready if they were Not Ready before the call. If an agent was Ready before the call, the system should put them in Work Ready when they select the Work button, which would automatically go back to Ready after wrap-up time is up.

Adding calculations around Reason Code 71 is certainly possible if you have access to editing Report Definitions. You would have to grab data from the Agent_Event_Detail table. The thing that makes this more difficult, is you would likely have to write the SQL as an anonymous block, not a normal query. This is largely due to the need to summarize data from the Agent_Event_Detail table before joining it with the various tables in the Agent Team Historical All Fields report.

If you've got an issue with Work Ready mode functioning, I would endeavor to correct that... perhaps an issue for TAC.

-Jameson

-Jameson

Jameson,

Thanks for your reply!

I get your point - but the only workaround I see is to create a custom report definition.

I´ve started with it but it shows really strange figures for "Calls handled" - anyone who want to help me with this?

SELECT

   A.EnterpriseName,

  aed.SkillTargetID,

(t9.CallsHandled * 1.0) as CallsHandled,

  aed.LoginDateTime,

  Event = CASE Event WHEN 1 THEN 'Login (1)' WHEN 2 THEN 'Logout(2)' WHEN 3 THEN 'NotReady (3)' END ,

  EventStart = DATEADD(ss,-Duration,aed.DateTime),

  EventEnd = aed.DateTime,

  aed.Duration,

  Reason = rc.ReasonText + '(' + CONVERT(varchar(10),rc.ReasonCode) + ')',

  aed.ReasonCode

FROM Agent_Event_Detail aed

LEFT OUTER JOIN Reason_Code rc on aed.ReasonCode = rc.ReasonCode

JOIN Agent A on aed.SkillTargetID = A.SkillTargetID

join Agent_Skill_Group_Interval t9 on t9.SkillTargetID = aed.SkillTargetID

WHERE  aed.SkillTargetID IN (:AgentSkillTargetID)

and aed.DateTime >= :startDate

and aed.DateTime < :endDate

and aed.ReasonCode = '71'

group by

aed.LoginDateTime,

t9.CallsHandled,

rc.ReasonCode,

aed.ReasonCode,

rc.ReasonText,

aed.Duration,

aed.DateTime,

aed.Event,

CONVERT(VARCHAR(10), aed.DateTime, 120) ,

A.EnterpriseName

,aed.SkillTargetID,A.SkillTargetID

Robert,

You're likely getting a log of extra rows of data... you don't have anything limiting the DateTime range of Agent_Skill_Group_Interval, and you have nothing tying the Agent_Event_Detail records to your intervals either.

I don't understand why you're doing a GROUP BY statement, as you're not using any aggregate functions yet (SUM, MAX, COUNT, etc).

All the data in Agent_Event_Detail will need to be summarized in a separate query before joining with your other tables. Something along the lines of this:

SELECT

ColumnName1,

ColumnName2,

ColumnName3,

etc...

FROM

(SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

SkillTargetID,

SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Half_Hour ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN more tables

and so on...

Computing the Interval for AED is a little tricky... more information on why I did is as above here:

https://supportforums.cisco.com/discussion/12263946/ucce-cuic-interval-boundary-agent-not-ready

The calculation is for 30-minute intervals... if you use 15-minute intervals in your environment it will take a bit more work to get it set up for that.

You won't need any GROUP BY in the outer SELECT statement unless you're doing any further aggregation after joining with ASGHH or ASGI, for example if you were to make a version of the report definition that summarized by Day, Week, or Month.

-Jameson

-Jameson

Jameson,

Thank you so much for the code example, I really appreciate it!

I didn´t succeed to make it work though, see my query below.

I´m getting the following error message:

Create the parameters or correct the query syntax and recreate the parameters. No column was specified for column 3 of 'AED'.

SELECT

aed.Duration as NotReadyDuration,

(ASGHH.CallsHandled * 1.0) as CallsHandled

FROM

(SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

SkillTargetID,

SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Half_Hour ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID