Details per queue
This standard report’s description is available in the Puzzel Administration Portal on page Statistics, where the report is generated.
In the SQL query:
• We only count queue events with start in the chosen time period
(we don’t fetch speaktime or wrap-up time from conversation events “belonging” to the selected queue events or from other conversation events with start in this time period)
• We did not include column Incoming calls (which counts call_ids grouped by the call_id’s first queue event’s queue_key)
• We did not include column Answered call-backs
(it requires some logic to decide if a callback (queue event with result=q) is answered by agent and the one that ordered call-back)
• We did not include columns Avg. speaktime and Max. speaktime (since these are values from conversation events which you may think “belong” in another time period)
• We have added some extra columns for different queue event results (queue exits)
• We have only one column for Answered within n sec, and in the query we have used 10 sec:
• We only count queue events with start in the chosen time period
(we don’t fetch speaktime or wrap-up time from conversation events “belonging” to the selected queue events or from other conversation events with start in this time period)
• We did not include column Incoming calls (which counts call_ids grouped by the call_id’s first queue event’s queue_key)
• We did not include column Answered call-backs
(it requires some logic to decide if a callback (queue event with result=q) is answered by agent and the one that ordered call-back)
• We did not include columns Avg. speaktime and Max. speaktime (since these are values from conversation events which you may think “belong” in another time period)
• We have added some extra columns for different queue event results (queue exits)
• We have only one column for Answered within n sec, and in the query we have used 10 sec:
duration_tot_sec < 10
You can change from 10 sec to your value (e.g. 30) and rename the column from:
• PercAnswWithinSec10 to e.g. PercAnswWithinSec30
declare @FromDate datetime, @ToDate datetime
set @FromDate = '16-feb-2016 10:45'
set @ToDate = '16-feb-2016 11:00'
select
coalesce(queues.descript,call_events.queue_key) [Queue],
sum(case when result_code <> 'd' then 1 else 0 end) TotalCalls,
sum(case when result_code = 'h' then 1 else 0 end) HungUp,
sum(case when result_code = 'h' AND duration_tot_sec < 20 then 1 else 0 end) HungUpBlw20,
sum(case when result_code = 'q' then 1 else 0 end) CallBacks,
sum(case when result_code = 't' then 1 else 0 end) TimeOuts,
sum(case when result_code = 'a' then 1 else 0 end) Exits,
sum(case when result_code = 'k' then 1 else 0 end) Answered,
sum(case when result_code = 'd' then 1 else 0 end) Deleted,
sum(case when result_code = 's' then 1 else 0 end) Empties,
sum(case when result_code = 'e' then 1 else 0 end) Errors,
sum(case when result_code = 'f' then 1 else 0 end) Fulls,
sum(case when result_code = 'b' then 1 else 0 end) Fallbacks,
sum(case when result_code = 'c' then 1 else 0 end) Cancels,
case when ( sum( case when result_code <> 'd' then 1 else 0 end) - sum(case when result_code = 'q' then 1 else 0 end) ) > 0 then
convert( int, Round(100.0 * sum(case when result_code = 'k' then 1 else 0 end) / ( sum( case when result_code <> 'd' then 1 else 0 end) - sum(case when result_code = 'q' then 1 else 0 end) ),0) )
else
NULL
end AnswRate,
case when sum(case when result_code = 'k' then 1 else 0 end) > 0
then
convert(int, round(1.0*sum(case when result_code='k' then duration_tot_sec else 0 end) / sum(case when result_code = 'k' then 1 else 0 end),0))
else
NULL
end secAvgTimeInQAnswered,
max( case when result_code='k' then duration_tot_sec else 0 end) secMaxTimeInQAnswered,
case when sum(case when result_code = 'k' then 1 else 0 end) > 0
then
convert(int, round(1.0*sum(case when result_code='h' then duration_tot_sec else 0 end) / sum(case when result_code = 'k' then 1 else 0 end),0))
else
NULL
end secAvgTimeInQHangup,
max( case when result_code='h' then duration_tot_sec else 0 end) secMaxTimeInQHangup,
case when sum( case when result_code <> 'd' then 1 else 0 end) > 0
then
Convert(int,Round(100.0*sum(case when result_code = 'k' AND duration_tot_sec < 10 then 1 else 0 end) / sum( case when result_code <> 'd' then 1 else 0 end),0))
else
NULL
end PercAnswWithinSec10
from call_events
left outer join queues on queues.queue_key = call_events.queue_key
where dte_start between @FromDate and @ToDate
and event_type = 'q' -- queue_events
and media_type_id = 1 -- Phone only
group by coalesce(queues.descript,call_events.queue_key)
order by 1,2