Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Community Member

Service Level calculation on UCCE 10.0

Hi guys,

I have been asked by one customer to calculate their SL for 20 seconds threshold for number of skill groups, for last 11 months,

without changing their current (default) measuring value (30 sec)!

 

Is this possible somehow?

Can anybody point me in the direction to resolve this?

 

P.S. I tried to revert SL value for one skill group, and than to execute one CUIC report but without success (I recived same SL % as before).

 

Thanks,

Lazar

Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions

Hi,this might be a good start

Hi,

this might be a good start:

DECLARE @dateFrom DATETIME, @dateTo DATETIME, @SLLimit INT
SET @dateFrom   = '2014-11-12 00:00:00'
SET @dateTo     = '2014-11-13 00:00:00'
SET @SLLimit    = 20
SELECT SUM(1) AS AllCalls,
SUM(CASE WHEN tcd.NetQTime <= @SLLimit THEN 1 ELSE 0 END) AS SLMet
FROM Termination_Call_Detail tcd
WHERE tcd.DateTime >= @dateFrom AND
tcd.DateTime < @dateTo AND
tcd.AgentSkillTargetID IS NOT NULL AND
tcd.SkillGroupSkillTargetID IS NOT NULL AND
tcd.RouterCallKeyDay > 0 AND
tcd.TalkTime > 0

The result of this query is actually two numbers: AllCalls gives you the number of calls (explained bellow) and the SLMet is the number of calls where the length of time spent in the Router Queue (NetQTime) is less than or equal to 20 seconds.

The input parameters are

@dateFrom, @dateTo: the start and the end of the observed period;

@SLLimit: the upper bound of the interval for one call's queue time, if it is not passed then the call is considered answered within this "service level" interval.

Please note there are multiple conditions in the WHERE clause: the AgentSkillTargetID and the SkillGroupSkillTargetID cannot be NULL (this ensures we only take a look at calls handled by agents and skill groups) also the RouterCallKeyDay > 0 ensures we count ACD calls only and the TalkTime > 0 filters out answered calls only.

You can sort of enhance this with for instance

SUM(CASE WHEN (tcd.NetQTime + tcd.RingTime) <= @SLLimit THEN 1 ELSE 0 END) AS SLMet

That would also count with RingTime.

G.

23 REPLIES

Hi,yes, this is possible,

Hi,

yes, this is possible, although it's not going to be that easy.

Could you do the following for me (just to check whether we have available data) on the <instancename>_hds database?

SELECT TOP 1 tcd.DateTime FROM Termination_Call_Detail tcd ORDER BY tcd.DateTime ASC

Is the datetime value returned in this query older than the start of the above mentioned 11 month period?

G.

Community Member

Hello Szabo, thanks for

Hello Szabo,

 

thanks for prompt respone again!

 

Yes, we have data for last 2 years! (2012-05-19 08:00:09.017 )

 

Lazar

 

Hi, fyi, I didn't forget

Hi, fyi, I didn't forget about this, I'm just a bit busy at this moment. As soon as a "window" appears, I will create the SQL query that would generate the requested info.

G.
 

Hi,this might be a good start

Hi,

this might be a good start:

DECLARE @dateFrom DATETIME, @dateTo DATETIME, @SLLimit INT
SET @dateFrom   = '2014-11-12 00:00:00'
SET @dateTo     = '2014-11-13 00:00:00'
SET @SLLimit    = 20
SELECT SUM(1) AS AllCalls,
SUM(CASE WHEN tcd.NetQTime <= @SLLimit THEN 1 ELSE 0 END) AS SLMet
FROM Termination_Call_Detail tcd
WHERE tcd.DateTime >= @dateFrom AND
tcd.DateTime < @dateTo AND
tcd.AgentSkillTargetID IS NOT NULL AND
tcd.SkillGroupSkillTargetID IS NOT NULL AND
tcd.RouterCallKeyDay > 0 AND
tcd.TalkTime > 0

The result of this query is actually two numbers: AllCalls gives you the number of calls (explained bellow) and the SLMet is the number of calls where the length of time spent in the Router Queue (NetQTime) is less than or equal to 20 seconds.

The input parameters are

@dateFrom, @dateTo: the start and the end of the observed period;

@SLLimit: the upper bound of the interval for one call's queue time, if it is not passed then the call is considered answered within this "service level" interval.

Please note there are multiple conditions in the WHERE clause: the AgentSkillTargetID and the SkillGroupSkillTargetID cannot be NULL (this ensures we only take a look at calls handled by agents and skill groups) also the RouterCallKeyDay > 0 ensures we count ACD calls only and the TalkTime > 0 filters out answered calls only.

You can sort of enhance this with for instance

SUM(CASE WHEN (tcd.NetQTime + tcd.RingTime) <= @SLLimit THEN 1 ELSE 0 END) AS SLMet

That would also count with RingTime.

G.

Community Member

Thanks a lot Szabo for

Thanks a lot Szabo for sharing Your knowledge!

Can You clarify this below for me please?

 

Now I can calculate  the SL for particular Call Type like this:

