Loading phone list - bulk vs One by One

[Kamil]

Hi, I'm new to Vicidialer and have few questions.

I have a phone list for my state that consists of separate lists (2500 files) by zip code and also contains one huge (620mb) file which I can load at once. My question is if there is any advantage loading file by file or if I can load one huge file and later sort it by the zip code?
Someone told me that it is easier to call specific zip codes in your campaign when you have separate lists for each zip code.

Another question is, if it is better to load file by file, then is there any way to automate this process to avoid doing this multi step process 2500 times?

[geoff3dmg]

You can use a lead filter to control what ZIP codes you dial. There's no need to split the file from a technical point of view. You may wish to do it for management or reporting purposes though.

You can use the 'VICIDIAL_IN_new_leads_file.pl' command line perl script to load large lead files.

[perci100]

Id suggest breaking it up into 50k or < chunks and loading it like that. Larger lists are a pain to work with. seems to me like they slow things down a bit. Plus like Geoff said it will be much better if you find a common point in your data and separate it out for reporting purposes.

[williamconley]

Hi, I'm new to Vicidialer and have few questions.

I have a phone list for my state that consists of separate lists (2500 files) by zip code and also contains one huge (620mb) file which I can load at once. My question is if there is any advantage loading file by file or if I can load one huge file and later sort it by the zip code?
Someone told me that it is easier to call specific zip codes in your campaign when you have separate lists for each zip code.

Another question is, if it is better to load file by file, then is there any way to automate this process to avoid doing this multi step process 2500 times?

If you are not using custom fields, load the entire thing via the command line lead loader (it'll even send you a summary email when completed if you ask nicely). then you can use mysql to move them between lists and segregate them any way you like. Just remember the rule: each list can only be in One campaign at a time. So if you sort by zip code, each zip would then be relegated to a single campaign with that group of agents until you move that entire list to a new campaign.

If you ARE using custom fields, be sure to populate the field that designates the original list_id (to keep the list's leads associated with it's custom field data even if you move leads to new lists later!)

[Kamil]

Thank you all for your replies. As someone new to Vici this really does help.

I can see that loading list by zip code can give you more reporting power and nicely separates things, my only concern is, how do I load 2500 with one shot and not load every single file. I know some people hire inexpensive labor and have them do it, but I see two issues here, 1st, user error, if you load so many lists and create so many lists there is a chance of potential user error. Another issue is time, it will easily take over 10 days for someone to load this list manually.

Can someone share a step by step instructions on how to load single files with one shot? It sounds like if I can do that, then I'd rathe do that, than load huge 600mb file.

[williamconley]

list ID is a field during load. as long as you do not 'override' the list_id field, you can load ALL your lists in a single file.

[Kamil]

list ID is a field during load. as long as you do not 'override' the list_id field, you can load ALL your lists in a single file.

But in this case I would need to have matching lists already made, right? I just tried to load a lead and used zip code as a list ID and it didn't create a new list. Is there a way to create new list(s) with one stop based on zip codes or area codes and not do it manually one by one? If I can generate lists for each zip code, then I can run one large list file and map zip code to List ID in the loading process.

[williamconley]

But in this case I would need to have matching lists already made, right?

Nope. Leads will be added to lists and you can create the lists later. SQL is quite capable of creating lists in bulk if you like. I do not know if the command line lead-loader is capable of making them on the fly except "one per list loaded".

However, if you have your zip files split into multiple files it is possible to have lists made automatically .. but I think they will be numbered sequentially instead of "per zip". You'd have to check the options for the loader.

If it were me, and I didn't have time to check, I'd load the whole thing with the zip as the list ID and then write a script to generate all those lists after the fact (the leads will be waiting in those lists and become usable when the lists are created). Probably with a simple PHP file (in case I ever have need to duplicate the process for the same client).

If you export the "vicidial_lists" table you will find the proper format for creating a list. Dupe it.

[Kamil]

But in this case I would need to have matching lists already made, right?

Nope. Leads will be added to lists and you can create the lists later. SQL is quite capable of creating lists in bulk if you like. I do not know if the command line lead-loader is capable of making them on the fly except "one per list loaded".

However, if you have your zip files split into multiple files it is possible to have lists made automatically .. but I think they will be numbered sequentially instead of "per zip". You'd have to check the options for the loader.

If it were me, and I didn't have time to check, I'd load the whole thing with the zip as the list ID and then write a script to generate all those lists after the fact (the leads will be waiting in those lists and become usable when the lists are created). Probably with a simple PHP file (in case I ever have need to duplicate the process for the same client).

If you export the "vicidial_lists" table you will find the proper format for creating a list. Dupe it.

Great reply thanks!
Actually just an hour ago I downloaded mySQL windows client and was able to connect to the DB and see all tables and that's when idea of writing a script to create lists based on zip code came to me.

My only question is if things like listID and other fields in vicidial_list and vicidial_lists tables have foreign keys in other tables. Other words, do I only need to populate vicidial_lists table or other tables too?

[geoff3dmg]

If you are unsure, go look at the code.

[perci100]

to be honest I have never thought to load them any other way but it makes sense. Turned out to be a good post for me as well . I am grateful for the correction. One of my biggest pet peeves is spreading incorrect information so i hate to be an offender lol. Thanks again all.

[Kamil]

If you are unsure, go look at the code.

If this was Microsoft code I could, but I'm not that familiar with PHP. I thought maybe people already had some kind of input regarding this. Because it just doesn't make any sense for someone to create 2000 lists one by one and do this for each state that you call if you create your lists based on zip code.

[geoff3dmg]

If you are unsure, go look at the code.

If this was Microsoft code I could, but I'm not that familiar with PHP. I thought maybe people already had some kind of input regarding this. Because it just doesn't make any sense for someone to create 2000 lists one by one and do this for each state that you call if you create your lists based on zip code.

You might want to read up on how 'grep' works.

Code: Select all

vicidial-db:/srv/www/htdocs/vicidial # grep INSERT.*vicidial_lists admin.php
            $stmt="INSERT INTO vicidial_lists (list_id,list_name,campaign_id,active,list_description,list_changedate) values('$list_id','$list_name','$campaign_id','$active','$list_description','$SQLdate');";
         $stmt="INSERT INTO vicidial_lists_custom (audit_comments, list_id) VALUES ('" . mysqli_real_escape_string($link, $audit_comments) . "','$list_id') ON DUPLICATE KEY UPDATE audit_comments='" . mysqli_real_escape_string($link, $audit_comments) . "';";

As you can see, it's a single Insert unless you use QC.

[Kamil]

geoff3dmg, thanks for the info. So it looks like vicidial_lists_custom only needs to be populated if you have customization, so in my case I don't. Now I guess I need to generate a long query with lots of inserts based on zip codes.

[williamconley]

My only question is if things like listID and other fields in vicidial_list and vicidial_lists tables have foreign keys in other tables. Other words, do I only need to populate vicidial_lists table or other tables too?

No foreign keys. You're quite safe as long as you duplicate an existing record properly. Follow the numbering convention rules, that sort of thing (ie: nothing under 100.)

[Kamil]

I ended up loading my csv file directly into a temp table and then creates a query to populate vicidial_list table from that temp file. Seems to be working just fine.

[williamconley]

Bad idea. The lead loader takes care of a lot of "small stuff" you could easily overlook. Trust me when I say that everyone who has tried to bypass it has (eventually, and usually quickly) regretted it unless they used the lead loader .pl file as the basis of their own loader.

After it's done doing it's job (well), there's rarely a problem (as long as custom fields are not in play). So move leads around at will (after). But skipping it entirely has always turned out poorly.