Discrepency in User Stats compared to Agent Detail report

[tbenson]

Just had a client point out to me that he was having issues with running reports for an Agent. Seems that when he was running The Agent Detail for the day we see 8 "Sales" (being XFER's counted as sales). However when going to this Agent under User, and selecting stats, it shows 3 XFERS (being the real status not the combined agent detail report status)..

I looked into this and found that the vicidial_log actually only holds 3 XFERS for this agent. Pulling the select statement from the vicidial_agent_log it shows all 8 XFERS. The second, fourth and fifth entries from the vicidial_agent_log were in the vicidial_log, but not the rest.

Selecting one of the missing lead id's from vicidial_agent_log and performing a lookup against the vicidial_log for the lead_id and the date range (without the user) shows that the lead id doesnt even exist in the vicidial_log for the day.

Am I missing something or is the code that updates the vicidial_log not working properly?

[mflorell]

That certainly sounds like something is wrong, is there anything custom in this installation?

Was any code altered?

What is the loadavg on the web server at peak load?

Could you give us more information on the specifics of this setup?
- hardware
- number of agents
- any recording?
- kind of outside trunks
etc...

[tbenson]

Was any code altered?

manager_send.php was altered to increase logging and changed path for stored log files to a logs subdirectory.

vicidial.php was altered to log to the same subdirectory. As well as changed mainxfer_send_redirect function to manually set redirectvalue = lastcustchannel to bypass manual dial problem we were having (listed in bug tracker)

What is the loadavg on the web server at peak load?

Web server is actually a set of load balanced servers that never goes above 0.2

Could you give us more information on the specifics of this setup?

- hardware

Dialer is a PowerEdge 860 Dual Core 2.8GHz with 1GB of RAM.

Web Server is a load balanced set of PowerEdge 860 Dual Core 2.8Ghz with 1GB of RAM.

Database Server is a PowerEdge 850 Dual Core 3.0Ghz with 2GB of RAM>

- number of agents

10 Simlutanious maximum, and usually running around 6-8.

- any recording?

On demand, but none are stored or being requested.

- kind of outside trunks

Two PRI running on a Sangoma A104D

[mflorell]

What kind of load balancer are you using?

Are you using eaccellerator with PHP/Apache?

[tbenson]

Hardware load balanced device using BSD, not software cache accelerator.

[mflorell]

I would recommend using only one web server unless you have over 120 agents. Also, you should be using eaccellerator for PHP if you have that many agents.

[tbenson]

I looked further into this and found that the entries are actually in the call_log as well as vicidial_agent_log, and the timestamps show up with the correct delay. However since the leads are being set to XFER my select statement was only looking for them. So the problem is that the call_log status for the call is marked incorrectly compared to the actual disposition selected, which is correctly placed into vicidial_agent_log.

This was noticed mainly that the XFER listing for agents under user stats did not match the agent detail report. It appears agent detail comes from vicidial_agent_log and the stats come from the call_log.

Any idea why the records would be there for every call, but have an incorrect status set for them?

Thanks

[mflorell]

The call_log status is not related to the vicidial_log, vicidial_agent_log or vicidial_list status.

If you turn on logging, can you post the transfer AGI output in the agiout logfile for one of these calls?

[kbenson]

The call_log status is not related to the vicidial_log, vicidial_agent_log or vicidial_list status.

Sorry, he meant vicidial_log, not call log. He wasn't referring to vicidial_list status, but to the disposition/status listed in vicidial_log and vicidial_agent_log. Specifically, vicidial_agent_log reports more SALEs for users than vicidial_log, and not because it's reporting SALEs and XFERs together. The missing calls usually show up in vicidial_log, but with different dispositions (N, NI, DNC, etc).

This is easily noticable in our system as the "Agent Performance" report (using vicidial_log) shows different values than the "Agent Performance Detail" report (using vicidial_agent_log).

If you turn on logging, can you post the transfer AGI output in the agiout logfile for one of these calls?

We'll try to get this to you shortly.

[kbenson]

Code: Select all

mysql> select agent_log_id, user, event_time, lead_id, campaign_id, status, comments, sub_status from vicidial_agent_log where status="XFER" and user="brandy" and event_time > '2007-08-22 10:30:00' and event_time < '2007-08-22 11:30:00';
+--------------+--------+---------------------+---------+-------------+--------+----------+------------+
| agent_log_id | user   | event_time          | lead_id | campaign_id | status | comments | sub_status |
+--------------+--------+---------------------+---------+-------------+--------+----------+------------+
|       225591 | brandy | 2007-08-22 10:50:59 |  323758 | BLEND_01    | XFER   | NULL     | LOGIN      |
+--------------+--------+---------------------+---------+-------------+--------+----------+------------+
1 row in set (0.30 sec)


1 entry in vicidial_agent_log.

Code: Select all

mysql> select uniqueid, lead_id, list_id, campaign_id, call_date, status, processed from vicidial_log where status="XFER" and user="brandy" and call_date > '2007-08-22 10:30:00' and call_date < '2007-08-22 11:30:00';
Empty set (0.00 sec)

mysql> select uniqueid, lead_id, list_id, campaign_id, call_date, status, processed from vicidial_log where lead_id=323758;
Empty set (0.00 sec)


No entry in the vicidial_log. There's no entry at all for that lead, no matter the time frame. Sometimes the entry exists (at roughly the same time) but has the wrong status.

Here's the agent logs:

Code: Select all

# grep brandy vicidial_debug.txt  | grep '2007-08-22 1[01]'
2007-08-22 10:00:14|RDX||brandy||$SIP/dmafinancial-b7637680||FPvdcW1187801995andy|8600055|default|ext_priority|
2007-08-22 10:00:22|RDX|FIRST|brandy|CL_BLEND_01_L|SIP/dmafinancial-b7637680 and Zap/7-1 to 8600004 on 192.168.5.10|
2007-08-22 10:19:47|RDX||brandy||$Zap/4-1||LPvdcW1187803180andy|8301|default|ext_priority|
2007-08-22 10:20:25|RDX||brandy||$Zap/4-1||FPvdcW1187803217andy|8600054|default|ext_priority|
2007-08-22 10:20:31|RDX|FIRST|brandy|CL_BLEND_01_L|Zap/4-1 and Zap/6-1 to 8600006 on 192.168.5.10|
2007-08-22 10:54:21|RDX||brandy||$SIP/dmafinancial-b764eb08||LPvdcW1187805254andy|8301|default|ext_priority|
2007-08-22 10:54:52|RDX||brandy||$SIP/dmafinancial-b764eb08||FPvdcW1187805285andy|8600051|default|ext_priority|
2007-08-22 10:54:59|RDX|FIRST|brandy|CL_BLEND_01_L|SIP/dmafinancial-b764eb08 and Zap/3-1 to 8600002 on 192.168.5.10|

# grep brandy manager_send.log | grep -v Hang | grep '2007-08-22 1[01]'
2007-08-22 10:00:14|1:|2:brandy|3:192.168.5.10|4:RedirectFromPark|5:FPvdcW1187801995andy|6:text|7:SIP/dmafinancial-b7637680|8:8600055|9:default|10:|11:|12:|13:|14:|15:|16:|17:|18:|19:|21:|22:192.168.5.10|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:00:22|1:FIRST|2:brandy|3:192.168.5.10|4:RedirectXtra|5:VXvdcW1187802003andy|6:text|7:SIP/dmafinancial-b7637680|8:NEXTAVAILABLE|9:default|10:FIRST|11:|12:|13:Zap/7-1|14:|15:CL_BLEND_01_L|16:|17:293996|18:|19:|21:|22:192.168.5.10|23:|24:1|25:8634129179|26:|27:|28:|29:|30:|31:
2007-08-22 10:19:47|1:|2:brandy|3:192.168.5.10|4:RedirectToPark|5:LPvdcW1187803180andy|6:text|7:Zap/4-1|8:8301|9:default|10:|11:park|12:SIP/1006|13:|14:|15:|16:|17:|18:|19:|21:|22:192.168.5.10|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:19:54|1:|2:brandy|3:192.168.5.10|4:Originate|5:DCagcW1187803187andy|6:text|7:Local/917027379500@default|8:8600054|9:default|10:|11:|12:|13:|14:|15:|16:|17:|18:|19:8663663410|21:|22:|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:20:25|1:|2:brandy|3:192.168.5.10|4:RedirectFromPark|5:FPvdcW1187803217andy|6:text|7:Zap/4-1|8:8600054|9:default|10:|11:|12:|13:|14:|15:|16:|17:|18:|19:|21:|22:192.168.5.10|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:20:31|1:FIRST|2:brandy|3:192.168.5.10|4:RedirectXtra|5:VXvdcW1187803223andy|6:text|7:Zap/4-1|8:NEXTAVAILABLE|9:default|10:FIRST|11:|12:|13:Zap/6-1|14:|15:CL_BLEND_01_L|16:|17:273675|18:|19:|21:|22:192.168.5.10|23:|24:1|25:3135370487|26:|27:|28:|29:|30:|31:
2007-08-22 10:54:21|1:|2:brandy|3:192.168.5.10|4:RedirectToPark|5:LPvdcW1187805254andy|6:text|7:SIP/dmafinancial-b764eb08|8:8301|9:default|10:|11:park|12:SIP/1006|13:|14:|15:|16:|17:|18:|19:|21:|22:192.168.5.10|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:54:27|1:|2:brandy|3:192.168.5.10|4:Originate|5:DCagcW1187805260andy|6:text|7:Local/917027379500@default|8:8600051|9:default|10:|11:|12:|13:|14:|15:|16:|17:|18:|19:8663663410|21:|22:|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:54:52|1:|2:brandy|3:192.168.5.10|4:RedirectFromPark|5:FPvdcW1187805285andy|6:text|7:SIP/dmafinancial-b764eb08|8:8600051|9:default|10:|11:|12:|13:|14:|15:|16:|17:|18:|19:|21:|22:192.168.5.10|23:|24:|25:|26:|27:|28:|29:|30:|31:
2007-08-22 10:54:59|1:FIRST|2:brandy|3:192.168.5.10|4:RedirectXtra|5:VXvdcW1187805292andy|6:text|7:SIP/dmafinancial-b764eb08|8:NEXTAVAILABLE|9:default|10:FIRST|11:|12:|13:Zap/3-1|14:|15:CL_BLEND_01_L|16:|17:323758|18:|19:|21:|22:192.168.5.10|23:|24:1|25:2145868296|26:|27:|28:|29:|30:|31:


Let me know if you need anything more (like hangup entries from the manager_send log, there's a lot).

[mflorell]

Does anything show up in vicidial_closer_log?

Is this call inbound, or fronter/closer?

[kbenson]

Does anything show up in vicidial_closer_log?

Yes! That's where the other calls are.

Code: Select all

mysql> select * from vicidial_closer_log where user='brandy' and call_date > '2007-08-22 10:30:00' and call_date < '2007-08-22 11:30:00';
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+--------+----------+-----------+---------------+-------------+
| closecallid | lead_id | list_id | campaign_id | call_date           | start_epoch | end_epoch  | length_in_sec | status | phone_code | phone_number | user   | comments | processed | queue_seconds | user_group  |
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+--------+----------+-----------+---------------+-------------+
|       14084 |  323758 |     132 | qloot       | 2007-08-22 10:48:02 |  1187804882 | 1187805299 |           417 | XFER   | 1          | 2145868296   | brandy | AUTO     | N         |        147.00 | AllOutbound |
+-------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+--------+----------+-----------+---------------+-------------+
1 row in set (0.00 sec)


It seems like the Agent Performance report should check both the vicidial_log
and vicidial_closer_log tables then, to get accurate numbers. Is there some other
report that I can get call disposition breakdown for closing calls? If not, I can just
code something up to get the sum of these tables or modify the agent perfomance
report.

Is this call inbound, or fronter/closer?

In truth, I'm not sure, and not sure how to determine this from the sql entry.

[mflorell]

If it is showing up in the closer log then it has to be inbound or transfer from fronter.

As for a report, I have done a few things(most of them heavily customized for clients) and a perl script(the export records perl script) that will combined vicidial_log and vicidial_closer_log records into a single report depending on parameters entered.

Can you tell me more about the exact campaign that this client of yours is running?

[kbenson]

If it is showing up in the closer log then it has to be inbound or transfer from fronter.

As for a report, I have done a few things(most of them heavily customized for clients) and a perl script(the export records perl script) that will combined vicidial_log and vicidial_closer_log records into a single report depending on parameters entered.

Yeah, I can get all the data into a report now that I know where it is, I was just commenting that the report that doesn't check the closer entries (Agent Performance) seems like it either needs a note explaining why it's different from the report that checks the agent log (Agent Performance Detail) or to be modified to check all relevant tables. It was disconcerting to see two reports purporting to show somewhat similar data that had very disparate numbers in our case.

It's a shame the vicidial_log and vicidial_closer_log tables don't have the same format, you could use a MyISAM merge table and get data from both with a single query, but retain the benefits of different tables. At that point though, you might as well stick all the data in one table with an extra field to designate the difference.

Can you tell me more about the exact campaign that this client of yours is running?

Vicidial is handling what you would probably term fronters, the closers are actually on a different phone system, and are transfered to from the vicidial agents. Often, people want to call back at a later date after they've gotten some required data on their end (possibly 50% of the time or more), and the inbound campaign line is given to these people, so it's quite active.

[mflorell]

There are a couple of scripts in SVN(pre 2.0.4 release) that will offer some combined stats for vicidial_log and vicidial_closer_log. The command-line AST_VDsales_export.pl script has many options and can export all calls across a campaign and inbound groups all to a flat file in one of many pre-set formats.

The second is the Agent Spreadsheet performance report which actually generates an Excel spreadsheet of fronter and closer performance.

[kbenson]

There are a couple of scripts in SVN(pre 2.0.4 release) that will offer some combined stats for vicidial_log and vicidial_closer_log. The command-line AST_VDsales_export.pl script has many options and can export all calls across a campaign and inbound groups all to a flat file in one of many pre-set formats.

The second is the Agent Spreadsheet performance report which actually generates an Excel spreadsheet of fronter and closer performance.

Thanks!

[januariustm]

Hi everyone,

Please enlighten us how the AGENT SPREADSHEET PERFORMANCE report under the Reports menu is populated? There are almost always multiple duplicates in this specific report wherein it shows a lead as being flagged as a SALE by 2 separate agents (See attached file, for the IMGB campaign today). But when one goes to the CALLS TO THIS LEAD: portion on the Lead Record page, although it does show a lead as being called by the initial agent, a different disposition is displayed.

Also, based on example #2 below, there appears to be a problem with the way the server saves changes to lead records made from agent workstations.


----------------------------

Example #1: Agent - Julius Alaon says he did not flag this lead as a sale, yet he appears on the Agent Spreadsheet Performance report. Recording IDs are identical.

From AGENT SPREADSHEET PERFORMANCE report:

Agent ID Rep(s) Customer Name Phone Recording ID Timestamp
20004 Agent - Donna Silveri Bradford Electrics 397022814 44698 6/26/2008 16:23
20010 Agent - Julius Alaon Bradford Electrics 397022814 44698 6/26/2008 12:54


From Lead Record report:
CALLS TO THIS LEAD:
# DATE/TIME LENGTH STATUS TSR CAMPAIGN LIST LEAD
1 2008-06-26 16:23:58 120 SALE 20004 IMGB 10008 145879
2 2008-06-26 12:54:28 5 A 20010 IMGB 10008 145879

----------------------------


----------------------------

Example #2: Agent - Julius Alaon claims he was able to call this lead via the autodialer and then flagged it as a SALE. The problem is, the Lead Record page shows him as flagging the lead as a NO ANSWER, with no other records of any calls made to the number from him. Given that, the lead was apparently redialed by the server a few hours later and was called and closed as a sale by another agent, Irene Abayon. Recording IDs are identical as well. The particular call made by Julius Alaon was personally observed by our QA team, which validates his claim, but QA really only noticed the issue when a duplicate sale appeared on the AGENT SPREADSHEET PERFORMANCE report a few hours later.

From AGENT SPREADSHEET PERFORMANCE report:
Agent ID Rep(s) Customer Name Phone Recording ID Timestamp
20001 Agent - Irene Abayon J Gem 397020240 44835 6/26/2008 16:58
20001 Agent - Irene Abayon J Gem 397020240 44835 6/26/2008 16:54
20010 Agent - Julius Alaon J Gem 397020240 44835 6/26/2008 13:17

From Lead Record report:

CALLS TO THIS LEAD:
# DATE/TIME LENGTH STATUS TSR CAMPAIGN LIST LEAD
1 2008-06-26 16:58:22 42 SALE 20001 IMGB 10008 146121
2 2008-06-26 16:54:44 183 CBHOLD 20001 IMGB 10008 146121
3 2008-06-26 13:17:54 20 N 20010 IMGB 10008 146121

----------------------------



Based from our investigation, we have found out some miscalculations on its report generation scripts ("AGENT SPREADSHEET PERFORMANCE REPORT") primarily on the sql join queries.

Nevertheless, there were no discrepancies on the database records as follows:


----------------------------

VICIDIAL LOG FOR LEAD ID: 145879

+--------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+-------+----------+-----------+------------+
| uniqueid | lead_id | list_id | campaign_id | call_date | start_epoch | end_epoch | length_in_sec | status | phone_code | phone_number | user | comments | processed | user_group |
+--------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+-------+----------+-----------+------------+
| 1214448842.3578000 | 145879 | 10008 | IMGB | 2008-06-26 12:54:28 | 1214448868 | 1214448873 | 5 | A | 61 | 397022814 | 20010 | AUTO | N | IMReps |
| 1214461427.1825099 | 145879 | 10008 | IMGB | 2008-06-26 16:23:58 | 1214461438 | 1214461558 | 120 | SALE | 61 | 397022814 | 20004 | AUTO | N | IMReps |
+--------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+-------+----------+-----------+------------+


RECORDING FOR USER: 20004
RECORDING ID : 44698

+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+
| recording_id | channel | server_ip | extension | start_time | start_epoch | end_time | end_epoch | length_in_sec | length_in_min | filename | location | lead_id | user |
+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+
| 44698 | Local/78600056@default | 192.168.1.152 | 8309 | 2008-06-26 16:24:00 | 1214461440 | 2008-06-26 16:25:58 | 1214461558 | 118 | 1.97 | 20080626-142359_397022814_IMGB_20004 | http://192.168.1.152/RECORDINGS/GSM/200 ... 04-all.gsm | 145879 | 20004 |
+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+


RECORDING FOR USER: 20010
RECORDING ID : 42404

+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+
| recording_id | channel | server_ip | extension | start_time | start_epoch | end_time | end_epoch | length_in_sec | length_in_min | filename | location | lead_id | user |
+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+
| 42404 | Local/78600054@default | 192.168.1.152 | 8309 | 2008-06-26 12:54:30 | 1214448870 | 2008-06-26 12:54:33 | 1214448873 | 3 | 0.05 | 20080626-105429_397022814_IMGB_20010 | http://192.168.1.152/RECORDINGS/GSM/200 ... 10-all.gsm | 145879 | 20010 |
+--------------+------------------------+---------------+-----------+---------------------+-------------+---------------------+------------+---------------+---------------+--------------------------------------+----------------------------------------------------------------------------------+---------+-------+


AGENT LOGS FOR LEAD ID: 145879

+--------------+-------+---------------+---------------------+---------+-------------+-------------+-----------+------------+----------+------------+----------+-------------+-----------+--------+------------+----------+------------+
| agent_log_id | user | server_ip | event_time | lead_id | campaign_id | pause_epoch | pause_sec | wait_epoch | wait_sec | talk_epoch | talk_sec | dispo_epoch | dispo_sec | status | user_group | comments | sub_status |
+--------------+-------+---------------+---------------------+---------+-------------+-------------+-----------+------------+----------+------------+----------+-------------+-----------+--------+------------+----------+------------+
| 43792 | 20010 | 192.168.1.152 | 2008-06-26 12:54:00 | 145879 | IMGB | 1214448840 | 0 | 1214448840 | 29 | 1214448873 | 4 | 1214448874 | 1 | A | IMReps | NULL | NULL |
| 46127 | 20004 | 192.168.1.152 | 2008-06-26 16:23:19 | 145879 | IMGB | 1214461399 | 0 | 1214461399 | 40 | 1214461558 | 119 | 1214461561 | 3 | SALE | IMReps | NULL | NULL |
+--------------+-------+---------------+---------------------+---------+-------------+-------------+-----------+------------+----------+------------+----------+-------------+-----------+--------+------------+----------+------------+


LEAD STATUS OF LEAD ID: 145879

+---------+---------------------+---------------------+--------+-------+------------------+-----------+---------+----------------+-------------------------+------------+--------------+-------+--------------------+----------------+-----------+---------------+----------+----------+---------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+---------------------------------------------+--------------+
| lead_id | entry_date | modify_date | status | user | vendor_lead_code | source_id | list_id | gmt_offset_now | called_since_last_reset | phone_code | phone_number | title | first_name | middle_initial | last_name | address1 | address2 | address3 | city | state | province | postal_code | country_code | gender | date_of_birth | alt_phone | email | security_phrase | comments | called_count |
+---------+---------------------+---------------------+--------+-------+------------------+-----------+---------+----------------+-------------------------+------------+--------------+-------+--------------------+----------------+-----------+---------------+----------+----------+---------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+---------------------------------------------+--------------+
| 145879 | 2008-06-24 17:42:46 | 2008-06-26 16:49:11 | SALE | 20004 | NULL | NULL | 10008 | 10.00 | N | 61 | 397022814 | NULL | Bradford Electrics | NULL | NULL | 4 Selby Court | NULL | NULL | BERWICK | NULL | VIC | 3806 | NULL | NULL | 0000-00-00 | NULL | NULL | NULL | nelly ing 3 300 100 part-time ft 60 martin | 2 |
+---------+---------------------+---------------------+--------+-------+------------------+-----------+---------+----------------+-------------------------+------------+--------------+-------+--------------------+----------------+-----------+---------------+----------+----------+---------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+---------------------------------------------+--------------+

----------------------------



Please enlighten us if our alllegations were correct or there is/are another thing/s that was/were causing these issues.


Thank you very much!


- Januarius