Example SQL queries for 2 Contact Centre reports

In the Puzzel Administration Portal’s statistics reports, the main principle is that a call_id with all its events are placed in the 15 minute time period where the call_id’s Initiation event has its Finish.

With Puzzel Raw data, you can create your own queries/reports and put calls/events in the time period you think is most appropriate.

Please note that the queries that are used to generate the standard statistics reports available from Admin Portal's Statistics page can not be used in Raw data, since the data format in Puzzel's internal database is different from the format in Raw data. When data is transferred to each customer's Raw data sql db we are changing some columns/values especially related to callback/Dialler (aka 'CiQ') and transfer.   

However, we have made SQL queries that generate approximately the same result as is shown in the standard reports:

• Details per agent per queue (all media types)
• Details per queue (phone only)

The main difference in the output between these SQL queries and the standard statistics reports is that

• with these sample queries we report the conversation events/queue events in the time period these queue/conversation events have their START
• in standard statistics the conversation and queue events are reported in the time period where their 'belonging' initiation event (same call_id) had its Finish, which might be significantly earlier for Callback in queue and Dialler.    

When you run one of these queries for a whole day, you will get results quite similar to the numbers in the standard statistics reports, but when you run a query for a small time period, e.g. 15 minute, there will be quite big differences compared to the numbers in the corresponding Puzzel standard report. 

Please study the chapter In which time period does a call belong in Puzzel Raw Data Detailed Description.

These SQL queries are just examples of queries that can be run, and customers may of course change these queries to fit their needs.

It is recommended that developers study the detailed documentation for raw data in order to run and understand any SQL queries and examples in this document.

Puzzel can provide additional consulting/advice on how to write queries to report what you need, please contact your Puzzel Account Manager to discuss and arrange this.