Create a time slots metric

Owned by Valerio Ciotta
Last updated: Jan 19, 2016
2 min read

We want to create a report to group the calls by 3 time slots: from 00 to 08, from 09 to 16 and from 17 to 24.

Step-by-step guide

Add a new metric:

Edit the created metric and set the query

The query to create the time slots is:

 

The new metric "TimeSlot" is now ready to be used in your custom reports.

For example, use the time slot in the group-by clause:

SELECT call_log.origcalldate AS "Day", 
CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END AS "TimeSlot", 
COUNT(IF(call_log.connect=1 AND call_log.enterqueue=1 AND call_log.ivrevent is NULL AND call_log.outboundcall=0 ,1,NULL)) AS "Answered", 
COUNT(IF(call_log.abandon=1,1,NULL)) AS "Abandoned", 
ROUND(AVG(IF(call_log.connect=1 AND call_log.enterqueue=1,call_log.calltime,NULL))) AS "avg Talk Time", 
ROUND(AVG(IF(call_log.connect=1 AND call_log.enterqueue=1,call_log.holdtime,NULL))) AS "avg Hold Time", 
ROUND(AVG(IF(call_log.connect=0 AND call_log.enterqueue=1 AND abandon=1,call_log.waittime,NULL))) AS "avg Abandon Time"
 FROM call_log WHERE ( call_log.connect='1'  or call_log.connect='0')
 GROUP BY call_log.origcalldate,CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END
 ORDER BY call_log.origcalldate  ,CASE
WHEN call_log.origcallhour IN ('0','1','2','3','4','5','6','7') THEN '00-08'
WHEN call_log.origcallhour IN ('8','9','10','11','12','13','14','15') THEN '08-16'
WHEN call_log.origcallhour IN ('16','17','18','19','20','21','22','23') THEN '16-24'
END

 

To generate a report as:

 

Â