Populating the vicidial_nanpa_prefix_codes table

[zando101]

Hi,

Regarding the use of "Time Zone Setting" for a list and the "NANPA_PREFIX" option ...

The only way to enable this feature is to purchase the $250 NANPA(North America) phone prefix from vicidial.org?

* Note: I am not against paying for it just asking.
* I have purchased the admin manual already.

I've downloaded the area code database (MS Access format) from http://www.nanpa.com/area_codes/.

I have converted this to Mysql and imported it into a seperate database. Comparing the two tables =>

vicidial_nanpa_prefix_codes
and
public_npa_database_table

They are not the same. So it seems you can't just download the free data and populate the vicidial table yourself?

Is it viable to craft a script that imports or basically just purchase the data from vicidial.org?

I'm pretty new to this so please forgive any silly questions. I have read the manual and searched the forum here.

Here is the database schema from vicidial's asterisk database and the mysql table that is freely available online for download:

mysql> describe vicidial_nanpa_prefix_codes;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| areacode | char(3) | YES | MUL | NULL | |
| prefix | char(3) | YES | | NULL | |
| GMT_offset | varchar(6) | YES | | NULL | |
| DST | enum('Y','N') | YES | | NULL | |
| latitude | varchar(17) | YES | | NULL | |
| longitude | varchar(17) | YES | | NULL | |
| city | varchar(50) | YES | | | |
| state | varchar(2) | YES | | | |
| postal_code | varchar(10) | YES | | | |
| country | varchar(2) | YES | | | |



mysql> describe public_npa_database_table;
+--------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| NPA | int(11) | YES | | NULL | |
| Type of Code | varchar(40) | YES | | NULL | |
| Assignable | varchar(50) | YES | | NULL | |
| Explanation | varchar(25) | YES | | NULL | |
| Reserved | varchar(255) | YES | | NULL | |
| Assigned? | varchar(50) | YES | | NULL | |
| Asgt Date | datetime | YES | | NULL | |
| Use | varchar(1) | YES | | NULL | |
| Service | varchar(50) | YES | | NULL | |
| Location | varchar(50) | YES | | NULL | |
| Country | varchar(50) | YES | | NULL | |
| In Service? | varchar(50) | YES | | NULL | |
| In Svc Date | datetime | YES | | NULL | |
| Status | varchar(255) | YES | | NULL | |
| PL | varchar(60) | YES | | NULL | |
| Overlay | varchar(255) | YES | | NULL | |
| Overlay Complex | varchar(255) | YES | | NULL | |
| Parent | int(11) | YES | | NULL | |
| Time Zone | varchar(50) | YES | | NULL | |
| Map | varchar(50) | YES | | NULL | |
| Is NPA in Jeoparady? | varchar(255) | YES | | NULL | |
| Is Relief Planning in Progress | varchar(255) | YES | | NULL | |
| Home NPA Local Calls | varchar(20) | YES | | NULL | |
| Home NPA Toll Calls | varchar(20) | YES | | NULL | |
| Foreign NPA Local Calls | varchar(20) | YES | | NULL | |
| Foreign NPA Toll Calls | varchar(20) | YES | | NULL | |
| perm HNPA local | varchar(20) | YES | | NULL | |
| perm HNPA toll | varchar(20) | YES | | NULL | |
| perm FNPA local | varchar(20) | YES | | NULL | |
| dp Notes | varchar(200) | YES | | NULL | |

My goal is to more accurately dial in the USA.

We don't want to call an area code too early or too late.

Thanks!

VERSION: 2.4-361a | BUILD: 120221-1647 | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel(R) Core(TM)2 Duo CPU | ViciBox_Redux.x86_64-3.1.15.preload.iso

[mflorell]

The public data that we have found does not have time zone in it, which was the whole point for adding it, so we had to go to a vendor that would provide the data all together. From that we arrived at the price of $250 because we have to pay licensing on the data(which we update yearly).

Keep in mind that the prefix list is only 0.6% more accurate than using the default are-code only option.

[zando101]

Hi,

> Keep in mind that the prefix list is only 0.6% more accurate than using the default are-code only option.

Ah ok .. thanks very much for that clarification.

When you say "the default are-code only option" you are referring to "POSTAL_CODE" correct?

The complete list of options is =>

COUNTRY_AND_AREA_CODE
POSTAL_CODE
NANPA_PREFIX
OWNER_TIME_ZONE_CODE
COUNTRY_AND_AREA_CODE

I'm pretty sure you meant POSTAL_CODE only.

Cheers.

[mflorell]

No, I meant COUNTRY_AND_AREA_CODE.

The problem that most of our clients have with using zip codes is that they are usually not that accurate in most lead files(unless it is something like a utility company), so using areacode has been the default.

[VFRDavid]

Sorry for replying to an old thread - but - it was the most related to my question...at least that I could find...

My question has to do with loading leads - I was trying to find where the Load Leads program got its data for the "USA-Canada Check" when I use the (NON NANPA) "CHECK FOR VALID PREFIX and AREACODE" option. From what I can find, the vicidial_phone_codes table only has the country and area code (NPA) - not the exchange (NXX). I don't believe that a list of valid North American exchanges by area code (NXXs for each NPA) exists in the default data, does it? So - what I have done is just check that the 4th digit is not a 0 or 1 - but are you able to do something more thorough than that during the import? Is the data somewhere outside of the asterisk database? Or is it just late and I'm blind?

