User Call Statistics
The example below queries the data warehouse for data surrounding user calls including how many were taken (answered) and made (placed) and what the call duration for these categories
The data selected based on a specific month and groups the data by location and user
/*
* This usage reports is based on the BroadsoftUserDimension table so all users are represented even if they did not gernerate any traffic.
* The columns in this report are:
* Month,Location,User Id,User Name,Extension,Inbound Calls,Inbound Answered Calls,Inbound Minutes,Outbound Calls,Outbound Answered Calls,Outbound Minutes,Total Calls,Total Answered Calls,Total Minutes,User Type
*/ -- Snowflake Scripting code EXECUTE IMMEDIATE $$
-- Snowflake Scripting code
DECLARE
monthLabel VARCHAR;
fDate INTEGER;
tDate INTEGER;
BEGIN
SELECT rdd_label, rdd_firstDate, rdd_lastDate INTO :monthLabel, :fDate, :tDate FROM EVOLVEIP.PUBLIC.REPORTDATESDIMENSION WHERE rdd_label = '2022-05';
LET res RESULTSET := (SELECT
:monthLabel Month, CONCAT( CONCAT( lcd_city, ' - '), lcd_stateProvince ) Location, REPLACE(lower(bud_broadsoftUserId), '.', ' - ') AS UserId, REPLACE(lower(bud_broadsoftUserDisplayName), ',', ' - ') AS UserName,
CASE WHEN bud_broadsoftExtension IS NOT NULL THEN bud_broadsoftExtension ELSE '' END Extension,
SUM(CASE WHEN bf_direction = 'Terminating' AND bf_answerIndicator IS NOT NULL THEN 1 ELSE 0 END) InboundCalls,
SUM(CASE WHEN bf_direction = 'Terminating' AND bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) InboundAnsweredCalls,
to_number(SUM(CASE WHEN bf_direction = 'Terminating' AND bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) InboundMinutes,
SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL THEN 1 ELSE 0 END) OutboundCalls,
SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) OutboundAnsweredCalls,
to_number(SUM(CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) OutboundMinutes,
SUM(CASE WHEN bf_answerIndicator IS NOT NULL THEN 1 ELSE 0 END) TotalCalls,
SUM(CASE WHEN bf_answerIndicator IS NOT NULL AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END) TotalAnsweredCalls,
to_number(SUM(CASE WHEN bf_answerIndicator IS NOT NULL THEN bf_billDuration ELSE 0 END) / 60.0) TotalMinutes,
CASE WHEN lower(bud_broadsoftUserId) ILIKE 'aa-%' THEN 'AUTO ATTENDANT'
ELSE CASE WHEN lower(bud_broadsoftUserId) ILIKE 'cc-%' THEN 'CALL CENTER'
ELSE CASE WHEN lower(bud_broadsoftUserId) ILIKE 'hg-%' THEN 'HUNT GROUP' ELSE
CASE WHEN lower(bud_broadsoftUserId) ILIKE 'cb-%' THEN 'CONF BRIDGE' ELSE
CASE WHEN lower(bud_broadsoftUserId) ILIKE 'casa-%' THEN 'AMP' ELSE 'USER' END END END END END UserType
FROM
BROADSOFTUSERDIMENSION
LEFT OUTER JOIN BroadsoftCDRFact ON bud_id = bf_userFk AND bf_callStartDateLocal >= :fDate AND bf_callStartDateLocal <= :tDate
LEFT OUTER JOIN LocationDimension ON bud_locationId = lcd_location
WHERE
bud_id <> 0 AND bud_broadsoftUserId NOT LIKE '%-Default' AND bud_broadsoftUserId NOT LIKE '%_MOH' AND bud_broadsoftUserId NOT LIKE '%_VMR' AND
bud_broadsoftUserId NOT LIKE 'vb-%'
GROUP BY lower(bud_broadsoftUserId), bud_broadsoftExtension, lower(bud_broadsoftUserDisplayName), CONCAT( CONCAT( lcd_city, ' - '), lcd_stateProvince )
ORDER BY Location, UserId, Extension, UserName);
return TABLE(res);
END;
$$
;
Hunt Group Statistics
The example below queries the data warehouse for data surrounding hunt group calls including how many were answered, abandoned and transferred to voicemail as well as duration of the ring times and calls
It is selected based on a specific date range and groups the data by location and user
SELECT
-- Date
bf_callStartDateLocal AS Date,
-- Location
CONCAT(lcd_stateProvince, ', ', lcd_city, ' - ', lcd_streetAddress) AS Location,
-- Hunt Group
bud_broadsoftUserDisplayName AS HuntGroup,
-- Inbound calls to Hunt group
SUM( CASE bf_direction WHEN 'Terminating' THEN 1 ELSE 0 END ) AS InboundCalls,
-- Calls abandoned before answer or VM
SUM( CASE bf_direction WHEN 'Terminating' THEN 1 ELSE 0 END ) -
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END ) AS AbandonedCalls,
-- Percentage of calls abandoned
CAST (CASE WHEN SUM( CASE bf_direction WHEN 'Terminating' THEN 1 ELSE 0 END ) > 0
THEN
CAST((SUM( CASE bf_direction WHEN 'Terminating' THEN 1 ELSE 0 END ) -
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END )) AS DECIMAL) /
SUM( CASE bf_direction WHEN 'Terminating' THEN 1 ELSE 0 END )
ELSE 0
END * 100.0 AS DECIMAL(4,1)) AS PercentAbandoned,
-- Calls routed to agents
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END ) AS RoutedCalls,
-- Handled calls (calls answered by a person)
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) AS HandledCalls,
-- Precent handled calls (calls answered by a person)
CAST (CASE WHEN SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END ) > 0
THEN
CAST (SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) AS DECIMAL) /
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' THEN 1 ELSE 0 END )
ELSE 0
END * 100.0 AS DECIMAL(5,2)) AS PercentHandledCalls,
-- Talk duration of handled calls in minutes
CAST (SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN bf_billDuration ELSE 0 END ) / 60.0 AS DECIMAL(8,2)) AS TalkDuration,
-- Average duration of handled calls in minutes
CAST (CASE WHEN SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) > 0
THEN
CAST (SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN bf_billDuration ELSE 0 END ) AS DECIMAL) /
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END )
ELSE 0
END / 60.0 AS DECIMAL(6,2)) AS AverageTalkDuration,
-- Call overflowed to VM
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber = '5000' THEN 1 ELSE 0 END ) AS VMOverflowCalls,
-- Average speed of answer (ASA) (wait time) in minutes
CAST (CASE WHEN SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END ) > 0
THEN
CAST (SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN bf_ringDuration ELSE 0 END ) AS DECIMAL) /
SUM( CASE WHEN bf_direction = 'Originating' AND bf_answerIndicator = 'Y' AND bf_calledNumber <> '5000' THEN 1 ELSE 0 END )
ELSE 0
END / 60.0 AS DECIMAL(5,2)) AS ASA
FROM BroadsoftCDRFact LEFT OUTER JOIN BroadsoftUserDimension ON bf_userFK = bud_id
LEFT OUTER JOIN LocationDimension ON SUBSTRING(bf_group, 4, 10) = lcd_location
WHERE bf_callStartDateLocal >= 20220414 AND bf_callStartDateLocal < 20220415 AND
bf_userId LIKE 'hg-%'
GROUP BY bf_callStartDateLocal, CONCAT(lcd_stateProvince, ', ', lcd_city, ' - ', lcd_streetAddress), bud_broadsoftUserDisplayName;
Monthly Call Volume
Download SQL file: CDR-MonthlyCallVolumSnow.sql
The example below queries the data warehouse to summarize call counts and duration by phone #
It is selected based on a date range and then ordered by number and month
-- Sum call counts and call durations for inbound answered calls for dialed numbers by month
-- Broadsoft CDR Fact
SELECT
bf_callStartDateLocal / 100 AS month,
bf_calledNumber AS DNIS,
COUNT(*) AS callCount,
CAST(SUM(bf_billDuration) / 60.0 AS DECIMAL(8,2)) AS minutes
FROM BroadsoftCDRFact
WHERE bf_direction = 'Terminating' AND bf_answerIndicator ='Y' AND bf_callStartDateLocal >= 20220201 AND bf_callStartDateLocal < 20220301
GROUP BY bf_callStartDateLocal / 100, bf_calledNumber
ORDER BY month, DNIS;