Conversation Aggregate Data Model

Analytics and Reporting
1

Hello All,

I am trying to get an Division, Queue, User Aggregated data from Conversation Aggregated Query and store in DB.

When I query an API by grouping Division, Queue, User received the below response.
Concern in below response is "nConnected" value is different. For voice calls it is "1" and again voice calls under "Division" is "2".
As per my understanding first section should be giving total voice calls and then followed by number of calls on each division.

  1. Could you please elaborate the difference between "nConnected" value each section?
  2. Do you have best practice for storing Division, Queue, User aggregated data in to DB on daily basis?
  3. What is the algorithm/logic is applied to get the aggregated data from conversation details query output.
  4. Please explain me the response order for Conversation Aggregate Data.

{
"results": [
{
"group": {
"mediaType": "voice"
},
"data": [
{
"interval": "2022-09-04T18:30:00.000Z/2022-09-11T18:30:00.000Z",
"metrics": [
{
"metric": "nConnected",
"stats": {
"count": 1
}
}
]
}
]
},
{
"group": {
"divisionId": "d0b618e5-ea69-4273-a1d3-ddeb7413825c",
"mediaType": "voice"
},
"data": [
{
"interval": "2022-09-04T18:30:00.000Z/2022-09-11T18:30:00.000Z",
"metrics": [
{
"metric": "nConnected",
"stats": {
"count": 2
}
},
{
"metric": "tIvr",
"stats": {
"max": 18784,
"min": 18784,
"count": 1,
"sum": 18784
}
}
]
}
]
},
{
"group": {
"divisionId": "d0b618e5-ea69-4273-a1d3-ddeb7413825c",
"mediaType": "voice",
"userId": "99dbadad-0f1a-4f76-a576-09b57f6981f9"
},
"data": [
{
"interval": "2022-09-04T18:30:00.000Z/2022-09-11T18:30:00.000Z",
"metrics": [
{
"metric": "tContacting",
"stats": {
"max": 16572,
"min": 16572,
"count": 1,
"sum": 16572
}
},
{
"metric": "tDialing",
"stats": {
"max": 14771,
"min": 14771,
"count": 1,
"sum": 14771
}
},
{
"metric": "tHandle",
"stats": {
"max": 150784,
"min": 150784,
"count": 1,
"sum": 150784
}
},
{
"metric": "tHeld",
"stats": {
"max": 91852,
"min": 91852,
"count": 1,
"sum": 91852
}
},
{
"metric": "tHeldComplete",
"stats": {
"max": 91852,
"min": 91852,
"count": 1,
"sum": 91852
}
},
{
"metric": "tTalk",
"stats": {
"max": 27589,
"min": 27589,
"count": 1,
"sum": 27589
}
},
{
"metric": "tTalkComplete",
"stats": {
"max": 27589,
"min": 27589,
"count": 1,
"sum": 27589
}
}
]
}
]
}
]
}

2

There is no rollup in the aggregates, you get a distinct value for every permutation of every group, no additional levels of summarazation or subtotals.

If you were to tablify the results they're literal;

image
image990×77 3.41 KB

Result 1 is before the call got the an agent, probably the IVR participant. Those metrics are not agent metrics, therefore there is no agent dimension or division dimension. It's not a summary of all voice mediatypes.

Result 2 is probably the same call, and probably from the ACD participant, where it connected twice, or another IVR participant where it actually went through a flow. There's still no agent in that life cycle of the call so no agent metrics. It's not a summary of every voice call in that dimension.

Result 3 is the call reaching an agent, there is no connected and no IVR because those metrics don't peg on the agent participant, but the agent data begins showing up.

And so on.
For every metric you've asked for it's building the table upwards through the conversation tree finding the applicable elements of that metric you've asked to group by.

It really helps to find a specific conversation id that a bunch of stuff happened to, and run a conversation detail on that and then run a conversation aggregate on just that same call id and look at how it rolled the data up from the raw to the aggregate.

1 Like
3

Thank you so much for your help.

Do you have schema design for Conversation Aggregate Query?
It would be great if you can share the algorithm used to build Aggregate logic.

4

I don't think it's possible to generate more than a vague schema because the shape will always depend on the inputs, which is why the documentation is so vague POST /api/v2/analytics/conversations/aggregates/query

image
image763×586 24.5 KB

The best I can tell you is to imagine a SQL GROUP BY being applied as a transform to the result object from this API - GET /api/v2/analytics/conversations/details/jobs/{jobId}/results to envision what your end result may look like. It can be tricky and unexpected because there are a lot of moving parts.

5

Again, Thank you for help on this.

I am looking for Generating Division, Queue, User aggregate Metrics from Conversation Details Query response.

Do you have logic to compute on conversation details response to come up aggregate metrics? Appreciate your help on this.

6