Archive vicidial_list and vicidial_lists

[Acidshock]

Hi Everyone,

I was interested in seeing what it would cost to archive lists and lead data in vicidial_list. It was suggested that I post this into the features section.

Some people have rather large vicidial_list tables. Most of the time lists cant be really deleted unless you want to lose the information on those leads. I was thinking maybe there could be two new tables created, vicidial_lists_archive and vicidial_list_archive. The information from the live tables could get moved to these tables when designated by the user. By doing this, the vicidial_list table in particular could be kept lean and would prevent locking problems when a user runs a large report or an intensive query gets ran on a vicidial_list table with millions of records. If it gets ran on the archive it is probably not much of a concern since. Alternatively the concept could be reversed and a "staging" version of the active tables could be created and the records copied. Then copied back and removed from staging when the lists are disabled. I think this concept could be tweaked to a much better solution so please feel free to share input.

[mflorell]

We have been doing this manually for clients for years, using the vicidial_list_archive table and queries like this:

CREATE TABLE vicidial_list_archive LIKE vicidial_list;
ALTER TABLE vicidial_list_archive MODIFY lead_id INT(9) UNSIGNED NOT NULL;

insert ignore into vicidial_list_archive select * from vicidial_list where list_id IN('118603','115503','112903','8112503','117103','118003','111903','111402','115203','117503','111603','116303','115103','117703','116703','110803','107903','112703');
delete from vicidial_list where list_id IN('102932','102915','118603','115503','112903','8112503','117103','118003','111903','111402','115203','117503','111603','116303','115103','117703','116703','110803','107903','112703');

We usually don't touch the vicidial_lists table at all.

If you archive leads in this manner, they are still searchable from lead search if you set Archive = Yes at the top of the lead search page.


As for making this something you can do through a web interface, there are some issues with that. Namely, the long amount of time it can take to execute these archive queries. The example queries listed above took about 2 hours to complete. During that time the dialer would be unusable. We usually don't want those kind of things to be triggered by web admin actions.

[Acidshock]

Maybe we can have a flag and then it runs the query during a specified time via cron? I could probably make a php script that could execute and archive all lists X days old.

As for the lead search portion. I am not seeing anything that shows archive. Even checked some of my newest cluster I setup just last week. Is there something I am missing? Will the export lead reports use the archive too?

Thanks!

[mflorell]

The archive search option will only show up on the lead search page if the vicidial_list_archive table exists.

As for an option to archive lists after-hours, that would probably be best done through an Admin Utilities page and a crontab entry that you have to set up manually. I really wouldn't want this to be a feature that is enabled by default.

[williamconley]

We have a feature page to allow archiving lists one at a time (checkbox) or Per Campaign (push the button, instant Campaign archive). Had to create it for more than one client that had far too many leads.

Presently runs outside the Vicidial interface, but only because no client has paid for us to submit it. Probably upgrade-proof, but I'd have to review the code to be sure it's not specifying any field names/numbers.

[Acidshock]

What do you think it would take to sponsor integration of the vicidial_list_archive table into the lead loader, any reports that use vicidial_list, and possibly into pulling up call information on an inbound call? Got a table with 48 million records and I need to start doing something about it haha. The lead loader kills the system during dedupe.

Also what would it take to sponsor having the lead loader use a slave server if that is even possible(not sure if it would cause issues when trying to dedupe the list by itself.)

Also looks like this may be popular. Lots of views on this topic.

[mflorell]

My first suggestion would be to do your lead management outside of Vicidial. It wasn't really designed to work with a lead base of almost 50 million active leads.

There are a lot of different pieces in here, so I'll try to break them down:

- Lead Loader integration with vicidial_list_archive
QUESTIONS:
- web lead loader, API and/or CLI lead loader?
- which specific features would need to work this way? Only duplicate check?
ISSUES:
- adding archive duplicate check would significantly increase the time it takes to run, since it still has to duplicate check against the live vicidial_list table

- Any reports that use vicidial_list
QUESTIONS:
- which reports specifically?
ISSUES:
- this could take dozens of hours of programming alone, as well as making those reports take significantly longer to run, if not time out

