Hunt Group Statistics

Download SQL file:   CDR-HuntGroupStats-Complete.sql

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;