we have fronter's transferring calls to closers. wanted to know how many out of those were sold.
vici reports can do this with the fronter/closer report extended, show fronter/closer stats.
but we wanted to make our own reports from the asterisk database, i worked out most of it and put it into a nice table with (per fronter) total calls, total xfers, and additional columns dictating. out of the total calls and transfers, how many came from each inbound group. except for the amount of sales that were closed per transfer.
so my friend sent me this nice little query
- Code: Select all
select count(*) as xfersales, v1.user from vicidial_closer_log v1 where v1.call_date BETWEEN '2017-11-10' and '2017-11-10 23:59:59' and v1.status = 'XFER' and exists (select * from vicidial_closer_log v2 where v2.call_date BETWEEN '2017-11-10' and '2017-11-10 23:59:59' and v2.status = 'SALE' and v1.phone_number = v2.phone_number) group by v1.user;
hope it helps someone else!