Introduction

TVox provides standard reporting and a set of insights dashboards that allow monitoring of the service levels and occupation of Contact Center resources.

TVox Data Model responds the need to create customized reports or dashboards (for example integrating CRM data) by providing access to the TVox Contact Center statistical data.

TVox Data Model consists in:

• Complete documentation of the data model that describes the statistical data
• API to query and retrieve statistical data

Statistical data covers channels:

• Phone
• Video
• Chat

Requirements

• Fault tolerance (strongly recommended)
• Basic knowledge of Web API concept
• Basic knowledge of GraphQL query language (recommended)

API Reference

Overview

TVox Data Model API provides access to statistical data through Web requests.

Endpoint for requests is constructed as follows:

https://<TVOX_HOST>/datamodel/query

The API is build on GraphQL query language, which allows you to request exactly the information you need with a simple and intuitive syntax.

GraphQL Glossary

Below a short GraphQL glossary of the main terms used in reference to the example on the right, reporting a basic GraphQL query for retrieving some users info searching them by name.

# Query "users" searches for users with name equal to "John"
# and displays their name, surname and address (street and city)
query {
  users(searchName: "John") {
    name
    surname
    address {
      street
      city
    }
  }
}

Request

TVox Data Model API allows GET or POST requests over HTTPS protocol.

GET request

In GET request the query must be passed as value of the query parameter.

query {
  me {
    name
  }
}

Example:

https://<TVOX_HOST>/datamodel/query?query={me{name}}

POST request

POST request should use the application/json content type, and include a JSON-encoded body of the following form:

{ "query": "{me{name}}" }

Response

Regardless of the method by which the request was sent, the response is returned in the body of the request in JSON format. A query might result in some data and some errors, and those are returned in a JSON object of the form:

{ "data": { ... }, "errors": [ ... ] }

If there were no errors returned, the errors field is not be present on the response. If no data is returned the data field is only be included if the error occurred during execution.

Authentication

TVox Data Model provides authentication via API Key, provided by Telenia Software.
To authenticate requests it is necessary to have an X-Telenia-APIKey header:

X-Telenia-APIKey: <YOUR_APIKEY>

Rate Limit

Requests to TVox Data Model API are subject to rate limit, i.e. a maximum number of requests that can be made within a certain time period.
The rate limit is 100 requests per minute.

Time Window

Given the nature of the statistical data that are reported by year / month, their request needs to specify the year and month in which to search the data.

Example:

search: { year: 1970, month: 1, ... }

Pagination

Statistical data can have very high cardinalities and a query without limits could lead to a reduction in server performance or, worse, to failure situations.
To avoid this, queries that include a list of elements as result are paged. This means that a limit (max: 1000) and an offset must be specified for each request.

Example:

search: { ..., pagination: { limit: 1000, offset: 0 } }

Sorting

It is possibile to return lists of elements thar are sorted (ordered) according to sort key and sort direction (ascending or descending).

Example:

search: { ..., sorting: { key: START_TIME, direction: ASC } }

Development & Testing

TVox Data Model provides an interactive web environment (Playground) for develop and testing your queries.

It can be reached at the following link:

https://<TVOX_HOST>/datamodel

The Playground consists of two main sections:

• a section (left) in which to insert queries in GraphQL format
• a section (right) where the query result will be displayed

To run the query just press the play button in the center.

Authentication header can be provided in HTTP HEADER section on bottom-left as JSON object with X-Telenia-APIKey field.

{ "X-Telenia-APIKey": "<YOUR_APIKEY>" }

The Playground allows also to generate the query's cURL command as an HTTP POST request.

Testing queries in Playground is a good way to verify that your request is correct and gives you the result you expect.

Phone

Statistical data for phone channel include:

• Inbound service calls
• Outbound service calls

These data can be retrieved by ID, searched and counted.

Inbound Service Call

Get Inbound Service Call by ID.

Result is the list of steps that make up the requested call.

Example:

Get call with ID equals to 1588581589.1320 and of this call get id, step, start time and end time.

