LIVE STATS - Addon

Any and all non-support discussions

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

LIVE STATS - Addon

Postby nrc » Mon May 06, 2013 3:47 pm

So my company uses a TV at the front of the room for the agents to see everyone's stats throughout the day. See how many sales, or how many upsales, or how long they have been on break, etc...

Well VICIDIAL doesn't currently have something like this, so we spent a few hundred man hours and created something, its not fancy, but it certianly gets the job done!

To make this work, we had to create custom fields in the lists. and then when we dial a campaign, our query goes and finds out what the listid is and then gathers the data from those list(s) (multiple lists if we call more than 1 campaign in a day).

Here is the outcome of what it would look like on a supervisors station or even a dedicated TV in the room:
Image
full pic - http://i39.tinypic.com/34sm9zb.jpg

The code has built-in cell flashing when the user/agent goes over a certain threshold (for us pause time over 45mins...wrap % over 2%)
D amount, Up$, Monthly$ are all pulled from the custom fields in the listid table. These numbers ONLY show up if the dispo was marked as a SALE, otherwise it doesn't calculate a number (we did this on purpose, because agents will mismark a call and not realize it until they compare their written stats with the stats on the TV, makes it easier for us to pinpoint).
All other numbers are calculated by dividing other numbers already displayed, or by doing a COUNT in the SQL.

We have a column called Ver. $ and Ver. #....This column is based on weather the agent got a Credit Card sale or a Bank draft sale. If they did then it shows under the Verified column.

And you can sort by any column heading as well.


We have also implemented a slim version into the agent screen as a rollover button:
Image
full pic - http://i40.tinypic.com/2eqaz55.jpg

And that same button, once rolled over if you click will display stats for all the agents (so the agent doesn't have to leave there desk!):
Image
full pic - http://i44.tinypic.com/29xthg.jpg


Just putting this out there to see if anyone was interested in something like this? We won't do any modifying for you, but the hard part should be done, you'll just have to edit the code to reflect your database or your custom field names.
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby omarrodriguezt » Mon May 27, 2013 9:22 am

I like it. Can you please share the code in the issue tracker?
http://www.ITContinental.com
Dedicated USA Servers - Vicibox - Vicidial - Limesurvey - Vtiger CRM - More than 15 years experience - Hablamos Español
omarrodriguezt
 
Posts: 667
Joined: Fri Jun 05, 2009 12:22 pm
Location: Dominican Republic

Re: LIVE STATS - Addon

Postby dspaan » Thu Jun 06, 2013 9:02 am

Looks awesome! Please share it in the issue tracker + short instruction. We are looking for something like this too and have been working on it although we don't need the figures expressed in money. Do the stats get reset on a daily basis at a fixed time?
Regards, Dennis

Vicibox 9.0.1
Version: 2.14b0.5
SVN Version: 3199
DB Schema Version: 1588
Build: 200310-1801
dspaan
 
Posts: 1374
Joined: Fri Aug 21, 2009 1:40 pm
Location: The Netherlands

Re: LIVE STATS - Addon

Postby GaD » Fri Jun 07, 2013 9:14 am

This is awesome! I like the look and the 'preliminary feel' of it! Great job! I've been looking into programing something like this myself. Of course you went way over what I was planning on doing (err..., have my programmer do :) ). I'd love to take your live report for a test run. Is this in the issue tracker somewhere...?
GaD
 
Posts: 195
Joined: Fri Jul 08, 2011 3:56 pm

Re: LIVE STATS - Addon

Postby nrc » Tue Jun 11, 2013 3:49 pm

Sorry guys, we had a few issues with the stats. We were pulling from wrong tables, getting wrong numbers, etc... we are trying to fine tune it now.

But we've also had to change it up a bit...We actually have a cron job run once a min. gather the data and put it into a table. and then the end user script just pulls the data from that table on each refresh. (we are still trying to figure out how to output all the data to a variable and loop correctly, and this so far has been our work around for it.)

As soon as we can get the last part of code in working order, I will try and add it to the tracker for you guys to play with.
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby dspaan » Thu Jun 13, 2013 9:37 pm

Thanks in advance for sharing :)
Regards, Dennis

