Sangoma Contact Center Data Export Technical Guide

Edited: 07/14/2022
Access: Everyone
Supports: Business Voice, Business Voice+

Introduction

Sangoma Contact Center is able to provide access to the data collected and generated by the platform in relation to calls, agents, queues, etc. This document describes how this data is provided to clients that can be used to feed different reporting solutions to perform advanced data analysis.

To guarantee the platform security and stability, the data is provided by generating a daily data export into one of these two cloud storage options provided by the client:

  1. AWS S3 bucket
  1. MS Azure Blob storage

Sangoma schedules this process during an off-peak time ensuring the platform performance.

This service is provided as an add-on, and is only available for activation in the Contact Center Bundle. If you have any questions about activation, please contact a Sangoma sales representative.

The document has four main sections

  1. Operation: This details, filename convention, files & container association, file format used, file frequency generation and records processes by content type.
  1. Containers: This details the fields per container. This second section is vital to understand the content available on each file and relationship between files.
  1. Annex A: This details how clients have to configure access to the AWS S3 Bucket and the information needed by Sangoma personnel to configure the data export.
  1. Annex B: This details how clients have to configure access to the MS Azure Blob Storage and the information needed by Sangoma personnel to configure the data export.

Operation and Exporting Data into AWS S3 Bucket or MS Azure Blob Storage

Considerations

  1. This is an optional service only available for the Contact Center Bundle.
  1. AWS S3 and MS Azure Blob resources must be provided by the customer.
  1. The information is needed by Sangoma to programmatically access and export the files is:
    1. With AWS S3:
      1. S3 Bucket Name
      1. AWS Access Key ID
      1. AWS Secret Access Key
    1. With MS Azure Blob:
      1. Azure Storage Connection String
      1. Azure Storage Container Name
  1. Detailed information
    1. How to set up the the IAM user for S3 bucket sharing is available in Annex A: Setting up IAM user for AWS S3 bucket sharing
    1. How to setup the MS Azure Blob storage container in Annex B: Setting up MS Azure Blob Storage Container

Folder Structure and File Convention

Facts

  1. The path \CCAAS\FACTS\ will have one folder for each container that represents a fact table. These folders will be created using the following nomenclature:

Folder / Path: \CCAAS\FACTS\[TENANT_ID]-[CONTAINER_NAME]-[CONTENT_TYPE]

  1. TENANT_ID: a number that identifies the client tenant
  1. CONTAINER_NAME: name of the fact container that is referenced in this document
  1. CONTENT_TYPE: the letter F that represent facts
  1. Each folder will contain all the .csv export files that match the container name following this nomenclature:

Filename: [YYYYMMDD]-[TENANT_ID]-[CONTAINER_NAME]-[CONTENT_TYPE].csv

  1. YYYYMMDD: Year Month and day of the last data extracted on the file.
  1. TENANT_ID: matches the TENANT_ID of the folder
  1. CONTAINER_NAME: matches the CONTAINER_NAME of the folder
  1. CONTENT_TYPE: the letter F that represent facts
  1. Fact data export:
    1. Each file contains new and unique incremental data
    1. Files already exported into AWS S3 or MS Azure Blob Storage are not deleted or modified

Dimensions

  1. The path \CCAAS\DIMENSIONS\ will have one folder for each container that represents a dimension table. These folders will be created using the following nomenclature:

Folder / Path: \CCAAS\DIMENSIONS\[TENANT_ID]-[CONTAINER_NAME]-[CONTENT_TYPE]

  1. TENANT_ID: a number that identifies the client tenant
  1. CONTAINER_NAME: name of a dimension container that is referenced on this document
  1. CONTENT_TYPE: the letter D that represent dimensions

Each folder will contain all the .csv export files that matches the container name following this nomenclature:

Filename: [TENANT_ID]-[CONTAINER_NAME]-[CONTENT_TYPE].csv

  1. TENANT_ID: matches the TENANT_ID of the folder
  1. CONTAINER_NAME: matches the CONTAINER_NAME of the folder
  1. CONTENT_TYPE: the letter D that represent dimensions
  1. Dimension data export:
    1. Each new file generated overwrites the existing file with the same name

File Format

Follows the standard: https://datatracker.ietf.org/doc/html/rfc4180

The CSV files will be created using the following specifications:

  1. Each record is located on a separate line, delimited by a line break (CRLF). For example:

aaa,bbb,ccc CRLF

zzz,yyy,xxx CRLF

  1. A header line will appear as the first line of the file with the same format as normal record lines. The header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file. For example:

field_name,field_name,field_name CRLF

aaa,bbb,ccc CRLF

