Relate transferred call with inbound call for reporting

Any and all non-support discussions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Relate transferred call with inbound call for reporting

Postby josecapurro » Wed Aug 21, 2019 12:19 pm

Hello there,

I'm writing a custom report and i need to show to which number was a inbound call transferred.

So, i see i have vicidial_log with the detail of all calls, and vicidial_xfer_log with only the transferred ones.

I can't see how to JOIN to produce the result i want.

vicidial_xfer_log doesn't have uniqueid.

Any insights?
josecapurro
 
Posts: 25
Joined: Tue Sep 01, 2015 9:12 am

Re: Relate transferred call with inbound call for reporting

Postby mflorell » Thu Aug 22, 2019 6:34 am

How exactly are these inbound calls being transferred?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Relate transferred call with inbound call for reporting

Postby josecapurro » Sat Aug 24, 2019 9:48 am

Those are manual transfers done with the PARK CUSTOMER DIAL button, using the LEAVE 3WAY CALL button.

Some things i see:
- In call_log i can identify the calls by its prefix looking at the extension column. But how can i identify which agent transferred the call?
In the caller_code column i see DCXXXXXXWYYYYYYYYYYW, but i do not know what to do with that.

- In user_call_log i see the transferred calls done by the user by looking at the call_type column. It shows XFER_3WAY.
I have there the user who transferred the call and the campaign, which is fine, but in the number_dialed it only shows what seems the Asterisk channel, in the form Local/XXXXXXXX@default, but i do not have a uniqueid or something to JOIN to another table and retrieve the actual number dialed, like shown in the call_log table on the extension column.
Last edited by josecapurro on Sat Aug 24, 2019 10:15 am, edited 1 time in total.
josecapurro
 
Posts: 25
Joined: Tue Sep 01, 2015 9:12 am

Re: Relate transferred call with inbound call for reporting

Postby josecapurro » Sat Aug 24, 2019 9:56 am

More:

If i look up the lead_id of the row in user_call_log, i see the phone number of the incoming call. But i also need to retrieve the number to which the incoming call was transferred, with the length_in_sec and start_time, as shown in call_log.
josecapurro
 
Posts: 25
Joined: Tue Sep 01, 2015 9:12 am

Re: Relate transferred call with inbound call for reporting

Postby williamconley » Mon Sep 09, 2019 10:22 pm

Remember that while you refer to it as a "transfer", it's actually a "3-way call" which should be logged in the closer log. Leaving the 3-way call later results in what you call a transfer, but when it starts ... it's a conference (3-way) call initiated by an agent with the power to do so (ie: closer). We actually had to write a Real Time screen version to watch these calls for a client who had a lot of them (since these disappear from the real time screen when the agent leaves the 3-way).
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Relate transferred call with inbound call for reporting

Postby mflorell » Tue Sep 10, 2019 6:22 am

The 'user_call_log' table is where the 3-way call log data is stored, it will show you the dial string used to place that call as well as the lead_id of the customer call.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Relate transferred call with inbound call for reporting

Postby josecapurro » Thu Nov 14, 2019 9:10 am

Final one: and how can i retrieve the duration of the 3WAY call?
josecapurro
 
Posts: 25
Joined: Tue Sep 01, 2015 9:12 am

Re: Relate transferred call with inbound call for reporting

Postby mflorell » Fri Nov 15, 2019 7:11 am

That depends on how that call is being placed. If you are using a dialplan path that goes through the NVA recording agi script there are several options for how it will log the calls.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Relate transferred call with inbound call for reporting

Postby geilt » Thu May 28, 2020 2:44 pm

I am having a HELL of a time trying to figure out how to connect the user_call_log to the ACTUAL call that was made, since there is NO uniqueid on the user_call_log so I can actually find the DURATION of the call that took place.

Do you have any info on how I can connect the call to the actual call_log, or relate it back to the conference / call that took place where the transfer took place?

Please?
Alexander D. Conroy
CTO - Esotech Inc. / TLDCRM
@geilt.com @esotech.com @tldcrm.com
Running 40+ ViciBoxes in AWS with a Custom Interface.
VERSION: 2.14-714a BUILD: 190628-1511 openSUSE Leap 42.3
geilt
 
Posts: 9
Joined: Thu Apr 05, 2018 9:27 am
Location: Costa Mesa, CA

Re: Relate transferred call with inbound call for reporting

Postby williamconley » Thu May 28, 2020 3:29 pm

No time to check, but I've bumped into this scenario before in both this and other software.

This scenario: Primary call record covers a period of time, other calls *may* occur during that call and are related, but since they begin and end at random times (based on agent activity) there is no "unique ID" or "matching start/end time". Thus you are left with HOPING that both records are related to yet a 3rd record in another table. For instance, is there an Agent Log that will have a link (directly OR indirectly) to the original call_log and also to the transfer call and/or recording.

Essentially, you are going to have to check the spider web of linked events and records and see if they will pull in the one you're trying to track. If you're lucky. If that works, you can use a JOIN in SQL to directly link the records and you're good. If not ...

The next phase is to build a routine that will use code to return records from the related tables "during this call". Some will create a link table to store the results and run the routine hourly to create an easier-to-use SQL link for later reporting.

Had similar issues in FreePBX. Their solution was imperfect, but pretty dang good.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: Relate transferred call with inbound call for reporting

Postby mflorell » Thu May 28, 2020 4:33 pm

If properly configured, the NVA recording script can log the lead_id on those 3way calls. That and the call_date within the range of the original call are usually enough to link the events together.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Relate transferred call with inbound call for reporting

Postby williamconley » Thu May 28, 2020 4:54 pm

mflorell wrote:If properly configured, the NVA recording script can log the lead_id on those 3way calls. That and the call_date within the range of the original call are usually enough to link the events together.


That identifies a table with the necessary information ... and "within the range of the original call" is the falling down point for a SQL join statement to work. Thus the routine to create links in a sep table for a later simple sql join.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 56 guests