Vicibox 9.0.1
Version: 2.14b0.5
SVN Version: 3199
DB Schema Version: 1588
Build: 200310-1801
dspaan
 
Posts: 1374
Joined: Fri Aug 21, 2009 1:40 pm
Location: The Netherlands

Re: LIVE STATS - Addon

Postby nrc » Wed Jul 24, 2013 12:03 pm

Sorry guys for the wait....I've upload the majority of the script to the tracker:
http://www.vicidial.org/VICIDIALmantis/view.php?id=689

This is only the script that shows on the TV, I didn't add the agent scripts...that can easily be done by modifying the allstats file

I WILL HELP WHEN I CAN
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby dspaan » Wed Jul 24, 2013 4:08 pm

Thank you so much for sharing :D

I got two questions to start with:

Can you give a brief instruction on where to alter the code to remove some columns?
Can you specify the field types of the custom fields?

Also which SVN version are you using this on right now?
Regards, Dennis

Vicibox 9.0.1
Version: 2.14b0.5
SVN Version: 3199
DB Schema Version: 1588
Build: 200310-1801
dspaan
 
Posts: 1374
Joined: Fri Aug 21, 2009 1:40 pm
Location: The Netherlands

Re: LIVE STATS - Addon

Postby nrc » Thu Jul 25, 2013 8:17 am

dspaan,

Version: 2.6b0.5
SVN Version: 1975

A majority of the code will be found within the allstats.php file. This is the file that does a majority of the gathering and placing into the temp DB.
starting at line 414 is what gets displayed in each cell column

config.php is the file that for the most part calls the tempDB and displays the data. config.php is also the file that holds the column names.
starting at line 137 is what sets the column header names


All of my fields are just plain TXT fields, but you can make the system work with any type of field.
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby nrc » Wed Nov 13, 2013 2:05 pm

Just and FYI:

If you used these live stats, PLEASE PLEASE make sure to build a replication box to pull the stats from.

We have found that these stats create a LOCK on the tables and can slow the screen pops for the agents and cause dispo errors

also attached is the most recent version of the stats - 11-13-2013
http://l.bitcasa.com/kHMrgXSK
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby nrc » Mon Feb 03, 2014 10:26 am

Another update to this...

THANKS TO DOMEDAN and his LEADERBOARD addon he did a while back.

I was able to edit his file to make mine more mysql friendly! (this shouldn't cause any major table locks now) and most of the work is done by PHP now instead of inside mysql tables and query's!

New Screenshots:
Image

You can now pick by campagin, and/or usergroup

Image


Here is the code...its now 1 file (make sure you place within your vicidial folder, as it needs dbconnect and functions.php)

http://pastebin.com/GCBTT4ms
Last edited by nrc on Wed Feb 19, 2014 12:52 pm, edited 1 time in total.
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby nrc » Mon Feb 03, 2014 10:28 am

Still working on the DOWNLOAD button...so you can download the stats to CSV vs. printing to the page.
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby DomeDan » Tue Feb 04, 2014 7:28 am

Good work nrc! :)

for people interested in this kinds of reports:
* nrc Live Stats Addon is using custom tables
* my domedan Leaderboard is using security_phrase as order value
that's the major difference, so if you use custom tables then you probably want to look at nrc addon and maybe build your own report from that one
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: LIVE STATS - Addon

Postby nrc » Wed Feb 19, 2014 12:54 pm

What DomeDan said :-)


Here is an updated version that now prints to txt file which can be opened in excel.

http://pastebin.com/mArds4Mv
VERSION: 2.6-399a | BUILD: 130425-0700 | 1.4.44-vici 1975 | Preload ISO 4.0
nrc
 
Posts: 80
Joined: Wed Feb 13, 2013 3:51 pm

Re: LIVE STATS - Addon

Postby dspaan » Sat Mar 22, 2014 2:03 pm

Hey guys, i tried Domedan's version but i'm getting error's. Maybe it's better that each version uses it's on thread because i can't follow anymore what the setup instruction for each branch is now.

Do i still need to make all those custom fields with the Domedan version? I just made a SALE as agent and filled out the security phrase field but no luck:

