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

[roberteliasson]

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

[Geevarghese Cheria]

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

[roberteliasson]

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]

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

[roberteliasson]

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!

[Jameson Gagnepain]

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

[roberteliasson]

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

[Jameson Gagnepain]

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

[roberteliasson]

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

[Jameson Gagnepain]

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

[roberteliasson]

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

[Jameson Gagnepain]

Robert,

Try this:

SELECT

AED.Duration as NotReadyDuration,

ASGHH.CallsHandled,

ASGHH.SkillTargetID

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,

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)<>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

Looks like I forgot to name the SUM(Duration) statement.

-Jameson

[roberteliasson]

That made the trick! I'm now able to get some data!

It looks a bit strange though - it does not get nearly as close as many calls handled in this report as on any stock agent report. I assumed there must be something wrong with the intervals so I changed the query to 15 instead of 30 mins.

I changed the table where I get the calls handled and so on.. maybe it has to do with this?

Any ideas?

SELECT

Agent.EnterpriseName,

AED.Duration as NotReadyDuration,

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)

FROM

(SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15) 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) 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

LEFT JOIN Agent_Skill_Group_Interval ASGHH

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

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

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

Output example ( I will group it with summary only later, don\t need any details per interval)

[Jameson Gagnepain]

Robert,

Your Interval evaluation for 15 minutes is not quite right, try this instead:

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

Also, we should probably change the first join to an OUTER JOIN, as LEFT JOIN will only keep rows where your AED query has values. You'll have to add this to the outer query as well, otherwise there will be too much data after the JOIN change:

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

Your AHT, ATT, and ACW calculations are not going to work properly... I would rethink how you build them. Keep this in mind:

(A1/B1)+(A2/B2) IS NOT EQUAL TO (A1+A2)/(B1+B2)

You should SUM before division. I usually just save calculations like this for Formula Fields... it keeps the SQL query simpler, and makes it easier to build the custom footer for these fields (Never use a Sum or an Average footer for AHT and such).

-Jameson

[roberteliasson]

Jameson,

It seems like it still does not fetch enough data from the _ table.

Also I couldnt get the "WHERE" into the outer join, error message.

Thanks for the hint about the calculations - I´ll focus on this as soon as I know the query gets all the data

Here´s the query after the latest changes.

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 (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

LEFT OUTER JOIN Agent_Skill_Group_Interval ASGHH

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

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