You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
Version 1
Next »
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 [Hunt Group],
-- Inbound calls to Hunt group
SUM( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) AS [Inbound Calls],
-- 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 [Abandoned Calls],
-- 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 [Percent Abandoned],
-- Calls routed to agents
SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) AS [Routed Calls],
-- 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 [Handled Calls],
-- 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 [Percent Handled Calls],
-- 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 [Talk Duration],
-- 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 [Average Talk Duration],
-- Call overflowed to VM
SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber = '5000' THEN 1 ELSE 0 END ) AS [VM Overflow Calls],
-- 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] >= 20210414 AND [bf_callStartDateLocal] < 20210415 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-MonthlyCallVolume.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 dailed numbers by month
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 [dbo].[BroadsoftCDRFact]
WHERE bf_direction = 'Terminating' AND [bf_answerIndicator] = 'Y' AND [bf_callStartDateLocal] >= 20210301 AND [bf_callStartDateLocal] < 20210501
GROUP BY [bf_callStartDateLocal] / 100, [bf_calledNumber]
ORDER BY month, DNIS;