Background

CompressedUA contains statistics regarding:

• Calls to users with respect to particular ACD groups and hotlines
• Transfer calls made by users
• Transfer calls received by users
• Calls made by users for dialler campaigns
• Calls made by users by click to dial

Information such as:

• The service number
• The ACD group
• The dialler campaign
• The number of calls
  • Direct 
  • From a transfer
  • Transfered by agent
• Information on results
  
  • Answered
  • Busy
  • Caller Hangup
  • Rejected
• Times
  • Ring time
  • Talk time

is provided.

Note: because these statistics are aggragated, not quite as much information is available as from the call detail records. However, this aggregated data can be retained for a much longer time.

Parameters

Data is aggregated according to the settings of the parameters:

Query

Two tables are provided, CompressedUA15 (15 minute time slices) and CompressedUADay (day compression), aggregated according to the following queries.

The first query aggregates calls made TO the agent or BY the agent (direct calls from the ACD and calls transferred to the agent, click to dial calls and dialler calls):

SELECT      UsersID,
			(Various Aggregations)
FROM		JTELStats.StatisticsPartB b
LEFT JOIN	JTELStats.StatisticsPartA a
	ON		b.StatisticsPartAID = a.ID
WHERE		( b.ClientsID = _ClientsID )
			AND
			( b.dtCallStart BETWEEN _Timeslice_Begin AND Timeslice_End_ );

The second query aggregates calls made BY the agent (calls transferred by the agent):

SELECT      OriginUsersID,
			(Various Aggregations)
FROM		JTELStats.StatisticsPartB b
JOIN		JTELStats.StatisticsPartA a
	ON		b.StatisticsPartAID = a.ID
WHERE		( b.ClientsID = _ClientsID )
			AND
			( b.dtCallStart BETWEEN _Timeslice_Begin AND Timeslice_End_ )
			AND
			( b.OriginUsersID IS NOT NULL );

Partitioning and Aggregations 

When the data is aggregated, it is divided into several consistent partitions, according to the following logic. The resulting aggregations then either count a call, if a condition is met, or determine the time between two timestamps.

Partitioning Inbound and Outbound

Partitioning on CONNRES

All results are partitioned by CONNRES.

Inbound: Partitioning on calls to UsersID

Inbound: Partitioning on calls made by OriginUsersID

Outbound: Partitioning on DiallerCampaignsID

Tables

CompressedUA15

This table contains compressed statistics for 15 minute intervals.

CompressedUADay

This table contains compressed statistics for day intervals.

Fields

The field definitions are essentially the same, in both tables, only the aggregation intervals are different.