ECE 11.6 Data definitions

[david.kelly1]

Greetings,
I'm going to start out with that I'm not the IT guy(s) in my company, I'm the low level powerbi report builder for our call center.  So alot of the technical stuff I may not know.

What I'm looking for is some sort of definition guide to the database schema.  I found this  but it doesn't "define" what the column in the table are.  For example  looking at the "EGMLR_SMY_Activity" table,  I look at "handle_time" column, it "defines it" as "A 64-bit long value with precision 19 and scale 3 representing the handle time in seconds."  which if I was a DBA it might mean something, but I'm looking more for a "Handle time is the time from when the email is received in the system to the time it was marked as completed"  or something like that.   Like what is the difference between handle time, response time, wrap time, work time, resolve time, close time, turnaround time.... they all have the same definition.  So any help with that would be amazing.

2nd question I have is how is when pulling information from the egmlr_smy_queue table it has "Completed_mails" column.  Which I'm guessing is the number of emails/activities that are marked as "completed" but when I pull the data I'm running into a situation were the number of "completed" emails exceed the number of "incoming" (New Cases, No cases, Existing Cases, and composed all added together)  Am I missing something on the incoming?   How could a queue have more completed emails than they have incoming?

First post here so if I posted this in the wrong place sorry and could you help me by pointing me in the right direction.  Thanks in advance. 

[Omar Deen · Accepted solution]

Unfortunately, this information is not available to us. Been asking for years for a database schema document, similar to what Cisco publishes for CCE, but eGain won't give it up.

[bill.king1]

This is from a very old version (pre ECE but more the former email product), but may help you with some things?

https://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/cisco_interaction_manager/cim_42/maintenance/guide/cisco_im_cce_schemaguide.pdf

[bill.king1]

This doesn't have all of the tables you're looking for either, but since it is somewhat buried on the site, posting the link here as well in case it helps you.
https://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/enterprise_chat_email/enterprise_chat_email_11_6_1/Programming/Guide/ece_11_6_data_dictionary.pdf

[Konstantin Vaksin · Accepted solution]

There is no DB schema guide available.

[david.kelly1]

Greetings,
Thanks for all the replies thus far, it looks like no schema is available. As such I've been doing some testing and I want to see if others have come up to the same conclusion that I've come to.  I sent in two emails into our ece box, had them transfer to my box, and went through the process of typing up a response and clicking the send/complete buttons.  Here is what I've found:

First email 

Activity ID

1824874

1st Assigned to completion

0:27:00

Resolve time

Time of Sent

2:14 PM

2:14 PM

2nd assigned to completion

0:18:00

Time in system

2:15 PM

Creation to Completion

0:28:00

time assigned

2:15 PM

Creation to replied

0:26:00

Response time

time 1st read

2:24 PM

Creation to 1st assignment

0:01:00

time transfer

2:24 PM

2:24 PM

Creation to 2nd assignemtn

0:10:00

Time 2nd read

2:29 PM

2:29 PM

1st assignemtn to replied

0:25:00

turnaround

time replied

2:40 PM

2:40 PM

2nd assignemtn to replied

0:16:00

end wrap

2:42 PM

1st read to reply

0:16:00

time completed

2:42 PM

2:42 PM

2nd read to reply

0:11:00

1st Assignement to Read

0:09:00

2nd Assignement to Read

0:05:00

1st assignemtn to transfer

0:09:00

ACTIVITY_ID

WORK_TIME

WRAP_TIME

RESPONSE_TIME

HANDLE_TIME

CLOSE_TIME

RESOLVE_TIME

INCOMING_WAIT_TIME

OUTGOING_WAIT_TIME

TURNAROUND_TIME

TURNAROUND_TIME_BH

RESPONSE_TIME_BH

1824874

13

0

0

13

0

0

0

00:00.0

534

534

0

1824874

50

0

0

50

0

0

0

00:00.0

0

0

0

1824874

116

0

1553

116

0

1622

0

00:00.0

951

951

1553

179

0

1553

179

0

1622

0

0

1485

1485

1553

0:02:59

0:00:00

0:25:53

0:02:59

0:00:00

0:27:02

0:00:00

0:00:00

0:24:45

0:24:45

0:25:53

2nd email

Activity ID

1825435

1st Assigned to completion

0:30:00

Resolve time

Time of Sent

3:35 PM

2nd assigned to completion

0:09:00

Time in system

3:36 PM

Creation to Completion

0:32:00

time assigned

3:37 PM

Creation to replied

0:29:00

Response time

time 1st read

3:58 PM

Creation to 1st assignment

0:02:00

time transfer

3:58 PM

Creation to 2nd assignemtn

0:23:00

Time 2nd read

4:00 PM

4:00 PM

1st assignemtn to replied

0:28:00

turnaround

started typing

4:04 PM

Started typing to completed

0:02:00

Work Time/Handle time

reply - sent only

4:05 PM

2nd assignemtn to replied

0:07:00

hit completed

4:06 PM

end wrap

4:07 PM

4:07 PM

1st read to reply

0:07:00

time completed

