UCCE 7.5 - Call Duration for transfer to IVR campaigns

[Ion Cristian Radescu]

Hi,

We have the following situation: one of our customers wants to know the call duration for one transfer to IVR campaign. He wants to know if his customer listened the entire message or he closed the call before reaching the end.

We tried to do this in two ways:

1. We tried to SET AccountNumber in t_Dialer_detail with some timestamps but it seems that this variable is ReadOnly because we don't see the result in the table. Also CallDuration is NULL in this table.

2. We tried to find the outbound call in t_Termination_Call_Detail via "RecoveryKey" but we have some strange results. How are related the RecoveryKey columns from the two tables? Is is possible to be different?

What is the best method to resolve this request in your opinion?

Any help would be greatly appreciated.

Thanks,

Cristian

[Gergely Szabo]

Hi,

first of all, do not write into any ICM database table, you might royally screw up the whole system.

What I would recommend is: if this is an IP IVR system anyway - meaning it has got the Database Subsystem enabled -, then why not send a pair of SQL UPDATE's right before the Play Prompt step and right after it; and use the On Exception (ContactInactiveException) step to send the execution to a block where it can also use an SQL UPDATE but with a different flag.

Like this (assuming you're using a MS SQL Server > 2000):

Set up a database table with CREATE TABLE calls (id INT PRIMARY KEY IDENTITY(5000,1), action VARCHAR(50), timestamp DATETIME)

In your IP IVR script:

1. On Exception (ContactInactiveException) Goto CUSTOMER_HUNG

2. DB Write ("INSERT INTO calls VALUES ("PLAY_START",GETDATE())

3. Play Prompt (of your choice)

4. DB Write("INSERT INTO calls VALUES ("PLAY_END_NORMAL", GETDATE())

5. End

6. (Label: CUSTOMER_HUNT) DB Write ("INSERT INTO calls VALUES ("PLAY_END_CUSTOMER_HUNG_UP", GETDATE())

7. End

Make sure you set this IP IVR script as uninterruptible (so the customer can only kill the prompt by killing the whole phone call.

If you don't want to go over this hassle and you know exactly the prompt's length, you may as well use the Dialer_Detail and the Termination_Call_Detail tables in ICM. Use PeripheralCallKey to join them. Then look for the duration column in TCD.

The Outbount Option guide has got some nice examples on this (especially when it comes to explaining database values).

G.

[geoff · Accepted solution]

Are these IVE Campaigns in IP-IVR, CVP microapps or CVP VXML?

Regards,

Geoff

[Ion Cristian Radescu]

Hi,

Thank you both for your answers.

The campaigns are in IP-IVR.

Cristian

[Ion Cristian Radescu]

Hi Gergely,

After I used "PeripheralCallKey" everything looks good now in the select query. Still I have problems with Duration column in TCD. This value is not accurate. I know the prompt has exactly 16 seconds, I am listening all the message and in duration column I have values like 9- 10 seconds. This means I can not trust the values from "Duration" column.

I am trying in the outbound script to SET timestamps in Call Variables from TCD table but it doesn't work. All variables are null. Do you have any idea about what might happen here? For an inbound script I can set call variables without problems.

Thanks,

Cristian

[Gergely Szabo]

Hi,

for some strange reason setting variables in outbound scripts does not work. So no use setting them there, Outbound Option will set the AccountNumber, Firstname, Lastname etc from the dialling lists anyway.

About the Duration field: did you check the document titled "Outbound Option Guide for Cisco Unified Contact Center Enterprise and Hosted" for your UCCE version. If not, then please do read it. There's a section, or, actually, appendix titled "Appendix D: Termination_Call_Detail Table", describing various scenarios, including the one you are looking for (see the subsection titled "Transfer to IVR TCD Records". It might happen that you are looking at the wrong rows - match a row in TCD by PeripheralCallKey, then get all the rows with the same RouterCallKeyDay and RouterCallKey. There should be at least 3 rows for completed calls.

G.

[Ion Cristian Radescu]

Hi,

You are right. We can not set variables in outbound script Not even CED.

About the duration: something strange is happening there. Please see the attached image.

For every call we have two entries in the TCD table. One for internal call and one for the call to the customer. I masked the customer number with red. Please see the duration values there. When I am listening all the message I have there 10 seconds(even if the prompt has 16 seconds, and I am seeing 16 seconds on my mobile phone). When I close the call after 2-3 seconds I have in TCD 9 seconds as duration. Anyway, because I have a 16 seconds message, all these values are strange.

Do you know what should I verify in order to have these values accurate?

Cristian

[Gergely Szabo]

Hi,

I've got a strong feeling you are still looking at the wrong rows. Or there's something wrong with your call routing.

Can you please post the whole SQL query? And, please, do not use tables (t_*) directly.

Also, you can try joining by RCKD and RCK

SELECT dd.*, tcd.* FROM Dialer_Detail dd JOIN Termination_Call_Detail tcd ON (dd.RouterCallKeyDay = tcd.RouterCallKeyDay AND dd.RouterCallKey = tcd.RouterCallKey) WHERE dd.CampaignID = 5001

Save it as a CSV file then post it to this thread.

G.

[Ion Cristian Radescu]

Hi,

I don't have RouterCallKeyDay and RouterCallKey in Dialer Detail view or (t_) table.

Cristi

[Gergely Szabo · Accepted solution]

Hi,

sorry, then this was a misunderstanding, I thought you've got UCCE 7.5.

Anyway. Can you just get the RouterCallKeyDay and RouterCallKey combination for one call?

SELECT tcd.RouterCallKeyDay, tcd.RouterCallKey FROM Dialer_Detail dd JOIN Termination_Call_Detail tcd ON (dd.PeripheralCallKey = tcd.PeripheralCallKey) WHERE  dd.CampaignID = 5001

Take one RCKD and RCK pair, then do this again:

SELECT * FROM Termination_Call_Detail tcd WHERE tcd.RouterCallKeyDay = [your RCKD value] AND tcd.RouterCallKey = [ your RCK value ]

G/.

[Ion Cristian Radescu]

Hi Gergely,

Great idea! Now everything is fine!

For every combination of RCKD and RCK I have two entries in TCD: one with call disposition 7(short) for internal calls to CTIs and one with call disposition 13(handled) if the customer listened all the message or 6(abandoned) if the customer closed the call. I can see now that "Talk time"/"Duration" in TCD table are accurate for all these calls.

Thank you very much for your help. You have 5 points from me :-)

Cristian

P.S: I have UCCE 7.5(9) but I don't have RKCD and RKC columns in Dialer Detail.

[Gergely Szabo]

Hi, glad to hear that.

Actually, I don't have any 7.5.x system handy so the only reference was the Database Schema document for 7.5(1) from 2008 - apparently it has got some "bugs".

G.