Comprehensive
guide to PhoneSeal's Area Code Correction Process
If
you want to know how to use PhoneSeal, (rather than how
PhoneSeal works), then we recommend that you continue on to
the PhoneSeal Tutorial page, which
will give you screenshots showing what the program looks like
and descriptions on how you would use it.
We receive many questions about PhoneSeal that result from the
incorrect assumptions people make about how the software works.
Hopefully this page will explain to you everything you will want
to know, and then some, so you will understand perfectly how PhoneSeal
actually works.
Access
to data
PhoneSeal's
first problem is getting access to your data. It can access your
data in two basic ways:
-
Text files
-
ODBC database access
Most programs make their address and phone data available by one
of these two methods. If neither of these two methods will work
for you, please contact us.
For text files, you tell PhoneSeal what file to look at, what
columns the program should examine, and how PhoneSeal can identify
the columns -- PhoneSeal currently works only with "delimited"
text files, which means the columns are separated by a character,
called a "delimiter", usually a tab or a comma. Most programs
that export data to text files will also put quote marks around
the data. If PhoneSeal encounters this, it will look at the data
inside the quote marks. If you are running Corrector, PhoneSeal
will correct the number inside the quote marks without removing
the quote marks.
For ODBC access, PhoneSeal can either access a Microsoft Access
database or a System DSN (data source name). We have a special
option for Access, so Access users don't need to go through the
process of creating a System DSN, but Access databases are still
accessed through ODBC just like all the others. Please consult
your database vendor's information on ODBC database access for
more information.
PhoneSeal will create a standard SQL query based on the table
and field names you enter. If there is a problem with the query,
PhoneSeal will not be able to access your data. PhoneSeal always
uses simple SQL statements rather than advanced database features
because it can access more different database systems that way.
If you are running PhoneSeal Corrector, it will correct the data
by issuing SQL update statements.
If
you find that PhoneSeal can read your database quickly but is
slow up perform updates, it's probably because the database is
taking a long time searching the database to find the records
to update. Putting indexes on every field with a phone number
will speed this up.
When using text files, PhoneSeal writes the updates to a new file.
The new file contains every record from the original file, including
every number, whether corrected or not corrected. The new file
keeps quote marks and any other formatting found in the original.
The idea is that after PhoneSeal creates the new file, you examine
it to make sure it is okay, and then replace the original file
with the new one. (Or re-import it into whatever program it came
out of).
PhoneSeal is fastest on text files, especially with the "Professional"
flavors which use our new code which was rewritten to run fast.
We also allow OLE DB access, but to our knowledge nobody uses
this feature yet.
PhoneSeal Advanced/Professional can also access ZIP and date fields.
The ZIP codes can be either 5-digit ZIP or ZIP+4 or Zip+4+2. Any
other format will be rejected by PhoneSeal and it will not use
the ZIP code on that record. For the date, on text files, PhoneSeal
uses Windows API's to translate the date. So if PhoneSeal is reporting
problems processing your dates, try changing your date settings
in the Control Panel. The place to do this is on the "Regional
Settings" control panel, on the "Date" tab. For databases, if
your database vendor returns the date as a OLE Date data type
(Microsoft databases, such as Access and SQL Server, do this),
then PhoneSeal will understand it. If the date is returned as
a text string, then PhoneSeal will try to interpret the date as
a text string, the same way it does with text files. PhoneSeal
never changes the date that it reads from your file or database.
Phone Number
Parsing
The
next thing PhoneSeal needs to do is interpret your phone number.
PhoneSeal needs to know what part of the number is the area code,
what part is the exchange code, and what is the rest of the local
number.
To do this, PhoneSeal compares the number to a list of phone number
formats. This includes most common 10-digit phone number formats,
such as using dashes to separate the fields, putting the area
code inside parenthesis, and putting the US country code at the
begnning with a plus sign or brackets. However, we don't guarantee
that PhoneSeal can figure out all 10-digit formats that exist
out there.
Some people have the area code in a separate field from the rest
of the number in their files or databases. If you do it this way,
what PhoneSeal will do is combine the fields together to make
a number, and then try to parse it. PhoneSeal does not put a space
in between the fields. PhoneSeal has 3 formats just for recognizing
the combined number. PhoneSeal is generally able to interpret
the number this way.
If PhoneSeal does not recognize the input as a phone number, it
will put a message about it in the error log and leave that field
alone. It will not be used in any of the counts in PhoneSeal Analyzer,
it will not be checked for area code ambiguities by PhoneSeal
PreCleaner, and it will not be corrected by PhoneSeal Corrector.
PhoneSeal is very picky about the phone number format. It wants
to be absolutely certain that it is interpreting the number correctly,
so it won't misinterpret the number and update something that
is not, in fact, an area code!
PhoneSeal Advanced/Professional allows the program to be extended
with custom formats.
PreCleaner:
Checking for Ambiguities
The
job of PhoneSeal PreCleaner is to find all the "ambiguous" numbers
in your file or database.
An "ambiguous" number is a number where the area code cannot be
determined for certain. For example, suppose the number is (206)
231-7777. The 206 area code split on January 15, 1995, to 206
and 360, with the 231 exchange assigned to 360. On April 27, 1997,
the 206 code split again to 206 and 425, with the 231 exchange
assigned to 425.
So, if the phone number (206) 231-7777 was active before Jan 15,
1995, it would have changed to (360) 231-7777. If it was active
between Jan 15, 1995 and April 27, 1997, it would have changed
to (425) 231-7777.
So, you see, it is not possible to tell what the correct new area
code should be: 360, or 425.
The purpose of PreCleaner is to find the ambiguous numbers and
report them to you. You will have to actually call these
numbers to determine which new area code is the correct one.
We find that the number of ambiguous numbers varies greatly from
file to file. Some files have none at all. Some have a very high
percentage. It all depends on where, geographically, the phone
numbers are -- are they in an area where there have been lots
of multiple splits?
PhoneSeal will also perform what we call "recursion" on the ambiguities.
For example, the number (205) 296-1111 could have changed to (334)
296-1111 in a 1995 split. Then 334 splits again, into 334 and
251. PhoneSeal will report not only the 334 possibility, but also
251, as a possible new area code. Again, you will have to actually
call each number to find the correct new area code.
Now, if there is just one new area code, PhoneSeal will use the
new area code and won't report an ambiguity. You may notice, it's
possible for the number to have been issued after the split. Consider
the number (206) 882-8080. PhoneSeal finds only one split for
the 882 exchange of the 206 area code, in 1997, assigning the
new area code 425. But what if the number was issued after 1997?
It's impossible to tell, so won't PhoneSeal report this as an
ambiguity?
The answer is: no it won't. The reason is that this would mean
every phone number involved in a split would be reported
as an ambiguity (between the new area code and the original area
code) -- too many to call by hand, for even the smallest of files!
So we take a gamble: that if there is one split, the area code
should be changed to the new area code.
We believe we are right almost all the time. We got some idea
from a file with 6.4 million numbers in it. (6,497,387
to be exact). When running PhoneSeal Analyzer Professional and
using the Time Traveler "date-per-record" feature (described in
the next paragraph), the number of "need correcting" numbers decreased
by about 14,000 (from 2,036,450 to 2,022,785). That is about 0.2%
of the file. So we believe PhoneSeal guesses wrong about 0.2%
of the time. That means it guesses right 99.8% of the time.
Since a typical file, if it is 5 or so years old, has wrong area
codes for about 30% of the numbers, you can see, it is still clearly
very cost-effective to run PhoneSeal, even with the 0.2%
error rate.
If you're using ZipChecker, which is described below, there is
an option in PhoneSeal called "Use ZIP codes to verify all changes
within the date bracket" which will disable the automatic application
of single splits that was just described. It is also disabled
whenever you're using dates that come from fields in your data
(described next), on the assumption that the dates from your data
narrow down the dates of the phone numbers sufficiently.
PhoneSeal Advanced/Professional has a special feature, which we
call "Time Traveler", for addressing these ambiguities. If your
database has a date in each record, indicating exactly
when the phone number was active, then the ambiguities usually
disappear completely! The only ambiguities that remain are when
area codes were split down the middle of an exchange,
and you can't tell from the exchange code what the new area code
is. We know this is very rare -- affects only certain exchanges
in area codes 612, 914, 516, and 716. Most of the time, PhoneSeal
Advanced/Professional will reduce ambiguities to zero! The catch
is, you must know the date for each record in your database.
If you have dates in your database, but the dates do not
indicate the last day the phone number was known to be working,
then you may be able use the date as a boundary. You can indicate
to PhoneSeal that the date is the "earliest possible" or "latest
possible" date for the phone number. For example, if the date
indicates the date that a store opened, that would make it an
"earliest possible" date. If the date indicates the last time
the record was updated, but the phone number might not have been
updated or checked at that time, then the date indicates the "latest
possible" date.
If you don't know the date for each record in your database, PhoneSeal
Advanced/Professional will allow you to assign two dates for the
entire file or database. The first date is the "most recent all
good date". The second is the "most recent update" date. The first
date is the most recent date that you are certain every
number in the file or database was working. The second date is
the last date that any change was made.
The way PhoneSeal uses these two dates is as follows: If PhoneSeal
finds a split that occured before the first date, it will ignore
it. The number is assumed to have been working on that date, so
PhoneSeal will assume that the split does not apply. If PhoneSeal
finds a split that occured after the second date, it will change
the area code to the new area code, and then check that area code
for splits. If that area code, with the same exchange, splits,
PhoneSeal will change the number again. PhoneSeal will do this
any number of times, applying all splits in time-sequential order.
The number will not be flagged as "ambiguous", but will be changed
to the final area code.
If, on the other hand, PhoneSeal encounters a split that lies
between the dates you entered, then it has to handle it more carefully.
If there is only one split, PhoneSeal will change the area code
to the new code. The reason for this is our estimate, described
above (in the paragraph about the 6.5 million number database),
that such guesses are right almost all the time, all but 0.2%
of the time. If, on the other hand, PhoneSeal encounters two or
more splits, even if the other splits occur outside the two dates
you enter, PhoneSeal will still flag the number as "ambiguous"
and will not correct it. At the current time, the number will
be flagged as "ambiguous" regardless of whether the subsequent
splits outside of the dates you enter are splits off the original
area code or splits off the new area code that the ambiguous split
within the dates you enter split to. This means that PhoneSeal
will err somewhat on the side of reporting more ambiguities than
might be absolutely necessary. We are considering changing this
for subsequent versions of PhoneSeal. Please contact us if you
have an opinion on this issue.
PhoneSeal can substitute a date from a record for either the first
or second date, or both. So the two dates combine to define a
"ambiguity range". This "ambiguity range" may be set for an entire
database, or it may be different for each record in the database.
The
more accurately you know the dates, the better PhoneSeal will
do at correcting your files or databases. Entering dates always
reduces the number of ambiguities reported. If you enter the wrong
dates, PhoneSeal will either report numbers as "ambiguous" which
it could correct, or it will correct numbers that it should report
as "ambiguous". So be sure to enter correct dates so that you
will get a clean correction from PhoneSeal.
Corrector:
Area Code Correction
As
you have undoubtedly figured out so far, PhoneSeal uses the area
code and exchange code to analyze a number and determine what
corrections to apply to it.
This is because area codes are usually split by exchange. In the
example above, if (206) 231-7777 was active before Jan 15, 1995,
it would have changed to (360) 231-7777. This is because all the
numbers in the 231 exchange were reassigned to area code 360 on
Jan 15, 1995. When deciding the split, the telcos and state regulators
will decide which exchanges to leave in the original area code
and which ones to assign to the new one. This is usually based
on where the exchanges are located, geographically. In rare cases
(currently just in area codes 612 and 914), they split on a physical
line (such as a county line), and the exchange code isn't enough
-- some numbers go one way, some go the other. In this case, PhoneSeal
has no choice but to report the number as "ambiguous" (see above).
Unless you are also using Zip Checker (see next section below).
Corrector will only correct an area code on a number if the correction
is not ambiguous. A number can be ambiguous if the area
code is involved in more than one split (which we call "multiple"
splits), or if any splits it is involved in split again later
on (which we call "recursive" splits).
The
rules for determining ambiguity are explained in the section above
on PreCleaner, because identifying ambiguities is PreCleaner's
main job. Corrector will use exactly the same rules, and will
therefore identify the same numbers as ambiguous (and won't correct
them).
PhoneSeal
Advanced/Professional: Time Traveler
Normally
PhoneSeal Corrector just rejects any ambiguous numbers and is
not date-sensitive. But the "Advanced" and "Professional" versions
of PhoneSeal have a special set of features known collectively
as "Time Traveler", and one of these special features is the ability
to use dates to resolve ambiguities.
If the date for a number is known exactly, from a date stored
in the record along with the number, there are no possible ambiguities
(unless you happen to hit one of the 612/914/etc numbers mentioned
above):
-
If there is only 1 split for the given area code/exchange, then
the date determines whether the split applies or not.
-
If there are 2 or more splits for the given area code/exchange
("multiple splits"), then the date determines which of the splits
applies.
-
If the number goes through a split, and then that area code
splits again ("recursion" on splits), with the given exchange
getting the new area code on the second split, then the date
determines whether both splits apply or not. And just in case
any area code is ever split a 3rd time, PhoneSeal can follow
any number of recursive splits.
-
PhoneSeal Advanced/Professional can handle any combinations
of multiple and recursive splits.
On the other hand, if you give PhoneSeal Advanced/Professional
a date for your entire file/database, then PhoneSeal Advanced/Professional
can only perform splits deterministically, as described above,
for splits after the "most recent update" date you give.
Before that date, PhoneSeal Advanced/Professional can't tell.
If PhoneSeal Corrector Advanced/Professional detects ambiguity
in splits prior to the given date, the number will not get corrected.
(And PhoneSeal PreCleaner Advanced/Professional will report the
number as ambiguous).
Splits
that occur prior to the "most recent all good date" are ignored
completely and won't result in corrections or ambiguities.
As explained above, PhoneSeal now allows you to use dates in your
database records as either a "earliest possible" (known good)
date or a "latest possible" (last update) date.
If one or both of the dates come from database records, the dates
may "cross". This means that the second date comes before the
first date. In this case, PhoneSeal behaves as follows:
- If
PhoneSeal reads a last known good date from the file or database
record, and it comes after a last update date that you entered
in the UI, PhoneSeal will use the date read from the record
as both the last known good date and last update date (that
is, it will treat the last known good date read from the file
or database as the phone number's exact date).
- If
PhoneSeal reads a last update date from the file or database
record, and it comes before a last known good date that you
entered in the UI, PhoneSeal will use the date read from the
record as both the last known good date and last update date
(that is, it will treat the last known good date read from the
file or database as the phone number's exact date).
- If
PhoneSeal reads both the last known good date and the last update
date from the file or database and the last update date preceeds
the last known good date, phoneseal will flag it as an error
and will not update the phone number.
Time Traveler's other job is allowing you to pick a target date
for the corrected list. If this date is in the future, PhoneSeal
Advanced/Professional will change the area code for the split,
even if today's date is before the beginning of the permissive
dialing period for the split.
The permissive dialing period is the period of time between when
the new area code can be used for calling a number, and
when it must be used. In other words, there is a period
of time when both the old area code and the new area code will
work, and then at the end of that time, only the new area code
will work.
PhoneSeal uses the two dates differently. PhoneSeal compares the
closing date of the permissive dialing period with the dates that
you enter as "last known good" or "last updated" dates, whether
they come from the UI or from your data. PhoneSeal compares the
beginning date of the permissive dialing period with today's date
(or the target date, if you have chosen a different date), to
decide whether to apply a split.
PhoneSeal
Advanced/Professional: Zip Checker
Zip
Checker is a set of features, part of the Advanced/Professional
suite, which allow PhoneSeal to use ZIP code information. If your
file or database has ZIP codes in it, you can use Zip Checker.
Most files and databases contain both phone numbers and addresses,
which means the ZIP code is available.
For ZIP Checker, in addition to consulting its list of area code
splits, PhoneSeal Advanced/Professional also comes with a list
of ZIP codes and their associated area codes.
Zip Checker can do two things: first, it can assign an area code
when the area code is missing. It will do this if the phone number
has a "000" placeholder where the area code should go, or if the
area code is simply not present (and the field is accordingly
shorter).
If you are using a separate field for the area code, PhoneSeal
puts the area code into that field. (See note at bottom of page
on custom formats for details).
The second thing Zip Checker can do is to use the ZIP code to
resolve ambiguities.
It's important to understand that PhoneSeal Advanced/Professional
usually does not consult the ZIP code field. It only
does it when necessary to assign an area code or to resolve an
ambiguity!
Here is the procedure that PhoneSeal Corrector Advanced/Professional
uses for each number in your file or database:
-
If the area code is missing, look up the ZIP code, and assign
the area code, based on the ZIP code. If there is more than
one area code, no area code is assigned.
-
Look up the new area code(s) in the area code split list --
using procedure described above, including employing all the
Time Traveler features
-
If the area code was looked up by ZIP code, the area code can
be changed again by checking the area code split list. This
is important because the ZIP code list is not as current as
the area code split list. The ZIP list is assumed to be up to
6 months out of date, so only splits for the past 6 months are
checked.
-
If the exchange was not affected by any splits, but the area
code was assigned by a ZIP code lookup, correct the number to
the new area code, count the number as a "corrected number",
and go on to the next phone number.
-
If the exchange was not affected by any splits, and the area
code was not assigned by ZIP code, then leave the number alone,
and go on to the next.
-
If the resulting number is unambiguous, that is, there is exactly
one possible new area code, correct the number to the new area
code, and go on to the next number.
-
If the resulting number is ambiguous, look up the ZIP code.
-
If none of the number(s) indicated by the ZIP code
are one of the possible new area codes, log it as an error,
count the number as "ambiguous", add the new number to the list
of numbers for the user to dial, do not perform any correction,
and go on to the next number.
-
If one of the number(s) indicated by the ZIP code is
one of the possible new area codes, then correct the area code
to the area code indicated by the ZIP code, and go on to the
next phone number.
-
If several of the number(s) indicated by the ZIP code
are possible new area codes, log it as an error, count the number
as "ambiguous", add the new number to the list of numbers for
the user to dial, do not perform any correction, and go on to
the next number.
In this way, PhoneSeal Advanced/Professional's various features
interact to give you the cleanest update possible with the phone
numbers, dates, and ZIP codes that you can make available.