zzz,yyy,xxx CRLF

  1. Within the header and each record, there may be one or more fields, separated by commas. Each line should contain the same number of fields throughout the file. Spaces are considered part of a field and should not be ignored. The last field in the record must not be followed by a comma. For example:

aaa,bbb,ccc CRLF

  1. Each field may or may not be enclosed in double quotes. For example:

aaa,”bbb”,ccc CRLF

  1. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example:

aaa,“b CRLF

bb”,ccc CRLF

  1. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:

Aaa,“b"“bb”,ccc CRLF

Frequency

A full set of files that will represent each content name detailed under the Containers section will be created every night between 03:00 and 05:00 EST.

Containers

The process will create one file per container. This section details the content provided on per container

Agent_Details

Container Name: Agent_Details

Content Description: Basic agent information

Content Type: Dimension

Field Name

Field Description

Data Type and Format

agent_id

Agent_ Details id

Integer

name

agent full name

String

login_name

agent login username

String

email

agent email

String

enabled

whether agent is active or not

Boolean

Agent_Group

Container Name: Agent_Group

Content Description: Agent group information

Content Type: Dimension

Field Name

Field Description

Data Type and Format

agent_id

Agent_Details id

Integer

agent_group_id

Agent Group id

Integer

group_name

group name

String

group_description

group description

String

Agent_Login

Container Name: Agent_Login

Content Description: Details when an agent logs in and out

Content Type: Fact

Field Name

Field Description

Data Type and Format

agent_login_id

Agent login id

Integer

agent_id

Agent_Details id

Integer

time_logged_in

epoch timestamp agent logged in

Integer (Epoch)

time_logged_off

epoch timestamp agent logged out

Integer (Epoch)

extension

which extension agent was using

String

logoff_reason_id

Agent_Logoff_Reason id

Integer

Agent_Login_Queue

Container Name: Agent_Login_Queue

Content Description: Details which queue an agent logs in and out of (can be many queues per login)

Content Type: Fact

Field Name

Field Description

Data Type and Format

agent_login_queue_id

Record id on this container / table

Integer

queue_id

Queue_Details id

Integer

agent_login_id

Agent_Login id

Integer

Agent_Pause

Container Name: Agent_Pause

Content Description: Details when an agent pauses/unpauses, with a reason code

Content Type: Fact

Field Name

Field Description

Data Type and Format

agent_pause_id

Agent_Pause id

Integer

agent_id

Agent_Details id

Integer

agent_login_id

Agent_Login id

Integer

time_start

epoch timestamp of when pause started

Integer (Epoch)

time_end

epoch timestamp of when pause stopped

Integer (Epoch)

Reason_id (pause_code_id)

Pause_Code id

Integer

Agent_ACW

Container Name: Agent_ACW

Content Description: When an agent start and stops after call work, and on which call they were on

Content Type: Fact

Field Name

Field Description

Data Type and Format

agent_acw_id

Record id on this container / table

Integer

agent_id

Agent_Details id

Integer

time_start

epoch timestamp of when acw started

Integer (Epoch)

time_end

epoch timestamp of when acw started

Integer (Epoch)

call_uuid

Call_Details uuid

String

Call_Attempt

Container Name: Call_Attempt

Content Description: Provides detail of each call attempt and no answer reason

Content Type: Fact

Field Name

Field Description

Data Type and Format

call_attempt_id

Record id on this container / table

Integer

agent_id

Agent_Details id

Integer

callerid_number

callerid of call

String

uuid

Call_Details uuid

String

queue_id

Queue_Details id

Integer

start_time

epoch timestamp of when the call attempt started

Integer (Epoch)

end_time

epoch timestamp of when the call attempt ended

Integer (Epoch)

no_answer_reason

reason call was not answered

String

Queue_Agents

Container Name: Queue_Agents

Content Description: Details association of Agents to Queues

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_id

Queue_Details id

Integer

agent_id

Agent_Details id

Integer

priority

priority

Integer

Queue_Details

Container Name: Queue_Details

Content Description: Provides basic information about the queue

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_id

Queue Details id

Integer

name

queue name

String

small_name

queue small name

String

enabled

whether the queue enabled

Boolean

number

internal number

String

public_number

external number

String

description

description

String

strategy

queue strategy

String

wrapuptime

wrapup time in seconds

Integer

sla_wait

maximum wait time goal in seconds

Integer

sla_wait_target

target percent of calls who meet SLA wait

Integer

sla_duration

Maximum agent talk time in seconds

Integer

sla_duration_target

Target percent of calls that meet SLA duration

Integer

abandoned_threshold

Seconds after an abandoned call is considered abandoned

Integer

agent_disconnection_rate

Acceptable rate of disconnected calls

Integer

Queue_Call

Container Name: Call_Attempt