Warning: mysql_query() expects parameter 2 to be resource, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 506 Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 507 Warning: Invalid argument supplied for foreach() in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 601 Warning: asort() expects parameter 1 to be array, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 647 Warning: array_reverse() expects parameter 1 to be array, null given in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 650 Warning: Invalid argument supplied for foreach() in /srv/www/htdocs/vicidial/domedan_leaderboard.php on line 669 Warning: array_sum
Regards, Dennis

Vicibox 9.0.1
Version: 2.14b0.5
SVN Version: 3199
DB Schema Version: 1588
Build: 200310-1801
dspaan
 
Posts: 1374
Joined: Fri Aug 21, 2009 1:40 pm
Location: The Netherlands

Re: LIVE STATS - Addon

Postby DomeDan » Mon Mar 24, 2014 4:26 am

Its not really a copy/paste run script, one query joins a table that are not part of vicidial, you should edit it a little.
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden

Re: LIVE STATS - Addon

Postby williamconley » Wed Mar 26, 2014 6:50 pm

DomeDan wrote:Its not really a copy/paste run script, one query joins a table that are not part of vicidial, you should edit it a little.

of course you could post the structure of the custom table and instructions for what data belongs in it ...
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: LIVE STATS - Addon

Postby DomeDan » Wed Jan 07, 2015 5:29 am

forgot this topic, here is the table: (the script looks for this table in database: ledger)
Code: Select all
CREATE TABLE `invoice_types` (
  `invoice_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'connection to invoices',
  `ledger` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT 'what ledger',
  `ledger_state` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = active, 0 =  inactive',
  `manage_invoices` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'should the system create invoices etc for this ledger, 0 = inactive, 1 = active, 2 = value for special management',
  `bank_client_id` varchar(4) COLLATE utf8_unicode_ci NOT NULL COMMENT 'client id this ledger got at the bank',
  `bank_account` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'bank account to ledger',
  `ledger_name` varchar(60) COLLATE utf8_unicode_ci NOT NULL COMMENT 'name printed on the invoice-address',
  `ledger_address1` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address  printed on the invoice-address',
  `ledger_postal_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'postal code printed on the invoice-address',
  `ledger_city` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'city printed on the invoice-address',
  `invoice_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after invoice is sent (invoice_print_date + invoice_deadline_days = invoice_deadline_date)',
  `invoice_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when invoice_deadline is reached (days waiting before sending the first reminder)',
  `reminder1_fee` int(2) DEFAULT NULL COMMENT 'fee added to invoice amount (invoice_amount + fee = reminder1_amount (sending reminder1 at this step)',
  `reminder1_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder1 is sent (reminder1_print_date + reminder1_deadline_days = reminder1_deadline_date',
  `reminder1_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder1_deadline is reached (days waiting before sending the second reminder)',
  `reminder2_fee` int(2) DEFAULT NULL COMMENT 'fee added to reminder1 amount (reminder1_amount + fee = reminder2_amount (sending reminder2 at this step)',
  `reminder2_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder2 is sent (reminder2_print_date + reminder2_deadline_days = reminder2_deadline_date',
  `reminder2_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder2_deadline is reached (days waiting before sending the third reminder)',
  `reminder3_fee` int(2) DEFAULT NULL COMMENT 'fee added to reminder1 amount (reminder1_amount + fee = reminder2_amount (sending reminder3 at this step)',
  `reminder3_deadline_days` int(2) DEFAULT NULL COMMENT 'days waiting after reminder3 is sent (reminder3_print_date + reminder3_deadline_days = reminder3_deadline_date',
  `reminder3_deadline_extra_days` int(2) DEFAULT NULL COMMENT 'days waiting when reminder3_deadline is reached (days waiting before sending the forth reminder witch were to call the customer)',
  `postage_fee` int(2) NOT NULL DEFAULT '0' COMMENT 'price in nok for how much the postage fee is, it is used in some reports',
  `description` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`invoice_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Vicidial Partner. Region: Sweden/Norway.
Does Vicidial installation, configuration, customization, add-ons, CRM implementation, support, upgrading, network-related, pentesting etc. Remote and onsite assistance.
Email: domedan (at) gmail.com
DomeDan
 
Posts: 1226
Joined: Tue Jan 04, 2011 9:17 am
Location: Sweden


Return to General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 46 guests