E-mail
US ADC and Zipcode Sorting
From Aaron Kreider
For a future version, it would be nice to be able to print out labels
in a way that handles the demands for US non-profit mass-mailing. You
have to check if you have 10 or more items with the same zip code, if
not then the same 3-digit zip code prefix, and if not then the same
ADC (Area District Code - includes multiple zip codes, not always in
a logical order).
i suspect many small nonprofits sort this by hand - which can take 5
hours if you only print your labels in zip code order. I've had the
personal "pleasure" of spreading out a magazine in a hundred piles on
the floor and figuring it out!
Outline of general steps to solve this:
Requirements
You need a database of ADCs and zipcodes. I entered this manually
using the PDF file that the post office produces. I'm not sure how
often this information changes (I suspect not that much, but it's
probably still worth keeping it up to date).
Obviously the SQL COUNT() function is your friend for most of these
queries =)
Process for Query
1) Create the Mailing Table (everyone who will get the mailing)
2) Run the "10 or more in the same zip code" query
3) Add these zip code values to a temporary table
4) Use this temporary table to generate mailing labels and then
delete everybody with these zip codes them from the Mailing Table
5) Run the "10 or more in the same 3-digit zip code prefix" query
6) Add these 3-digit prefixes to a temporary table
7) Use this temporary table to generate mailing labels and delete the
rows from the Mailing table that share the 3-digit prefix.
7) Run the "10 or more in the same ADC query"
8) Add to a temporary table
9) Generate the mailing labels from the temporary table and delete
values that have these ADCs from the Mailing Table
10) Print labels for everything remaining in the Mailing Table in
order of zip code (note: the post office doesn't require that
remainders be in zip code order - but since it's so easy to do - it
makes sense).
Complications
Different forms of mail use different ADCs (Eg. zip codes are in
different ADCs based on mail type). Thus my table of ADCs that I
manually entered is only for periodicals. There are slight
differences for other mail types (I think there are 2 or 3 mail
types).
Other Factors
Contributed by Walt Daniels
Unfortunately that is not the only way of saving money on USPS postage.
There are a bunch of discounts that all have different requirements, some
that depend on the weight of the whole mailing, or carrier route encoding
with barcodes, and some that depend on using certified software against a
database that you have to subscribe to (moderately expensive). A complete
USPS mailing program is a nontrivial undertaking.
Facilitating Label Printing (mail merge)
(requirements TBD)