The reason I thought my question was related to this thread is - Would the subscription to the NANPA data increase the accuracy of the NPA/NXX data available in the system? (What is the increase in accuracy for the NANPA option vs non-NANPA CHECK FOR VALID PREFIX and AREACODE option in the Load Leads page? Would it only be about the 0.6% stated in this thread in regards to the assigned time zone? Even though I am not looking for the time zone for this task - I just want to know whether or not an NPA (and NXX) exists and is active). Are the available Phone Code updates providing about the same level of accuracy that the subscription acheives for this purpose?

Previously (in the dark days before VICIdial) I subscribed to something called ACSED from BellCore - the Area Code Split Exchange Data - and used this to update my customers and leads when a new area code was introduced, due to the previous one being exhausted. In addition to the list of exchanges impacted for the NPA in question, it also included several dates - when the new area code would begin, when dialing with it would be optional and when it would become mandatory...I mention this because I see that I can obtain a list of active Central Office Code Assignment Records for all US States from nanpa.com - which, once parsed, appears to contain the NPA/NXX data I am looking for - but - again - if the subscription does all of this without the extra steps, plus contains additional information - like these dates for example - I think it would be well worth it.

The system I am in front of right now is a server cluster that (still) has SVN 2973, DB Schema 1542, it was built from the v7 ISO and the VERSION listed on the Admin web pages is 2.14-670a, BUILD 180424-1521, but I also have systems out there that were built from the v6x ISO, as well as v8 and v9...I am sure the subscription would be per server cluster / primary database server, correct?

Thanks for any help you can provide - again, my goal is to ensure the lead database I inherited in this cluster (and others) is as clean as possible - there appears to be a lot of non-standard North American numbers in the database, and I would like to make sure the system isn't even attempting to dial them or load them in the hopper. So - I am looking into what other potential benefits the NANPA subscription would provide beyond time zone accuracy - either built in to VICI - or - due to the presence of more complete / accurate data that I could use to add the features I need.

Thanks for any feedback - it is always appreciated!

David

[mflorell]

The most accurate option(and most expensive) is to subscribe to the DNC.com cellphone scrubbing batch data service. This has full number portability databases updated nightly, as well as the weekly updated NPA-NXX-X phone prefix database needed to determine if the 7-digit prefix for a phone number is valid. You can't get much better than that, and that service also allows you to filter your numbers for cellphones every night. The effect of this on most of our client databases that we have used this for removes roughly 1% of invalid phone numbers, although we have seen this remove as many as 5% of leads loaded for some client lists.

The next option is to get the NANPA prefix list that is available at the vicidial.org store that you have mentioned. This list is updated once a year and was primarily intended to help better classify the time zones that phone numbers(by 6-digit prefix) are located within. Our tests from several years ago show that using this data helped improved list timezone accuracy by about 0.6%. But it can also be used to scrub for invalid phone numbers. As for the amount of number that would be scrubbed by this data-set, just like the DNC.com data above that really depends on your data. We've heard that there are some free data sources available for most of this 6-digit-prefix NANPA data that we have had some clients use, although those sources do not include the timezone-per-prefix data that our paid source provides.

If you do choose to go one of these routes, please post back here what your experiences were like, we always like to hear from people about stuff like this

[VFRDavid]

The DNC.com option might be a little too rich for my budget - the cluster that prompted this post averages about 750,000 calls / day, Mon-Fri, 350k Saturdays and 100k Sundays...Of those calls, we average about 5% contact (37k/17k/5k) - so - that overall call volume might drop pretty quickly if we suppress all of the calls that DNC.com tells us to (either due to DNC or invalid phones) - and improve our contact percentage.

Can you tell me how you validate the phone numbers when choosing the "CHECK FOR VALID PREFIX and AREACODE" for the USA-Canada Check option in the Lead Loader? I am using LIST LOADER 4th Gen- VERSION 2.14-71 BUILD 180324-0943. Is it just a "4th digit isn't a 0 or 1" - or is the exchange / NXX data stored somewhere? And YES - I am being lazy - I could just look at the PHP code, I suppose...

Finally - I never heard that it's a "first seven" digit check - what rules apply to the first digit of the suffix / last 4???

Thanks again for your replies!

[mflorell]

DNC.com offers several services, not just DNC list scrubbing. The specific service I was referencing actually has nothing to do with the National DNC list, it's the cellphone filter batch file service that allows you to flag all of your leads on your own system as either a Standard landline, a Cellphone or an Invalid number. The scripts to perform this are built right into VICIdial.

As for "CHECK FOR VALID PREFIX and AREACODE", that just checks for an areacode being in the default populated phone codes table as well as a valid 4th digit being 2-9.

The first 7-digit's of the phone number prefix check is part of the DNC.com cellphone scrub batch file service. There is no formula, it's a database of valid phone number prefixes that go down to that level of detail that is updated weekly and only available by subscription to a data service like what DNC.com offers.