Content Description: Provides detail of each call attempt and no answer reason. Only calls that are queued

Content Type: Fact

Field Name

Field Description

Data Type and Format

queue_call_id

Queue Call id

Integer

queue_id

Queue_Details id

Integer

agent_id

Agent_Details id

Integer

exten

agent extension

String

call_reason_id

Queue_Call_Reason id

Integer

queue_call_end_by_id

Queue_Call_End_By id

Integer

exit_key

exit key

String

time_join

epoch timestamp call joined queue

Integer (Epoch)

time_connect

epoch timestamp call connected agent

Integer (Epoch)

time_leave

epoch timestamp call left queue

Integer (Epoch)

call_type_id

Call_Type id

Integer

callerid

caller id of incoming call

String

uuid

Call_Details uuid

String

to

to number called

String

acw_id

ACW id

Integer

Queue_Pause_Code

Container Name: Queue_Pause_Code

Content Description: Provides basic information about the pause settings applicable to a queue

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_pause_code_id

Record id on this container / table

Integer

queue_id

Queue_Details id

Integer

pause_code_id

Pause_Code id

Integer

limit_duration

limit duration

Integer

automatic_unpause

whether this automatically unpauses

Boolean

disable_unpause

whether to disable unpause after expiration

Boolean

limit_notification_enabled

whether to notify on limit

Boolean

Queue_Call_Contact

Container Name: Queue_Call_Contact

Content Description: Links a queue call to a contact

Content Type: Fact

Field Name

Field Description

Data Type and Format

queue_call_contact_id

Record id on this container / table

Integer

queue_call_id

Queue_Call id

Integer

contact_id

Contacts id

Integer

Call_Issue_Queue_Call

Container Name: Call_Issue_Queue_Call

Content Description: Links a call issue to a queue

Content Type: Fact

Field Name

Field Description

Data Type and Format

call_issue_queue_call_id

Record id on this container / table

Integer

queue_call_id

Queue_Call id

Integer

call_issue_id

Call_Issue id

Integer

Queue_Call_End_By

Container Name: Queue_Call_End_By

Content Description: List of End By codes

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_call_end_by_id

Queue_Call_End_By id

Integer

end_by

End By Reason

String

Queue_Call_Reason

Container Name: Queue_Call_Reason

Content Description: Links a call reason to a queue

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_id

Queue_Details id

Integer

call_reason_id

Call_Reason id

Integer

Queue_Call_Survey

Container Name: Queue_Call_Survey

Content Description: Details the survey scores of calls

Content Type: Fact

Field Name

Field Description

Data Type and Format

queue_call_survey_id

Record id on this container / table

Integer

uuid

Call_Details uuid

String

csat_score

customer satisfaction score

Integer

nps_score

net promoter score

Integer

created_at

epoch timestamp when survey was created

Integer (Epoch)

queue_id

Queue_Details id

Integer

nps_enterprise_score

net promoter enterprise score

Integer

Queue_Group

Container Name: Queue_Group

Content Description: List of queue groups

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_group_id

Queue group id

Integer

name

name

String

description

description

String

Queue_Group_Queues

Container Name: Queue_Group_Queues

Content Description: List or queue groups and relation with queue_id

Content Type: Dimension

Field Name

Field Description

Data Type and Format

queue_group_queues_id

Record id on this container / table

Integer

queue_id

Queue_Details id

Integer

queue_group_id

id

Integer

Call_Details

Container Name: Call_Details

Content Description: Information about each call. This includes all Queue Cal and any other call generated in the system, for example a transfer call to an agent.

Content Type: Fact

Field Name

Field Description

Data Type and Format

call_details_id

Record id on this container / table

Integer

callerid_name

callerid name

String

callerid_number

callerid number

String

destination_number

destination number

String

call_type

call type (incoming/outgoing)

String

codec

codec used

String

start_time

epoch timestamp call started

Integer (Epoch)

answer_time

epoch timestamp call answered

Integer (Epoch)

end_time

epoch timestamp call ended

Integer (Epoch)

duration

duration

Integer

status

status

String

trunk

trunk

String

did_number

DID

String

uuid

uuid identifier of call

String

Contact

Container Name: Contact

Content Description: Details contact information stored in the system

Content Type: Dimension

Field Name

Field Description

Data Type and Format

contact_id

Contact id

Integer

name

name

String

email

email

String

id_number

id_number

String

active

whether contact is active

Boolean

Destination

Container Name: Destination

Content Description: Details all the destinations

Content Type: Dimension

Field Name

Field Description

Data Type and Format

destination_id

Destination id

Integer

destination_type

type of destination

String

name

name

String

active

whether destination is active

Boolean

DID

Container Name: DID

Content Description: Links a DID to a destination

Content Type: Dimension