DECLARE @dateFrom DATETIME, @dateTo DATETIME, @SLLimit INT
SET @dateFrom   = '2014-10-22 00:00:00'
SET @dateTo     = '2014-10-22 23:59:00'
SET @SLLimit    = 20
SELECT SUM(1) AS AllCalls,
SUM(CASE WHEN tcd.NetQTime <= @SLLimit THEN 1 ELSE 0 END) AS SLMet
FROM Termination_Call_Detail tcd
WHERE tcd.DateTime >= @dateFrom AND
tcd.DateTime < @dateTo AND
tcd.AgentSkillTargetID IS NOT NULL AND
tcd.SkillGroupSkillTargetID IS NOT NULL AND
tcd.CallTypeID = 5043 AND
tcd.RouterCallKeyDay > 0 AND
tcd.TalkTime > 0

 

or for particular skill group, by matching the SkillGroupSkillTragetID of that skill group!

 

Am I correct?

 

Regards,

Lazar

Hi Lazar,yes, exactly. I

Hi Lazar,

yes, exactly. I assume you know your ICM scripts perfectly well and you are absolutely sure about the CallType nodes' placement :-)

G.

Community Member

Thanks Szabo :)

Thanks Szabo :)

Community Member

Hi Szabo, one more

Hi Szabo,

 

one more clarification, please.

Is time tcd.NetQTime  (time spent in Router Queue) are measured for all calls(answered+abandoned)?

In other words, are in this 20 sec SL, all calls that are answered in 20 sec + all calls that are abandoned in 20 sec?

 

Thanks,

Lazar

Lazar,Looking at the DB

Lazar,

Looking at the DB Schema entry for NetQTime:

Represents the time the call spent on Network Queue in the CallRouter.

There doesn't appear to be any distinction between Abandoned, Answered, Dequeued, et cetera for this field.

-Jameson

-Jameson
Community Member

Thanks Jameson, assumed that

Thanks Jameson,

 

I assumed that too.

 

Regards,

Lazar 

Community Member

Hello everyone,Is it possible

Hello everyone,

Is it possible to filter the calls somehow, by making distinction between Abandoned, Answered etc. in the query?

Thanks a lot!

Dragan

Hi,could you please clarify

Hi,

could you please clarify what you mean by "Answered" - Answered by an agent? And "Abandoned" would be abandoned within the queue or at the IVR, is this what you need?

G.

Community Member

Hi Gergely, Exactly, I meant

Hi Gergely,

 

Exactly, I meant the calls answered by agent and the calls "abandoned" within queue. Actually, I would like to implement the following:

SL = SL handled calls / (calls presented-SL abandoned calls)

 

Thanks in advance!

Dragan

Okay, do you use CallTypes in

Okay, do you use CallTypes in your scripts? If so, is there a CallType node before the Select node? The formula you presented may be easily calculated by using CallTypes. 

G.

Community Member

Yes, we use CallTypes in our

Yes, we use CallTypes in our scripts and CallType node is placed just before QueToSkillGroup node in each script.

Kind regards, Dragan

Hi, can you actually give me

Hi, can you actually give me a screenshot. G.

Community Member

Sure, I'm sending you a

Sure, I'm sending you a screenshot from one part of a test script, because it's huge actually.

Regards, Dragan

Hi,okay, I assume this is a

Hi,

okay, I assume this is a ICM 8.x or newer.

Try this

SELECT
cti.DateTime,
CAST(cti.ServiceLevelCalls AS FLOAT) / NULLIF((cti.ServiceLevelCalls - cti.ServiceLevelAband),0) AS MySL
FROM Call_Type_Interval cti
WHERE
cti.CallTypeID = 5161
AND cti.DateTime > '2015-01-29 00:00:00'
AND cti.DateTime < '2015-01-29 11:00:00'

Naturally, replace the placeholders for CallTypeID and the dates.

Can you tell me why this formula?

G.

Community Member

Hi Gergely,Our customer is

Hi Gergely,

Our customer is interested in that formula as a way of measuring service level. Anyway, the measuring period should be 20s, not 30 which is default. This is actually related to the initial question that Lazar asked, but we need to adjust formula according to the last formula: SL = SL handled calls / (calls presented-SL abandoned calls).

Thanks! Regards, Dragan

 

Community Member

Hi Gergely, Is it possible

Hi Gergely,

 

Is it possible perhaps to change SL threshold somehow in a query that you sent, so it calculates the same, just for 20 sec instead of 30 sec period?

SELECT
cti.DateTime,
CAST(cti.ServiceLevelCalls AS FLOAT) / NULLIF((cti.ServiceLevelCalls - cti.ServiceLevelAband),0) AS MySL
FROM Call_Type_Interval cti
WHERE
cti.CallTypeID = 5161
AND cti.DateTime > '2015-01-29 00:00:00'
AND cti.DateTime < '2015-01-29 11:00:00'

 

Thank you so much,

Dragan

 

 Hi, not this way. All those

 

Hi, not this way. All those three numbers are calculated using the SL thresholds that were applicable when the call was processed. If you want to use a sort of different SL level, you'll have to use TCD. I'll try to write a SQL query for you later in the afternoon. G.

Community Member

Thank you so much, Gergely!

Thank you so much, Gergely! It's not something urgent at all, so if it doesn't take much effort for you to write it and whenever you have time or if you have some hint about it, that would be great too! :)

Best regards, Dragan

Community Member

Hi Gergely,Do you have some

Hi Gergely,

Do you have some idea maybe if it is possible to do this query somehow?

We still haven't managed to figure it out :)

Thanks, Dragan

549
Views
25
Helpful
23
Replies
CreatePlease to create content