Example queries are intended to provide some insight into the various data sets found in the Evolve IP Data Warehouse. End users may use these queries as a good starting point for reports that are commonly requested. Examples are broken down by data set.
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
Code Block | ||||
---|---|---|---|---|
| ||||
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
Code Block | ||||
---|---|---|---|---|
| ||||
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
...
language | sql |
---|---|
title | Broadsoft Call Center Users |
...