Field Name

Field Description

Data Type and Format

did_id

DID id

Integer

number

number

String

destination_id

Destination id

Integer

DNIS

Container Name: DNIS

Content Description: Provides a description of each DNIS and if enable or disabled

Content Type: Dimension

Field Name

Field Description

Data Type and Format

dnis_id

DNIS id

Integer

name

name

String

number

number

String

description

description

String

enabled

whether this is enabled

Boolean

Recording

Container Name: Recording

Content Description: Provides details of each call recording

Content Type: Fact

Field Name

Field Description

Data Type and Format

recording_id

Record id on this container / table

Integer

uuid

Call_Details uuid

String

path

path to recording (local or on s3)

String

codec

codec used

String

duration_ms

duration

Integer

size

size

Integer

channels

number channels

Integer

sample rate

sample rate

Integer

bit rate

bit rate

Integer

checksum

checksum

String

storage

where it’s stored at

String

created_at

epoch timestamp recording was created

Integer

updated_at

epoch timestamp recording was updated

Integer

Agent_Logoff_Reason

Container Name: Agent_Logoff_Reason

Content Description: Links logoff_reason_id with a description

Content Type: Dimension

Field Name

Field Description

Data Type and Format

logoff_reason_id

Logoff reason id

Integer

description

description

String

Call_Issue

Container Name: Call_Issue

Content Description: Call issue list

Content Type: Dimension

Field Name

Field Description

Data Type and Format

call_issue_id

Call Issue id

Integer

description

description

String

Call_Reason

Container Name: Call_Reason

Content Description: Links call_reason_id with a description

Content Type: Dimension

Field Name

Field Description

Data Type and Format

call_reason_id

Call Reason id

Integer

description

description

String

Call_Type

Container Name: Call_Type

Content Description: Links call_type_id with a description

Content Type: Dimension

Field Name

Field Description

Data Type and Format

call_type_id

Call type id

Integer

call_type

call type name/description

String

Pause_Code

Container Name: Pause_Code

Content Description: Contains the full list of pause codes, descriptions and type

Content Type: Dimension

Field Name

Field Description

Data Type and Format

pause_code_id

Pause Code id

Integer

description

pause description

String

productive

was pause productive or not

Boolean

system

whether this is a system pause code

Boolean

Annex A: Setting up IAM user for AWS S3 bucket sharing

  1. Create an AWS S3 bucket. Highly recommend enabling bucket encryption with AWS-managed key.
  1. Download the json policy available here (the policy is provided as a s3rolepolicy.json file).
  1. Replace “externaltestbucket” with the name of the newly created S3 bucket.
  1. Go to IAM -> Access Management -> Policies. Click on “Create Policy”.
  1. In the next screen click on “JSON” tab.

Missing image link-1

  1. Cut and paste the updated policy into the text area. Click “Next:Tags”.

Missing image link-2

  1. Optionally create tag(s) on the Tags screen and click “Next.
  1. Fill out policy name and optional description fields and click “Create policy”.

Missing image link-3

  1. Navigate to IAM->Access Management->Users and click “Add users”. On the next screen enter the desired username and check the “Access Key - Programmatic access” checkbox only. Click the “Next:Permissions” button.

Missing image link-4

  1. On the next screen select “Attach existing policies directly” card and locate the policy name you created previously. Select the checkbox next to the policy name. You can use “Filter policies” input to find it quickly. Click “Next:tags”.

Missing image link-5

  1. Optionally enter tag(s) and continue.
  1. Review the user info and click “Create user”.

Missing image link-6

  1. Copy the “Access key ID” and “Secret access key” and save it somewhere. You will only be able to see this secret on this screen.

Missing image link-7

  1. Provide the bucket name, access key id and secret access key to Sangoma.

Annex B: Setting up MS Azure Blob Storage Container

  1. Create a Storage Account.

Missing image link-8

  1. Once created, open the storage account just created and click “Access Keys” in the left menu.

Missing image link-9

  1. Click on “Show Keys” and copy the data in the “Connection String” and save it somewhere.

Missing image link-10

  1. Click “Containers” in the left menu.

Missing image link-11

  1. Create a new container to store the Sangoma Contact Center Data Export.

Missing image link-12

 

  1. Copy the “Container Name” and save it somewhere.

Missing image link-13

  1. Provide the connection string and container name to Sangoma.
up
9 users have voted.

Technical Support

If you have any technical questions or need to get in touch with Tech Support, please email us at support@sangoma.com to open a ticket.

Customers With Disabilities

Sangoma values its customers with disabilities. If you are in need of assistance with a Sangoma service or feature, please email Support@Sangoma.com.

Website Suggestion

Do you have a suggestion about our Knowledge Base website, let us know?