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
Hunt Group Statistics
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], -- 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], -- 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], -- 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], -- Ring duration in minutes CAST (SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' AND bf_calledNumber <> '5000' THEN [bf_ringDuration] ELSE 0 END ) / 60.0 AS DECIMAL(8,2)) AS [Ring Duration], 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
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
Monthly Call Volume
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;
Broadsoft Contact Center User Statistics
The example below queries the data warehouse to display statistics for top 100 call center agents
Broadsoft Call Center Users
SELECT TOP (100) [acf_id] ,[acf_agentSkill] ,[acf_agentUserFk] ,[acf_dnisFk] ,[acf_callCenterFk] ,[acf_callHeldCount] ,[acf_callHeldDuration] ,[acf_callTotalDuration] ,[acf_callRingDuration] ,[acf_callTalkDuration] ,[acf_callStartedDateUTC] ,[acf_callStartedTimeUTC] ,[acf_callStartedDateLocal] ,[acf_callStartedTimeLocal] ,[acf_callReleasedDateUTC] ,[acf_callReleasedTimeUTC] ,[acf_callReleasedDateLocal] ,[acf_callReleasedTimeLocal] ,[acf_status] ,[acf_key] ,[acf_accountId] ,[acf_callId] ,[acf_networkCallId] ,[acf_remoteNumber] ,[acf_numberCalled] ,[acf_creationTimestamp] ,[acf_lastUpdateTimestamp] ,[acf_callAnsweredTimestamp] ,[acf_callStartedTimestamp] ,[acf_callHeldTimestamp] ,[acf_callResumedTimestamp] ,[acf_callReleasedTimestamp] ,[acf_eipId] ,[acf_nonCallCenterCall] ,[acf_direction] ,[acf_callType] ,[acf_redirectLocation] ,[acf_redirectNumber] ,[acf_wrapUpDuration] ,[acf_wrapUpExitTimestamp] ,AU.[bud_broadsoftUserId] AS agentUserId ,QU.[bud_broadsoftUserId] AS queueUserId FROM [dbo].[BroadsoftCCAgentCallFact] AS CF LEFT OUTER JOIN [dbo].[BroadsoftUserDimension] AS AU ON CF.[acf_agentUserFk] = AU.[bud_id] LEFT OUTER JOIN [dbo].[BroadsoftCallCenterDimension] AS QJ ON CF.[acf_callCenterFk] = QJ.[bccd_id] LEFT OUTER JOIN [dbo].[BroadsoftUserDimension] AS QU ON QU.[bud_broadsoftFQUserId] = QJ.bccd_userId