Page History

User Call Statistics

Download SQL file: CDR-UsageReportAllUsersSnow.sql

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

Code Block

language	sql
title	User Call Statistics
collapse	true

/*
 * 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

Download SQL file: CDR-HuntGroupStats-

...

CompleteSnow.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

...

Code Block

language	sql
title	Hunt Group Statistics
collapse	true

SELECT
	-- Date
	[bf_callStartDateLocal] AS [Date],

	-- Location
	CONCAT([lcd_stateProvince], ', ', [lcd_city], ' - ', [lcd_streetAddress]) AS [Location],

	-- Hunt Group
	[bud_broadsoftUserDisplayName] AS [Hunt Group]HuntGroup,

	-- Inbound calls to Hunt group
	SUM( CASE [bf_direction] WHEN 'Terminating' THEN 1 ELSE 0 END ) AS [Inbound Calls]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 [Abandoned Calls]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 [Percent Abandoned]PercentAbandoned,

	-- Calls routed to agents
	SUM( CASE WHEN [bf_direction] = 'Originating' AND [bf_answerIndicator] = 'Y' THEN 1 ELSE 0 END ) AS [Routed Calls]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 [Handled Calls]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 [Percent Handled Calls]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 [Talk Duration]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 [Average Talk Duration]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 [VM Overflow Calls]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] >= 2021041420220414 AND [bf_callStartDateLocal] < 2021041520220415 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-MonthlyCallVolumeMonthlyCallVolumSnow.sql

The example below queries the data warehouse to summarize call counts and duration by phone #

...

Code Block

language	sql
title	Monthly Call Volume
collapse	true

-- Sum call counts and call durations for inbound answered calls for daileddialed numbers by month

SELECT [ 
-- Broadsoft CDR Fact

SELECT 
    bf_callStartDateLocal] / 100 100 AS monthAS month, 
 [   bf_calledNumber] AS DNIS, COUNT AS DNIS, 
    COUNT(*) AS callCount, CAST AS callCount, 
    CAST(SUM(bf_billDuration) / 60.0 AS DECIMAL0 AS DECIMAL(8,2)) AS minutes
  FROM [dbo].[BroadsoftCDRFact]
  WHERE bf AS minutes
FROM BroadsoftCDRFact
WHERE bf_direction =  'Terminating' AND [ AND bf_answerIndicator] = 'Y' AND [ AND bf_callStartDateLocal] >= 20220201 AND 20210301 AND [bf_callStartDateLocal] < 20210501
  GROUP BY [20220301
GROUP BY bf_callStartDateLocal] / 100, [bf_calledNumber]
  ORDER BY monthORDER BY month, DNIS;

Manage page tree

TEST

Are you sure?

Are you sure?

Page tree

Versions Compared

Old Version 1

New Version Current

Key

Download SQL file: CDR-UsageReportAllUsersSnow.sql

Download SQL file: CDR-HuntGroupStats-

CompleteSnow.sql