How to create high quality lists from low quality source?

[chrisclasman]

Hi everyone.

Newbie in the forums here, so bare with me if this has been discussed before (I searched, but couldn't find anything).

Over the course of our campaign, we've bought a lot of different quality lists. Over time, it has accumulated to having a total of 1.5mill records in our dialer.
When lists have very low connectivity (a lot of answering machines etc), is there a way to clean those lists and create higher quality lists out of them?
For examples, is there a way to filter out leads that have been dialed > 5 times and have been AA every time?
Or if anyone has another technique that works well, that would be much appreciated!

[Vince-0]

Hi chrisciasman,

There are some lead management tools in the "Admin Utilities" link, small at the bottom of the reporting page.

[blackbird2306]

As Vince-0 pointed out, it's possible to do this manually with "Advanced Lead Management Tools". But I think your aim is not to call these leads rather than making a new list? Then you can use the FILTERS feature. Make a new filter for example with FILTER SQL:

Code: Select all

(NOT(status='AA' AND called_count > 5) AND NOT(status='NA' AND called_count > 5))


After that assign the new filter to the campaign in campaign settings!

Also take a look at "List Order" and "List Order Secondary" in campaign settings. Try it with "DOWN COUNT", which means "it starts with least called leads and works its way up".

[williamconley]

is there a way to filter out leads that have been dialed > 5 times and have been AA every time

The "every time" bit is what kills it. Vicidial lead management tools do not take into account or have access to prior dispos. Just that last one.

It is very possible to write a script that can check the logs and move leads to new lists based on "crappiness"

It is also very possible to put this script in the DispoURL and/or No Agent URL so this decision can be made at the end of the phone call. This would allow the system to harvest, check *all* prior dispos and if they were, in fact, always AA or always NA push them to a "special" list for repeat offenders (that could be in a different campaign used to store the crappy leads, some call it the warehouse of the dead leads).

[blackbird2306]

The "every time" bit is what kills it. Vicidial lead management tools do not take into account or have access to prior dispos. Just that last one.

Yes, that is absolutely true "every time" is the problem here, though beyond a certain called_count number (maybe not after 6 calls) they are in bad quality no matter what status history they had before. But this "every time" is also possible to achieve with FILTER feature:
FILTER SQL:

Code: Select all

((SELECT count(*) FROM vicidial_log WHERE status IN('AA','CRAPPYSTATUS1','CRAPPYSTATUS2') AND vicidial_list.lead_id = vicidial_log.lead_id) <= 5)


Change the statuses AA, CRAPPYSTATUS1, CRAPPYSTATUS2 to the statuses, which are considered as "CRAPPY". This will filter all leads, where the sum of calls with crappy status are in total higher than 5 !! Be careful and pay attention for possibly high database load.

[williamconley]

The "every time" bit is what kills it. Vicidial lead management tools do not take into account or have access to prior dispos. Just that last one.

Yes, that is absolutely true "every time" is the problem here, though beyond a certain called_count number (maybe not after 6 calls) they are in bad quality no matter what status history they had before. But this "every time" is also possible to achieve with FILTER feature:
FILTER SQL:

Code: Select all

((SELECT count(*) FROM vicidial_log WHERE status IN('AA','CRAPPYSTATUS1','CRAPPYSTATUS2') AND vicidial_list.lead_id = vicidial_log.lead_id) <= 5)


Change the statuses AA, CRAPPYSTATUS1, CRAPPYSTATUS2 to the statuses, which are considered as "CRAPPY". This will filter all leads, where the sum of calls with crappy status are in total higher than 5 !! Be careful and pay attention for possibly high database load.

I'm gonna go out on a limb here and guess you have not tried this query on a live server with a large log. I'd advise against it. This could bring the server down.

[blackbird2306]

I tried it and it worked without any problems with call log size of 1.5 million rows. The higher impact on AST_VDhopper.pl script wasn't that significant. But again my advice "be careful and pay attention for possibly high database load". The filter feature should always be treated with extreme caution.

[williamconley]

I tried it and it worked without any problems with call log size of 1.5 million rows. The higher impact on AST_VDhopper.pl script wasn't that significant. But again my advice "be careful and pay attention for possibly high database load". The filter feature should always be treated with extreme caution.

I hope you meant "vicidial_log" since that's the log you are joining to the "vicidial_list" table. Both table sizes are relevant. (eg: in the server below, the call_log table is only about 1.2M)

I'm looking right now at a cluster with 9M in the vicidial_log and 16M in the vicidial_list.

Joining those two tables every minute while trying to load the hopper would result in a fatality. lol

But running a script/sql query once per day after hours ... perhaps not a problem.

[blackbird2306]

We don't need to talk about the names of the vicidial tables, of course I mean vicidial_log. Your cluster with 16 M leads in vicidial_list and 9 M in vicidial_log is working at limit by itself and this is not usual. Certainly on a database server with this large amount of leads it might be critical to make "experiments" of this kind. Look at the full sql query in hopper script with all WHERE clauses and limit! You are not joining the whole table only a part of it. Nevertheless I would be very interested in the impact of your mentioned cluster. Can you make some tests with exactly this filter and give us details about the load with and without this filter? Somehow there is no problem on my system.

[williamconley]

... You are not joining the whole table only a part of it. ...

That's not how MySQL works. It joins the entire table, then filters out what it doesn't need, showing only the result.

This can be very fast if based on indexed fields only, but when both tables are in the Millions, even indexed field joins are cumbersome. If some fields involved in the join are unindexed, and there are millions of leads in both tables, you may feel free to go get some takeout while awaiting the response, cuz it'll be a while. lol

Note that nowhere else in the stock Vicidial Hopper scripts and queries is there a join of the list table with any log tables. That's not an accident.

[chrisclasman]

As Vince-0 pointed out, it's possible to do this manually with "Advanced Lead Management Tools". But I think your aim is not to call these leads rather than making a new list? Then you can use the FILTERS feature. Make a new filter for example with FILTER SQL:

Code: Select all

(NOT(status='AA' AND called_count > 5) AND NOT(status='NA' AND called_count > 5))


After that assign the new filter to the campaign in campaign settings!

Also take a look at "List Order" and "List Order Secondary" in campaign settings. Try it with "DOWN COUNT", which means "it starts with least called leads and works its way up".

This seams like the simplest solution, so will try this first.
Thank you!