4:07 PM

4:05 PM

2nd read to reply

0:05:00

1st Assignement to Read

0:21:00

2nd Assignement to Read

0:02:00

1st assignemtn to transfer

0:21:00

ACTIVITY_ID

WORK_TIME

WRAP_TIME

RESPONSE_TIME

HANDLE_TIME

CLOSE_TIME

RESOLVE_TIME

INCOMING_WAIT_TIME

OUTGOING_WAIT_TIME

TURNAROUND_TIME

TURNAROUND_TIME_BH

RESPONSE_TIME_BH

1825435

13

0

0

13

0

0

0

00:00.0

1269

1268

0

1825435

117

0

1730

117

0

1824

0

00:00.0

399

400

1730

117

0

1730

130

0

1824

0

0

1668

1668

1730

0:01:57

0:00:00

0:28:50

0:02:10

0:00:00

0:30:24

0:00:00

0:00:00

0:27:48

0:27:48

0:28:50

From this I've concluded that:

• Resolve time: is the time between when it was first assigned to someone to the time it is completed.
• Response time: is the time between  creation(arrived in system) to the time it was replied.
• Turnaround time: is the time between 1st assignment to the time is was replied to.
• Work time: is from starting typing a message to hitting complete.
• Handle time: is work time + wrap time

the last one is a guess, none of our "wrap time" is recorded even though I had a period of wrap.  I'm also not sure if handle times is work time+wrap time  or if work time is handle+wrap.

From others testing is this what your seeing or am I missing something?

[david.kelly1]

Greetings,

Firstly thanks to all them who responded thus far.  It looks like no schema is available at this time, as such I'll have to figure them out on my own.  I've started some testing, and have, I believe, figure out the definitions, but want to compare notes with others who might have done some testing themselves.  Or to see if the type of testing I did was the right type or if I should do something else.

Here is what I did:  I created an e-mail in my outlook, and sent it into the department I'm workings in email ece box.  once it was assigned to an agent, I had them transfer it into my ece box.  From there I waited a minute or two, before I clicked on it(to read).  Then waited a minute before I started typing up a message. and spent a full 2 minutes typing out garbage. then clicked "send".  Waited a minute then clicked complete.  Then waited another minute to click "end wrap".   I then used the Audit trail, and the database entries to match things up.

Here is the data from the audit trail, and my clocking times.
Activity ID 1825435
Time of Sent 3:35 PM
Time in system 3:36 PM
time assigned 3:37 PM
time 1st read 3:58 PM <-- this is when the agent first open it to transfer to me.
time transfer 3:58 PM <-- this is when it was transferred to me.
Time 2nd read 4:00 PM  <-- this is when I read/open it.
started typing 4:04 PM
reply - sent only 4:05 PM
hit completed 4:06 PM
end wrap 4:07 PM

Here is the data from the DB.

ACTIVITY_ID	WORK_TIME	WRAP_TIME	RESPONSE_TIME	HANDLE_TIME	CLOSE_TIME	RESOLVE_TIME	TURNAROUND_TIME	TURNAROUND_TIME_BH	RESPONSE_TIME_BH
1825435	13	0	0	13	0	0	1269	1268	0
1825435	117	0	1730	117	0	1824	399	400	1730
Total in sec	117	0	1730	130	0	1824	1668	1668	1730
Converted to hh:mm:ss	0:01:57	0:00:00	0:28:50	0:02:10	0:00:00	0:30:24	0:27:48	0:27:48	0:28:50

When I math'ed the audit trail out I get this.

Creation to Completion 0:32:00
Creation to replied 0:29:00
Creation to 1st assignment 0:02:00
Creation to 2nd assignment 0:23:00

1st assignment to completion 0:30:00
1st assignment to replied 0:28:00

1st assignment to Read 0:21:00

1st read to reply 0:07:00

1st assignment to transfer 0:21:00

2nd assignment to completion 0:09:00

Started typing to completed 0:02:00
2nd assignment to replied 0:07:00
2nd read to reply 0:05:00
2nd assignment to Read 0:02:00

In conclusion what I'm guessing the definition for the key elements are as following:

• Resolve Time: The time from 1st assignment to Completion.  or Total time from assignment to completion no matter how many times it spends getting passed around.
• Response time: The time from Creation to Replied.  I believe this would be "final" reply and not just first reply.  Mean if I were to have hit replied, then waited a few minutes typed up a 2nd message and hit replied again.  it would include the time up to the 2nd reply.  Need to confirm with more testing.
• Turnaround Time: The time from 1st assignment to Replied.  Again I'm not sure if a 2nd reply has an affect on this, need to do more testing.
• Work Time:  The time from first started typing in the email to the time you hit complete.  *
• Handle time:  is the time of both work time and wrap time *

* I'm not sure if handle time is work+warp or if work time is handled+wrap.   For what ever reason, even though I have wrap turned on, the wrap time isn't recorded in the database.  

has anyone else done any of their own testing?  Based on the data above, are my conclusion sound?  What type of other testing would you recommend?  Did I miss anything?

Thanks again