- Pulling up inbound call information
QUESTIONS:
- since inbound calls have to be tied to a vicidial_list record, how exactly were you wanting this to work? Would the archived lead be moved to the live table and deleted from the archive? or would a new vicidial_list record be created using the data in the archive record?
ISSUES:
- time delay as the lead is searched for could be significant, and result in a lot of hangups while people are sitting in dead air as the system tries to find a match.

- Have lead loader use a slave server
BAD IDEA, this could result in duplicate leads being loaded if there is any kind of delay in the synchronization of the slave replication.

[Acidshock]

Lead Loader integration with vicidial_list_archive
QUESTIONS:
- web lead loader, API and/or CLI lead loader?
Web Lead Loader
- which specific features would need to work this way? Only duplicate check?
Anything that really needs to pull info from vicidial_list for the user. So lead search, which already does that, export leads report, etc.
ISSUES:
- adding archive duplicate check would significantly increase the time it takes to run, since it still has to duplicate check against the live vicidial_list table
What about running two processes? 1 pass checks the live table, 1 pass checks the archive, if either find a match it flags as a dupe. May take longer to load the list but the true goal is to minimize any problems the agents face. Whoever loads the data can just be patient if they select the option to dedupe via the archive too.

- Any reports that use vicidial_list
QUESTIONS:
- which reports specifically?
Export leads reports, export calls reports, etc. I really don't know all the reports that use vicidial_list. However I believe those two do use them.
ISSUES:
- this could take dozens of hours of programming alone, as well as making those reports take significantly longer to run, if not time out
Is there any way we can think that may mitigate this problem? Only other thing I can think of is have a staging table that the hopper pulls from rather than vicidial_list. Really most of these problems come from vicidial_list being such a integral component in all the scripts compared to working with a small staging database, and then pushing out the changes to vicidial_list.

- Pulling up inbound call information
QUESTIONS:
- since inbound calls have to be tied to a vicidial_list record, how exactly were you wanting this to work? Would the archived lead be moved to the live table and deleted from the archive? or would a new vicidial_list record be created using the data in the archive record?
I was thinking very basic on this one. Just query the archive table, and pull the lead information into the agents screen, then reupdate the archive table. In our particular case this isn't as much of a concern. Definitely open to suggestions.
ISSUES:
- time delay as the lead is searched for could be significant, and result in a lot of hangups while people are sitting in dead air as the system tries to find a match.
Definitely understand this one. Maybe the simply way is to just avoid this part and make a user script that can search lead information and bring it up. Again, open to suggestions.

- Have lead loader use a slave server
BAD IDEA, this could result in duplicate leads being loaded if there is any kind of delay in the synchronization of the slave replication.
Figured as much. I could definitely see how a sync delay could prevent this from happening.

[mflorell]

- Lead Loader integration with vicidial_list_archive

This one is actually not that difficult, adding a Web Lead Loader option to do the selected duplicate check against both the vicidial_list and vicidial_list_archive tables would be 4 hours.


- Any reports that use vicidial_list

Adding vicidial_list_archive as an option to the Export leads reports and Export calls reports would take 6 hours


- Pulling up inbound call information

Querying then updating the vicidial_list_archive table for a live call is not an option. The live call has to have a vicidial_list record associated with it. Your suggestion of having an agent use the existing Lead Search option to search for the lead in the archive table might work, and would remove any issues with initial delays in calls getting to agents, but those agents would need to keep in mind that these archive searches would be slower, and if you were loading and deduping leads at that same time then they could be waiting a while for their search to return results. Then once the agent selects a lead from the results, the lead could be moved to the vicidial_list table and deleted from the vicidial_list_archive table so the agent could proceed with their call.

Adding an "archive search" option to the existing agent screen Lead Search functionality, with ability to move archived lead active, would take 8 hours.

[williamconley]

Solution Artist says: DID Modify page "Filter URL".

This could be converted to accomplish the task for inbound calls. It runs before the call is passed to the ingroup. It COULD be used to move the lead to the live table "On Demand" before the call passes to the ingroup. A later script could push out older leads regularly.

As for the lead loader ... yep, lots of work.