Technical Setup for Raw data
SQL database, IP-addresses, access and security
Each customer's Puzzel Raw data DB is hosted in a dedicated instance of Microsoft SQL Server 2019, so that each customer's data is completely isolated.
Communication to the instance is over a dedicated port (per customer), and IP address allow list is enforced, ensuring communication is only allowed from the IP addresses specified by the customer.
The read-only user accounts Puzzel creates for each customer are dedicated for the customer’s DB and defined inside the SQL instance.
The SQL server is configured to enforce encryption on all client connections, so that the connection between server and client will always be encrypted by SSL regardless of whether 'Force Encryption' is set on the Client. The Certificate is issued by third party provider (DigiCert), with Key length 2048 bits and Hash algorithm sha256.
TLS 1.2 will be used for all clients supporting this. Older TLS and SSL protocols are no longer supported.
https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server.
A user can access the database only if
- traffic comes from agreed source IP addresses
- traffic is to the correct IP address and the correct port number is used
- correct username and password are entered
How long to save data?
Data records will be stored in the database for as many months as is agreed between the customer and Puzzel (e.g. 12). Data records older than the agreed storage time will be deleted every night.
Normal usage
If the customer uses the database in a way that results in a very high bandwidth consumption and very heavy load on Puzzel’s infrastructure, Puzzel has the right to limit the available bandwidth on Puzzel’s Internet access towards the database.
Time format / time zone
For PCC Raw data, records (events) in tables call_events, agent_events etc have dte_start/ dte_updated on format YYYY-MM-DD hh:mm:ss.nnn, e.g. 2019-05-20 12:17:43.440. The data type is datetime.
A Puzzel customer solution is linked to a “statistics country” and a time zone (e.g. Norway and W. Europe Standard Time)
The values in dte_start/ dte_updated is in local time, that is, in the local time of the Puzzel solution’s defined “statistics” country.
For PCM it's slightly different. In PCM Raw data we have both dte_created__at / dte_updated_at and a utc_transfer_timestamp.
Changes in database
Puzzel may change the database schema when needed, e.g. add new fields or introduce new values in a field, or add new tables, when new Puzzel functionality is added or functionality is changed, and this change requires the database to be updated. Puzzel will notify customers before the database schema is changed.
The responsibility to handle the data
It is the customer’s responsibility to apply to the relevant laws and regulations for storing and processing the call and agent detailed records. The customer must fill out a data processing agreement and define therein the scope of Puzzel as a data processor on behalf of the customer. The agreement must be signed by both parties.
How to utilise the data
The customer can log on to the database with a SQL tool and run his own queries or use the Puzzel specific database-functions and stored procedures that are available. See details in chapter Functions and Stored Procedures. The customer can also connect to the database using e.g. Microsoft Excel.
What can the detailed records be used for?
Examples:
- Find a specific call based on known caller’s number, agent or time
- Show all details for one agent for a given time period
- Generate your own report for a very specific time period (hh:mm:ss-hh:mm:ss)
- Generate your own report at fixed intervals (every hour, day, week or month)
- Look for repeat callers (callers calling back within a day or two) / analyse first call resolution
- Select necessary data for the last x minutes and calculate needed input to your workforce management system
You do not need to download all the data to your own local database to analyse the data and generate reports, but if you want to store all the data in your local database/data warehouse, you may do this. One way to do it is to download all new/updated records every night. Remember that records may be updated in the database after initially created, e.g. for wrap-up, and remember that callback in queue results in at least 2 sessions. See chapter Guideline for downloading to own local database(s)/system(s).
Initial training and support
An introductory Raw data training session is compulsory, and we recommend that this session is arranged before or shortly after anyone starts writing SQL queries.
This is to be sure that both the ones that want reports from Raw data and the ones that will write the SQL queries actually understand the Raw data basics (what events that are generated for different call/email/chat scenarios) and Puzzel statistics basics.
Note: Puzzel does not include support for how to write SQL in the Raw data product, and Puzzel’s Support department cannot assist with customer-created SQL queries.
If you need additional consulting/training after the initial training, please contact your Puzzel Account Manager to discuss and arrange an extra consulting/training session.