Inbound Service Call

query {
  inboundCall(id: "1588581589.1320") {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundCall(id: \"1588581589.1320\") {\n    id\n    step\n    startTime\n    endTime\n  }\n}"}' --compressed

Inbound Service Calls

Search Inbound Service Calls.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the list of calls that respect search criteria; each call is in turn a list of steps that make up it.

Example:

Search calls according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Calls

query {
  inboundCalls(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  ) {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundCalls(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  ) {\n    id\n    step\n    startTime\n    endTime\n  }\n}\n"}' --compressed

Inbound Service Calls Count

Count Inbound Service Calls.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the 32-bit postive integer counting searched calls.

Example:

Count calls according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Calls Count

query {
  inboundCallsCount(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  )
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundCallsCount(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  )\n}\n"}' --compressed

Video

Statistical data for video channel include:

• Inbound service video calls

These data can be retrieved by ID, searched and counted.

Inbound Service Video Call

Get Inbound Service Video Call by ID.

Result is the list of steps that make up the requested video call.

Example:

Get video call with ID equals to 1588581589.1320 and of this video call get id, step, start time and end time.

Inbound Service Call

query {
  inboundVideoCall(id: "1588581589.1320") {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundVideoCall(id: \"1588581589.1320\") {\n    id\n    step\n    startTime\n    endTime\n  }\n}"}' --compressed

Inbound Service Video Calls

Search Inbound Service Video Calls.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the list of video calls that respect search criteria; each video call is in turn a list of steps that make up it.

Example:

Search video calls according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Video Calls

query {
  inboundVideoCalls(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  ) {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundVideoCalls(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  ) {\n    id\n    step\n    startTime\n    endTime\n  }\n}\n"}' --compressed

Inbound Service Video Calls Count

Count Inbound Service Video Calls.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the 32-bit postive integer counting searched video calls.

Example:

Count video calls according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Calls Count

query {
  inboundVideoCallsCount(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  )
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundVideoCallsCount(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  )\n}\n"}' --compressed

Chat

Statistical data for chat channel include:

• Inbound service chats

These data can be retrieved by ID, searched and counted.

Inbound Service Chat

Get Inbound Service Chat by ID.

Result is the list of steps that make up the requested chat.

Example:

Get chat with ID equals to 1588581589.1320 and of this chat get id, step, start time and end time.

Inbound Service Call

query {
  inboundChat(id: "1588581589.1320") {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundChat(id: \"1588581589.1320\") {\n    id\n    step\n    startTime\n    endTime\n  }\n}"}' --compressed

Inbound Service Chats

Search Inbound Service Chats.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the list of chats that respect search criteria; each chat is in turn a list of steps that make up it.

Example:

Search chats according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Chats

query {
  inboundChats(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  ) {
    id
    step
    startTime
    endTime
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundChats(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  ) {\n    id\n    step\n    startTime\n    endTime\n  }\n}\n"}' --compressed

Inbound Service Chats Count

Count Inbound Service Chats.

Argument search is the object containing search criterias.
Search criterias include time window (year, month), pagination and sorting.

Result is the 32-bit postive integer counting searched chats.

Example:

Count chats according to the following search criteria:

• year 2020
• month 5
• limit 100 with offset 0
• sorted by ID (ascending)
• start time between 1 May 2020 and 15 May 2020

Inbound Service Chats Count

query {
  inboundChatsCount(
    search: {
      year: 2020
      month: 5
      pagination: { limit: 100, offset: 0 }
      sorting: { key: ID, direction: ASC }
      startTime: {
        operator: BETWEEN
        valueLeft: "2020-05-01T00:00:00Z"
        valueRight: "2020-05-15T00:00:00Z"
      }
    }
  )
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundChatsCount(\n    search: {\n      year: 2020\n      month: 5\n      pagination: { limit: 100, offset: 0 }\n      sorting: { key: ID, direction: ASC }\n      startTime: {\n        operator: BETWEEN\n        valueLeft: \"2020-05-01T00:00:00Z\"\n        valueRight: \"2020-05-15T00:00:00Z\"\n      }\n    }\n  )\n}\n"}' --compressed

Schema Types

This section documents the schema types of the TVox Data Model API.

Basic knowledge

Query

List of queries to retrieve statistical data.

Objects

CallResult

The CallResult type represents call result data

CallTag

The CallTag type represents call tagging data

Contact

The Contact type represents contact data

GenericFields

The GenericFields type represents generic fields data

InboundInteraction

The InboundInteraction type represents a single inbound interaction step (call, video call and chat).

MultiChannel

The MultiChannel type represents multichannel data

Popup

The Popup type represents popup data

TransferTo

The TransferTo type represents transfer destination data

Inputs

ComparisonDuration

The ComparisonDuration type represents comparison between duration values.

ComparisonString

The ComparisonString type represents comparison between string values.

ComparisonTime

The ComparisonTime type represents comparison between time values.

Pagination

The Pagination type represents pagination.

SearchCallResult

The SearchCallResult type represents search criteria for call result.

SearchCallTag

The SearchCallTag type represents search criteria for call tag.

SearchContact

The SearchContact type represents search criteria for contact.

SearchGenericFields

The SearchGenericFields type represents search criteria for generic fields.

SearchInteraction

The SearchInteraction type represents search criterias for interactions (calls, video calls and chats).

SearchMultiChannel

The SearchMultiChannel type represents search criteria for multichannel.

SearchPopup

The SearchPopup type represents search criteria for popup.

SearchTransferTo

The SearchTransferTo type represents search criteria for transfer destination.

SortingInboundInteraction

The SortingInboundInteraction type represents sorting for inbound interactions (calls, video calls and chats).

Enums

ChannelDestinationType

The ChannelDestinationType enum represents the channel destination type of transfer.

ComparisonStringOperator

The ComparisonStringOperator enum represents operator to compare string values.

ComparisonTimeOperator

The ComparisonTimeOperator enum represents operator to compare time values.

ContactLookupType

The ContactLookupType enum represents contact lookup type.

ContactType

The ContactType enum represents contact type.

Context

The Context enum represents the time context of interaction.

MultiChannelType

The MultiChannelType enum represents multichannel type.

PopupType

The PopupType enum represents popup type.

SortDirection

The SortKeyInboundInteraction enum represents sort direction for inbound interactions search.

SortKeyInboundInteraction

The SortKeyInboundInteraction enum represents sort key for inbound interactions search.

Status

The Status enum represents the status of interaction.

Step

The Step enum represents interaction step to return to the interaction type result.

TransferToType

The TransferToType enum represents the type of transfer destination.

Scalars

Boolean

The Boolean scalar type represents true or false.

Int

The Int scalar type represents non-fractional signed whole numeric values. Int can represent values between -(2^31) and 2^31 - 1.

OrderedMap

The OrderedMap scalar type represents a map where keys keeps an order and values are strings.

String

The String scalar type represents textual data, represented as UTF-8 character sequences. The String type is most often used by GraphQL to represent free-form human-readable text.

Time

The Time scalar type represents date/time (RFC 3339) as string at UTC.

Advanced

Sometimes there is the need to produce data through complex queries.
To achieve this, a request can be made by directly specifying a SQL query on the available tables (described below).

Statistical data are stored in tables grouped by year / month. For example, data for May 2020 can be found in <TABLE>_202005.

Result is represented by a list of objects where the keys are the names of the selected fields and values are strings.

Example:

Count received service calls on May 2020 (table: ast_calls_202005) grouped by caller number (column: cli), reporting start time (columns: datainizio and orainizio) of the latest received call.

SELECT COUNT( DISTINCT ( uniqueid )) AS total, cli AS callerNumber, MAX( datainizio ) AS lastStartDate, MAX( orainizio ) AS lastStartTime FROM ast_calls_202005 GROUP BY cli;

Advanced Query

query {
  advancedQuery(query: "SELECT COUNT( DISTINCT ( uniqueid )) AS total, cli AS callerNumber, MAX( datainizio ) AS lastStartDate, MAX( orainizio ) AS lastStartTime FROM ast_calls_202005 GROUP BY cli;")
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  advancedQuery(query: \"SELECT COUNT( DISTINCT ( uniqueid )) AS total, cli AS callerNumber, MAX( datainizio ) AS lastStartDate, MAX( orainizio ) AS lastStartTime FROM ast_calls_202005 GROUP BY cli;\")\n}\n"}' --compressed

The advanded query can be applied on:

• Inbound Service Interactions
• Outbound Service Interactions
• Calls detail
• IVR Calls detail
• Callback Service Calls
• Abandoned Service Calls
• Calls Recordings
• Outbound Campaign Calls

Inbound Service Interactions

Table: ast_calls_yyyymm

Keys: idlastcall, step

Outbound Service Interactions

Table: ast_calls_outbound_yyyymm

Keys: idlastcall, step

Calls detail

Table: ast_cdr_YYYYMM

Keys: uniqueid, id_node

IVR Calls detail

Table: ast_ivr_records_bis_yyyymm

Keys: idcall, passaggio, nodo, passo

Callback Service Calls

Table: ast_calls_callback_yyyymm

Key: id

Abandoned Service Calls

Table: ast_calls_abandoned_yyyymm

Key: id

Calls Recordings

Table: smartrec_yyyymm

Key: filewave

Outbound Campaign Calls

Table: ast_pd_history_yyyymm

Key: id, item_number, campagna, lista, insert_time

Integration

TVox Data Model can be integrated with any third-party software that can have a Web API (HTTP GET/POST requests) as its data source and can manipulate its response in JSON format.

Microsoft and Google provide some of the most popular tools for data analysis / BI and reporting:

• Microsoft Excel & Power BI
• Google Sheets

Microsoft Excel & Power BI

Microsoft Excel, both in its desktop and cloud version (Microsoft Office 365), is one of the industry standard for spreadsheet applications featuring calculation and graphing tools for data analysis and reporting.

Microsoft Power BI is a business analytics service. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards.

Both Excel and Power BI use the Power Query tool to get data from an external source and transform it.

Example:

Below is an example of how to integrate the search for inbound service calls on Excel 2016.

query {
  inboundCalls(search: { year: 2020, month: 5 }) {
    id
    step
    cli
    dnis
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundCalls(search: { year: 2020, month: 5 }) {\n    id\n    step\n    cli\n    dnis\n  }\n}\n"}' --compressed

Power Query

let
    /* Return error messages in table */
    ErrorHandler = (errors) =>   
        let
            #"Conversione in tabella" = Table.FromList(errors, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Tabella Column1 espansa" = Table.ExpandRecordColumn(#"Conversione in tabella", "Column1", {"message"}, {"message"})
        in  
            #"Tabella Column1 espansa",
    /* Return query result data in table */
    DataHandler = (data) =>
        let
            calls = data[inboundCalls],
            #"Conversione in tabella" = Table.FromList(calls, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
            #"Tabella Column1 espansa" = Table.ExpandListColumn(#"Conversione in tabella", "Column1"),
            #"Tabella Column1 espansa1" = Table.ExpandRecordColumn(#"Tabella Column1 espansa", "Column1", {"id", "step", "cli", "dnis"}, {"id", "step", "cli", "dnis"})
        in
            #"Tabella Column1 espansa1",

    Source = Web.Contents(
    "https://<TVOX_HOST>/datamodel/query",
    [
        Headers=[
            #"Method"="POST",
            #"Content-Type"="application/json",
            #"X-Telenia-APIKey"="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
            #"Access-Control-Allow-Origin"="*" 
        ],
        Content=Text.ToBinary("{""query"": ""query { inboundCalls(search: { year: 2020, month: 5 }) { id step cli dnis }}""}")
    ]
    ),
    #"JSON" = Json.Document(Source),
    Result = try ErrorHandler(JSON[errors]) otherwise if JSON[data] <> null then DataHandler(JSON[data]) else null
in
    Result

The Power Query code on the right makes an HTTP POST call to the inboundCalls API passing the APIKey in the header; the result is converted into a table showing for each call step its id, step, cli and dnis.

To execute the request:

1. Open Excel 2016.
2. Select the Data tab.
3. From Get & Transform select New Query > Other sources > Empty query.
4. From Query open Advanded editor and write/paste the code.

This is just a simple example of how to integrate the TVox Data Model with Excel 2016 but it can be developed according to your needs and also integrated on Microsoft Excel for Office 365 and Microsoft PowerBI.

Google Sheets

Google Sheets is a cloud-based software where you can create and edit spreadsheets directly in your web browser or mobile app (Android or iOS). Multiple people can work simultaneously, you can see people’s changes as they make them, and every change is saved automatically.

Google Sheets does not natively allow you to retrieve data from a web source in JSON format, but it is possible to integrate custom functions (in Javascript code) capable of doing so.

IMPORTJSONAPI (complete documentation here) provides a custom function to selectively extract data from a JSON or GraphQL API in a tabular format suitable for import into a Google Sheets spreadsheet.
Following example uses this function.

Example:

Below is an example of how to integrate the search for inbound service calls on Google Spredsheet.

The Function Query code on the right makes an HTTP POST call to the inboundCalls API passing the APIKey in the header; the result is converted into a table showing for each call step its id, step, cli and dnis.

query {
  inboundCalls(search: { year: 2020, month: 5 }) {
    id
    step
    cli
    dnis
  }
}

curl 'https://<TVOX_HOST>/datamodel/query' -H 'Accept-Encoding: gzip, deflate, br' -H 'Content-Type: application/json' -H 'Accept: application/json' -H 'Connection: keep-alive' -H 'DNT: 1' -H 'Origin: https://<TVOX_HOST>' -H 'X-Telenia-APIKey: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --data-binary '{"query":"query {\n  inboundCalls(search: { year: 2020, month: 5 }) {\n    id\n    step\n    cli\n    dnis\n  }\n}\n"}' --compressed

Function Query

=IMPORTJSONAPI("https://<TVOX_HOST>/datamodel/query"; "$..inboundCalls[*][*]"; "id,step,cli,dnis"; "method=post"; "contentType=application/json"; "headers={ 'X-Telenia-APIKey' : 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' }"; "payload={ 'query': '{ inboundCalls(search: { year: 2020, month: 5 }) { id step cli dnis }}'}")

To add IMPORTJSONAPI custom function to your spreadsheet and use it, follow this procedure:

1. Open the a Google Sheet spreadsheet in your browser.
2. Select the Tools > Script editor menu option. This will open a script editor window. You will need to copy and paste the function code into a blank script file.
3. Copy the entire contents of the IMPORTJSONAPI.gs file. The raw file can be found here.
4. Paste this into the blank Code.gs script file or another blank script file that you have created.
5. Select the File > Save menu option to save the script.
6. You should now be able to use the =IMPORTJSONAPI() function in your sheet.
7. In your spreadsheet write/paste the code in a cell.

This is just a simple example of how to integrate the TVox Data Model with Google Sheets but it can be developed according to your needs and also integrated on Google Sheets with you own custom function.

Changelog

2.2.2

Available from: 24.8.0, 24.3.25, 24.7.4
* Features / Enhancements
* added new chatsession count query: chatSessionCount

2.2.1

Available from: 24.6 * Features / Enhancements * added new tickets history query: TicketsHistory

2.2.0

Available from: 23.0.0 * Features / Enhancements * added new Power Dialer query: PowerDialerCampaigns, PowerDialerCampaignExecutions, PowerDialerMultiChannelHistory * added new Power Dialer Instant Messaging query: PowerDialerInstantMessagingCampaignStatus, PowerDialerInstantMessagingHistory * added new Chat Channel query: chatHistorySummary, chatHistorySummaryCount, chatSession * added new Callback Call query: callbackCallsHistory, callbackCallsHistoryCount * added new Call Detail Record (CDR) query: callDetailRecords, callDetailRecordsCount, callDetailRecordsSummary

2.1.4

Available from: 22.2.0 * Features / Enhancements * added new Power Dialer query: PowerDialerCampaigns, PowerDialerCampaignExecutions, PowerDialerMultiChannelHistory * added new Power Dialer Instant Messaging query: PowerDialerInstantMessagingCampaignStatus, PowerDialerInstantMessagingHistory

2.1.3

Available from: 23.0.0, 22.1.0, 22.0.12

• Features / Enhancements
  • added new Survery Report query: surveyList, surveyReport, surveyReportCount

2.1.2

Available from: 23.0.0, 22.1.0, 22.0.9

• Features / Enhancements
  • added new Business Process Manager query: bpmExecutionReport, bpmExecutionReportCount

2.1.1

Available from: 23.0.0, 22.1.0, 22.0.4

• Features / Enhancements
  • search method ticketArticles: added the ticketNumber in the search filter and result

2.1.0

Available from: 23.0.0, 22.1.0, 22.0.2

• Features / Enhancements
  • added new search method abandonedCall, abandonedCalls, abandonedCallsCount

2.0.0

Available from: 22.0.0

• Features / Enhancements
  • added new search method "multiChannelSummary"
  • tickets method adaptation for contacts oblivion, 4 new parameters have been added (forgottenTimeUnit, notForgottenTimeUnit, forgottenArticleCount, notForgottenArticleCount)

1.2.9

• Features / Enhancements
  • added new search method "IvrNodeInteractions"
  • added new search method "IvrNodeInteractionCount"
  • added new search method "IvrNodeInteractionCountGroup"
  • added new search method "IvrNodeInteractionCountGroups"

1.2.8

• Features / Enhancements
  • same as 1.2.7
• Bug Fixes
  • support - ticketArticlesCountGroup sort by

1.2.7

• Features / Enhancements
  • datamodel now can be switched between master/slave machines
  • addressbook contacts - a contact can now have multiple addresses
  • added new search method "ticketArticles"
  • added new search method "ticketArticlesCount"
  • added new search method "ticketArticlesCountGroup"
• Bug Fixes
  • support - search ticket, fix on "first response time" search
  • support - search ticket, fix on "knowledge base use" search

1.2.6

• Features / Enhancements
  • knowledge base answer/category
  • support, added new serachable property: escalation level, updated at, queue type
  • In Redundant architecture, comprehensive warnings are provided when the slave is not accessible
  • added new search method "PowerDialerCampaignSummary"
  • added new search method "PowerDialerCampaignAnalytic"
• Bug Fixes
  • support, createArticleSenderType now is corectly valorized

1.2.5

• Features / Enhancements
  • Removed the pagination, sorting properties from the search objects, and add them to the parameters of the respective GraphQL methods
  • Sorting result of Group Count methods
  • inboundCallsCountGroups
  • outboundCallsCountGroups
  • inboundVideosCountGroups
  • inboundChatsCountGroups
  • ticketsCountGroups
• Bug Fixes
  • The search by count filtered for a contact that does not exist returns the total count

1.2.4

• Features / Enhancements
  • Count groupings for phone, chat, video, support channels

1.2.3

• Features / Enhancements
  • Call, Video and Chat interaction are no longer treated as a set of interactions having the same unique-id, but as individual (atomic) interaction
  • For the support, phone, video and chat channels it is now possible to view and search the use that the operator has made of the knowledge base, through the new knowledgeBaseUse property
  • For phone, video and chat channels, it is now possible to view and search operator, customer and organization as AdressBoockContact

1.2.2

• Features / Enhancements
  • Support

1.2.1

• Features / Enhancements
  • Addressbook

1.2.0

• Features / Enhancements
  • Advanced queries for Addressbook
  • Advanced queries for Support

1.1.0

• Features / Enhancements
  • Outbound Service Calls

1.0.0

• First release
  • Inbound Service Calls
  • Inbound Service Video Calls
  • Inbound Service Chats
  • Advanced queries for calls, video calls and chats