Sangoma CX Data Export Technical Guide (Formerly Sangoma Contact Center)

( Sangoma Contact Center has been rebranded as Sangoma CX )

Sangoma CX 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 Sangoma CX 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.

1. This is an optional service only available for the Sangoma CX 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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

Container Name: Queue_Call

Content Description: Information about each queued call.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Create an AWS S3 bucket. Highly recommend enabling bucket encryption with AWS-managed key.

2. Download the json policy available here (the policy is provided as a s3rolepolicy.json file).

3. Replace “externaltestbucket” with the name of the newly created S3 bucket.

4. Go to IAM -> Access Management -> Policies. Click on “Create Policy”.

5. In the next screen click on “JSON” tab.

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

7. Optionally create tag(s) on the Tags screen and click “Next.

8. Fill out policy name and optional description fields and click “Create policy”.

9. 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.

10. 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”.

11. Optionally enter tag(s) and continue.

12. Review the user info and click “Create user”.

13. 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.

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

1. Create a Storage Account.

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

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

4. Click “Containers” in the left menu.

5. Create a new container to store the Sangoma CX Data Export.

 

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

7. Provide the connection string and container name to Sangoma.